WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst num Startup Time Release RAC

TYQXDB 1493581804 tyqxdb1 1 21-12月-13 03:12 11.2.0.3.0 YES

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
TYQXDB	1493581804	tyqxdb1	1	21-12月-13 03:12	11.2.0.3.0	YES

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 8675 18-11月-14 09:00:21 434 2.5

End Snap: 8676 18-11月-14 10:00:04 559 3.1

Elapsed: 59.72 (mins)

DB Time: 923.18 (mins)

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	8675	18-11月-14 09:00:21	434	2.5
End Snap:	8676	18-11月-14 10:00:04	559	3.1
Elapsed:		59.72 (mins)
DB Time:		923.18 (mins)

SQL Summary

SQL Id	Elapsed Time (ms)	Module	Action	SQL Text
6ph89trc2y0um	5,304,922			/* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA...

SQL ID: 6ph89trc2y0um

1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
/* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA...

#	Plan Hash Value	Total Elapsed Time(ms)	Executions	1st Capture Snap ID	Last Capture Snap ID
1	702789096	5,304,911	133	8676	8676
2	0	11	0	8676	8676

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)	5,304,911	39,886.55	9.58
CPU Time (ms)	582,284	4,378.08	10.63
Executions	133
Buffer Gets	20,738,594	155,929.28	8.79
Disk Reads	17,866,638	134,335.62	21.61
Parse Calls	0	0.00	0.00
Rows	1,406	10.57
User I/O Wait Time (ms)	4,688,275
Cluster Wait Time (ms)	52,971
Application Wait Time (ms)	0
Concurrency Wait Time (ms)	0
Invalidations	0
Version Count	43
Sharable Mem(KB)	3,718

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

Execution Plan

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time
0	DELETE STATEMENT				377 (100)
1	DELETE	GETUSERSBYORGROLE_MVIEW
2	HASH JOIN RIGHT SEMI		203	36743	377 (1)	00:00:05
3	TABLE ACCESS FULL	MLOG$_ISC_USER	12	1656	371 (0)	00:00:05
4	MAT_VIEW ACCESS FULL	GETUSERSBYORGROLE_MVIEW	203	8729	5 (0)	00:00:01

dynamic sampling used for this statement (level=2)

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

Plan 2(PHV: 0)

Plan Statistics
Execution Plan

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	% Snap Total
Elapsed Time (ms)	11	0.00
CPU Time (ms)	10	0.00
Executions	0
Buffer Gets	0	0.00
Disk Reads	0	0.00
Parse Calls	7	0.00
Rows	0
User I/O Wait Time (ms)	0
Cluster Wait Time (ms)	0
Application Wait Time (ms)	0
Concurrency Wait Time (ms)	0
Invalidations	0
Version Count	43
Sharable Mem(KB)	67

Back to Plan 2(PHV: 0)
Back to Top

Execution Plan

No data exists for this section of the report.

Back to Plan 2(PHV: 0)
Back to Top

Full SQL Text

SQL Id SQL Text

6ph89trc2y0um /* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA$ WHERE "AROWID" IN (SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "ISC"."MLOG$_ISC_USER" "MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)