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:
Post a Comment