| Some missing concepts and definitions [message #259880] |
Thu, 16 August 2007 14:08  |
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
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)
"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 #259943 is a reply to message #259902] |
Thu, 16 August 2007 21:57  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
