SQL Monitoring Report


SQL Text

SELECT /*+ monitor */ p.category_id, sum(i.quantity) AS quantity_sold, sum(i.unit_price * i.quantity) AS amount_sold FROM orders o, order_items i, products p WHERE o.order_id = i.order_id AND i.product_id = p.product_id AND o.order_status BETWEEN 5 and 9 GROUP BY p.category_id ORDER BY p.category_id

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

.

206K

.

.

10767

.

.

35s

.

.

100%
Session : SOE (167:13851)
SQL ID : 5kwfj03dc3dp1
SQL Execution ID : 16777216
Execution Started : 02/14/2014 10:59:17
First Refresh Time : 02/14/2014 10:59:17
Last Refresh Time : 02/14/2014 10:59:53
Duration : 36s
Module/Action : SQL*Plus/-
Service : DBM11203.antognini.ch
Program : sqlplus.exe
Fetch Calls : 101


SQL Plan Monitoring Details (Plan Hash Value=463527652)
Id Operation Name Estimated
Rows
Cost Active Period
(36s)
Execs Rows Memory
(Max)
Temp
(Max)
IO Requests CPU Activity Wait Activity

.

0 SELECT STATEMENT

.

.

.

.

.

1 199

.

.

.

.

.

1 . SORT GROUP BY

.

199 109K

.

.

1 199 20.0KB

.

.

.

.

2 .. NESTED LOOPS OUTER

.

1000 109K

.

.

.

1 1000

.

.

.

.

.

3 ... HASH JOIN

.

1000 109K

.

.

.

1 1000 1.2MB

.

.

.

.

4 .... TABLE ACCESS FULL PRODUCT_INFORMATION 1000 9

.

.

.

1 1000

.

.

.

.

.

5 .... VIEW VW_GBC_9 1000 109K

.

.

.

1 1000

.

.

.

.

.

6 ..... HASH GROUP BY

.

1000 109K

.

.

.

1 1000 2.6MB

.

.

26%

.

7 ...... HASH JOIN

.

18M 108K

.

.

.

1 18M 14.0MB 942.0MB

.

.

7534 (69%)

.

57%

.

11%

.

8 ....... TABLE ACCESS FULL ORDERS 6M 23642

.

.

.

1 6M

.

.

.

1357 (12%)

.

11%

.

22%

.

9 ....... TABLE ACCESS FULL ORDER_ITEMS 32M 34238

.

.

.

1 32M

.

.

.

1876 (17%)

.

3.8%

.

66%

.

10 ... INDEX UNIQUE SCAN PRD_DESC_PK 1

.

.

1000

.

.

.

.

.