Friday, June 26, 2009

Nested loop join puzzle - unique scan or range scan?

In a 9.2.0.8 database, I puzzled about a nested loop join execution plan which showed index range scan on the unique index of the inner table. After reading the Chapter 11 "Nested loops" of Jonathan Lewis's execllent book "Cost-Based Oracle Fundamentals", things are clear.

There could be two forms of the nested loop join. The classic form shows unique scan on the index of the inner table, whereas the new form shows range scan. Oracle can switch from one to another depending on the number of rows of the driving table. Below is the execution plan (new form) of a query from the sql_trace/tkprof (object names are modified as usual):


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7     57.61    1585.67     153862     379507          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     57.62    1585.67     153862     379507          0          81

Rows     Row Source Operation
-------  ---------------------------------------------------
81  TABLE ACCESS BY INDEX ROWID SMT
188718   NESTED LOOPS
94359    TABLE ACCESS FULL SIFD
94358    INDEX RANGE SCAN PK_SMT (object id 227634)




The CPU costing model has to be enabled for Oracle to pick up this new form. We can verify it by the following query

SQL> select pname, pval1
2 from sys.aux_stats$
3 where sname = 'SYSSTATS_MAIN';

PNAME PVAL1
------------------------------ ----------
SREADTIM 12.052
MREADTIM 10.325
CPUSPEED 560
MBRC 7
MAXTHR 115623936
SLAVETHR 31744


To test the classic form, I issued the following command to turn off the CPU costing.

SQL> alter session set "_optimizer_cost_model"=io;

The following was obtained from the sql_trace/tkprof for the classic nested loop execution plan:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        7     54.98    2268.90     160543     383987          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9     54.98    2268.90     160543     383987          0          81


Rows     Row Source Operation
-------  ---------------------------------------------------
81  NESTED LOOPS
94178   TABLE ACCESS FULL SIFD
81   TABLE ACCESS BY INDEX ROWID SMT
94177    INDEX UNIQUE SCAN PK_SMT (object id 227634)



The run time of the classic form is about 39 min as compared to 28 min of the new form. Noticed the total IIO is 544,530 vs 533,369. The slight difference of the number of rows of table SIFD seen in the tkprof report should be due to execution at different time (last afternoon and this morning for new form and classic form respectively).

No comments: