Explain Plan

[sid1987]

Can someone please help me understand the numbers from this example in the Oracle 9i Tuning Guide?

For example, in the following explain plan, the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows, throws away 99% of them, and retains 76563 rows. Why access 11432983 rows to realize that only 76563 rows are needed?
Example 9-1 Looking for Throw-Away in an Explain Plan

Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

I think i understand the access path, but the numbers dont make sense to me. The example says the RANGE SCAN is doen 76563 times to get 11432983 rows, but isnt the driver of that NESTED LOOPS the previous NESTED LOOPS which had 76575 rows in its results set?

Very confused

[malay_biswal]

the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows

I guess you got it wrong. The index range scan scanned 11432983 rows rows and the result set of the predicate was 765763. Hope this clarifies.

[sid1987]

Just to prove I must be missing something fundemental, I read the first NESTED LOOPS

   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)

as:

1. FULL SCAN CN_PAYRUNS_ALL returns 19 rows
2. For each row, do the INDEX RANGE SCAN to get rowid's then access table CN_PAYRUNS_ALL and pass any joined rows up to the NESTED LOOPS operation
3. These 19 scans return 76570 rows

But then the NESTED LOOPS reports 76575 rows??

I'm obviously not getting something that's pretty straight forward?!

[malay_biswal]

No it's not pretty straight forward. It's not going to do 19*76570 . These records will be processed now according to the condition and i do not think you can calculate how many number of records returned by nested loop as simple as that.

[sid1987]

the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows

I guess you got it wrong. The index range scan scanned 11432983 rows rows and the result set of the predicate was 765763. Hope this clarifies.

I understand that what the documentation says, but I dont understand why

   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

Dosnt the NESTED LOOPS get its row set by using the NESTED LOOPS as the driver and doing the RANGE SCAN for each of its 76575 rows?

[sid1987]

Can anyone point me in the direction of any articles/examples that help explain this as the numbers still dont add up to me?

TIA

[PAVB]

How about Oracle documentation?

Oracle® Database Performance Tuning Guide
10g Release 1 (10.1)
Part Number B10752-01
19 - Using EXPLAIN PLAN

http://download-uk.oracle.com/docs/c...52/ex_plan.htm

[sid1987]

As I said earlier, I've read the documentation but its not clear to me so I was asking for some other resources that might help clear it up in my mind.

[tamilselvan]

Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

The plan explains in detail how the rows are gotten. You have to read from the inner most joins.

Step 1 -    Join 2 tables 
        19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)

Step 2  
    Output (76575) of Step 1 joined with 
     76563     TABLE ACCESS BY INDEX ROWID    
                       CN_PAYMENT_WORKSHEETS_ALL
 11432983      INDEX RANGE SCAN (object id 186024)

Step 3  Output from Step-2 is 76563 rows
           Sort Group by is done

Step 4 Sort Aggregate is done