	\| Worldwide
	Secure Search
PRODUCTS AND SERVICES INDUSTRIES SUPPORT PARTNERS COMMUNITIES ABOUT

ORACLE CORPORATION

Legal | Privacy

AWR Report

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst num Release RAC Host

VIS 192418558 VISA 1 10.2.0.3.0 YES oracle16.engr.sgi.com

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 1 08-Jan-08 14:55:19 851 126.5

End Snap: 3 08-Jan-08 16:00:44 307 171.1

Elapsed: 65.42 (mins)

DB Time: 421.28 (mins)

Report Summary

Cache Sizes

Begin End

Buffer Cache: 8,000M 8,000M Std Block Size: 8K

Shared Pool Size: 3,008M 3,008M Log Buffer: 30,736K

Load Profile

Per Second Per Transaction

Redo size: 7,037,834.81 60,895.25

Logical reads: 265,245.80 2,295.05

Block changes: 41,662.98 360.49

Physical reads: 181.94 1.57

Physical writes: 1,078.06 9.33

User calls: 2,056.39 17.79

Parses: 1,033.71 8.94

Hard parses: 1.05 0.01

Sorts: 1,375.16 11.90

Logons: 1.58 0.01

Executes: 12,354.89 106.90

Transactions: 115.57

% Blocks changed per Read: 15.71 Recursive Call %: 91.01

Rollback per transaction %: 3.76 Rows per Sort: 10.82

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.93 Redo NoWait %: 100.00

Buffer Hit %: 99.96 In-memory Sort %: 100.00

Library Hit %: 100.02 Soft Parse %: 99.90

Execute to Parse %: 91.63 Latch Hit %: 98.35

Parse CPU to Parse Elapsd %: 80.40 % Non-Parse CPU: 97.63

Shared Pool Statistics

Begin End

Memory Usage %: 40.96 54.61

% SQL with executions>1: 60.94 83.94

% Memory for SQL w/exec>1: 55.87 51.86

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class

CPU time 21,902 86.6

gc buffer busy 51,345 669 13 2.6 Cluster

latch: cache buffers chains 2,736,027 384 0 1.5 Concurrency

enq: HW - contention 98,124 306 3 1.2 Configuration

log file sync 109,534 224 2 .9 Commit

RAC Statistics

Begin End

Number of Instances: 2 2

Global Cache Load Profile

Per Second Per Transaction

Global Cache blocks received: 118.14 1.02

Global Cache blocks served: 67.73 0.59

GCS/GES messages received: 566.04 4.90

GCS/GES messages sent: 698.60 6.04

DBWR Fusion writes: 9.19 0.08

Estd Interconnect traffic (KB) 1,733.99

Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %: 99.92

Buffer access - remote cache %: 0.04

Buffer access - disk %: 0.04

Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms): 0.2

Avg global cache cr block receive time (ms): 0.9

Avg global cache current block receive time (ms): 1.0

Avg global cache cr block build time (ms): 0.2

Avg global cache cr block send time (ms): 0.1

Global cache log flushes for cr blocks served %: 23.1

Avg global cache cr block flush time (ms): 2.7

Avg global cache current block pin time (ms): 0.3

Avg global cache current block send time (ms): 0.1

Global cache log flushes for current blocks served %: 2.0

Avg global cache current block flush time (ms): 3.9

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): 0.1

Avg message sent queue time on ksxp (ms): 0.3

Avg message received queue time (ms): 0.1

Avg GCS message process time (ms): 0.0

Avg GES message process time (ms): 0.0

% of direct sent messages: 37.06

% of indirect sent messages: 30.99

% of flow controlled messages: 31.95

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

Wait Events Statistics

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

Time Model Statistics

Total time in database user-calls (DB Time): 25276.9s
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	23,799.04	94.15
DB CPU	21,901.55	86.65
PL/SQL execution elapsed time	3,297.75	13.05
Java execution elapsed time	2,784.65	11.02
inbound PL/SQL rpc elapsed time	1,825.67	7.22
parse time elapsed	676.88	2.68
hard parse elapsed time	466.25	1.84
sequence load elapsed time	97.64	0.39
connection management call elapsed time	19.14	0.08
repeated bind elapsed time	17.80	0.07
PL/SQL compilation elapsed time	4.34	0.02
hard parse (sharing criteria) elapsed time	4.26	0.02
hard parse (bind mismatch) elapsed time	1.47	0.01
failed parse elapsed time	0.04	0.00
DB time	25,276.87
background elapsed time	1,513.37
background cpu time	950.57

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
Cluster	893,678	0.07	1,220	1	1.97
Concurrency	3,585,254	0.27	810	0	7.90
Other	1,145,084	33.92	563	0	2.52
Configuration	214,442	41.70	351	2	0.47
System I/O	710,666	0.00	313	0	1.57
User I/O	184,902	0.00	266	1	0.41
Application	43,542	0.78	259	6	0.10
Commit	109,534	0.00	224	2	0.24
Network	9,632,183	0.00	53	0	21.23

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
gc buffer busy	51,345	1.13	669	13	0.11
latch: cache buffers chains	2,736,027	0.00	384	0	6.03
enq: HW - contention	98,124	0.02	306	3	0.22
log file sync	109,534	0.00	224	2	0.24
db file sequential read	151,301	0.00	224	1	0.33
enq: UL - contention	35,409	0.77	216	6	0.08
gc cr block busy	163,091	0.00	185	1	0.36
log file parallel write	452,103	0.00	185	0	1.00
DFS lock handle	90,309	0.00	138	2	0.20
db file parallel write	246,746	0.00	125	1	0.54
enq: TX - index contention	31,566	0.07	112	4	0.07
gc cr block 2-way	176,558	0.00	103	1	0.39
wait for scn ack	336,739	0.00	103	0	0.74
cursor: pin S wait on X	10,458	90.82	102	10	0.02
buffer busy waits	678,037	0.00	100	0	1.49
gcs log flush sync	70,191	18.81	92	1	0.15
gc cr multi block request	52,096	0.00	64	1	0.11
enq: FB - contention	44,081	0.01	61	1	0.10
gc current block 2-way	94,292	0.00	58	1	0.21
row cache lock	115,740	0.00	54	0	0.26
enq: SQ - contention	26,432	0.00	43	2	0.06
gc current grant busy	33,795	0.00	39	1	0.07
enq: TX - contention	7,039	0.07	34	5	0.02
gc current multi block request	211,563	0.00	32	0	0.47
SQL*Net more data to client	884,234	0.00	32	0	1.95
library cache lock	2,466	0.49	30	12	0.01
read by other session	23,229	0.00	28	1	0.05
gc current block busy	1,802	0.00	27	15	0.00
cr request retry	28	92.86	23	838	0.00
gc cr grant 2-way	64,125	0.00	19	0	0.14
enq: TX - row lock contention	726	3.58	17	23	0.00
latch: undo global data	5,223	0.00	16	3	0.01
enq: KO - fast object checkpoint	113	25.66	16	140	0.00
library cache load lock	725	0.00	15	20	0.00
gc current grant 2-way	43,475	0.00	15	0	0.10
SQL*Net more data from client	263,230	0.00	13	0	0.58
db file scattered read	4,035	0.00	11	3	0.01
buffer deadlock	42,917	98.63	11	0	0.09
gcs drm freeze in enter server mode	41	0.00	9	213	0.00
global enqueue expand wait	1,057	75.40	9	8	0.00
SQL*Net message to client	8,484,719	0.00	8	0	18.70
latch: redo allocation	12,689	0.00	8	1	0.03
enq: RO - fast object reuse	60	18.33	7	118	0.00
latch: cache buffers lru chain	4,940	0.00	7	1	0.01
Streams AQ: qmn coordinator waiting for slave to start	1	100.00	7	6666	0.00
latch: library cache	4,689	0.00	6	1	0.01
latch free	6,826	0.00	6	1	0.02
latch: object queue header operation	5,063	0.00	5	1	0.01
kksfbc child completion	156	65.38	5	33	0.00
reliable message	405	0.25	5	12	0.00
LGWR wait for redo copy	22,790	0.17	4	0	0.05
SQL*Net break/reset to client	7,064	0.00	3	0	0.02
wait list latch free	1,509	0.00	3	2	0.00
gc recovery quiesce	10	30.00	3	293	0.00
enq: DV - contention	331	0.00	3	9	0.00
name-service call wait	45	0.00	3	56	0.00
os thread startup	25	0.00	2	99	0.00
latch: library cache pin	2,212	0.00	2	1	0.00
enq: US - contention	4,663	0.00	2	0	0.01
control file sequential read	9,993	0.00	2	0	0.02
ksxr poll remote instances	240,883	49.33	2	0	0.53
library cache pin	2,214	0.00	2	1	0.00
db file parallel read	154	0.00	2	12	0.00
gc cr block congested	671	0.00	2	2	0.00
PX Deq Credit: send blkd	3,939	36.76	1	0	0.01
gc remaster	31	0.00	1	46	0.00
control file parallel write	1,824	0.00	1	1	0.00
latch: ges resource hash list	5,415	0.00	1	0	0.01
latch: gcs resource hash	1,089	0.00	1	1	0.00
rdbms ipc reply	1,296	0.00	1	1	0.00
cursor: pin S	15,329	0.00	1	0	0.03
CGS wait for IPC msg	116,014	99.75	1	0	0.26
gc current block congested	343	0.00	1	2	0.00
enq: TX - allocate ITL entry	296	0.00	1	2	0.00
gc cr grant congested	221	0.00	0	2	0.00
PX Deq: reap credit	20,439	86.96	0	0	0.05
latch: checkpoint queue latch	275	0.00	0	1	0.00
enq: PS - contention	267	0.00	0	2	0.00
latch: row cache objects	720	0.00	0	1	0.00
latch: cache buffer handles	1,425	0.00	0	0	0.00
PX qref latch	78,093	99.87	0	0	0.17
gc current retry	17	0.00	0	22	0.00
local write wait	267	0.00	0	1	0.00
undo segment extension	89,463	99.92	0	0	0.20
gc current grant congested	177	0.00	0	2	0.00
L1 validation	43	65.12	0	7	0.00
latch: redo writing	121	0.00	0	2	0.00
enq: CU - contention	7	0.00	0	40	0.00
enq: CF - contention	153	0.00	0	2	0.00
PX Deq: Signal ACK	104	30.77	0	2	0.00
latch: KCL gc element parent latch	700	0.00	0	0	0.00
enq: WF - contention	7	0.00	0	31	0.00
enq: TQ - TM contention	115	0.00	0	2	0.00
latch: session allocation	812	0.00	0	0	0.00
enq: TM - contention	170	0.00	0	1	0.00
enq: TT - contention	150	0.00	0	1	0.00
latch: library cache lock	94	0.00	0	1	0.00
IPC send completion sync	79	89.87	0	1	0.00
ges global resource directory to be frozen	7	100.00	0	11	0.00
gc current split	19	0.00	0	4	0.00
latch: shared pool	142	0.00	0	1	0.00
latch: messages	902	0.00	0	0	0.00
enq: TQ - DDL contention	41	0.00	0	2	0.00
enq: TO - contention	39	0.00	0	1	0.00
latch: enqueue hash chains	142	0.00	0	0	0.00
enq: TD - KTF dump entries	34	0.00	0	1	0.00
enq: TS - contention	18	0.00	0	2	0.00
kjbdrmcvtq lmon drm quiesce: ping completion	7	28.57	0	4	0.00
gc cr failure	28	0.00	0	1	0.00
enq: TA - contention	38	0.00	0	1	0.00
enq: PI - contention	15	0.00	0	1	0.00
direct path read	5,738	0.00	0	0	0.01
cursor: mutex S	111	0.00	0	0	0.00
enq: DL - contention	8	0.00	0	2	0.00
lock deadlock retry	13	100.00	0	1	0.00
direct path write temp	42	0.00	0	0	0.00
KJC: Wait for msg sends to complete	86	0.00	0	0	0.00
gc current block unknown	14	0.00	0	1	0.00
cursor: pin X	1	100.00	0	11	0.00
enq: MW - contention	2	0.00	0	5	0.00
latch: object queue header heap	15	0.00	0	1	0.00
enq: AF - task serialization	1	0.00	0	5	0.00
write complete waits	6	0.00	0	1	0.00
ges2 LMON to wake up LMD - mrcvr	16	0.00	0	0	0.00
enq: DR - contention	2	0.00	0	1	0.00
enq: MD - contention	7	0.00	0	0	0.00
cursor: mutex X	28	0.00	0	0	0.00
direct path write	124	0.00	0	0	0.00
gc cr disk read	1	0.00	0	0	0.00
gc quiesce wait	4	0.00	0	0	0.00
enq: JQ - contention	1	0.00	0	0	0.00
lock escalate retry	11	63.64	0	0	0.00
latch: parallel query alloc buffer	1	0.00	0	0	0.00
direct path read temp	12	0.00	0	0	0.00
SQL*Net message from client	8,484,960	0.00	1,717,703	202	18.71
pipe get	32,656	50.93	50,883	1558	0.07
gcs remote message	2,124,071	76.74	15,014	7	4.68
Streams AQ: waiting for messages in the queue	1,718	98.60	10,174	5922	0.00
PX Idle Wait	2,425	95.22	5,731	2363	0.01
jobq slave wait	2,219	63.72	5,694	2566	0.00
Streams AQ: qmn slave idle wait	144	0.69	3,891	27021	0.00
DIAG idle wait	19,427	0.00	3,830	197	0.04
Streams AQ: qmn coordinator idle wait	278	49.64	3,819	13738	0.00
ges remote message	407,137	38.52	3,806	9	0.90
Streams AQ: waiting for time management or cleanup tasks	173	8.09	3,544	20485	0.00
PL/SQL lock timer	273	95.24	2,940	10769	0.00
PX Deq: Execution Msg	1,241	5.80	19	16	0.00
PX Deq: Execute Reply	9,215	13.83	11	1	0.02
PX Deq: Parse Reply	131	19.85	5	38	0.00
PX Deq: Msg Fragment	673	0.59	1	1	0.00
PX Deq Credit: need buffer	1,816	15.31	0	0	0.00
PX Deq: Table Q Normal	769	2.86	0	0	0.00
PX Deq: Join ACK	161	38.51	0	1	0.00
Streams AQ: RAC qmn coordinator idle wait	278	100.00	0	0	0.00
class slave wait	3	0.00	0	0	0.00

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
events in waitclass Other	815,239	30.42	231	0	1.80
log file parallel write	452,093	0.00	185	0	1.00
db file parallel write	246,746	0.00	125	1	0.54
latch: cache buffers chains	4,810	0.00	11	2	0.01
db file scattered read	1,393	0.00	5	4	0.00
control file sequential read	7,881	0.00	2	0	0.02
os thread startup	17	0.00	1	81	0.00
db file parallel read	116	0.00	1	11	0.00
control file parallel write	1,823	0.00	1	1	0.00
gc cr multi block request	3,418	0.00	1	0	0.01
db file sequential read	344	0.00	1	3	0.00
gc cr block 2-way	715	0.00	0	1	0.00
latch: redo writing	121	0.00	0	2	0.00
gc current block 2-way	497	0.00	0	1	0.00
row cache lock	273	0.00	0	1	0.00
buffer busy waits	448	0.22	0	0	0.00
gc cr grant 2-way	188	0.00	0	0	0.00
gc cr block congested	8	0.00	0	3	0.00
enq: HW - contention	27	0.00	0	1	0.00
enq: KO - fast object checkpoint	22	0.00	0	1	0.00
gc current grant busy	38	0.00	0	0	0.00
library cache lock	22	0.00	0	1	0.00
gc cr grant congested	6	0.00	0	2	0.00
enq: TM - contention	10	0.00	0	1	0.00
enq: RO - fast object reuse	7	0.00	0	1	0.00
gc current grant 2-way	16	0.00	0	0	0.00
gc current block congested	1	0.00	0	2	0.00
latch: row cache objects	19	0.00	0	0	0.00
log file sync	1	0.00	0	1	0.00
latch: shared pool	3	0.00	0	0	0.00
library cache pin	1	0.00	0	0	0.00
rdbms ipc message	1,363,390	4.59	40,808	30	3.01
gcs remote message	2,123,960	76.74	15,013	7	4.68
DIAG idle wait	19,427	0.00	3,830	197	0.04
Streams AQ: qmn coordinator idle wait	278	49.64	3,819	13738	0.00
pmon timer	3,204	95.51	3,819	1192	0.01
ges remote message	407,030	38.51	3,805	9	0.90
smon timer	115,480	0.00	3,792	33	0.25
Streams AQ: waiting for time management or cleanup tasks	173	8.09	3,544	20485	0.00
Streams AQ: qmn slave idle wait	12	0.00	332	27658	0.00
PX Deq: Join ACK	26	46.15	0	2	0.00
PX Deq: Execute Reply	30	36.67	0	1	0.00
PX Deq: Parse Reply	28	39.29	0	0	0.00
Streams AQ: RAC qmn coordinator idle wait	278	100.00	0	0	0.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic	Total
BUSY_TIME	26,427,090
IDLE_TIME	37,824,319
IOWAIT_TIME	384,391
NICE_TIME	0
SYS_TIME	2,544,662
USER_TIME	22,487,891
LOAD	2
RSRC_MGR_CPU_WAIT_TIME	0
PHYSICAL_MEMORY_BYTES	6,075,136
NUM_CPUS	16
NUM_CPU_SOCKETS	8

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	25,123.00	21,747.50	631,339	1,031,838,311
SYS$USERS	184.60	158.00	64,962	7,722,223
SYS$BACKGROUND	0.00	0.00	17,811	1,604,301

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	Network Total Wts	Network Wt Time
VIS	175012	24545	3570141	79402	9614653	5313
SYS$USERS	7871	1267	9253	303	6336	2
SYS$BACKGROUND	2051	799	5896	1320	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
SQL ordered by Cluster Wait Time
Complete List of SQL Text

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
3,734	3,619	8	466.76	14.77	ghw9svn8x4vzu	WSHPSRS	BEGIN WSH_PICK_LIST.RELEASE_BA...
2,256	2,145	279	8.09	8.92	0z318y6g3uagc	INCTCW	declare l_retstat varchar2 ( 3...
1,899	1,705	10	189.88	7.51	bkxrrs52m7d8p	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
1,462	1,092	14	104.41	5.78	8c30hkqv9n7cr	OEHVIMP	BEGIN OE_BULK_ORDER_IMPORT_PVT...
1,142	1,115	50,342	0.02	4.52	60t2tt1564j0k		SELECT SUBSTRB(MACHINE, 1, 60)...
969	938	8	121.13	3.83	7g05xfvc5vp74	ARTERRPW	BEGIN arp_run.revenue_recognit...
679	509	55,456	0.01	2.69	gksvm8wmzk8w0	WSHINTERFACE	begin OE_INVOICE_WF.INVOICE_IN...
643	424	1,093	0.59	2.55	bt0w082dfrm8v	WSHINTERFACE	BEGIN WSH_SHIP_CONFIRM_ACTIONS...
631	610	11	57.33	2.49	1du9v021t8m46	ARGLTP	insert into gl_interface(creat...
602	559	16	37.62	2.38	5qyckzm5v1qmj	RAXTRX	begin :return_value:i_return_v...
552	532	5,000	0.11	2.18	2fjd6cndjby9p	ARTERRPW	INSERT INTO RA_CUST_TRX_LINE_G...
541	503	919	0.59	2.14	3cb7xm8ykx1qh	icx.por.req.server.RequisitionAM	begin :1 := POR_UTIL_PKG.subm...
526	481	8	65.71	2.08	8vmj377jqyqwq	WSHASCSRS	BEGIN WSH_BATCH_PROCESS.CONFIR...
476	464	14,316	0.03	1.88	8h16w67202y4g		begin fnd_global.initialize(:s...
445	354	55,456	0.01	1.76	9wdrypwkpf607	WSHINTERFACE	begin OE_FULFILL_WF.START_FULF...
441	398	55,504	0.01	1.75	4yak7x2zqz0dh	WSHFRREL	BEGIN inv_transfer_order_pvt.f...
337	330	11	30.66	1.33	bp8crdjba8zgt	ARGLTP	update ra_cust_trx_line_gl_dis...
336	334	984	0.34	1.33	0qcpgw43yqj5r	WSHPSRS	( SELECT DISTINCT WDD.SOURCE_L...
328	276	10,000	0.03	1.30	64qc329wgjv39	PYUSLV	select /*+ ORDERED INDEX(ASS...
316	196	729,718	0.00	1.25	gnhmcahkmrnzs		INSERT INTO WF_ITEM_ACTIVITY_S...
286	29	16	17.86	1.13	cz148dcrzr2rw	RAXTRX	UPDATE RA_INTERFACE_LINES SET ...
281	87	64	4.39	1.11	3a7mfaapst783	RAXTRX	update RA_CUSTOMER_TRX T set ...
272	253	10,000	0.03	1.08	by2f4fw8tac6r	RAXTRX	UPDATE RA_CUST_TRX_LINE_GL_DIS...
265	177	96	2.76	1.05	37f1qguwxw0sk	RAXTRX	begin IF ( :v_msg_level:i_msg_...
261	245	55,035	0.00	1.03	gzkhp7cph992f	INCTCW	BEGIN INV_TXN_MANAGER_PUB.rel_...