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: EXECUTING
Instance ID : 1
Buffer Gets IO Count Database Time Wait Activity

.
501K

.

.
749K IO

.

.

.
1380s

.

.
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:27
Fetch Calls :


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.9%)

.

.

.
99s (7.2%)

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

.

.
54s (3.9%)

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

.

.

.
68s (4.9%)

.

.
1.2%
p003 Set 1 4

.
222K (44%)

.
220K IO (29%)

.

.

.
169s (12%)

.
19%
p004 Set 1 5

.
213K (42%)

.
211K IO (28%)

.

.

.
157s (11%)

.

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

.

.
134s (9.7%)

.

.

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

.

.
133s (9.6%)

.
.60%
p007 Set 1 8

.
4541 (.9%)

.
4289 IO (.6%)

.

.

.
93s (6.7%)

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

.

.
31775 IO (4.2%)

.

.
59s (4.3%)

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

.

.
31775 IO (4.2%)

.

.
60s (4.3%)

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

.

.
32147 IO (4.3%)

.

.
58s (4.2%)

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

.

.
31558 IO (4.2%)

.

.
59s (4.3%)

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

.

.
32086 IO (4.3%)

.

.
60s (4.3%)

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

.

.
31961 IO (4.3%)

.

.
60s (4.3%)

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

.

.
31931 IO (4.3%)

.

.
58s (4.2%)

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

.

.
32272 IO (4.3%)

.

.
59s (4.3%)

.
4.8%


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

.

 0 SELECT STATEMENT

.

.

 342K

.

 1

.

.

.

.

.

.

 1 . COUNT STOPKEY

.

.

.

.

 1

.

.

.

.

.

.

 2 .. PX COORDINATOR

.

.

.

.

 17

.

.

.

.

.

.

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

.

 8

.

.

.

.

.

.

 4 .... COUNT STOPKEY

.

.

.

.

 8

.

.

.

.

.

.

 5 ..... VIEW

.

 54864K 342K

.

 8

.

.

.

.

.

.

 6 ...... WINDOW BUFFER

.

 54864K 342K

.

 8

.

.

.

.

.
> 7 ....... SORT GROUP BY

.

 54864K 342K

.

.
8 0 28633K

.

.
47%
> 8 ........ PX RECEIVE

.

 54864K 13552

.

.
8 52647K

.

.

.
17%

.

.

.
3.6%
> 9 ......... PX SEND HASH :TQ10003 54864K 13552

.

.
8 52648K

.

.

.
13%

.
43%
> 10 .......... HASH JOIN BUFFERED

.

 54864K 13552

.

.
8 52648K 15758K 1160M

.
14%

.

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

.

 204K 342

.

.

.
8 204K

.

.
.21%

.

 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.6%

.

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

.

 55371K 13017

.

.

.
8 55320K

.

.

.
1.0%

.

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

.

.

.
8 55320K

.

.

.
1.8%

.

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

.

 55371K 13017

.

.

.
8 55320K

.

.

.

.

.

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

.

.

.

.

.

.
8 2928

.

.

.

.

.

 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

.

.
.21%

.

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

.

.

.
4006K 55320K

.

.

.
4.7%

.
49%