WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst num Startup Time Release RAC

DEMO 3430975138 DEMO4 4 30-Jun-09 12:06 11.1.0.7.0 YES

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
DEMO	3430975138	DEMO4	4	30-Jun-09 12:06	11.1.0.7.0	YES

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 1045 02-Jul-09 13:04:52 45 3.0

End Snap: 1046 02-Jul-09 13:06:13 45 3.0

Elapsed: 1.35 (mins)

DB Time: 4.25 (mins)

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	1045	02-Jul-09 13:04:52	45	3.0
End Snap:	1046	02-Jul-09 13:06:13	45	3.0
Elapsed:		1.35 (mins)
DB Time:		4.25 (mins)

SQL Summary

SQL Id	Elapsed Time (ms)	Module	Action	SQL Text
dw7vptwvsn6bw	241,960	SQL*Plus		select /* four / /+ parallel (t1, 16) parallel (t2, 16) */ ...

SQL ID: dw7vptwvsn6bw

1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
select /* four */ /*+ parallel (t1, 16) parallel (t2, 16) */ ...

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	1755470101	241,960	1	1046	1046

Plan Statistics

% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	241,960	241,960.03	94.86
CPU Time (ms)	202,296	202,296.25	97.53
Executions	1
Buffer Gets	1,887,905	1,887,905.00	97.49
Disk Reads	2,017,656	2,017,656.00	98.47
Parse Calls	5	5.00	1.07
Rows	1	1.00
User I/O Wait Time (ms)	38,316
Cluster Wait Time (ms)	81
Application Wait Time (ms)	7
Concurrency Wait Time (ms)	7
Invalidations	0
Version Count	1
Sharable Mem(KB)	30

Back to Plan 1(PHV: 1755470101)
Back to Top

Execution Plan

Id	Operation	Name	Rows	Bytes	TempSpc	Cost (%CPU)	Time	Pstart	Pstop	TQ	IN-OUT	PQ Distrib
0	SELECT STATEMENT					302K(100)
1	SORT AGGREGATE		1	88
2	PX COORDINATOR
3	PX SEND QC (RANDOM)	:TQ10000	1	88						Q1,00	P->S	QC (RAND)
4	SORT AGGREGATE		1	88						Q1,00	PCWP
5	PX PARTITION HASH ALL		470M	38G		302K (1)	01:00:29	1	64	Q1,00	PCWC
6	HASH JOIN		470M	38G	1395M	302K (1)	01:00:29			Q1,00	PCWP
7	PX PARTITION RANGE ALL		468M	16G		73512 (1)	00:14:43	1	31	Q1,00	PCWC
8	TABLE ACCESS STORAGE FULL	DWB_RTL_TRX	468M	16G		73512 (1)	00:14:43	1	1984	Q1,00	PCWP
9	PX PARTITION RANGE ALL		468M	21G		73481 (1)	00:14:42	1	31	Q1,00	PCWC
10	TABLE ACCESS STORAGE FULL	DWB_RTL_TRX	468M	21G		73481 (1)	00:14:42	1	1984	Q1,00	PCWP

Back to Plan 1(PHV: 1755470101)
Back to Top

Full SQL Text

SQL Id SQL Text

dw7vptwvsn6bw select /* four */ /*+ parallel (t1, 16) parallel (t2, 16) */ min (t1.bsns_unit_key + t2.bsns_unit_key), max (t1.day_key + t2.day_key), avg (t1.day_key + t2.day_key), max (t1.bsns_unit_typ_cd), max (t2.curr_ind), max (t1.load_dt) from d31.dwb_rtl_trx t1, d31.dwb_rtl_trx t2 where t1.trx_nbr = t2.trx_nbr