Tracking nested loop joins
Oracle Tips by Burleson Consulting
One of the problems in Oracle9i was the single bit-flag that was
used to monitor index usage. The flag can be set with the alter
index xxx monitoring usage command, and see if the index was
accessed by querying the
The goal of any index access is to use the most selective index for
a query. This would be the one that produces the smallest number of
rows. The Oracle data dictionary is usually quite good at this, but
it is up to the DBA to define the index. Missing function-based
indexes are a common source of suboptimal SQL execution because
Oracle will not use an indexed column unless the WHERE clause
matches the index column exactly.
Tracking SQL nested loop joins
As a review, nested loop joins are the most common method for Oracle
to match rows in multiple tables. Nested loop joins always invoke
an index and they are never parallelized. The following
script to count nested loop joins per hour (See
book errata for details):
Note: These scripts
will only track SQL that you have directed Oracle to capture via your
threshold settings in AWR or STATSPACK, and STATSPACK and AWR will not
collect "transient SQL" that did not appear in v$sql at snapshot time. Hence, not all SQL will
appear in these reports. See my notes here on
adjusting the SQL capture thresholds and
what SQL is
included in AWR/STATSPACK tables?
col c1 heading ‘Date’ format a20
col c2 heading ‘Nested|Loops|Count’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
col c4 heading ‘Disk|Reads’ format 99,999,999
col c5 heading ‘CPU|Time’ format 99,999,999
accept nested_thr char prompt ‘Enter Nested Join Threshold: ‘
ttitle ‘Nested Join Threshold|&nested_thr’
SEE CODE DEPOT FOR FULL SCRIPTS
st.dbid = sn.dbid
st.instance_number = sn.instance_number
sp.sql_id = st.sql_id
sp.dbid = st.dbid
sp.plan_hash_value = st.plan_hash_value
sp.operation = 'NESTED LOOPS'
count(*) > &nested_thr;
The output below shows the number of total nested loop joins during
the snapshot period along with a count of the rows processed and the
associated disk I/O. This report is useful where the DBA wants to
know if increasing
pga_aggregate_target will improve performance.
Nested Loop Join Thresholds
Loops Rows Disk CPU
Date Count Processed Reads Time
-------------------- ----------- ----------- -----------
04-10-10 16 22 750 796 4,017,301
04-10-10 17 25 846 6 3,903,560
04-10-10 19 26 751 1,430 4,165,270
04-10-10 20 24 920 3 3,940,002
04-10-10 21 25 782 5 3,816,152
04-10-11 02 26 905 0 3,935,547
04-10-11 03 22 1,001 0 3,918,891
04-10-11 04 29 757 8 3,939,071
04-10-11 05 28 757 745 4,395,197
04-10-11 06 24 839 4 4,010,775
In the report above, nested loops are favored by SQL that returns a
small number of rows_processed
than hash joins, which tend to return largest result sets.
This is a STATSPACK script that will track nested loops joins over
col c1 heading 'Date'
col c2 heading 'Nested Loops|join|Count'
col c3 heading 'Rows|Processed'
col c4 heading 'Disk|Reads'
col c5 heading 'CPU|Time'
accept hash_thr char prompt 'Enter Nested Loops Join Threshold: '
ttitle 'Nested loops Join Threshold|&hash_thr'
st.sql_id = p.sql_id
p.plan_hash_value = sp.plan_hash_value
sn.dbid = st.dbid
sp.operation like '%NESTED%'
count(*) > &hash_thr
Ion tool is
also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
SEE CODE DEPOT FOR FULL SCRIPTS
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: