Home » RDBMS Server » Performance Tuning » Some missing concepts and definitions
Some missing concepts and definitions [message #259880] Thu, 16 August 2007 14:08 Go to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

I would ask for 02 doubt:
1) Regarding the EXECUTION PLAN below:
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     30   SORT (ORDER BY)
     30    FILTER
     46     HASH (GROUP BY)
   7104      NESTED LOOPS
   7104       HASH JOIN
     99        TABLE ACCESS   MODE: ANALYZED (FULL) OF 'GRUPPROD' 
                   (TABLE)
   7104        HASH JOIN
   3964         TABLE ACCESS   MODE: ANALYZED (FULL) OF 'PRODUTO' 
                    (TABLE)
   7104         HASH JOIN
   3963          TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ARTIGO' 
                     (TABLE)
   7104          VIEW
   7104           UNION-ALL
   2370            HASH JOIN
  97979             TABLE ACCESS   MODE: ANALYZED (BY INDEX 
                        ROWID) OF 'MOVIMENT' (TABLE)
 100350              NESTED LOOPS
   2370               NESTED LOOPS
   2370                VIEW
   2370                 HASH (GROUP BY)
   2370                  NESTED LOOPS
   2370                   VIEW
   2370                    HASH (GROUP BY)
   3367                     VIEW
   3367                      FILTER
  80134                       HASH (GROUP BY)
12936656                        FILTER
12936656                         HASH JOIN
  80134                          TABLE ACCESS   
What does the first column number mean (rows label)? I mean... for instance... what "12936656" means?

2) Regarding the EXPLAIN PLAN below:
Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT ORDER BY (cr=243696 pr=9232 pw=2975 time=40405671 us)
     30   FILTER  (cr=243696 pr=9232 pw=2975 time=40403408 us)
     46    HASH GROUP BY (cr=243696 pr=9232 pw=2975 time=40403361 us)
   7104     NESTED LOOPS  (cr=243696 pr=9232 pw=2975 time=36919391 us)
   7104      HASH JOIN  (cr=236590 pr=9229 pw=2975 time=36689710 us)
     99       TABLE ACCESS FULL GRUPPROD (cr=3 pr=0 pw=0 time=263 us)
   7104       HASH JOIN  (cr=236587 pr=9229 pw=2975 time=36611463 us)
   3964        TABLE ACCESS FULL PRODUTO (cr=57 pr=56 pw=0 time=25207 us)
   7104        HASH JOIN  (cr=236530 pr=9173 pw=2975 time=36534137 us)
   3963         TABLE ACCESS FULL ARTIGO (cr=32 pr=31 pw=0 time=18274 us)
   7104         VIEW  (cr=236498 pr=9142 pw=2975 time=36460010 us)
   7104          UNION-ALL  (cr=236498 pr=9142 pw=2975 time=36438515 us)
   2370           HASH JOIN  (cr=96779 pr=7750 pw=2520 time=36393262 us)
  97979            TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=86752 pr=6845 pw=1935 time=32974819 us)
 100350             NESTED LOOPS  (cr=21134 pr=6055 pw=1935 time=3395519424 us)
   2370              NESTED LOOPS  (cr=16034 pr=5640 pw=1935 time=26465882 us)
   2370               VIEW  (cr=13662 pr=5626 pw=1935 time=26369706 us)
   2370                HASH GROUP BY (cr=13662 pr=5626 pw=1935 time=26360224 us)
   2370                 NESTED LOOPS  (cr=13662 pr=5626 pw=1935 time=26227431 us)
   2370                  VIEW  (cr=8920 pr=5544 pw=1935 time=26133993 us)
   2370                   HASH GROUP BY (cr=8920 pr=5544 pw=1935 time=26126878 us)
   3367                    VIEW  (cr=8920 pr=5544 pw=1935 time=25819398 us)
   3367                     FILTER  (cr=8920 pr=5544 pw=1935 time=25809292 us)
  80134                      HASH GROUP BY (cr=8920 pr=5544 pw=1935 time=25918383 us)
12936656                       FILTER  (cr=8920 pr=4809 pw=1200 time=80357701 us)
12936656                        HASH JOIN  (cr=8910 pr=4807 pw=1200 time=28602874 us)
  80134                         TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=4453 pr=2004 pw=0 time=5343191 us)
  99457                          INDEX RANGE SCAN XIE1MOVIMENT (cr=265 pr=265 pw=0 time=318737 us)(object id 43797)
      1                           NESTED LOOPS  (cr=5 pr=2 pw=0 time=8048 us)
What does this line mean? (an example)
"97979 TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=86752 pr=6845 pw=1935 time=32974819 us)"
Also... what is "cr", "pr", "pw", "time" mean? Any URL/reference to suggest?
Thank you.

mson77
Re: Some missing concepts and definitions [message #259902 is a reply to message #259880] Thu, 16 August 2007 16:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Expert one-one oracle by thomas kyte / asktom.oracle.com

Regards

Raj
Re: Some missing concepts and definitions [message #259943 is a reply to message #259902] Thu, 16 August 2007 21:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
1) It tells you how many rows were processed by that step of the plan.

2)97979 TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=86752 pr=6845 pw=1935 time=32974819 us)

It means 97970 rows were accessed from the MOVIMENT table using ROWIDs supplied from an index scan elsewhere in the plan.

Ross Leishman
Re: Some missing concepts and definitions [message #259960 is a reply to message #259943] Fri, 17 August 2007 00:29 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Thank you.
I am also reading at http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i19260.
Hard to read and apply immediately.
Need to understand between nested loops join and hash join... first.
Thank you ALL,

mson77
(PS: 99% lost... ANY HELP IS WELCOME!)
Previous Topic: Indexes on LMT
Next Topic: TUNNING
Goto Forum:
  


Current Time: Thu Dec 08 12:28:28 CST 2016

Total time taken to generate the page: 0.25666 seconds