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

.

.
630K IO

.

.

.
334s

.

.
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:50:26
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%)
0s (.1%)
p000 Set 1 1

.
52278 (10%)

.
51425 IO (8.2%)

.

.

.
26s (7.7%)

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

.

.
4s (1.3%)

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

.

.

.
6s (1.9%)

.
1.1%
p003 Set 1 4

.
222K (44%)

.
220K IO (34%)

.

.

.
73s (21%)

.
34%
p004 Set 1 5

.
213K (42%)

.
211K IO (33%)

.

.

.
70s (20%)

.
38%
p005 Set 1 6
3351 (.7%)
3168 IO (.5%)

.

.

.
12s (3.5%)

.
2.2%
p006 Set 1 7
832 (.2%)
684 IO (.1%)

.

.

.
11s (3.2%)
p007 Set 1 8

.
4541 (.9%)

.
4289 IO (.7%)

.

.

.
9s (2.7%)

.
3.2%
p008 Set 2 1
12 (<0.1%)

.

.
17112 IO (2.7%)

.

.

.
16s (4.7%)
p009 Set 2 2
14 (<0.1%)

.

.
17112 IO (2.7%)

.

.
16s (4.7%)
p010 Set 2 3
14 (<0.1%)

.

.
17205 IO (2.7%)

.

.

.
15s (4.5%)
p011 Set 2 4
12 (<0.1%)

.

.
16864 IO (2.7%)

.

.

.
16s (4.7%)

.
2.2%
p012 Set 2 5
14 (<0.1%)

.

.
17112 IO (2.7%)

.

.

.
16s (4.8%)

.
1.1%
p013 Set 2 6
12 (<0.1%)

.

.
17267 IO (2.7%)

.

.

.
16s (4.7%)
p014 Set 2 7
12 (<0.1%)

.

.
17050 IO (2.7%)

.

.
14s (4.3%)

.
1.1%
p015 Set 2 8
14 (<0.1%)

.

.
17330 IO (2.7%)

.

.

.
15s (4.4%)


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

.

 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 12376K

.

.
20%

.
> 8 ........ PX RECEIVE

.

 54864K 13552

.

.
8 4225K

.

.

.
5.0%

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

.

.
8 4321K

.

.

.
3.5%

.
4.3%

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

.

 54864K 13552

.

.
8 4321K 63840K 1127M

.
32%

.

151s

.

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

.

 204K 342

.

.

.
8 204K

.

.

.
1.0%

.

.

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

.

.

.
8 204K

.

.

.
.50%

.

.

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

.

 204K 342

.

.

.
8 204K

.

.

.

.

.

.

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

.

.

.
111 204K

.

.

.
6.5%

.

.

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

.

 55371K 13017

.

.

.
8 55320K

.

.

.
5.0%

.

.

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

.

.

.
8 55320K

.

.

.
8.5%

.

.

 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

.

.

.
1.0%

.

.

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

.

.

.
4006K 55320K

.

.

.
22%

.
89%

.