Re: sql explain plan issue

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Feb 2004 08:49:47 -0000
Message-ID: <004f01c3ec8e$326f6c20$6702a8c0@Primary>

The Oracle 9 plan is showing you an optimisation option known as 'table pre-fetching'. Essentially the plans are the same, but Oracle can make a cost-based decision when using index range scans in nested loops to stop the access path at the index leaf block and accumulate all the rowids from the inner table. These rowids can then be sorted and the inner table accessed in block order.

On the plus side, you may see a reduction in latched gets on table blocks and even a reduction in physical I/O due to the elimination of highly random table accesses. On the minus side, the rowids have to be sorted - which may consume extra CPU and memory. This trade-off can (apparently) be weighed by the v9 CBOptimizer.
(I've yet to see this path actually take place at run time,
the CBO always seems to fall back to the traditional NL mechanism, even though it always seems to report the new mechanism)

The v9 plan is essentially the same as the v8 plan - but the outermost steps can be read as:

The nested loop joins only to the index, and then feeds a sorted set of rowids to the table access operation.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Sai Selvaganesan" <ssaisundar_at_sbcglobal.net> To: <oracle-l_at_freelists.org> Sent: Wednesday, February 04, 2004 7:13 PM Subject: sql explain plan issue

hi
the below is a sql code and explain plan on two environments the first one is 81.7.3 and the next one is 9i. can someone please point out why there is a diff

SELECT SQ1_T2.ORG_ID
FROM S_PARTY SQ1_T1, S_ORG_BU SQ1_T2, S_ORG_EXT SQ1_T3 WHERE
(SQ1_T3.INT_ORG_FLG = 'Y') AND
(SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID AND SQ1_T2.BU_ID = SQ1_T1.ROW_ID) AND
(SQ1_T3.PAR_BU_ID = '0-R9NH')
Execution Plan

0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' 4 3 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE) 5 2 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU' 7 6 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE) Execution Plan

0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU' 2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'

5 4 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE)
6 3 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
7 2 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE)

the objects are the same but the plan is different. this is a third party software, hence rule based.

thanks
sai

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 06 2004 - 02:49:47 CST