Explain Plan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Explain Plan

  1. #1
    Join Date
    Jan 2008
    Posts
    10

    Explain Plan

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

    Code:
    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

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    HTML Code:
    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.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Jan 2008
    Posts
    10

    Also . . . .

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

    Code:
       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?!

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Jan 2008
    Posts
    10
    Quote Originally Posted by malay_biswal
    HTML Code:
    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

    Code:
       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?

  6. #6
    Join Date
    Jan 2008
    Posts
    10

    Resources

    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

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jan 2008
    Posts
    10
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    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.
    Code:
    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
    Last edited by tamilselvan; 01-22-2008 at 03:00 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width