Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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 1000+ 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 Chris 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!)

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.