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

.
2395

.

.
0s

.
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:48:58
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

.

.
0s
p000 Set 1 1
p001 Set 1 2

.
16%
p002 Set 1 3

.
16%
p003 Set 1 4

.
16%
p004 Set 1 5

.
16%
p005 Set 1 6

.
16%
p006 Set 1 7
p007 Set 1 8

.
16%
p008 Set 2 1
p009 Set 2 2
p010 Set 2 3
p011 Set 2 4
p012 Set 2 5
p013 Set 2 6
p014 Set 2 7
p015 Set 2 8


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

.

 0 SELECT STATEMENT

.

.

 342K

.

 1

.

.

.

.

.

.

 1 . COUNT STOPKEY

.

.

.

.

 1

.

.

.

.

.

.

 2 .. PX COORDINATOR

.

.

.

.

 1

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

 4 .... COUNT STOPKEY

.

.

.

.

.

.

.

.

.

.

.

 5 ..... VIEW

.

 54864K 342K

.

.

.

.

.

.

.

.

 6 ...... WINDOW BUFFER

.

 54864K 342K

.

.

.

.

.

.

.

.

 7 ....... SORT GROUP BY

.

 54864K 342K

.

.

.

.

.

.

.

.

 8 ........ PX RECEIVE

.

 54864K 13552

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

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

.

 54864K 13552

.

.

.

.

.

.

.

.
62%
> 11 ........... PX RECEIVE

.

 204K 342

.

.

.

.

.

.

.
25%
> 12 ............ PX SEND HASH :TQ10001 204K 342

.

.

.

.

.

.

.
12%

.

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

.

 204K 342

.

.

.

.

.

.

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

.

.

.

.

.

.

.
100%

.

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

.

 55371K 13017

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

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

.

 55371K 13017

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

.

.

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

.

 367 32

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.

.

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

.

 151K 255

.

.

.

.

.

.

.

.

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

.

.

.

.

.

.

.