SQL Monitoring Report


SQL Text

select * from ( select i_item_desc ,i_category ,sum(ss_ext_sales_price) as itemrevenue ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio from store_sales ,item ,date_dim where ss_item_sk = i_item_sk and ss_sold_date_sk = d_date_sk and d_date between to_date('1999-01-01','YYYY-MM-DD') and to_date('2000-01-01','YYYY-MM-DD') group by i_item_id ,i_class ,i_item_desc ,i_category ) where rownum <= 1000

Global Information: DONE (ALL ROWS)
Instance ID : 1
Buffer Gets IO Count Database Time Wait Activity

.
501K

.

.
755K IO

.

.

.
1439s

.

.
100%
Session ID : 89
SQL ID : d3cbkh5wptrt0
SQL Execution ID : 16777217
Plan Hash Value : 4268948700
Execution Started : 01/04/2008 18:48:54
First Refresh Time : 01/04/2008 18:48:56
Last Refresh Time : 01/04/2008 18:54:54
Fetch Calls : 68


Parallel Execution Details (DOP=8)

PX Processes Drill-Down
Name Type Server# Buffer Gets IO Count Database Time Wait Events
PX Coordinator QC
2395 (.5%)
1s (<0.1%)
p000 Set 1 1

.
52278 (10%)

.
51425 IO (6.8%)

.

.

.
104s (7.2%)

.
8.1%
p001 Set 1 2
301 (<0.1%)
126 IO (<0.1%)

.

.
57s (3.9%)

.
.58%
p002 Set 1 3
2901 (.6%)
2618 IO (.3%)

.

.

.
71s (4.9%)

.

.
1.2%
p003 Set 1 4

.
222K (44%)

.
220K IO (29%)

.

.

.
175s (12%)

.
18%
p004 Set 1 5

.
213K (42%)

.
211K IO (27%)

.

.

.
162s (11%)

.

.
21%
p005 Set 1 6
3351 (.7%)
3168 IO (.4%)

.

.
141s (9.8%)

.

.

.
2.3%
p006 Set 1 7
832 (.2%)
684 IO (<0.1%)

.

.
140s (9.7%)

.
.58%
p007 Set 1 8

.
4541 (.9%)

.
4289 IO (.6%)

.

.

.
98s (6.8%)

.
1.7%
p008 Set 2 1
20 (<0.1%)

.

.
32550 IO (4.3%)

.

.
61s (4.3%)

.
4.6%
p009 Set 2 2
20 (<0.1%)

.

.
32612 IO (4.3%)

.

.
62s (4.3%)

.
4.0%
p010 Set 2 3
22 (<0.1%)

.

.
32984 IO (4.4%)

.

.
61s (4.2%)

.
5.2%
p011 Set 2 4
20 (<0.1%)

.

.
32023 IO (4.2%)

.

.
61s (4.2%)

.
5.8%
p012 Set 2 5
18 (<0.1%)

.

.
32613 IO (4.3%)

.

.
62s (4.3%)

.
8.7%
p013 Set 2 6
20 (<0.1%)

.

.
32829 IO (4.4%)

.

.
63s (4.4%)

.
5.2%
p014 Set 2 7
16 (<0.1%)

.

.
32582 IO (4.3%)

.

.
60s (4.2%)

.
6.4%
p015 Set 2 8
22 (<0.1%)

.

.
33171 IO (4.4%)

.

.
62s (4.3%)

.
5.2%


SQL Plan Monitoring Details:
Id Operation Name Estimated
Rows 		Cost Active Period
(361s) 		Starts Actual
Rows 		Memory
(Max) 		Temp
(Max) 		CPU Activity Wait Activity

.

 0 SELECT STATEMENT

.

.

 342K

.

.
1 1000

.

.

.

.

.

 1 . COUNT STOPKEY

.

.

.

.

.
1 1000

.

.

.

.

.

 2 .. PX COORDINATOR

.

.

.

.

.
17 1000

.

.

.

.

.

 3 ... PX SEND QC (RANDOM) :TQ10004 54864K 342K

.

.
8 1167

.

.

.

.

.

 4 .... COUNT STOPKEY

.

.

.

.

.
8 1167

.

.

.

.

.

 5 ..... VIEW

.

 54864K 342K

.

.
8 1167

.

.

.

.

.

 6 ...... WINDOW BUFFER

.

 54864K 342K

.

.
8 1167 25407K

.

.

.

.

 7 ....... SORT GROUP BY

.

 54864K 342K

.

.

.
8 102K 28827K

.

.
47%

.

 8 ........ PX RECEIVE

.

 54864K 13552

.

.

.
8 55320K

.

.

.
17%

.

.

.
3.5%

.

 9 ......... PX SEND HASH :TQ10003 54864K 13552

.

.

.
8 55320K

.

.

.
13%

.
45%

.

 10 .......... HASH JOIN BUFFERED

.

 54864K 13552

.

.

.
8 55320K 64162K 1160M

.
14%

.

 11 ........... PX RECEIVE

.

 204K 342

.

.

.
8 204K

.

.
.20%

.

 12 ............ PX SEND HASH :TQ10001 204K 342

.

.

.
8 204K

.

.
.10%

.

 13 ............. PX BLOCK ITERATOR

.

 204K 342

.

.

.
8 204K

.

.

.

.

.

 14 .............. TABLE ACCESS FULL ITEM 204K 342

.

.

.
111 204K

.

.

.
3.5%

.

 15 ........... PX RECEIVE

.

 55371K 13017

.

.

.
8 55320K

.

.

.
.99%

.

 16 ............ PX SEND HASH :TQ10002 55371K 13017

.

.

.
8 55320K

.

.

.
1.7%

.

 17 ............. NESTED LOOPS

.

 55371K 13017

.

.

.
8 55320K

.

.

.

.

.

 18 .............. BUFFER SORT

.

.

.

.

.

.
8 2928 57344

.

.

.

.

 19 ............... PX RECEIVE

.

.

.

.

.

.
8 2928

.

.

.

.

.

 20 ................ PX SEND BROADCAST :TQ10000

.

.

.

.

.
8 2928

.

.

.

.

.

 21 ................. PX BLOCK ITERATOR

.

 367 32

.

.

.
8 366

.

.

.

.

.

 22 .................. TABLE ACCESS FULL DATE_DIM 367 32

.

.

.
99 366

.

.

.

.

.

 23 .............. PX BLOCK ITERATOR

.

 151K 255

.

.

.
2928 55320K

.

.
.20%

.

 24 ............... TABLE ACCESS FULL STORE_SALES 151K 255

.

.

.
4006K 55320K

.

.

.
4.5%

.
47%