Database Reference
In-Depth Information
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL | T1 |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | T2 |
|* 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL| T3 |
| 7 | TABLE ACCESS FULL| T4 |
-------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."N"=19)
3 - access("T2"."ID"="T3"."T2_ID")
5 - access("T3"."ID"="T4"."T3_ID")
One of the differences between the two execution plans (the left-deep tree and the right-deep tree) is the number
of active work areas (hash tables) that are being used at a given time. With a left-deep tree, at most two work areas are
used at the same time. In addition, when the last table is processed, only a single work area is needed. On the other
hand, in a right-deep tree, during almost the entire execution a number of work areas (that is equal to the number of
joins) are allocated and probed. Another difference between the two execution plans is the size of their work areas.
Whereas the right-deep tree work areas contain data from a single table, the left-deep tree work areas can contain
data resulting from the join of several tables. Therefore, the size of the left-deep tree work areas varies depending on
whether the joins restrict the amount of data that's returned.
The
v$sql_workarea_active
dynamic performance view provides information about the active work areas.
The following query shows the work areas used by one session that is currently executing the previous execution plan.
Although the
operation_id
column is used to relate the work areas to an operation in the execution plan, the
actual_mem_used
column shows the size (in bytes), and the
tempseg_size
columns and tablespace give information
about the utilization of temporary space:
SQL> SELECT operation_id, operation_type, actual_mem_used, tempseg_size, tablespace
2 FROM v$session s, v$sql_workarea_active w
3 WHERE s.sid = w.sid
4 AND s.sid = 24
5 ORDER BY operation_id;
OPERATION_ID OPERATION_TYPE ACTUAL_MEM_USED TEMPSEG_SIZE TABLESPACE
------------ -------------- --------------- ------------ ----------
1 HASH-JOIN 79872
3 HASH-JOIN 161792
5 HASH-JOIN 185344 1048576 TEMP
Work Areas
To process a hash join, a work area in memory is used to store the hash table. If the work area is large enough to store
the whole hash table, the hash join is fully processed in memory. If the work area isn't large enough, data is spilled into
a temporary segment. (I explain how to recognize whether a join is fully executed in memory earlier in the chapter.)
