Saturday, June 06, 2009

Different explain-time and run-time execution plans due to parallel degree setting of a table

I have recently faced a query that has different explain-time and run-time execution plans due to parallel degree setting of a table.

The query involvs joining three tables, SEED, SSD and SOD. The execution plan shown by AUTOTRACE is as follows:


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=1 Bytes=124)
1 0 SORT (UNIQUE) (Cost=21 Card=1 Bytes=124)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SEED' (Cost=12 Card=1 Bytes=38)
3 2 NESTED LOOPS (Cost=20 Card=1 Bytes=124)
4 3 NESTED LOOPS (Cost=9 Card=1 Bytes=86)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'SOD' (Cost=6 Card=1 Bytes=54)
6 5 INDEX (RANGE SCAN) OF 'RECV_DATE' (NON-UNIQUE) (Cost=4 Card=3)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'SSD' (Cost=4 Card=1 Bytes=32)
8 7 INDEX (RANGE SCAN) OF 'SSD_UQ1' (UNIQUE) (Cost=3 Card=1)
9 3 INDEX (RANGE SCAN) OF 'SEED_IDX11' (NON-UNIQUE) (Cost=4 Card=3)




 

It looks like a simple plan and the optimzer statistics is up-to-date. However, when run in a 9i database, it runs 2 hours without return before being killed.

When I checked the run-time execution plan, I found it used parallel execution (truncated some middle columns):



--------------------------------------------------------------~~~------------------------------
Id Operation Name Rows ~~~ TQ IN-OUT PQ Distrib
--------------------------------------------------------------~~~-----------------------------
0 SELECT STATEMENT ~~~
1 SORT UNIQUE 33174 ~~~
2 FILTER ~~~
3 HASH JOIN 33174 ~~~ 72,02 P->S QC (RAND)
4 TABLE ACCESS BY INDEX ROWID SSD 1 ~~~ 72,02 PCWP
5 NESTED LOOPS 31793 ~~~ 72,02 PCWP
6 TABLE ACCESS BY INDEX ROWID SOD 33786 ~~~ 72,02 PCWP
7 INDEX RANGE SCAN RECV_DATE 266K ~~~ 72,02 PCWP
8 INDEX RANGE SCAN SSD_UQ1 1 ~~~ 72,02 PCWP
9 TABLE ACCESS FULL SEED 2566K ~~~ 72,01 P->P HASH
--------------------------------------------------------------~~~------------------------------





From the dba_tables, I can see the "DEGREE" of the table SEED is 'DEFAULT'. This setting is questionable.

Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition.Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level. Check this to know hwo Oracle determines the default degree of a table.

I tested with hint: no_parallel(SEED), not helpful, Oracle still uses parallel execution plan. When using parallel(SEED, 1), the query returns in less than a second.

No comments: