Hello,
I did some research on the internet before posting this but I could find sufficient information.
Is "A-Time" column in dbms_xplan.display_cursor summarized ?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 155 |00:06:36.28 | 4957K| 34952 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 155 |00:06:36.28 | 4957K| 34952 | 55296 | 55296 |49152 (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.04 | 4957K| 34952 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.04 | 4957K| 34952 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.04 | 4957K| 34952 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.04 | 4956K| 34952 | | | |
| 6 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.04 | 4956K| 34952 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4956K| 34952 | | | |
| 8 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4956K| 34952 | | | |
| 9 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4956K| 34952 | | | |
| 10 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4955K| 34952 | | | |
| 11 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4955K| 34952 | | | |
| 12 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4955K| 34952 | | | |
| 13 | NESTED LOOPS | | 1 | 1 | 155 |00:06:30.03 | 4954K| 34952 | | | |
|* 14 | HASH JOIN | | 1 | 1 | 155 |00:06:30.03 | 4954K| 34952 | 872K| 872K| 927K (0)|
| 15 | VIEW | | 1 | 15 | 8 |00:06:28.63 | 1305K| 34883 | | | |
|* 16 | FILTER | | 1 | | 8 |00:06:28.63 | 1305K| 34883 | | | |
| 17 | HASH GROUP BY | | 1 | 15 | 8 |00:06:28.63 | 1305K| 34883 | 760K| 760K| 1077K (0)|
| 18 | VIEW | | 1 | 15 | 341 |00:00:50.44 | 1305K| 34883 | | | |
| 19 | UNION-ALL | | 1 | | 341 |00:00:50.44 | 1305K| 34883 | | | |
| 20 | VIEW | V_POSNR_2011000 | 1 | 7 | 303 |00:00:50.44 | 645K| 31282 | | | |
| 21 | UNION-ALL | | 1 | | 303 |00:00:50.44 | 645K| 31282 | | | |
| 22 | VIEW | V_POSNR_0200011 | 1 | 2 | 20 |00:00:50.42 | 429K| 31244 | | | |
| 23 | UNION-ALL | | 1 | | 20 |00:00:50.42 | 429K| 31244 | | | |
| 24 | NESTED LOOPS | | 1 | 1 | 20 |00:00:50.42 | 376K| 28979 | | | |
|* 25 | HASH JOIN | | 1 | 1 | 20 |00:00:50.42 | 376K| 28979 | 1096K| 1096K| 1348K (0)|
|* 26 | TABLE ACCESS BY INDEX ROWID | PROPERTIES | 1 | 6 | 2651 |00:00:00.02 | 2131 | 0 | | | |
|* 27 | INDEX RANGE SCAN | P_SETAALDATE_IDX | 1 | 6 | 2651 |00:00:00.01 | 21 | 0 | | | |
| 28 | VIEW | VW_JF_SET$7992605D | 1 | 2 | 504 |00:02:30.85 | 374K| 28979 | | | |
| 29 | UNION-ALL | | 1 | | 504 |00:02:30.85 | 374K| 28979 | | | |
( hope this execution plan is reasonably readable )
I've been thinking the A-Time Columns shows the time of the particular Operation ( summarizing all time from child operations )
but this seems different:
|* 25 | HASH JOIN | | 1 | 1 | 20 |00:00:50.42 | 376K| 28979 | 1096K| 1096K| 1348K (0)|
|* 26 | TABLE ACCESS BY INDEX ROWID | PROPERTIES | 1 | 6 | 2651 |00:00:00.02 | 2131 | 0 | | | |
|* 27 | INDEX RANGE SCAN | P_SETAALDATE_IDX | 1 | 6 | 2651 |00:00:00.01 | 21 | 0 | | | |
| 28 | VIEW | VW_JF_SET$7992605D | 1 | 2 | 504 |00:02:30.85 | 374K| 28979 | | | |
The line 25 is a HASH JOIN comprising a (1) tableTABLE ACCESS BY INDEX ROWID and (2) the result from a VIEW
The Timing of the HASH JOIN (line 25) is 00:00:50.42 but the timing for the VIEW is 00:02:30.85 which would both have to complete before hashing can occur
So I was thinking the HASH JOIN would (at least) have a timing of the 00:02:30.85 from the VIEW plus the 00:00:00.02 from the TABLE ACCESS BY INDEX ROWID.
But it seems this is a misconception , can someone shed some light on this ?
best regards
Edited by: x45r32 on Apr 4, 2012 8:25 AM
Edited by: x45r32 on Apr 4, 2012 8:46 AM