Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mikhail.

Asked: January 20, 2021 - 9:28 am UTC

Last updated: January 21, 2021 - 3:07 pm UTC

Version: 12.1.0.2

Viewed 100+ times

You Asked

Hello Chris, Hello Connor.

I know that hash join performance might suffer a lot if it has to store the hash table on a disk, but I have no idea how to check if that's happening and that is the reason a query is slow.

Admittedly I do not know much about memory usage in Oracle and how to track it.

Could you please, point me toward some articles on the topic or data dictionaries, that would help?

Unfortunately I have neither a dba access nor an access to trace files.

and we said...

If you get the execution plan for a query including the IOSTATS and MEMSTATS, you'll see non-zero disk reads/writes and Used-Tmp values.

You can also look in v$sql_workarea. This will have non-zero values for onepass_executions or multipasses_executions if the operation needed disk to process:

alter session set workarea_size_policy = manual;
alter session set sort_area_size = 5000;

set serveroutput off
alter session set statistics_level = all;

set feed only
select /*+ use_hash ( o oi )*/ * 
from   co.orders o
join   co.order_items oi
using  ( order_id );
set feed on

select *  
from   dbms_xplan.display_cursor ( format => 'ALLSTATS LAST') ;

---------------------------------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation          | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|    
---------------------------------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |             |      1 |        |   3914 |00:00:00.08 |      37 |    114 |     29 |       |       |          |         |    
|*  1 |  HASH JOIN         |             |      1 |   3914 |   3914 |00:00:00.08 |      37 |    114 |     29 |  1185K|  1185K|  311K (6)|    1024K|    
|   2 |   TABLE ACCESS FULL| ORDERS      |      1 |   1950 |   1950 |00:00:00.01 |      15 |      0 |      0 |       |       |          |         |    
|   3 |   TABLE ACCESS FULL| ORDER_ITEMS |      1 |   3914 |   3914 |00:00:00.01 |      22 |      0 |      0 |       |       |          |         |    
---------------------------------------------------------------------------------------------------------------------------------------------------    
                                                                                                                                                       
Predicate Information (identified by operation id):                                                                                                    
---------------------------------------------------                                                                                                    
                                                                                                                                                       
   1 - access("O"."ORDER_ID"="OI"."ORDER_ID")

select operation_type, 
       optimal_executions, onepass_executions, multipasses_executions 
from   v$sql_workarea
where  sql_id = 'fcrs9ngfh0adu';

OPERATION_TYPE   OPTIMAL_EXECUTIONS   ONEPASS_EXECUTIONS   MULTIPASSES_EXECUTIONS   
HASH-JOIN                         0                    0                        1 


(Setting workarea_size_policy & sort_area_size is to show this effect on a small data set; avoid doing this on production systems!)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.