WORKLOAD REPOSITORY report for

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
VIS	1412883651	VIS	1	10.2.0.3.0	NO	r12db.sanmateo.ibm.com

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	151	14-Aug-09 14:29:26	90	29.0
End Snap:	152	14-Aug-09 14:35:41	90	29.4
Elapsed:		6.26 (mins)
DB Time:		16.36 (mins)

Report Summary

Cache Sizes

	Begin	End
Buffer Cache:	8,160M	8,160M	Std Block Size:	8K
Shared Pool Size:	2,000M	2,000M	Log Buffer:	30,724K

Load Profile

	Per Second	Per Transaction
Redo size:	1,866,039.17	46,752.34
Logical reads:	379,176.54	9,500.01
Block changes:	13,335.99	334.12
Physical reads:	0.19	0.00
Physical writes:	72.54	1.82
User calls:	2,542.38	63.70
Parses:	2,006.69	50.28
Hard parses:	0.75	0.02
Sorts:	1,883.99	47.20
Logons:	0.24	0.01
Executes:	24,188.18	606.02
Transactions:	39.91

% Blocks changed per Read:	3.52	Recursive Call %:	95.17
Rollback per transaction %:	0.80	Rows per Sort:	2.02

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	99.96	Redo NoWait %:	100.00
Buffer Hit %:	100.00	In-memory Sort %:	100.00
Library Hit %:	100.14	Soft Parse %:	99.96
Execute to Parse %:	91.70	Latch Hit %:	96.28
Parse CPU to Parse Elapsd %:	63.33	% Non-Parse CPU:	99.23

Shared Pool Statistics

	Begin	End
Memory Usage %:	48.25	49.04
% SQL with executions>1:	81.85	92.66
% Memory for SQL w/exec>1:	74.96	89.50

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
CPU time		935		95.2
log file parallel write	8,377	23	3	2.3	System I/O
log file sync	6,748	18	3	1.8	Commit
enq: UL - contention	320	18	55	1.8	Application
cursor: pin S wait on X	324	3	9	.3	Concurrency

Main Report

• Report Summary
• Wait Events Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Buffer Pool Statistics
• Advisory Statistics
• Wait Statistics
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Streams Statistics
• Resource Limit Statistics
• init.ora Parameters

Back to Top

Wait Events Statistics

• Time Model Statistics
• Wait Class
• Wait Events
• Background Wait Events
• Operating System Statistics
• Service Statistics
• Service Wait Class Stats

Back to Top

Time Model Statistics

• Total time in database user-calls (DB Time): 981.3s
• Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name

Statistic Name	Time (s)	% of DB Time
sql execute elapsed time	950.19	96.83
DB CPU	934.56	95.23
PL/SQL execution elapsed time	303.24	30.90
parse time elapsed	13.63	1.39
hard parse elapsed time	1.31	0.13
repeated bind elapsed time	0.51	0.05
inbound PL/SQL rpc elapsed time	0.42	0.04
connection management call elapsed time	0.18	0.02
PL/SQL compilation elapsed time	0.17	0.02
sequence load elapsed time	0.10	0.01
hard parse (bind mismatch) elapsed time	0.02	0.00
hard parse (sharing criteria) elapsed time	0.02	0.00
DB time	981.33
background elapsed time	31.47
background cpu time	4.00

Back to Wait Events Statistics
Back to Top

Wait Class

• s - second
• cs - centisecond - 100th of a second
• ms - millisecond - 1000th of a second
• us - microsecond - 1000000th of a second
• ordered by wait time desc, waits desc

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn
System I/O	10,242	0.00	24	2	0.68
Commit	6,748	0.00	18	3	0.45
Application	460	0.22	18	39	0.03
Concurrency	110,394	0.26	5	0	7.37
Network	926,924	0.00	1	0	61.85
Other	4,341	87.54	0	0	0.29
User I/O	320	0.00	0	1	0.02
Configuration	290	0.00	0	0	0.02

Back to Wait Events Statistics
Back to Top

Wait Events

• s - second
• cs - centisecond - 100th of a second
• ms - millisecond - 1000th of a second
• us - microsecond - 1000000th of a second
• ordered by wait time desc, waits desc (idle events last)

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn
log file parallel write	8,377	0.00	23	3	0.56
log file sync	6,748	0.00	18	3	0.45
enq: UL - contention	320	0.31	18	55	0.02
cursor: pin S wait on X	324	89.81	3	9	0.02
control file parallel write	177	0.00	1	6	0.01
buffer busy waits	58,129	0.00	1	0	3.88
SQL*Net message to client	894,749	0.00	0	0	59.71
latch: cache buffers chains	47,445	0.00	0	0	3.17
enq: TX - index contention	2,190	0.00	0	0	0.15
db file sequential read	313	0.00	0	1	0.02
wait list latch free	17	0.00	0	11	0.00
SQL*Net more data from client	29,237	0.00	0	0	1.95
enq: TX - row lock contention	74	0.00	0	1	0.00
latch: In memory undo latch	2,226	0.00	0	0	0.15
kksfbc child completion	1	100.00	0	49	0.00
SQL*Net more data to client	2,938	0.00	0	0	0.20
enq: SQ - contention	288	0.00	0	0	0.02
buffer deadlock	3,856	98.52	0	0	0.26
control file sequential read	1,688	0.00	0	0	0.11
SQL*Net break/reset to client	66	0.00	0	0	0.00
rdbms ipc reply	54	0.00	0	0	0.00
latch: undo global data	110	0.00	0	0	0.01
latch free	96	0.00	0	0	0.01
latch: library cache	54	0.00	0	0	0.00
LGWR wait for redo copy	113	0.00	0	0	0.01
latch: cache buffer handles	17	0.00	0	0	0.00
latch: library cache lock	16	0.00	0	0	0.00
latch: library cache pin	8	0.00	0	0	0.00
latch: enqueue hash chains	4	0.00	0	0	0.00
cursor: pin S	69	0.00	0	0	0.00
enq: HW - contention	1	0.00	0	0	0.00
enq: TX - allocate ITL entry	1	0.00	0	0	0.00
latch: row cache objects	2	0.00	0	0	0.00
latch: cache buffers lru chain	1	0.00	0	0	0.00
latch: session allocation	2	0.00	0	0	0.00
direct path write	7	0.00	0	0	0.00
latch: object queue header operation	1	0.00	0	0	0.00
SQL*Net message from client	894,744	0.00	13,031	15	59.71
PX Idle Wait	2,179	100.83	4,253	1952	0.15
Streams AQ: waiting for messages in the queue	205	95.61	1,460	7122	0.01
pipe get	263	97.72	1,086	4129	0.02
Streams AQ: qmn slave idle wait	180	0.00	719	3996	0.01
PL/SQL lock timer	94	100.00	365	3885	0.01
Streams AQ: qmn coordinator idle wait	168	35.71	360	2140	0.01

Back to Wait Events Statistics
Back to Top

Background Wait Events

• ordered by wait time desc, waits desc (idle events last)

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn
log file parallel write	8,377	0.00	23	3	0.56
control file parallel write	177	0.00	1	6	0.01
events in waitclass Other	170	0.00	0	0	0.01
control file sequential read	52	0.00	0	0	0.00
latch: cache buffers chains	2	0.00	0	0	0.00
rdbms ipc message	24,001	5.36	3,262	136	1.60
Streams AQ: qmn slave idle wait	180	0.00	719	3996	0.01
pmon timer	152	100.00	366	2409	0.01
Streams AQ: qmn coordinator idle wait	168	35.71	360	2140	0.01
smon timer	172	0.00	354	2057	0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic	Total
BUSY_TIME	110,611
IDLE_TIME	490,259
IOWAIT_TIME	2,991
NICE_TIME	0
SYS_TIME	9,710
USER_TIME	99,785
LOAD	2
RSRC_MGR_CPU_WAIT_TIME	0
PHYSICAL_MEMORY_BYTES	4,304,536
NUM_CPUS	16
NUM_CPU_SOCKETS	2

Back to Wait Events Statistics
Back to Top

Service Statistics

• ordered by DB Time

Service Name	DB Time (s)	DB CPU (s)	Physical Reads	Logical Reads
VIS	980.40	933.70	12	142,339,599
SYS$USERS	0.90	0.90	43	6,301
SYS$BACKGROUND	0.00	0.00	0	21,240

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in centisecond (100th of a second)

Service Name	User I/O Total Wts	User I/O Wt Time	Concurcy Total Wts	Concurcy Wt Time	Admin Total Wts	Admin Wt Time	Network Total Wts	Network Wt Time
VIS	12	5	110392	451	0	0	926654	65
SYS$USERS	307	13	0	0	0	0	5	0
SYS$BACKGROUND	0	0	2	0	0	0	0	0

Back to Wait Events Statistics
Back to Top

SQL Statistics

• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• Complete List of SQL Text

Back to Top

SQL ordered by Elapsed Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
652	650	9,990	0.07	66.44	4f5mjr4hnaz3u	PYUSLV	declare begin pay_archive...
649	648	9,979	0.07	66.16	5sfm6rppc4y8x	PYUSLV	BEGIN pay_us_action_arch.actio...
69	68	9,534	0.01	6.99	9jv68n0ctas75	PYUSLV	begin pay_core_utils . get_pro...
51	50	279,828	0.00	5.15	3n5uwzsh3y8gr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
33	19	50	0.67	3.40	dab1prc9qyv3v	PYUSLV	begin hr_nonrun_asact...
33	33	9,948	0.00	3.38	80wvw6gatjvb2	PYUSLV	SELECT PTP_TD.START_DATE FROM ...
32	32	59,901	0.00	3.31	01pkua627ktjk	PYUSLV	SELECT fnd_number.number_to_ca...
30	30	10,000	0.00	3.06	4ds53shhfcynn	PYUSLV	declare begin hr_pre_pay....
24	24	149,876	0.00	2.47	g3835fnqtvy06	PYUSLV	SELECT NVL(SUM(prb.balance_val...
21	21	169,915	0.00	2.18	6500rnmy4f88w	PYUSLV	SELECT PAP.ACCRUAL_PLAN_ID, P...
19	19	44,963	0.00	1.97	8v2cvg5zhw9ua	PYUSLV	SELECT fnd_number.number_to_ca...
16	16	478,338	0.00	1.60	5w5up6vdp2qyp	PYUSLV	SELECT RP.SEQUENCE_NO, RPV.VA...
15	15	9,997	0.00	1.50	36z3h9rgkd8aa	PYUSLV	begin ...
14	14	479,103	0.00	1.40	fdv98jawj03hg	PYUSLV	SELECT FUE.USER_ENTITY_NAME , ...
13	13	109,888	0.00	1.33	6v07302134wyf	PYUSLV	INSERT INTO PAY_ACTION_INFORMA...
13	13	429,734	0.00	1.31	02k10jzw8c495	PYUSLV	SELECT INTLK.LOCKING_ACTION_ID...
13	13	699,202	0.00	1.31	4gz4b2bh1fs14	PYUSLV	SELECT CONTEXT_ID, SEQUENCE_N...
12	12	478,200	0.00	1.23	g6tx1p7d69dxg	PYUSLV	SELECT ENT.USER_ENTITY_ID, EN...
11	11	9,971	0.00	1.15	2z261p0p82p32	PYUSLV	BEGIN FFP51704_01010001.FORMUL...
10	10	9,909	0.00	1.03	gj45bp5w5anrf	PYUSLV	SELECT DISTINCT PEE.ASSIGNMENT...
10	10	9,902	0.00	1.02	a9pf25b5p69c6	PYUSLV	declare cursor dp ( p_act in n...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
650	652	9,990	0.07	66.44	4f5mjr4hnaz3u	PYUSLV	declare begin pay_archive...
648	649	9,979	0.06	66.16	5sfm6rppc4y8x	PYUSLV	BEGIN pay_us_action_arch.actio...
68	69	9,534	0.01	6.99	9jv68n0ctas75	PYUSLV	begin pay_core_utils . get_pro...
50	51	279,828	0.00	5.15	3n5uwzsh3y8gr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
33	33	9,948	0.00	3.38	80wvw6gatjvb2	PYUSLV	SELECT PTP_TD.START_DATE FROM ...
32	32	59,901	0.00	3.31	01pkua627ktjk	PYUSLV	SELECT fnd_number.number_to_ca...
30	30	10,000	0.00	3.06	4ds53shhfcynn	PYUSLV	declare begin hr_pre_pay....
24	24	149,876	0.00	2.47	g3835fnqtvy06	PYUSLV	SELECT NVL(SUM(prb.balance_val...
21	21	169,915	0.00	2.18	6500rnmy4f88w	PYUSLV	SELECT PAP.ACCRUAL_PLAN_ID, P...
19	19	44,963	0.00	1.97	8v2cvg5zhw9ua	PYUSLV	SELECT fnd_number.number_to_ca...
19	33	50	0.37	3.40	dab1prc9qyv3v	PYUSLV	begin hr_nonrun_asact...
16	16	478,338	0.00	1.60	5w5up6vdp2qyp	PYUSLV	SELECT RP.SEQUENCE_NO, RPV.VA...
15	15	9,997	0.00	1.50	36z3h9rgkd8aa	PYUSLV	begin ...
14	14	479,103	0.00	1.40	fdv98jawj03hg	PYUSLV	SELECT FUE.USER_ENTITY_NAME , ...
13	13	109,888	0.00	1.33	6v07302134wyf	PYUSLV	INSERT INTO PAY_ACTION_INFORMA...
13	13	429,734	0.00	1.31	02k10jzw8c495	PYUSLV	SELECT INTLK.LOCKING_ACTION_ID...
13	13	699,202	0.00	1.31	4gz4b2bh1fs14	PYUSLV	SELECT CONTEXT_ID, SEQUENCE_N...
12	12	478,200	0.00	1.23	g6tx1p7d69dxg	PYUSLV	SELECT ENT.USER_ENTITY_ID, EN...
11	11	9,971	0.00	1.15	2z261p0p82p32	PYUSLV	BEGIN FFP51704_01010001.FORMUL...
10	10	9,909	0.00	1.03	gj45bp5w5anrf	PYUSLV	SELECT DISTINCT PEE.ASSIGNMENT...
10	10	9,902	0.00	1.02	a9pf25b5p69c6	PYUSLV	declare cursor dp ( p_act in n...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 142,367,142
• Captured SQL account for 67.0% of Total

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
91,797,077	9,990	9,188.90	64.48	650.24	651.95	4f5mjr4hnaz3u	PYUSLV	declare begin pay_archive...
91,675,301	9,979	9,186.82	64.39	647.60	649.24	5sfm6rppc4y8x	PYUSLV	BEGIN pay_us_action_arch.actio...
16,075,961	279,828	57.45	11.29	50.40	50.59	3n5uwzsh3y8gr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
14,435,524	9,534	1,514.11	10.14	68.33	68.63	9jv68n0ctas75	PYUSLV	begin pay_core_utils . get_pro...
12,105,093	59,901	202.08	8.50	32.40	32.45	01pkua627ktjk	PYUSLV	SELECT fnd_number.number_to_ca...
9,075,669	9,948	912.31	6.37	33.15	33.15	80wvw6gatjvb2	PYUSLV	SELECT PTP_TD.START_DATE FROM ...
7,284,794	44,963	162.02	5.12	19.37	19.36	8v2cvg5zhw9ua	PYUSLV	SELECT fnd_number.number_to_ca...
6,438,084	149,876	42.96	4.52	24.12	24.28	g3835fnqtvy06	PYUSLV	SELECT NVL(SUM(prb.balance_val...
5,780,746	50	115,614.92	4.06	18.63	33.36	dab1prc9qyv3v	PYUSLV	begin hr_nonrun_asact...
5,264,259	478,338	11.01	3.70	15.75	15.74	5w5up6vdp2qyp	PYUSLV	SELECT RP.SEQUENCE_NO, RPV.VA...
4,725,406	429,734	11.00	3.32	12.82	12.82	02k10jzw8c495	PYUSLV	SELECT INTLK.LOCKING_ACTION_ID...
4,269,563	10,000	426.96	3.00	30.01	30.01	4ds53shhfcynn	PYUSLV	declare begin hr_pre_pay....
4,041,005	478,200	8.45	2.84	12.10	12.09	g6tx1p7d69dxg	PYUSLV	SELECT ENT.USER_ENTITY_ID, EN...
3,946,632	479,103	8.24	2.77	13.75	13.75	fdv98jawj03hg	PYUSLV	SELECT FUE.USER_ENTITY_NAME , ...
3,499,998	583,508	6.00	2.46	9.18	9.18	cn5un9cxnah8w	PYUSLV	SELECT NVL(PBD.PERIOD_TYPE, '...
2,673,146	9,997	267.39	1.88	14.70	14.77	36z3h9rgkd8aa	PYUSLV	begin ...
2,273,584	9,998	227.40	1.60	9.69	9.73	7d66dw0vz7yaa	PYUSLV	begin pay_cc_process_utils . r...
2,095,965	699,202	3.00	1.47	12.81	12.81	4gz4b2bh1fs14	PYUSLV	SELECT CONTEXT_ID, SEQUENCE_N...
2,006,616	109,888	18.26	1.41	13.02	13.08	6v07302134wyf	PYUSLV	INSERT INTO PAY_ACTION_INFORMA...
1,846,902	9,923	186.12	1.30	7.40	7.65	gxa19cf01sb0c	PYUSLV	SELECT PTS.TIME_IN_STATE, PTS...
1,776,784	9,902	179.44	1.25	10.01	10.01	a9pf25b5p69c6	PYUSLV	declare cursor dp ( p_act in n...
1,752,177	583,758	3.00	1.23	8.16	8.16	77d28bpwm9svg	PYUSLV	SELECT PBV.RUN_BALANCE_STATUS,...
1,714,514	9,845	174.15	1.20	8.78	8.78	7mmdd7sypzu9x	PYUSLV	BEGIN FFP1893_01010001.FORMULA...
1,714,396	9,909	173.01	1.20	10.05	10.08	gj45bp5w5anrf	PYUSLV	SELECT DISTINCT PEE.ASSIGNMENT...
1,677,073	19,950	84.06	1.18	7.55	7.59	bqvyzfu7y62t7	PYUSLV	SELECT PBG.LEGISLATION_CODE LE...
1,652,708	169,915	9.73	1.16	21.42	21.42	6500rnmy4f88w	PYUSLV	SELECT PAP.ACCRUAL_PLAN_ID, P...
1,555,380	19,834	78.42	1.09	7.44	7.46	0sku6ydxg1rtf	PYUSLV	SELECT PTP.START_DATE START_DA...
1,527,891	9,971	153.23	1.07	11.34	11.33	2z261p0p82p32	PYUSLV	BEGIN FFP51704_01010001.FORMUL...
1,447,437	9,964	145.27	1.02	9.71	9.71	g4jp561h1bs98	PYUSLV	select /*+ ORDERED USE_NL(AC...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

• Total Disk Reads: 72
• Captured SQL account for 0.0% of Total

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
0	500	0.00	0.00	1.09	1.09	0095dq943c6t3	PYUSLV	SELECT /*+ ORDERED ...
0	59,901	0.00	0.00	32.40	32.45	01pkua627ktjk	PYUSLV	SELECT fnd_number.number_to_ca...
0	429,734	0.00	0.00	12.82	12.82	02k10jzw8c495	PYUSLV	SELECT INTLK.LOCKING_ACTION_ID...
0	2	0.00	0.00	0.03	0.03	03s61gtq86zy3	INCTCM	update MTL_MATERIAL_TRANSACTIO...
0	1	0.00	0.00	0.01	0.01	0dwr2vd6vbqzs		SELECT count(*) over () as tot...
0	968	0.00	0.00	0.04	0.04	0h6b2sajwb74n		select privilege#, level from ...
0	88	0.00	0.00	0.01	0.01	0q8w0cndsp865		SELECT FPOV.PROFILE_OPTION_VAL...
0	350	0.00	0.00	0.26	0.26	0sfvm0sf84qan		select parent_owner, parent_na...
0	19,834	0.00	0.00	7.44	7.46	0sku6ydxg1rtf	PYUSLV	SELECT PTP.START_DATE START_DA...
0	5,056	0.00	0.00	0.10	0.10	0wmwsjy9kd92f		SELECT PROFILE_OPTION_ID, APP...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

• Total Executions: 9,081,789
• Captured SQL account for 54.3% of Total

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
699,202	1,168,991	1.67	0.00	0.00	4gz4b2bh1fs14	PYUSLV	SELECT CONTEXT_ID, SEQUENCE_N...
583,758	584,097	1.00	0.00	0.00	77d28bpwm9svg	PYUSLV	SELECT PBV.RUN_BALANCE_STATUS,...
583,508	583,738	1.00	0.00	0.00	cn5un9cxnah8w	PYUSLV	SELECT NVL(PBD.PERIOD_TYPE, '...
479,103	478,848	1.00	0.00	0.00	fdv98jawj03hg	PYUSLV	SELECT FUE.USER_ENTITY_NAME , ...
478,338	957,514	2.00	0.00	0.00	5w5up6vdp2qyp	PYUSLV	SELECT RP.SEQUENCE_NO, RPV.VA...
478,200	478,983	1.00	0.00	0.00	g6tx1p7d69dxg	PYUSLV	SELECT ENT.USER_ENTITY_ID, EN...
429,734	429,669	1.00	0.00	0.00	02k10jzw8c495	PYUSLV	SELECT INTLK.LOCKING_ACTION_ID...
279,828	279,530	1.00	0.00	0.00	3n5uwzsh3y8gr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
169,915	0	0.00	0.00	0.00	6500rnmy4f88w	PYUSLV	SELECT PAP.ACCRUAL_PLAN_ID, P...
149,876	149,712	1.00	0.00	0.00	g3835fnqtvy06	PYUSLV	SELECT NVL(SUM(prb.balance_val...
109,888	109,875	1.00	0.00	0.00	6v07302134wyf	PYUSLV	INSERT INTO PAY_ACTION_INFORMA...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

• Total Parse Calls: 753,439
• Captured SQL account for 91.0% of Total

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
277,563	279,828	36.84	3n5uwzsh3y8gr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
149,304	149,876	19.82	g3835fnqtvy06	PYUSLV	SELECT NVL(SUM(prb.balance_val...
59,842	59,901	7.94	01pkua627ktjk	PYUSLV	SELECT fnd_number.number_to_ca...
44,859	44,963	5.95	8v2cvg5zhw9ua	PYUSLV	SELECT fnd_number.number_to_ca...
29,917	29,973	3.97	264qzrka8fsps	PYUSLV	SELECT NVL(SUM(prb.balance_val...
20,000	20,000	2.65	83kbt99vg4svq	PYUSLV	begin ...
19,960	19,952	2.65	165d2bgc2b9hr	PYUSLV	SELECT NVL(SUM(prb.balance_val...
10,000	10,000	1.33	4ds53shhfcynn	PYUSLV	declare begin hr_pre_pay....
10,000	10,000	1.33	d7shgtk3p7s4u	PYUSLV	SELECT /*+ ordered index(BAL...
9,990	9,931	1.33	3rg5fyfgd5uwc	PYUSLV	begin pay_US_rules.get_tim...
9,986	9,979	1.33	5sfm6rppc4y8x	PYUSLV	BEGIN pay_us_action_arch.actio...
9,978	9,990	1.32	4f5mjr4hnaz3u	PYUSLV	declare begin pay_archive...
9,970	9,997	1.32	36z3h9rgkd8aa	PYUSLV	begin ...
9,949	9,761	1.32	97mt0g20bcfc3	PYUSLV	begin ...
8,901	8,901	1.18	cr74qtm56vcq6	NACHA	BEGIN PAY_US_NACHA_TAPE.RUN_FO...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
0095dq943c6t3	SELECT /*+ ORDERED index(pa2 PAY_PAYROLL_ACTIONS_PK) index(pos PER_PERIODS_OF_SERVICE_N3) index(as1 PER_ASSIGNMENTS_F_N4) index(act PAY_ASSIGNMENT_ACTIONS_N51) index(as2 PER_ASSIGNMENTS_F_PK) index(opm PAY_ORG_PAYMENT_METHODS_F_PK) USE_NL(pos pop act pa2 as1 as2) */ ACT.ASSIGNMENT_ACTION_ID, ACT.ASSIGNMENT_ID, ACT.TAX_UNIT_ID, PPP.PRE_PAYMENT_ID, PA1.ASSIGNMENT_SET_ID, AS1.PAYROLL_ID FROM PAY_PAYROLL_ACTIONS PA1, PAY_POPULATION_RANGES POP, PER_PERIODS_OF_SERVICE POS, PER_ALL_ASSIGNMENTS_F AS1, PAY_ASSIGNMENT_ACTIONS ACT, PAY_PAYROLL_ACTIONS PA2, PAY_ACTION_CLASSIFICATIONS PCL, PAY_PRE_PAYMENTS PPP, PER_ALL_ASSIGNMENTS_F AS2, PAY_ORG_PAYMENT_METHODS_F OPM WHERE PA1.PAYROLL_ACTION_ID = :B3 AND PA2.CONSOLIDATION_SET_ID +0 = PA1.CONSOLIDATION_SET_ID AND PA2.EFFECTIVE_DATE BETWEEN PA1.START_DATE AND PA1.EFFECTIVE_DATE AND ACT.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND ACT.ACTION_STATUS = 'C' AND PCL.CLASSIFICATION_NAME = :B4 AND PA2.ACTION_TYPE = PCL.ACTION_TYPE AND AS1.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA1.EFFECTIVE_DATE BETWEEN AS1.EFFECTIVE_START_DATE AND AS1.EFFECTIVE_END_DATE AND AS2.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS2.EFFECTIVE_START_DATE AND AS2.EFFECTIVE_END_DATE AND AS2.PAYROLL_ID + 0 = AS1.PAYROLL_ID + 0 AND POS.PERIOD_OF_SERVICE_ID = AS1.PERIOD_OF_SERVICE_ID AND POP.PAYROLL_ACTION_ID = :B3 AND POP.CHUNK_NUMBER = :B2 AND POS.PER SON_ID = POP.PERSON_ID AND (AS1.PAYROLL_ID = PA1.PAYROLL_ID OR PA1.PAYROLL_ID IS NULL) AND PPP.ASSIGNMENT_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID AND PA1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE AND OPM.PAYMENT_TYPE_ID +0 = :B1 AND PPP.ORGANIZATION_ID IS NULL AND (OPM.ORG_PAYMENT_METHOD_ID = PA1.ORG_PAYMENT_METHOD_ID OR PA1.ORG_PAYMENT_METHOD_ID IS NULL) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PAY_ACTION_INTERLOCKS INT, PAY_ASSIGNMENT_ACTIONS AC2 WHERE INT.LOCKED_ACTION_ID = ACT.ASSIGNMENT_ACTION_ID AND AC2.ASSIGNMENT_ACTION_ID = INT.LOCKING_ACTION_ID AND AC2.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID AND NOT EXISTS ( SELECT NULL FROM PAY_ASSIGNMENT_ACTIONS PAA_VOID, PAY_ACTION_INTERLOCKS PAI_VOID, PAY_PAYROLL_ACTIONS PPA_VOID WHERE PAI_VOID.LOCKED_ACTION_ID = AC2.ASSIGNMENT_ACTION_ID AND PAI_VOID.LOCKING_ACTION_ID = PAA_VOID.ASSIGNMENT_ACTION_ID AND PAA_VOID.PAYROLL_ACTION_ID = PPA_VOID.PAYROLL_ACTION_ID AND PPA_VOID.ACTION_TYPE = 'D') ) AND NOT EXISTS ( SELECT /*+ ORDERED*/ NULL FROM PER_ALL_ASSIGNMENTS_F AS3, PAY_ASSIGNMENT_ACTIONS AC3 WHERE :B5 = 'N' AND AC3.PAYROLL_ACTION_ID = PA2.PAYROLL_ACTION_ID AND AC3.ACTION_STATUS NOT IN ('C', 'S') AND AS3.ASSIGNMENT_ID = AC3.ASSIGNMENT_ID AND PA2.EFFECTIVE_DATE BETWEEN AS3.EFFECTIVE_START_DATE AND AS3.EFFECTIVE_END_DATE AND AS3.PERSON_ID = AS2.PERSON_ID) ORDER BY ACT.ASSIGNMENT_ID FOR UPDATE OF AS1.ASSIGNMENT_ID, POS.PERIOD_OF_SERVICE_I D
01pkua627ktjk	SELECT fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y')
02k10jzw8c495	SELECT INTLK.LOCKING_ACTION_ID FROM PAY_ACTION_INTERLOCKS INTLK, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE INTLK.LOCKED_ACTION_ID = :B1 AND INTLK.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('P', 'U') AND PAA.SOURCE_ACTION_ID IS NULL
03s61gtq86zy3	update MTL_MATERIAL_TRANSACTIONS_TEMP MMTT set TRANSACTION_HEADER_ID=:b0, LAST_UPDATE_DATE=SYSDATE, LAST_UPDATED_BY=:b1, LAST_UPDATE_LOGIN=:b2, PROGRAM_APPLICATION_ID=:b3, PROGRAM_ID=:b4, REQUEST_ID=:b5, PROGRAM_UPDATE_DATE=SYSDATE, LOCK_FLAG='Y', ERROR_CODE=null , ERROR_EXPLANATION=null where (((((PROCESS_FLAG='Y' and NVL(TRANSACTION_STATUS, 1) in (1, 3)) and NVL(LOCK_FLAG, 'N')='N') and TRANSACTION_MODE=3) and ROWNUM<=:b6) and exists (select 1 from ORG_ORGANIZATION_DEFINITIONS OOD where (OOD.ORGANIZATION_ID=MMTT.ORGANIZATION_ID and NVL(OOD.DISABLE_DATE, (SYSDATE+1))>SYSDATE)))
0dwr2vd6vbqzs	SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, sd_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHEN (sd_xe_ash_nm.parameter1 is NULL OR sd_xe_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END) as p1valid, sd_xe_ash_nm.parameter2 as p2text, (CASE WHEN (sd_xe_ash_nm.parameter2 is NULL OR sd_xe_ash_nm.parameter2 = '0') THEN 0 ELSE 1 END) as p2valid, sd_xe_ash_nm.parameter3 as p3text, (CASE WHEN (sd_xe_ash_nm.parameter3 is NULL OR sd_xe_ash_nm.parameter3 = '0') THEN 0 ELSE 1 END) as p3valid, sd_xe_ash_nm.keh_evt_id, nvl(xc.class#, 0) as class_num, sd_xe_ash_nm.wait_class_id, nvl(xc.keh_id, 0) as keh_ecl_id, sd_xe_ash_nm.ash_cnt, sd_xe_ash_nm.tot_wts_diff, sd_xe_ash_nm.tot_tmo_diff, sd_xe_ash_nm.tim_wait_diff FROM ( SELECT sd_xe_ash.*, evtname.event_name, evtname.wait_class_id, evtname.parameter1, evtname.parameter2, evtname.parameter3 FROM ( SELECT sd_xe.*, nvl(ash.cnt, 0) as ash_cnt FROM ( SELECT nvl(xe.keh_id, 0) as keh_evt_id, nvl(sd.event_id, xe.event_hash) as event_id, nvl(sd.tot_wts_diff, 0) as tot_wts_diff, nvl(sd.tot_tmo_diff, 0) as tot_tmo_diff, nvl(sd.tim_wait_diff, 0) as tim_wait_diff FROM ( SELECT endsn.event_id as event_id, (endsn.total_waits - nvl(begsn.total_waits, 0)) as tot_wts_diff, (endsn.total_timeouts - nvl(begsn.total_timeouts, 0)) as tot_tmo_diff, (endsn.time_waited_micro - nvl(begsn.time_waited_micro, 0)) as tim_wait_diff FROM ( SELECT end_snap.* FROM (SELECT t1.* FROM WRH$_SYSTEM_EVENT t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_SYSTEM_EVENT_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) end_snap WHERE end_snap.dbid = :dbid and end_snap.instance_number = :instance_number and end_snap.snap_id = :end_snap ) endsn LEFT OUTER JOIN ( SELECT beg_snap.* FROM (SELECT t1.* FROM WRH$_SYSTEM_EVENT t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_SYSTEM_EVENT_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) beg_snap WHERE beg_snap.dbid = :dbid and beg_snap.instance_number = :instance_number and beg_snap.snap_id = :beg_snap ) begsn ON endsn.event_id = begsn.event_id ) sd FULL OUTER JOIN X$KEHEVTMAP xe ON sd.event_id = xe.event_hash ) sd_xe LEFT OUTER JOIN (SELECT a.event_id, count(*) as cnt FROM (SELECT t1.* FROM WRH$_ACTIVE_SESSION_HISTORY t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_ACTIVE_SESSION_HISTORY_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) a WHERE a.dbid = :dbid and a.instance_number = :instance_number and a.snap_id > :beg_snap and a.snap_id <= :end_snap and a.wait_time = 0 GROUP BY a.event_id) ash ON sd_xe.event_id = ash.event_id ) sd_xe_ash, WRH$_EVENT_NAME evtname WHERE evtname.event_id = sd_xe_ash.event_id and evtname.event_id > 0 and evtname.dbid = :dbid ) sd_xe_ash_nm, X$KEHECLMAP xc WHERE sd_xe_ash_nm.wait_class_id = xc.class_hash ORDER BY sd_xe_ash_nm.wait_class_id, sd_xe_ash_nm.tim_wait_diff DESC, sd_xe_ash_nm.event_id
0h6b2sajwb74n	select privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0q8w0cndsp865	SELECT FPOV.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES FPOV, FND_PROFILE_OPTIONS FPO WHERE FPO.PROFILE_OPTION_NAME = 'AFCORE_LOGGING_ENABLED' AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID AND FPOV.LEVEL_ID = 10001 AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
0sfvm0sf84qan	select parent_owner, parent_name, parent_link_name, parent_type, parent_timestamp, property from ora_kglr7_dependencies where owner=:1 and name=:2 and type=:3 and obj#=:4 order by order_number
0sku6ydxg1rtf	SELECT PTP.START_DATE START_DATE, PTP.END_DATE END_DATE FROM PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP WHERE PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PTP.START_DATE AND PTP.END_DATE AND PPA.PAYROLL_ID = PTP.PAYROLL_ID
0wmwsjy9kd92f	SELECT PROFILE_OPTION_ID, APPLICATION_ID, SITE_ENABLED_FLAG , APP_ENABLED_FLAG , RESP_ENABLED_FLAG , USER_ENABLED_FLAG, ORG_ENABLED_FLAG , SERVER_ENABLED_FLAG, SERVERRESP_ENABLED_FLAG, HIERARCHY_TYPE, USER_CHANGEABLE_FLAG FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_NAME = :B1 AND START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE) >= SYSDATE
165d2bgc2b9hr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment within a Government Reporting Entity for YTD based on run balances */ pay_run_balances prb, pay_defined_balances pdb, pay_payroll_actions ppa, pay_assignment_actions paa where paa.assignment_action_id = :ASSIGNMENT_ACTION_ID and prb.tax_unit_id = :TAX_UNIT_ID and ppa.payroll_action_id = paa.payroll_action_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id and paa.assignment_id = prb.assignment_id and prb.action_sequence <= paa.action_sequence and prb.effective_date >= trunc(ppa.effective_date, 'Y')
264qzrka8fsps	SELECT NVL(SUM(prb.balance_value), 0) FROM /* US Assignment for Run based on run balances */ pay_run_balances prb, pay_defined_balances pdb where prb.assignment_action_id = :ASSIGNMENT_ACTION_ID and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id
2z261p0p82p32	BEGIN FFP51704_01010001.FORMULA( :N3, :I7, :N2, :T1, :I1, :T5, :I5, :T4, :I4, :N8, :I12, :T3, :I3, :T8, :I14, :N6, :I10, :T7, :I13, :N4, :I8, :D1, :N12, :I18, :N10, :I16, :N7, :I11, :N11, :I17, :T6, :I6, :N1, :T2, :I2, :N5, :I9, :N9, :I15, :FFERLN, :FFERCD, :FFERMT); END;
36z3h9rgkd8aa	begin hrassact.update_action_sequence (:aa_id, :itpflg); end;
3n5uwzsh3y8gr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N'), 'S ', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2
3rg5fyfgd5uwc	begin pay_US_rules.get_time_def_for_entry( :element_entry_id, :assignment_id, :assignment_action_id, :business_group_id, :time_def_id); end;
4ds53shhfcynn	declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4f5mjr4hnaz3u	declare begin pay_archive.process_employee(:asg_action); end;
4gz4b2bh1fs14	SELECT CONTEXT_ID, SEQUENCE_NO FROM FF_ROUTE_CONTEXT_USAGES WHERE ROUTE_ID = :B1 ORDER BY SEQUENCE_NO
5sfm6rppc4y8x	BEGIN pay_us_action_arch.action_archive_data(:aactid, :l_effective_date); END;
5w5up6vdp2qyp	SELECT RP.SEQUENCE_NO, RPV.VALUE FROM FF_ROUTE_PARAMETER_VALUES RPV , FF_ROUTE_PARAMETERS RP WHERE RPV.USER_ENTITY_ID = :B2 AND RP.ROUTE_ID = :B1 AND RPV.ROUTE_PARAMETER_ID = RP.ROUTE_PARAMETER_ID ORDER BY RP.SEQUENCE_NO
6500rnmy4f88w	SELECT PAP.ACCRUAL_PLAN_ID, PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID FROM PAY_ACCRUAL_PLANS PAP, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_CATEGORY = :B3 AND PEL.ELEMENT_TYPE_ID= PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEE.ASSIGNMENT_ID = :B2 AND :B1 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
6v07302134wyf	INSERT INTO PAY_ACTION_INFORMATION (ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 ) VALUES (:B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
77d28bpwm9svg	SELECT PBV.RUN_BALANCE_STATUS, PBV.BALANCE_LOAD_DATE FROM PAY_BALANCE_VALIDATION PBV WHERE PBV.DEFINED_BALANCE_ID = :B2 AND PBV.BUSINESS_GROUP_ID = :B1
7d66dw0vz7yaa	begin pay_cc_process_utils . reset_dates_for_run ( p_asg_id => :v_asg_id , p_sysdate => to_date ( :v_sysdate , :date_format ) , p_assact_id => :v_assact_id ) ; end ;
7mmdd7sypzu9x	BEGIN FFP1893_01010001.FORMULA( :N1, :N2, :T1, :I1, :D1, :T2, :I2, :T7, :I7, :T8, :I8, :T5, :I5, :N5, :T4, :I4, :N4, :N3, :T3, :I3, :T6, :I6, :FFERLN, :FFERCD, :FFERMT); END;
80wvw6gatjvb2	SELECT PTP_TD.START_DATE FROM PER_TIME_PERIODS PTP_TD, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP_PPA WHERE PTP_TD.TIME_DEFINITION_ID = :B2 AND PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.PAYROLL_ID = PTP_PPA.PAYROLL_ID AND PPA.DATE_EARNED BETWEEN PTP_PPA.START_DATE AND PTP_PPA.END_DATE AND PTP_TD.START_DATE > PTP_PPA.START_DATE AND PTP_TD.START_DATE <= PTP_PPA.END_DATE
83kbt99vg4svq	begin hr_nonrun_asact.reinterlock (:assactid, :pactype); end;
8v2cvg5zhw9ua	SELECT fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y') /* Within Jurisdiction */ and :JURISDICTION_LEVEL != 0 and substr (RR.jurisdiction_code, 1, :JURISDICTION_LEVEL) = substr (:JURISDICTION_CODE, 1, :JURISDICTION_LEVEL)
97mt0g20bcfc3	begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9jv68n0ctas75	begin pay_core_utils . get_prorated_dates ( :ee_id , :asg_act_id , :time_def_type , :time_def_id:time_def_ind , :pro_dates:prod_ind , :pro_types:prot_ind ) ; END ;
a9pf25b5p69c6	declare cursor dp ( p_act in number ) is select distinct paf2 . assignment_id , paa . action_sequence from pay_assignment_actions paa , pay_payroll_actions ppa , per_all_assignments_f paf , per_periods_of_service pos , per_all_assignments_f paf2 where paa . assignment_action_id = p_act and paa . payroll_action_id = ppa . payroll_action_id and paf . assignment_id = paa . assignment_id and ppa . effective_date between paf . effective_start_date and paf . effective_end_date and paf . person_id = pos . person_id and pos . period_of_service_id = paf2 . period_of_service_id ; BEGIN for dprec in dp ( :assactid ) loop begin select 1 into :act_exists from sys . dual where exists ( select null from pay_assignment_actions paa , pay_payroll_actions ppa , pay_action_classifications pcl where paa . assignment_id = dprec . assignment_id and paa . payroll_action_id = ppa . payroll_action_id and ppa . action_type = pcl . action_type and pcl . classification_name = 'SEQUENCED' and paa . action_status not in ( 'C' , 'S' , 'B' ) and paa . action_sequence < dprec . action_sequence ) ; exception when no_data_found then null ; end ; end loop ; END ;
bqvyzfu7y62t7	SELECT PBG.LEGISLATION_CODE LEGISLATION_CODE, PBG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID FROM PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PER_BUSINESS_GROUPS_PERF PBG WHERE PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
cn5un9cxnah8w	SELECT NVL(PBD.PERIOD_TYPE, 'NULL'), PBD.START_DATE_CODE FROM PAY_BALANCE_DIMENSIONS PBD, PAY_DEFINED_BALANCES PDB WHERE PDB.DEFINED_BALANCE_ID = :B1 AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
cr74qtm56vcq6	BEGIN PAY_US_NACHA_TAPE.RUN_FORMULA; END;
d7shgtk3p7s4u	SELECT /*+ ordered index(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(BACT PAY_PAYROLL_ACTIONS_PK) index(ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PACT PAY_PAYROLL_ACTIONS_PK) index(INTLCK2 PAY_ACTION_INTERLOCKS_FK1) index(PPASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PPACT PAY_PAYROLL_ACTIONS_PK) index(RR PAY_RUN_RESULTS_N50) index(TARGET PAY_RUN_RESULT_VALUES_N50) index(FEED PAY_BALANCE_FEEDS_F_N50) */ fnd_number.number_to_canonical(/*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0) ) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_assignment_actions ASSACT , pay_payroll_actions PACT , pay_run_results RR , pay_run_result_values TARGET , pay_balance_feeds_f FEED where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action _id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and nvl(PACT.future_process_mode, 'Y') = 'Y' and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and pay_core_utils.include_action_in_payment(BACT.action_type, BAL_ASSACT.assignment_action_id, ASSACT.assignment_action_id) = 'Y' and NOT EXISTS (SELECT /*+ NO_PUSH_SUBQ */ NULL FROM pay_payroll_actions RPACT , pay_assignment_actions RASSACT , pay_action_interlocks RINTLK where ASSACT.assignment_action_id = RINTLK.locked_action_id and RINTLK.locking_action_id = RASSACT.assignment_action_id and RPACT.payroll_action_id = RASSACT.payroll_action_id and RPACT.action_type = 'V')
dab1prc9qyv3v	begin hr_nonrun_asact.asact(:pactid, :atype, :itpflg, :ptype, :lub, :lul, :use_pop_person); end;
fdv98jawj03hg	SELECT FUE.USER_ENTITY_NAME , NVL(FUE.BUSINESS_GROUP_ID, -1) , NVL(FUE.LEGISLATION_CODE, ' ') FROM FF_USER_ENTITIES FUE , FF_USER_ENTITIES FUE_B WHERE FUE.CREATOR_ID = :B2 AND FUE_B.CREATOR_TYPE = 'B' AND FUE.CREATOR_ID = FUE_B.CREATOR_ID AND FUE.CREATOR_TYPE = 'RB' AND FUE.USER_ENTITY_NAME = FUE_B.USER_ENTITY_NAME||'_'||TO_CHAR(:B1 )
g3835fnqtvy06	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_balance_types pbt , pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N' ), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_dimension_id = :U2 and pbt.balance_type_id = :U1 and pdb.balance_type_id = pbt.balance_type_id and substr(prb.jurisdiction_code, 1, pbt.jurisdiction_level) = substr(:JURISDICTION_CODE, 1, pbt.jurisdiction_level)
g4jp561h1bs98	select /*+ ORDERED USE_NL(ACT EE ET EEV) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK, EE PAY_ELEMENT_ENTRIES_F_N50, ET PAY_ELEMENT_TYPES_F_PK, EEV PAY_ELEMENT_ENTRY_VALUES_F_N50) +*/ EE.entry_type , to_char(EE.element_entry_id) , decode(:b0, 'T', decode(EE.entry_type, 'R', to_char(EE.target_entry_id), 'A', to_char(EE.target_entry_id), to_char(EE.original_entry_id)), to_char(EE.original_entry_id)) , EE.element_link_id , to_char(EE.target_entry_id) , EE.subpriority , ET.element_type_id , ET.processing_priority , ET.processing_type , EEV.input_value_id , EEV.screen_entry_value , to_char(null ) , to_char(null ) , 0 , to_char(null ) , to_char(null ) , decode(EE.entry_type, 'S', 1, 'E', 2, 'R', 2, 'A', 2, 'D', 3) , nvl(EE.target_entry_id, EE.element_entry_id) , decode(EE.entry_type, 'E', 2, 1) , to_char(EE.effective_start_date, :b1) , to_char(EE.effective_end_date, :b1) , ET.proration_group_id , pay_retro_pkg.is_retro_entry(EE.creator_type) , to_char(null ) , 'RECALC_NON_RETRO' , 'E' , PEPD.tax_unit_id , to_char(null ) , PEPD.time_definition_id , nvl(ET.time_definition_type, 'N') , to_char(decode(ET.processing_type, 'R', to_date(null , :b1), EE.date_earned), :b1) , decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ) , decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ) , EE.label_identifier , decode(ACT.action_status, 'B', decode(EE.creator_type, 'EE', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b10, :b11:b12, EE.creator_id, ACT.act ion_sequence), 'RR', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b14, :b11:b16, EE.creator_id, ACT.action_sequence), 1), 1) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'AE', to_char(EE.source_start_date, :b1), 'AD', to_char(EE.source_start_date, :b1), null ) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ), 'AE', to_char(EE.source_end_date, :b1), 'AD', to_char(EE.source_end_date, :b1), null ) from pay_assignment_actions ACT , pay_element_entries_f EE , pay_element_types_f ET , pay_element_entry_values_f EEV , pay_entry_process_details PEPD where (((((((((((((((ACT.assignment_action_id=:b29 and EE.assignment_id=:b30) and ((:b31='N' and to_date(:b32, :b1) between EE.effective_start_date and EE.effective_end_date) or ((:b31='Y' and EE.effective_start_date<=to_date(:b32, :b1)) and EE.effective_end_date>=decode(ET.proration_group_id, null , decode(nvl(ET.time_definition_type, 'N'), 'N', to_date(:b32, :b1), pay_core_utils.get_time_period_start(ACT.payroll_action_id)), pay_interpreter_pkg.prorate_start_date(:b29, ET.proration_group_id))))) and EE.element_type_id=ET.element_type_id) and EE.element_entry_id=PEPD.element_entry_id(+)) and to_date(:b32, :b1) between ET.effective_start_date and ET.effective_end_date) and EEV.elem ent_entry_id(+)=EE.element_entry_id) and EEV.screen_entry_value(+) is not null ) and EE.effective_start_date=EEV.effective_start_date(+)) and EE.effective_end_date=EEV.effective_end_date(+)) and EE.entry_type<>'B') and ET.process_in_run_flag<>'N') and ((:b31='N' or ET.proration_group_id is null ) or (:b31='Y' and exists (select '' from pay_payroll_actions ppa , per_time_periods ptp , pay_element_entries_f pee where (((((pee.element_entry_id=EE.element_entry_id and ppa.payroll_action_id=ACT.payroll_action_id) and ppa.payroll_id=ptp.payroll_id) and to_date(:b32, :b1) between ptp.start_date and ptp.end_date) and pee.effective_start_date<=ptp.end_date) and pee.effective_end_date>=ptp.start_date))))) and not (ACT.action_status='B' and EE.creator_type='P')) and not (ACT.action_status='B' and EE.creator_type='R')) and ((((ET.processing_type='N' or EE.entry_type='D') and nvl(EE.date_earned, to_date('01/01/0001', 'DD/MM/YYYY'))<=to_date(:b32, :b1)) and not exists (select '' from pay_run_results prr where (((prr.source_id=EE.element_entry_id and prr.source_type='E') and nvl(prr.status, 'U') not in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or ((ET.processing_type<>'N' and EE.entry_type<>'D') and ((nvl(ET.once_each_period_flag, 'N')='Y' and not exists (select '' from pay_run_results prr , pay_assignment_actions paa , pay_payroll_actions ppa , per_time_periods ptp , per_time_periods ptp1 , pay_payroll_actions ppa1 where (((((((((((prr.source_id=EE.element_entry_id and prr.source_type='E') and paa.assignment_action_id=prr.assignment_action_id) and ppa.payroll_action_id=paa.payroll_action_id) and ptp.payroll_id=ppa.payroll_id) and ppa.date_earned between ptp.start_date and ptp.end_date) and ppa1.payroll_action_id=ACT.payroll_action_id) and ptp1.payroll_id=ppa1.payroll_id) and ppa1.date_earned between ptp1.start_date and ptp1.end_date) and ptp.time_period_id=ptp1.time_period_id) and nvl(prr.status, 'U') not in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or nvl(ET.once_each_period_flag, 'N')='N')))) order by 8 asc , 6 asc , 7 asc , 17 asc , 18 asc , 19 asc , 20 asc
g6tx1p7d69dxg	SELECT ENT.USER_ENTITY_ID, ENT.CREATOR_TYPE, ENT.NOTFOUND_ALLOWED_FLAG, RO.ROUTE_ID, RO.TEXT, RO.OPTIMIZER_HINT FROM FF_USER_ENTITIES ENT , FF_ROUTES RO WHERE ENT.USER_ENTITY_NAME = :B3 AND ( (ENT.LEGISLATION_CODE IS NULL AND ENT.BUSINESS_GROUP_ID IS NULL) OR (ENT.BUSINESS_GROUP_ID IS NULL AND :B2 = ENT.LEGISLATION_CODE ) OR ENT.BUSINESS_GROUP_ID + 0 = :B1 ) AND ENT.ROUTE_ID = RO.ROUTE_ID
gj45bp5w5anrf	SELECT DISTINCT PEE.ASSIGNMENT_ID , PET.PRORATION_GROUP_ID FROM PAY_ELEMENT_ENTRIES_F PEE, PAY_ELEMENT_TYPES_F PET WHERE PEE.ELEMENT_ENTRY_ID = :B2 AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PEE.EFFECTIVE_START_DATE <= :B1 AND PEE.EFFECTIVE_END_DATE >= PAY_INTERPRETER_PKG.TIME_FN(:B3 , PET.PRORATION_GROUP_ID , :B2 ) AND :B1 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
gxa19cf01sb0c	SELECT PTS.TIME_IN_STATE, PTS.STATE_NAME, PTS.JURISDICTION_CODE, PTS.FILING_STATUS_CODE, PTS.MEANING, PTS.WITHHOLDING_ALLOWANCES, PTS.SIT_ADDITIONAL_TAX, PTS.SIT_OVERRIDE_AMOUNT, PTS.SIT_OVERRIDE_RATE FROM PAY_US_EMP_TIME_IN_STATE_V PTS WHERE PTS.ASSIGNMENT_ID = :B2 AND PTS.JURISDICTION_CODE LIKE :B1

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Back to Top

Instance Activity Stats

Statistic	Total	per Second	per Trans
CPU used by this session	96,173	256.14	6.42
CPU used when call started	96,174	256.15	6.42
CR blocks created	635,604	1,692.85	42.41
Cached Commit SCN referenced	133,973	356.82	8.94
Commit SCN cached	59	0.16	0.00
DB time	396,377	1,055.70	26.45
DBWR checkpoint buffers written	26,744	71.23	1.78
DBWR checkpoints	0	0.00	0.00
DBWR object drop buffers written	0	0.00	0.00
DBWR parallel query checkpoint buffers written	0	0.00	0.00
DBWR revisited being-written buffer	0	0.00	0.00
DBWR thread checkpoint buffers written	26,744	71.23	1.78
DBWR transaction table writes	9	0.02	0.00
DBWR undo block writes	14,983	39.91	1.00
DFO trees parallelized	0	0.00	0.00
DML statements parallelized	0	0.00	0.00
IMU CR rollbacks	21,578,861	57,472.52	1,439.93
IMU Flushes	7,955	21.19	0.53
IMU Redo allocation size	146,480,520	390,132.00	9,774.49
IMU commits	7,099	18.91	0.47
IMU contention	3,181	8.47	0.21
IMU ktichg flush	85	0.23	0.01
IMU pool not allocated	0	0.00	0.00
IMU recursive-transaction flush	215	0.57	0.01
IMU undo allocation size	225,877,032	601,594.38	15,072.54
IMU- failed to get a private strand	0	0.00	0.00
PX local messages recv'd	0	0.00	0.00
PX local messages sent	0	0.00	0.00
Parallel operations downgraded 25 to 50 pct	0	0.00	0.00
Parallel operations downgraded 50 to 75 pct	0	0.00	0.00
Parallel operations downgraded to serial	0	0.00	0.00
Parallel operations not downgraded	0	0.00	0.00
SMON posted for undo segment shrink	2	0.01	0.00
SQL*Net roundtrips to/from client	894,484	2,382.34	59.69
active txn count during cleanout	2,502,274	6,664.48	166.97
application wait time	1,777	4.73	0.12
auto extends on undo tablespace	0	0.00	0.00
background checkpoints completed	0	0.00	0.00
background checkpoints started	0	0.00	0.00
background timeouts	1,327	3.53	0.09
branch node splits	4	0.01	0.00
buffer is not pinned count	60,468,402	161,049.80	4,034.99
buffer is pinned count	43,485,849	115,818.96	2,901.76
bytes received via SQL*Net from client	235,093,085	626,140.15	15,687.51
bytes sent via SQL*Net to client	138,546,225	369,000.02	9,245.04
calls to get snapshot scn: kcmgss	9,886,368	26,331.07	659.71
calls to kcmgas	697,550	1,857.83	46.55
calls to kcmgcs	21,720	57.85	1.45
change write time	956	2.55	0.06
cleanout - number of ktugct calls	617,644	1,645.02	41.21
cleanouts and rollbacks - consistent read gets	592,351	1,577.65	39.53
cleanouts only - consistent read gets	2,173	5.79	0.15
cluster key scan block gets	2,727	7.26	0.18
cluster key scans	1,278	3.40	0.09
commit batch performed	0	0.00	0.00
commit batch requested	0	0.00	0.00
commit batch/immediate performed	642	1.71	0.04
commit batch/immediate requested	642	1.71	0.04
commit cleanout failures: block lost	170	0.45	0.01
commit cleanout failures: buffer being written	0	0.00	0.00
commit cleanout failures: callback failure	3,942	10.50	0.26
commit cleanout failures: cannot pin	1,195	3.18	0.08
commit cleanouts	213,712	569.19	14.26
commit cleanouts successfully completed	208,405	555.06	13.91
commit immediate performed	642	1.71	0.04
commit immediate requested	642	1.71	0.04
commit txn count during cleanout	23,903	63.66	1.60
concurrency wait time	450	1.20	0.03
consistent changes	47,793,646	127,292.22	3,189.22
consistent gets	136,578,610	363,759.53	9,113.75
consistent gets - examination	86,520,820	230,437.06	5,773.44
consistent gets direct	7	0.02	0.00
consistent gets from cache	136,578,603	363,759.52	9,113.75
current blocks converted for CR	0	0.00	0.00
cursor authentications	260	0.69	0.02
data blocks consistent reads - undo records applied	47,757,686	127,196.44	3,186.82
db block changes	5,007,183	13,335.99	334.12
db block gets	5,788,532	15,417.01	386.26
db block gets direct	7	0.02	0.00
db block gets from cache	5,788,525	15,416.99	386.26
deferred (CURRENT) block cleanout applications	24,288	64.69	1.62
dirty buffers inspected	168	0.45	0.01
drop segment calls in space pressure	0	0.00	0.00
enqueue conversions	3,118	8.30	0.21
enqueue releases	92,278	245.77	6.16
enqueue requests	92,353	245.97	6.16
enqueue timeouts	74	0.20	0.00
enqueue waits	2,198	5.85	0.15
exchange deadlocks	3,857	10.27	0.26
execute count	9,081,789	24,188.18	606.02
failed probes on index block reclamation	20	0.05	0.00
free buffer inspected	90,876	242.04	6.06
free buffer requested	686,594	1,828.65	45.82
global undo segment hints helped	0	0.00	0.00
heap block compress	12,413	33.06	0.83
hot buffers moved to head of LRU	1,071	2.85	0.07
immediate (CR) block cleanout applications	594,524	1,583.44	39.67
immediate (CURRENT) block cleanout applications	35,197	93.74	2.35
index crx upgrade (found)	12,487	33.26	0.83
index crx upgrade (positioned)	511,665	1,362.75	34.14
index fast full scans (direct read)	0	0.00	0.00
index fast full scans (full)	0	0.00	0.00
index fast full scans (rowid ranges)	0	0.00	0.00
index fetch by key	17,626,541	46,946.02	1,176.20
index scans kdiixs1	14,444,030	38,469.81	963.83
leaf node 90-10 splits	1,406	3.74	0.09
leaf node splits	3,630	9.67	0.24
lob reads	0	0.00	0.00
lob writes	33	0.09	0.00
lob writes unaligned	33	0.09	0.00
local undo segment hints helped	0	0.00	0.00
logons cumulative	89	0.24	0.01
messages received	25,209	67.14	1.68
messages sent	25,209	67.14	1.68
no buffer to keep pinned count	0	0.00	0.00
no work - consistent read gets	47,647,995	126,904.30	3,179.50
opened cursors cumulative	693,786	1,847.81	46.30
parse count (failures)	0	0.00	0.00
parse count (hard)	281	0.75	0.02
parse count (total)	753,439	2,006.69	50.28
parse time cpu	715	1.90	0.05
parse time elapsed	1,129	3.01	0.08
physical read IO requests	72	0.19	0.00
physical read bytes	589,824	1,570.92	39.36
physical read total IO requests	2,018	5.37	0.13
physical read total bytes	30,359,552	80,858.76	2,025.86
physical read total multi block requests	0	0.00	0.00
physical reads	72	0.19	0.00
physical reads cache	65	0.17	0.00
physical reads cache prefetch	0	0.00	0.00
physical reads direct	7	0.02	0.00
physical reads direct (lob)	0	0.00	0.00
physical reads direct temporary tablespace	0	0.00	0.00
physical reads prefetch warmup	0	0.00	0.00
physical write IO requests	4,782	12.74	0.32
physical write bytes	223,125,504	594,266.04	14,888.93
physical write total IO requests	16,605	44.23	1.11
physical write total bytes	953,642,496	2,539,903.95	63,635.56
physical write total multi block requests	14,136	37.65	0.94
physical writes	27,237	72.54	1.82
physical writes direct	7	0.02	0.00
physical writes direct (lob)	7	0.02	0.00
physical writes direct temporary tablespace	0	0.00	0.00
physical writes from cache	27,230	72.52	1.82
physical writes non checkpoint	20,819	55.45	1.39
pinned buffers inspected	33	0.09	0.00
prefetch warmup blocks aged out before use	0	0.00	0.00
prefetched blocks aged out before use	549	1.46	0.04
process last non-idle time	344	0.92	0.02
queries parallelized	0	0.00	0.00
recursive aborts on index block reclamation	266	0.71	0.02
recursive calls	18,812,384	50,104.36	1,255.33
recursive cpu usage	84,998	226.38	5.67
redo blocks written	1,421,127	3,784.99	94.83
redo buffer allocation retries	0	0.00	0.00
redo entries	2,548,412	6,787.37	170.05
redo log space requests	0	0.00	0.00
redo log space wait time	0	0.00	0.00
redo ordering marks	28,952	77.11	1.93
redo size	700,630,532	1,866,039.17	46,752.34
redo subscn max counts	36,229	96.49	2.42
redo synch time	1,842	4.91	0.12
redo synch writes	5,851	15.58	0.39
redo wastage	2,962,104	7,889.18	197.66
redo write time	2,344	6.24	0.16
redo writer latching time	0	0.00	0.00
redo writes	8,377	22.31	0.56
rollback changes - undo records applied	2,147	5.72	0.14
rollbacks only - consistent read gets	46,042	122.63	3.07
rows fetched via callback	11,042,625	29,410.61	736.86
session connect time	0	0.00	0.00
session cursor cache hits	739,252	1,968.90	49.33
session logical reads	142,367,142	379,176.54	9,500.01
session pga memory max	131,072	349.09	8.75
session uga memory	3,422,248	9,114.72	228.36
session uga memory max	323,530,432	861,681.63	21,588.85
shared hash latch upgrades - no wait	631,559	1,682.08	42.14
shared hash latch upgrades - wait	21,297	56.72	1.42
sorts (disk)	0	0.00	0.00
sorts (memory)	707,369	1,883.99	47.20
sorts (rows)	1,429,421	3,807.08	95.38
space was found by tune down	0	0.00	0.00
sql area evicted	0	0.00	0.00
sql area purged	0	0.00	0.00
steps of tune down ret. in space pressure	0	0.00	0.00
summed dirty queue length	600	1.60	0.04
switch current to new buffer	17,323	46.14	1.16
table fetch by rowid	44,939,666	119,691.01	2,998.78
table fetch continued row	245	0.65	0.02
table scan blocks gotten	9,425,371	25,103.26	628.95
table scan rows gotten	410,665,353	1,093,754.27	27,403.27
table scans (cache partitions)	0	0.00	0.00
table scans (direct read)	0	0.00	0.00
table scans (long tables)	0	0.00	0.00
table scans (rowid ranges)	0	0.00	0.00
table scans (short tables)	557,156	1,483.91	37.18
total number of times SMON posted	172	0.46	0.01
transaction rollbacks	642	1.71	0.04
transaction tables consistent read rollbacks	0	0.00	0.00
transaction tables consistent reads - undo records applied	0	0.00	0.00
tune down retentions in space pressure	0	0.00	0.00
undo change vector size	240,389,352	640,246.07	16,040.93
user I/O wait time	25	0.07	0.00
user calls	954,572	2,542.38	63.70
user commits	14,866	39.59	0.99
user rollbacks	120	0.32	0.01
workarea executions - onepass	0	0.00	0.00
workarea executions - optimal	461,267	1,228.53	30.78
write clones created in background	0	0.00	0.00
write clones created in foreground	6	0.02	0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Statistic	Begin Value	End Value
session cursor cache count	81,902	85,333
opened cursors current	2,613	2,644
workarea memory allocated	487	487
logons current	90	90

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic	Total	per Hour
log switches (derived)	0	0.00

Back to Instance Activity Statistics
Back to Top

IO Stats

• Tablespace IO Stats
• File IO Stats

Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
APPS_UNDOTS1	0	0	0.00	0.00	1,322	4	25,348	0.01
SYSAUX	42	0	3.57	1.00	1,218	3	0	0.00
APPS_TS_TX_DATA	0	0	0.00	0.00	767	2	11,458	0.03
SYSTEM	0	0	0.00	0.00	713	2	22	0.00
APPS_TS_TX_IDX	8	0	3.75	1.00	630	2	21,271	0.03
APPS_TS_ARCHIVE	4	0	5.00	1.00	69	0	27	0.00
APPS_TS_SEED	0	0	0.00	0.00	26	0	0	0.00
APPS_TS_SUMMARY	0	0	0.00	0.00	15	0	0	0.00
APPS_TS_QUEUES	0	0	0.00	0.00	14	0	0	0.00
APPS_TS_INTERFACE	0	0	0.00	0.00	6	0	0	0.00

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
APPS_TS_ARCHIVE	/ds2/oracle/VIS/db/apps_st/data/archive1.dbf	4	0	5.00	1.00	69	0	27	0.00
APPS_TS_INTERFACE	/ds2/oracle/VIS/db/apps_st/data/apps_ts_interface27.	0	0			5	0	0	0.00
APPS_TS_INTERFACE	/ds2/oracle/VIS/db/apps_st/data/apps_ts_interface28.	0	0			1	0	0	0.00
APPS_TS_QUEUES	/ds2/oracle/VIS/db/apps_st/data/queues1.dbf	0	0			4	0	0	0.00
APPS_TS_QUEUES	/ds2/oracle/VIS/db/apps_st/data/queues2.dbf	0	0			3	0	0	0.00
APPS_TS_QUEUES	/ds2/oracle/VIS/db/apps_st/data/queues7.dbf	0	0			7	0	0	0.00
APPS_TS_SEED	/ds2/oracle/VIS/db/apps_st/data/reference1.dbf	0	0			18	0	0	0.00
APPS_TS_SEED	/ds2/oracle/VIS/db/apps_st/data/reference2.dbf	0	0			1	0	0	0.00
APPS_TS_SEED	/ds2/oracle/VIS/db/apps_st/data/reference3.dbf	0	0			4	0	0	0.00
APPS_TS_SEED	/ds2/oracle/VIS/db/apps_st/data/reference4.dbf	0	0			2	0	0	0.00
APPS_TS_SEED	/ds2/oracle/VIS/db/apps_st/data/reference5.dbf	0	0			1	0	0	0.00
APPS_TS_SUMMARY	/ds2/oracle/VIS/db/apps_st/data/summary4.dbf	0	0			10	0	0	0.00
APPS_TS_SUMMARY	/ds2/oracle/VIS/db/apps_st/data/summary5.dbf	0	0			3	0	0	0.00
APPS_TS_SUMMARY	/ds2/oracle/VIS/db/apps_st/data/summary8.dbf	0	0			1	0	0	0.00
APPS_TS_SUMMARY	/ds2/oracle/VIS/db/apps_st/data/summary9.dbf	0	0			1	0	0	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data10.dbf	0	0			2	0	106	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data11.dbf	0	0			12	0	138	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data12.dbf	0	0			9	0	90	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data13.dbf	0	0			8	0	9	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data15.dbf	0	0			2	0	21	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data16.dbf	0	0			2	0	68	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data17.dbf	0	0			3	0	109	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data19.dbf	0	0			3	0	88	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data2.dbf	0	0			10	0	46	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data21.dbf	0	0			10	0	20	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data22.dbf	0	0			18	0	14	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data24.dbf	0	0			8	0	76	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data27.dbf	0	0			9	0	106	0.09
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data28.dbf	0	0			24	0	0	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data29.dbf	0	0			26	0	593	0.12
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data31.dbf	0	0			8	0	0	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data32.dbf	0	0			31	0	369	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data33.dbf	0	0			10	0	395	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data34.dbf	0	0			6	0	201	0.05
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data35.dbf	0	0			8	0	945	0.06
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data36.dbf	0	0			6	0	890	0.03
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data37.dbf	0	0			5	0	598	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data38.dbf	0	0			4	0	699	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data39.dbf	0	0			15	0	944	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data40.dbf	0	0			2	0	104	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data41.dbf	0	0			2	0	136	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data42.dbf	0	0			2	0	119	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data43.dbf	0	0			16	0	771	0.05
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data44.dbf	0	0			14	0	715	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data45.dbf	0	0			13	0	112	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data46.dbf	0	0			14	0	50	0.20
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data47.dbf	0	0			12	0	28	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data48.dbf	0	0			11	0	41	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data49.dbf	0	0			11	0	22	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data50.dbf	0	0			8	0	36	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data51.dbf	0	0			17	0	70	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data52.dbf	0	0			4	0	21	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data53.dbf	0	0			17	0	36	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data54.dbf	0	0			14	0	27	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data55.dbf	0	0			22	0	19	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data56.dbf	0	0			18	0	15	0.67
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data57.dbf	0	0			16	0	30	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data58.dbf	0	0			13	0	41	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data59.dbf	0	0			8	0	48	0.21
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data60.dbf	0	0			7	0	5	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data61.dbf	0	0			11	0	60	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data62.dbf	0	0			15	0	9	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data63.dbf	0	0			10	0	84	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data64.dbf	0	0			12	0	73	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data65.dbf	0	0			12	0	208	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data66.dbf	0	0			15	0	174	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data67.dbf	0	0			9	0	221	0.05
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data68.dbf	0	0			19	0	149	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data69.dbf	0	0			17	0	180	0.06
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data7.dbf	0	0			7	0	74	0.14
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data70.dbf	0	0			24	0	113	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data71.dbf	0	0			13	0	171	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data72.dbf	0	0			18	0	183	0.05
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data73.dbf	0	0			19	0	258	0.04
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data74.dbf	0	0			24	0	181	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data75.dbf	0	0			19	0	79	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data76.dbf	0	0			18	0	67	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data77.dbf	0	0			14	0	95	0.00
APPS_TS_TX_DATA	/ds2/oracle/VIS/db/apps_st/data/tx_data78.dbf	0	0			11	0	108	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx1.dbf	0	0			11	0	1,805	0.02
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx10.dbf	0	0			12	0	762	0.07
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx11.dbf	0	0			10	0	826	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx12.dbf	0	0			11	0	867	0.05
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx13.dbf	0	0			8	0	852	0.01
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx14.dbf	0	0			7	0	607	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx15.dbf	0	0			16	0	1,208	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx16.dbf	0	0			10	0	526	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx17.dbf	0	0			10	0	133	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx18.dbf	0	0			10	0	72	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx19.dbf	0	0			6	0	27	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx2.dbf	0	0			25	0	196	0.05
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx20.dbf	3	0	0.00	1.00	5	0	297	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx21.dbf	0	0			6	0	214	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx22.dbf	0	0			4	0	298	0.10
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx23.dbf	0	0			26	0	66	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx24.dbf	1	0	10.00	1.00	3	0	0	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx25.dbf	0	0			13	0	216	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx26.dbf	0	0			17	0	314	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx27.dbf	0	0			12	0	180	0.11
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx29.dbf	0	0			4	0	188	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx3.dbf	0	0			8	0	168	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx30.dbf	0	0			5	0	338	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx31.dbf	4	0	5.00	1.00	26	0	384	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx32.dbf	0	0			21	0	536	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx33.dbf	0	0			12	0	396	0.05
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx34.dbf	0	0			12	0	380	0.08
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx35.dbf	0	0			8	0	358	0.08
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx36.dbf	0	0			18	0	236	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx37.dbf	0	0			19	0	112	0.45
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx38.dbf	0	0			17	0	148	0.14
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx39.dbf	0	0			16	0	577	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx4.dbf	0	0			17	0	174	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx40.dbf	0	0			20	0	248	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx41.dbf	0	0			13	0	279	0.07
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx42.dbf	0	0			24	0	458	0.02
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx43.dbf	0	0			25	0	640	0.05
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx44.dbf	0	0			19	0	936	0.02
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx45.dbf	0	0			24	0	939	0.09
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx46.dbf	0	0			14	0	937	0.02
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx47.dbf	0	0			15	0	866	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx48.dbf	0	0			20	0	669	0.01
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx49.dbf	0	0			21	0	398	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx5.dbf	0	0			2	0	0	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx6.dbf	0	0			12	0	672	0.01
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx7.dbf	0	0			11	0	742	0.01
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx8.dbf	0	0			1	0	4	0.00
APPS_TS_TX_IDX	/ds2/oracle/VIS/db/apps_st/data/tx_idx9.dbf	0	0			4	0	22	0.00
APPS_UNDOTS1	/ds2/oracle/VIS/db/apps_st/data/undo.dbf	0	0			1,322	4	25,348	0.01
SYSAUX	/ds2/oracle/VIS/db/apps_st/data/sysaux01.dbf	42	0	3.57	1.00	1,218	3	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys1.dbf	0	0			55	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys10.dbf	0	0			114	0	22	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys2.dbf	0	0			36	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys3.dbf	0	0			87	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys4.dbf	0	0			68	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys5.dbf	0	0			32	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys6.dbf	0	0			41	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys7.dbf	0	0			70	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys8.dbf	0	0			110	0	0	0.00
SYSTEM	/ds2/oracle/VIS/db/apps_st/data/sys9.dbf	0	0			100	0	0	0.00

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

P	Number of Buffers	Pool Hit%	Buffer Gets	Physical Reads	Physical Writes	Free Buff Wait	Writ Comp Wait	Buffer Busy Waits
D	1,009,287	100	142,028,746	56	27,230	0	0	58,129

Back to Top

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory

Back to Top

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

	Targt MTTR (s)	Estd MTTR (s)	Recovery Estd IOs	Actual Redo Blks	Target Redo Blks	Log File Size Redo Blks	Log Ckpt Timeout Redo Blks	Log Ckpt Interval Redo Blks
B	0	222	101829	3775193	3774870	3774870		100000000
E	0	228	113412	3774931	3774870	3774870		100000000

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

P	Size for Est (M)	Size Factor	Buffers for Estimate	Est Phys Read Factor	Estimated Physical Reads
D	816	0.10	100,929	1.09	43,612,136
D	1,632	0.20	201,858	1.06	42,668,524
D	2,448	0.30	302,787	1.05	42,132,667
D	3,264	0.40	403,716	1.01	40,720,926
D	4,080	0.50	504,645	1.01	40,563,582
D	4,896	0.60	605,574	1.01	40,483,001
D	5,712	0.70	706,503	1.01	40,402,392
D	6,528	0.80	807,432	1.00	40,311,152
D	7,344	0.90	908,361	1.00	40,241,041
D	8,160	1.00	1,009,290	1.00	40,159,218
D	8,976	1.10	1,110,219	1.00	40,106,477
D	9,792	1.20	1,211,148	1.00	40,061,852
D	10,608	1.30	1,312,077	1.00	40,017,534
D	11,424	1.40	1,413,006	0.99	39,903,177
D	12,240	1.50	1,513,935	0.99	39,607,745
D	13,056	1.60	1,614,864	0.98	39,227,201
D	13,872	1.70	1,715,793	0.97	39,026,298
D	14,688	1.80	1,816,722	0.96	38,575,993
D	15,504	1.90	1,917,651	0.96	38,359,592
D	16,320	2.00	2,018,580	0.95	38,095,772

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %	W/A MB Processed	Extra W/A MB Read/Written
100.00	12,035	0

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

	PGA Aggr Target(M)	Auto PGA Target(M)	PGA Mem Alloc(M)	W/A PGA Used(M)	%PGA W/A Mem	%Auto W/A Mem	%Man W/A Mem	Global Mem Bound(K)
B	6,144	5,377	332.13	0.48	0.14	100.00	0.00	629,140
E	6,144	5,377	295.58	0.48	0.16	100.00	0.00	629,140

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Low Optimal	High Optimal	Total Execs	Optimal Execs	1-Pass Execs	M-Pass Execs
2K	4K	450,226	450,226	0	0
64K	128K	7	7	0	0
256K	512K	22	22	0	0
512K	1024K	229	229	0	0
1M	2M	10,084	10,084	0	0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

PGA Target Est (MB)	Size Factr	W/A MB Processed	Estd Extra W/A MB Read/ Written to Disk	Estd PGA Cache Hit %	Estd PGA Overalloc Count
768	0.13	417,800.20	3,354.92	99.00	0
1,536	0.25	417,800.20	2,962.07	99.00	0
3,072	0.50	417,800.20	2,962.07	99.00	0
4,608	0.75	417,800.20	2,962.07	99.00	0
6,144	1.00	417,800.20	2,962.07	99.00	0
7,373	1.20	417,800.20	0.00	100.00	0
8,602	1.40	417,800.20	0.00	100.00	0
9,830	1.60	417,800.20	0.00	100.00	0
11,059	1.80	417,800.20	0.00	100.00	0
12,288	2.00	417,800.20	0.00	100.00	0
18,432	3.00	417,800.20	0.00	100.00	0
24,576	4.00	417,800.20	0.00	100.00	0
36,864	6.00	417,800.20	0.00	100.00	0
49,152	8.00	417,800.20	0.00	100.00	0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Shared Pool Size(M)	SP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
544	0.27	242	10,987	1,148,423	0.98	28,614	23.06	87,969,424
752	0.38	447	18,287	1,151,079	0.98	25,958	20.92	87,987,851
960	0.48	654	25,568	1,156,790	0.98	20,247	16.32	87,993,107
1,168	0.58	860	32,519	1,161,481	0.99	15,556	12.54	88,000,024
1,376	0.69	1,067	41,030	1,163,137	0.99	13,900	11.20	88,010,282
1,584	0.79	1,274	50,545	1,172,419	1.00	4,618	3.72	88,016,861
1,792	0.90	1,480	59,082	1,174,411	1.00	2,626	2.12	88,020,639
2,000	1.00	1,687	66,092	1,175,796	1.00	1,241	1.00	88,031,540
2,208	1.10	1,894	73,184	1,176,355	1.00	682	0.55	88,042,212
2,416	1.21	2,101	81,266	1,178,248	1.00	1	0.00	88,050,386
2,624	1.31	2,308	91,094	1,178,404	1.00	1	0.00	88,053,212
2,832	1.42	2,515	100,005	1,178,419	1.00	1	0.00	88,060,904
3,040	1.52	2,722	112,149	1,178,427	1.00	1	0.00	88,066,497
3,248	1.62	2,929	120,751	1,178,850	1.00	1	0.00	88,089,893
3,456	1.73	3,136	131,392	1,183,456	1.01	1	0.00	88,115,610
3,664	1.83	3,342	140,111	1,183,889	1.01	1	0.00	88,135,998
3,872	1.94	3,549	146,932	1,183,941	1.01	1	0.00	88,162,292
4,080	2.04	3,756	154,210	1,184,573	1.01	1	0.00	88,178,871

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)	SGA Size Factor	Est DB Time (s)	Est Physical Reads
2,560	0.25	140,538	43,524,537
5,120	0.50	134,163	42,046,678
7,680	0.75	132,262	40,400,151
10,240	1.00	131,985	40,159,196
12,800	1.25	131,721	39,934,305
15,360	1.50	130,810	39,147,184
17,920	1.75	129,807	38,279,746
20,480	2.00	129,504	38,018,711

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Size for Est (MB)	Size Factor	Est Spill Count	Est Spill Time (s)	Est Unspill Count	Est Unspill Time (s)
16	1.00	0	0	0	0
32	2.00	0	0	0	0
48	3.00	0	0	0	0
64	4.00	0	0	0	0
80	5.00	0	0	0	0
96	6.00	0	0	0	0
112	7.00	0	0	0	0
128	8.00	0	0	0	0
144	9.00	0	0	0	0
160	10.00	0	0	0	0
176	11.00	0	0	0	0
192	12.00	0	0	0	0
208	13.00	0	0	0	0
224	14.00	0	0	0	0
240	15.00	0	0	0	0
256	16.00	0	0	0	0
272	17.00	0	0	0	0
288	18.00	0	0	0	0
304	19.00	0	0	0	0
320	20.00	0	0	0	0

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Back to Top

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
data block	32,756	1	0
undo block	23,940	0	0
undo header	1,408	0	0
1st level bmb	22	0	0

Back to Wait Statistics
Back to Top

Enqueue Activity

• only enqueues with waits are shown
• Enqueue stats gathered prior to 10g should not be compared with 10g data
• ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)	Requests	Succ Gets	Failed Gets	Waits	Wt Time (s)	Av Wt Time(ms)
UL-User-defined	3,222	3,159	63	235	18	77.19
TX-Transaction (index contention)	1,727	1,727	0	1,723	0	0.15
TX-Transaction (row lock contention)	88	77	11	71	0	1.55
SQ-Sequence Cache	561	561	0	167	0	0.24
HW-Segment High Water Mark	296	296	0	1	0	0.00
TX-Transaction (allocate ITL entry)	1	1	0	1	0	0.00

Back to Wait Statistics
Back to Top

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Back to Top

Undo Segment Summary

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Undo Segment Stats

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics

Back to Top

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss
ASM db client latch	304	0.00		0	0
AWR Alerted Metric Element list	1,957	0.00		0	0
Consistent RBA	8,379	0.01	0.00	0	0
FOB s.o list latch	1,496	5.35	0.00	0	0
In memory undo latch	48,023,421	3.66	0.00	0	15,373	0.01
JS queue state obj latch	2,280	0.00		0	0
KMG MMAN ready and startup request latch	125	0.00		0	0
KTF sga latch	1	0.00		0	109	0.00
KWQMN job cache list latch	3	0.00		0	0
KWQP Prop Status	47	0.00		0	0
MQL Tracking Latch	0			0	8	0.00
Memory Management Latch	0			0	125	0.00
OS process	315	0.00		0	0
OS process allocation	319	0.00		0	0
OS process: request allocation	194	0.52	0.00	0	0
PL/SQL warning settings	255,841	0.00		0	0
SGA IO buffer pool latch	70	0.00		0	70	1.43
SQL memory manager latch	1	0.00		0	125	0.00
SQL memory manager workarea list latch	650,194	0.01	0.00	0	0
Shared B-Tree	241	0.00		0	0
active checkpoint queue latch	17,020	4.42	0.00	0	0
active service list	1,107	0.18	0.00	0	152	0.00
begin backup scn array	7	0.00		0	0
bq:time manger info latch	13	0.00		0	0
bufq statistics	7	0.00		0	0
cache buffer handles	6,311,696	1.35	0.00	0	0
cache buffers chains	306,569,296	4.84	0.00	0	442,169	11.20
cache buffers lru chain	89,841	0.46	0.00	0	1,327,202	0.60
channel handle pool latch	364	0.00		0	0
channel operations parent latch	3,621	0.11	0.25	0	0
checkpoint queue latch	230,766	0.00	0.00	0	38,768	0.00
client/application info	1,265	0.00		0	0
commit callback allocation	6	0.00		0	0
compile environment latch	17,371	0.00		0	0
dml lock allocation	118,183	0.18	0.00	0	0
dummy allocation	210	0.95	0.00	0	0
enqueue hash chains	190,703	0.18	0.01	0	22	0.00
enqueues	36,393	0.18	0.00	0	0
event group latch	89	0.00		0	0
file cache latch	385	0.00		0	0
global KZLD latch for mem in SGA	88	0.00		0	0
global ctx hash table latch	211	0.00		0	0
hash table column usage latch	0			0	52,752	0.00
hash table modification latch	46	0.00		0	0
job_queue_processes parameter latch	6	0.00		0	0
kks stats	769	0.00		0	0
ksuosstats global area	27	0.00		0	0
ktm global data	176	0.00		0	0
kwqbsn:qsga	224	0.00		0	0
lgwr LWN SCN	8,460	0.27	0.04	0	0
library cache	18,319,361	0.76	0.00	0	40	0.00
library cache lock	9,768,841	1.54	0.00	0	0
library cache lock allocation	8,685	0.00		0	0
library cache pin	7,663,983	0.57	0.00	0	0
library cache pin allocation	3,049	0.00		0	0
list of block allocation	12,311	0.02	0.00	0	0
loader state object freelist	200	0.50	0.00	0	0
logminer context allocation	1	0.00		0	0
longop free list parent	79	0.00		0	0
message pool operations parent latch	340	0.00		0	0
messages	77,801	0.26	0.00	0	0
mostly latch-free SCN	8,722	2.76	0.02	0	0
ncodef allocation latch	8	0.00		0	0
object queue header heap	68,404	0.00		0	1,393	0.00
object queue header operation	299,262	0.02	0.02	0	0
object stats modification	291	0.00		0	0
parallel query alloc buffer	52	0.00		0	0
parameter list	293	0.00		0	0
parameter table allocation management	115	3.48	0.00	0	0
post/wait queue	14,442	0.03	0.00	0	6,747	0.01
process allocation	194	0.00		0	89	0.00
process group creation	194	0.00		0	0
process queue	16	0.00		0	0
qmn task queue latch	467	7.92	0.00	0	0
query server freelists	16	6.25	0.00	0	0
query server process	0			0	16	0.00
redo allocation	59,911	0.39	0.00	0	2,547,760	0.49
redo copy	0			0	2,548,421	0.01
redo writing	45,600	0.76	0.00	0	0
resmgr group change latch	533	0.00		0	0
resmgr:actses active list	299	0.67	0.00	0	0
resmgr:actses change group	265	0.00		0	0
resmgr:free threads list	210	0.95	0.00	0	0
resmgr:schema config	1	0.00		0	0
row cache objects	2,314,828	1.47	0.00	0	28	0.00
rules engine rule set statistics	100	0.00		0	0
sequence cache	1,172,096	0.82	0.00	0	0
session allocation	910,956	2.23	0.00	0	0
session idle bit	1,926,427	0.01	0.01	0	0
session state list latch	566	1.06	0.00	0	0
session switching	8	0.00		0	0
session timer	152	0.00		0	0
shared pool	72,068	0.03	0.00	0	0
shared pool simulator	12,988	0.00		0	0
simulator hash latch	6,670,722	0.00	0.00	0	0
simulator lru latch	6,664,375	2.32	0.00	0	2,089	0.00
sort extent pool	1,200	0.83	0.00	0	0
state object free list	2	0.00		0	0
statistics aggregation	84	0.00		0	0
temporary table state object allocation	170	0.59	0.00	0	0
threshold alerts latch	7	0.00		0	0
transaction allocation	4,275	0.02	0.00	0	0
transaction branch allocation	8	0.00		0	0
undo global data	52,391,606	0.58	0.00	0	0
user lock	352	0.00		0	0

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

• ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets	Sleep1	Sleep2	Sleep3
cache buffers chains	306,569,296	14,823,228	47,445	14,777,297	0	0	0
In memory undo latch	48,023,421	1,756,120	2,226	1,754,338	0	0	0
undo global data	52,391,606	302,245	110	302,172	0	0	0
simulator lru latch	6,664,375	154,842	80	154,788	0	0	0
library cache lock	9,768,841	150,186	16	150,172	0	0	0
library cache	18,319,361	139,428	54	139,386	0	0	0
cache buffer handles	6,311,696	85,365	17	85,349	0	0	0
library cache pin	7,663,983	43,883	8	43,878	0	0	0
row cache objects	2,314,828	33,934	2	33,932	0	0	0
session allocation	910,956	20,269	2	20,267	0	0	0
sequence cache	1,172,096	9,651	8	9,645	0	0	0
cache buffers lru chain	89,841	411	1	410	0	0	0
enqueue hash chains	190,703	344	4	340	0	0	0
session idle bit	1,926,427	258	2	257	0	0	0
mostly latch-free SCN	8,722	241	4	237	0	0	0
object queue header operation	299,262	59	1	58	0	0	0
lgwr LWN SCN	8,460	23	1	22	0	0	0
channel operations parent latch	3,621	4	1	3	0	0	0

Back to Latch Statistics
Back to Top

Latch Miss Sources

• only latches with sleeps are shown
• ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
In memory undo latch	ktiFlush: child	0	1,177	291
In memory undo latch	kturbk	0	698	1,106
In memory undo latch	kticmt: child	0	196	4
In memory undo latch	ktichg: child	0	88	810
In memory undo latch	ktiTxnPoolFree	0	64	0
In memory undo latch	ktbgfc	0	3	15
cache buffer handles	kcbzfs	0	11	8
cache buffer handles	kcbzgs	0	6	9
cache buffers chains	kcbchg: kslbegin: bufs not pinned	0	189,875	196,063
cache buffers chains	kcbgtcr: fast path	0	55,525	995
cache buffers chains	kcbchg: kslbegin: call CR func	0	52,635	115,947
cache buffers chains	kcbgtcr: kslbegin excl	0	16,667	3,432
cache buffers chains	kcbgcur: kslbegin	0	2,018	517
cache buffers chains	kcbget: pin buffer	0	1,579	579
cache buffers chains	kcbzwb	0	926	545
cache buffers chains	kcbrls: kslbegin	0	792	2,570
cache buffers chains	kcbzgb: scan from tail. nowait	0	741	0
cache buffers chains	kcbcge	0	257	786
cache buffers chains	kcbnlc	0	18	62
cache buffers chains	kcb_is_private	0	10	111
cache buffers chains	kcb_pre_apply: kcbhq61	0	7	174
cache buffers chains	kcb_post_apply: kcbhq63	0	6	14
cache buffers chains	kcbbxsv	0	4	4
cache buffers chains	kcb_post_apply: kcbhq62	0	3	7
cache buffers chains	kcbget: exchange rls	0	1	1
cache buffers lru chain	kcbbxsv: move to being written	0	1	1
channel operations parent latch	ksrapublish()	0	1	0
enqueue hash chains	ksqrcl	0	3	1
enqueue hash chains	ksqgtl3	0	1	0
lgwr LWN SCN	kcs023	0	3	0
library cache	kglpnp: child	0	22	28
library cache	kglpndl: child: after processing	0	15	0
library cache	kglLockCursor	0	6	7
library cache	kglpndl: child: before processing	0	4	9
library cache	kglobpn: child:	0	2	8
library cache	kglpin	0	2	1
library cache lock	kgllkal: child: multiinstance	0	9	9
library cache lock	kgllkdl: child: cleanup	0	7	7
library cache lock	kgllkdl: child: no lock handle	0	3	0
library cache pin	kglpndl	0	5	3
library cache pin	kglpnp: child	0	3	5
mostly latch-free SCN	kcs024	0	1	0
mostly latch-free SCN	kcslcu3	0	1	5
object queue header operation	kcbw_unlink_q	0	1	0
row cache objects	kqreqd: reget	0	1	0
row cache objects	kqrpre: find obj	0	1	1
sequence cache	kdnssd	0	4	0
sequence cache	kdnnxt: cached seq	0	3	0
sequence cache	kdnss	0	1	8
session allocation	ksudlc	0	1	1
session allocation	ksuprc	0	1	1
session idle bit	ksupuc: set busy	0	2	0
simulator lru latch	kcbs_simulate: simulate set	0	78	80
simulator lru latch	kcbs_simbufseq	0	2	0
undo global data	kturimugur: child	0	93	40
undo global data	ktudba: KSLBEGIN	0	17	70

Back to Latch Statistics
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Row Lock Waits
• Segments by ITL Waits
• Segments by Buffer Busy Waits

Back to Top

Segments by Logical Reads

• Total Logical Reads: 142,367,142
• Captured Segments account for 66.4% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_PK		INDEX	11,994,736	8.43
HR	APPS_TS_TX_IDX	PAY_PAYROLL_ACTIONS_PK		INDEX	8,602,912	6.04
HR	APPS_TS_TX_DATA	PER_TIME_PERIODS		TABLE	8,528,448	5.99
HR	APPS_TS_TX_DATA	PAY_ASSIGNMENT_ACTIONS		TABLE	8,089,824	5.68
HR	APPS_TS_TX_DATA	PAY_PAYROLL_ACTIONS		TABLE	6,010,208	4.22

Back to Segment Statistics
Back to Top

Segments by Physical Reads

• Total Physical Reads: 72
• Captured Segments account for 2.8% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
SYS	SYSAUX	WRH$_LATCH_MISSES_SUMMARY	883651_130	TABLE PARTITION	1	1.39
SYS	SYSAUX	WRH$_SQLSTAT_PK	883651_130	INDEX PARTITION	1	1.39

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

• % of Capture shows % of row lock waits for each top segment compared
• with total row lock waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Row Lock Waits	% of Capture
HR	APPS_TS_TX_IDX	PAY_ACTION_INTERLOCKS_PK		INDEX	240	12.13
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_N50		INDEX	217	10.97
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_PK		INDEX	204	10.31
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_N2		INDEX	144	7.28
HR	APPS_TS_TX_IDX	PAY_RUN_BALANCES_N2		INDEX	141	7.13

Back to Segment Statistics
Back to Top

Segments by ITL Waits

• % of Capture shows % of ITL waits for each top segment compared
• with total ITL waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	ITL Waits	% of Capture
HR	APPS_TS_TX_IDX	PAY_ACTION_INFORMATION_N2		INDEX	1	100.00

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

• % of Capture shows % of Buffer Busy Waits for each top segment compared
• with total Buffer Busy Waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Buffer Busy Waits	% of Capture
HR	APPS_TS_TX_DATA	PAY_ASSIGNMENT_ACTIONS		TABLE	4,929	15.09
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_PK		INDEX	4,078	12.48
HR	APPS_TS_TX_IDX	PAY_ACTION_INFORMATION_PK		INDEX	2,540	7.78
HR	APPS_TS_TX_DATA	PAY_POPULATION_RANGES		TABLE	2,111	6.46
HR	APPS_TS_TX_IDX	PAY_ASSIGNMENT_ACTIONS_N50		INDEX	1,498	4.59

Back to Segment Statistics
Back to Top

Dictionary Cache Stats

• "Pct Misses" should be very low (< 2% in most cases)
• "Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Scan Reqs	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	10	0.00	0		2	1
dc_global_oids	411	0.00	0		0	52
dc_histogram_data	1,194	0.00	0		0	6,295
dc_histogram_defs	5,253	0.00	0		0	250,490
dc_object_grants	24	0.00	0		0	103
dc_object_ids	81,356	0.00	0		0	54,633
dc_objects	1,361	0.00	0		0	42,968
dc_profiles	88	0.00	0		0	1
dc_rollback_segments	341	0.00	0		0	66
dc_segments	14,328	0.00	0		18	34,484
dc_sequences	392	0.00	0		392	17
dc_table_scns	2	0.00	0		0	1
dc_tablespace_quotas	16	0.00	0		0	3
dc_tablespaces	331,940	0.00	0		0	55
dc_usernames	1,199	0.00	0		0	172
dc_users	335,563	0.00	0		0	133

Back to Top

Library Cache Activity

• "Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	2,371	0.00	1,653,358	-0.00	0	0
PIPE	54	0.00	54	0.00	0	0
SQL AREA	6	0.00	14,601,206	-0.18	268	0
TABLE/PROCEDURE	4,271	0.00	2,059,920	-0.00	0	0
TRIGGER	198	0.00	216,917	-0.01	0	0

Back to Top

Memory Statistics

• Process Memory Summary
• SGA Memory Summary
• SGA breakdown difference

Back to Top

Process Memory Summary

• B: Begin snap E: End snap
• All rows below contain absolute values (i.e. not diffed over the interval)
• Max Alloc is Maximum PGA Allocation size at snapshot time
• Hist Max Alloc is the Historical Max Allocation for still-connected processes
• ordered by Begin/End snapshot, Alloc (MB) desc

	Category	Alloc (MB)	Used (MB)	Avg Alloc (MB)	Std Dev Alloc (MB)	Max Alloc (MB)	Hist Max Alloc (MB)	Num Proc	Num Alloc
B	Other	223.52		2.13	3.15	22	31	105	105
	Freeable	80.50	0.00	1.10	1.87	10		73	73
	SQL	22.01	10.71	0.22	0.36	2	73	99	79
	PL/SQL	6.15	2.61	0.06	0.05	0	0	105	89
E	Other	202.19		2.27	3.17	22	31	89	89
	Freeable	65.38	0.00	1.21	2.16	10		54	54
	SQL	21.89	10.59	0.26	0.38	2	73	83	79
	PL/SQL	6.16	2.44	0.07	0.05	0	0	89	89

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regions	Begin Size (Bytes)	End Size (Bytes) (if different)
Database Buffers	8,556,380,160
Fixed Size	2,090,528
Redo Buffers	31,461,376
Variable Size	2,147,486,176

Back to Memory Statistics
Back to Top

SGA breakdown difference

• ordered by Pool, Name
• N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot

Pool	Name	Begin MB	End MB	% Diff
java	free memory	16.00	16.00	0.00
large	PX msg pool	0.56	0.49	-11.77
large	free memory	15.44	15.51	0.43
shared	ASH buffers	29.00	29.00	0.00
shared	CCursor	55.44	56.44	1.82
shared	Heap0: KGL	42.75	43.01	0.61
shared	KGLS heap	173.62	173.59	-0.02
shared	KQR L PO	42.04	42.02	-0.04
shared	KQR M PO	166.42	166.31	-0.07
shared	PCursor	36.90	37.04	0.39
shared	db_block_hash_buckets	45.00	45.00	0.00
shared	free memory	1,034.97	1,019.17	-1.53
shared	kglsim object batch	21.69	21.69	0.00
shared	library cache	104.61	104.65	0.04
shared	obj stat memo	28.83	28.83	0.00
shared	sql area	36.34	50.69	39.48
streams	KGH: NO ACCESS	15.98	15.98	0.00
streams	free memory	16.00	16.00	0.00
	buffer_cache	8,160.00	8,160.00	0.00
	fixed_sga	1.99	1.99	0.00
	log_buffer	30.00	30.00	0.00

Back to Memory Statistics
Back to Top

Streams Statistics

• Streams CPU/IO Usage
• Streams Capture
• Streams Apply
• Buffered Queues
• Buffered Subscribers
• Rule Set

Back to Top

Streams CPU/IO Usage

• Streams processes ordered by CPU usage
• CPU and I/O Time in micro seconds

Session Type	CPU Time	User I/O Time	Sys I/O Time
QMON Coordinator	7,423	0	0
QMON Slaves	7,269	0	0

Back to Streams Statistics
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Queues

• The Spill Rate should be very close to zero
• The Diff in Percentage Spilled should be very close to zero or negative

Queue Schema and Name	Incoming per second	Outgoing per second	Spilled per second	Diff Pct Spilled
APPLSYS.WF_CONTROL	0	0	0	0

Back to Streams Statistics
Back to Top

Buffered Subscribers

• All Subscribers should have a zero spill rate

Subscriber Name	Incoming per second	Outgoing per second	Spilled per second
WFBESCL_20610	0	0	0
WFBESCL_20636	0	0	0
WFBESCL_20641	0	0	0
WFBESCL_20652	0	0	0
WFBESCL_20654	0	0	0

Back to Streams Statistics
Back to Top

Rule Set

• Rule Sets ordered by Evaluations

Ruleset Name	Evals	Fast Evals	SQL Execs	CPU Time	Elapsed Time
SYS.ALERT_QUE_R	0	0	0	0	0

Back to Streams Statistics
Back to Top

Resource Limit Stats

No data exists for this section of the report.

Back to Top

init.ora Parameters

Parameter Name	Begin value	End value (if different)
_b_tree_bitmap_plans	FALSE
_fast_full_scan_enabled	FALSE
_like_with_bind_as_equality	TRUE
_sort_elimination_cost_ratio	5
_sqlexec_progression_cost	2147483647
_system_trig_enabled	TRUE
_trace_files_public	TRUE
aq_tm_processes	1
background_dump_dest	/ds2/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/bdump
compatible	10.2.0.3
core_dump_dest	/ds2/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/cdump
cursor_sharing	EXACT
db_block_checking	FALSE
db_block_checksum	TRUE
db_block_size	8192
db_file_multiblock_read_count	8
db_files	512
db_name	VIS
dml_locks	10000
ifile	/ds2/oracle/VIS/db/tech_st/10.2.0/dbs/VIS_r12db_ifile.ora
job_queue_processes	2
log_buffer	30507008
log_checkpoint_interval	100000000
log_checkpoint_timeout	999999999
log_checkpoints_to_alert	TRUE
max_dump_file_size	20480
nls_comp	binary
nls_date_format	DD-MON-RR
nls_language	american
nls_length_semantics	BYTE
nls_numeric_characters	.,
nls_sort	binary
nls_territory	america
olap_page_pool_size	4194304
open_cursors	600
optimizer_secure_view_merging	FALSE
parallel_max_servers	16
parallel_min_servers	0
pga_aggregate_target	6442450944
plsql_code_type	INTERPRETED
plsql_native_library_dir	/ds2/oracle/VIS/db/tech_st/10.2.0/plsql/nativelib
plsql_native_library_subdir_count	149
plsql_optimize_level	2
processes	600
session_cached_cursors	500
sessions	1200
sga_target	10737418240
shared_pool_size	2097152000
timed_statistics	TRUE
undo_management	AUTO
undo_retention	47400
undo_tablespace	APPS_UNDOTS1
user_dump_dest	/ds2/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/udump
utl_file_dir	/usr/tmp, /ds2/oracle/VIS/db/tech_st/10.2.0/appsutil/outbound/VIS_r12db, /usr/tmp
workarea_size_policy	AUTO

Back to Top

End of Report