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.
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!)