Oracle, The World's Largest Enterprise Software Company
  |  WorldwideChange Country, Oracle Worldwide Web SitesSitefinder
Secure Search
PRODUCTS AND SERVICES INDUSTRIES SUPPORT PARTNERS COMMUNITIES ABOUT

AWR Report

WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numReleaseRACHost
VIS200195696VIS1110.2.0.3.0YEShprac1.ebs.hp.com

Snap IdSnap TimeSessionsCursors/Session
Begin Snap:111-Mar-08 08:36:561388 147.2
End Snap:211-Mar-08 09:37:071360 155.2
Elapsed:  60.18 (mins)  
DB Time:  323.15 (mins)  

Report Summary

Cache Sizes

BeginEnd
Buffer Cache: 22,480M 22,480MStd Block Size: 8K
Shared Pool Size: 3,008M 3,008MLog Buffer: 30,696K

Load Profile

Per SecondPer Transaction
Redo size: 8,007,651.31 56,615.15
Logical reads: 362,028.08 2,559.59
Block changes: 48,396.09 342.17
Physical reads: 46.12 0.33
Physical writes: 802.98 5.68
User calls: 3,279.34 23.19
Parses: 1,806.26 12.77
Hard parses: 1.07 0.01
Sorts: 1,999.83 14.14
Logons: 1.76 0.01
Executes: 17,125.77 121.08
Transactions: 141.44 

% Blocks changed per Read: 13.37Recursive Call %: 90.12
Rollback per transaction %: 7.20Rows per Sort: 9.75

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.95Redo NoWait %: 100.00
Buffer Hit %: 99.99In-memory Sort %: 100.00
Library Hit %: 99.78Soft Parse %: 99.94
Execute to Parse %: 89.45Latch Hit %: 98.98
Parse CPU to Parse Elapsd %: 87.82% Non-Parse CPU: 97.54

Shared Pool Statistics

BeginEnd
Memory Usage %: 67.62 81.18
% SQL with executions>1: 82.14 84.98
% Memory for SQL w/exec>1: 60.74 60.93

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time  17,855  92.1 
gc buffer busy 27,053 269 10 1.4Cluster
log file sync 138,296 261 2 1.3Commit
log file parallel write 456,757 237 1 1.2System I/O
db file sequential read 61,820 191 3 1.0User I/O

RAC Statistics

BeginEnd
Number of Instances: 2 2

Global Cache Load Profile

Per SecondPer Transaction
Global Cache blocks received: 54.72 0.39
Global Cache blocks served: 79.63 0.56
GCS/GES messages received: 554.85 3.92
GCS/GES messages sent: 607.61 4.30
DBWR Fusion writes: 7.42 0.05
Estd Interconnect traffic (KB) 1,301.84 

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

Buffer access - local cache %: 99.97
Buffer access - remote cache %: 0.02
Buffer access - disk %: 0.01

Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms): 0.1
Avg global cache cr block receive time (ms): 0.9
Avg global cache current block receive time (ms): 0.6
Avg global cache cr block build time (ms): 0.0
Avg global cache cr block send time (ms): 0.0
Global cache log flushes for cr blocks served %: 10.1
Avg global cache cr block flush time (ms): 1.4
Avg global cache current block pin time (ms): 2,361.6
Avg global cache current block send time (ms): 0.0
Global cache log flushes for current blocks served %: 0.9
Avg global cache current block flush time (ms): 27.6

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): 416.7
Avg message sent queue time on ksxp (ms): 0.2
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: 20.40
% of indirect sent messages: 37.19
% of flow controlled messages: 42.41


Main Report

More RAC Statistics


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 19389s
  • 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 NameTime (s)% of DB Time
DB CPU 17,854.98 92.09
sql execute elapsed time 17,113.40 88.26
PL/SQL execution elapsed time 4,183.70 21.58
inbound PL/SQL rpc elapsed time 3,421.42 17.65
Java execution elapsed time 607.32 3.13
parse time elapsed 545.00 2.81
hard parse elapsed time 326.48 1.68
sequence load elapsed time 28.76 0.15
repeated bind elapsed time 27.02 0.14
connection management call elapsed time 10.45 0.05
PL/SQL compilation elapsed time 4.53 0.02
hard parse (sharing criteria) elapsed time 4.23 0.02
hard parse (bind mismatch) elapsed time 1.19 0.01
DB time 19,389.04  
background elapsed time 1,114.30  
background cpu time 612.39  

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 ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
Cluster 558,757 0.03 467 1 1.09
System I/O 695,561 0.00 344 0 1.36
User I/O 90,396 0.00 323 4 0.18
Commit 138,296 0.00 261 2 0.27
Other 1,116,839 50.66 259 0 2.19
Application 39,964 1.04 241 6 0.08
Concurrency 1,510,959 0.33 210 0 2.96
Network 15,140,649 0.00 68 0 29.65
Configuration 49,894 23.80 66 1 0.10

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)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
gc buffer busy 27,053 0.68 269 10 0.05
log file sync 138,296 0.00 261 2 0.27
log file parallel write 456,757 0.00 237 1 0.89
db file sequential read 61,820 0.00 191 3 0.12
enq: TX - row lock contention 814 28.87 118 145 0.00
enq: UL - contention 31,536 0.56 117 4 0.06
db file parallel write 228,109 0.00 99 0 0.45
db file scattered read 13,109 0.00 85 6 0.03
wait for scn ack 278,363 0.00 71 0 0.55
enq: HW - contention 31,415 0.14 62 2 0.06
cursor: pin S wait on X 5,204 94.00 53 10 0.01
enq: TX - index contention 29,036 0.06 51 2 0.06
read by other session 14,607 0.00 46 3 0.03
gc cr block busy 17,405 0.00 46 3 0.03
DFS lock handle 10,970 0.63 40 4 0.02
buffer busy waits 670,470 0.00 40 0 1.31
gc cr block 2-way 93,864 0.00 39 0 0.18
SQL*Net more data from client 1,407,643 0.00 31 0 2.76
gc current block 2-way 53,169 0.00 23 0 0.10
gc current multi block request 240,973 0.00 23 0 0.47
global enqueue expand wait 2,394 82.66 22 9 0.00
latch: cache buffers chains 710,013 0.00 21 0 1.39
row cache lock 81,210 0.00 21 0 0.16
gc cr multi block request 42,865 0.00 20 0 0.08
SQL*Net more data to client 1,267,593 0.00 20 0 2.48
gcs log flush sync 30,162 24.04 19 1 0.06
Streams AQ: qmn coordinator waiting for slave to start 3 100.00 18 5867 0.00
SQL*Net message to client 12,465,413 0.00 17 0 24.41
enq: TX - contention 3,192 0.25 16 5 0.01
enq: FB - contention 23,034 0.00 15 1 0.05
gc current grant busy 23,821 0.00 15 1 0.05
library cache load lock 683 0.00 14 20 0.00
cr request retry 16 87.50 10 611 0.00
gc current grant 2-way 32,271 0.00 9 0 0.06
gcs drm freeze in enter server mode 73 0.00 9 117 0.00
gc cr grant 2-way 26,397 0.00 7 0 0.05
gc current block busy 766 0.00 7 9 0.00
control file sequential read 8,979 0.00 6 1 0.02
gc recovery quiesce 8 62.50 5 611 0.00
reliable message 608 0.16 4 7 0.00
name-service call wait 68 0.00 4 58 0.00
enq: RO - fast object reuse 89 3.37 4 43 0.00
kksfbc child completion 97 79.38 4 40 0.00
PX Deq Credit: send blkd 12,619 38.97 4 0 0.02
gc quiesce wait 8 37.50 3 367 0.00
library cache lock 2,455 0.00 3 1 0.00
PX Deq: reap credit 146,432 81.26 3 0 0.29
enq: SQ - contention 6,414 0.00 3 0 0.01
enq: US - contention 8,110 0.00 2 0 0.02
ksxr poll remote instances 237,420 55.04 2 0 0.46
os thread startup 26 0.00 2 94 0.00
SQL*Net break/reset to client 7,384 0.00 2 0 0.01
latch: library cache 5,574 0.00 2 0 0.01
enq: DV - contention 338 0.00 2 6 0.00
latch: object queue header operation 6,167 0.00 2 0 0.01
control file parallel write 1,716 0.00 2 1 0.00
LGWR wait for redo copy 32,855 0.00 2 0 0.06
gc remaster 55 0.00 1 26 0.00
wait list latch free 114 0.00 1 11 0.00
enq: WF - contention 8 25.00 1 127 0.00
latch: undo global data 3,529 0.00 1 0 0.01
CGS wait for IPC msg 142,333 99.79 1 0 0.28
latch: redo allocation 2,512 0.00 1 0 0.00
buffer deadlock 35,869 98.81 1 0 0.07
library cache pin 3,527 0.00 1 0 0.01
enq: TX - allocate ITL entry 165 0.00 1 4 0.00
latch: library cache pin 1,896 0.00 1 0 0.00
latch free 2,640 0.00 1 0 0.01
latch: cache buffers lru chain 590 0.00 1 1 0.00
PX Deq: Signal ACK 510 40.00 1 1 0.00
PX qref latch 124,128 99.92 1 0 0.24
enq: PS - contention 1,221 0.00 1 0 0.00
latch: session allocation 1,022 0.00 1 1 0.00
rdbms ipc reply 1,242 0.00 0 0 0.00
gc current retry 13 0.00 0 19 0.00
latch: ges resource hash list 1,399 0.00 0 0 0.00
ges global resource directory to be frozen 15 100.00 0 11 0.00
latch: row cache objects 384 0.00 0 0 0.00
enq: TM - contention 141 0.00 0 1 0.00
db file parallel read 19 0.00 0 7 0.00
enq: TT - contention 120 0.00 0 1 0.00
latch: library cache lock 192 0.00 0 1 0.00
latch: shared pool 93 0.00 0 1 0.00
enq: CF - contention 156 0.00 0 1 0.00
latch: gcs resource hash 309 0.00 0 0 0.00
gc current split 3 0.00 0 27 0.00
gc current grant congested 18 0.00 0 4 0.00
local write wait 29 0.00 0 2 0.00
gc cr block congested 28 0.00 0 2 0.00
latch: KCL gc element parent latch 128 0.00 0 1 0.00
write complete waits 5 0.00 0 12 0.00
enq: TO - contention 71 0.00 0 1 0.00
L1 validation 5 80.00 0 9 0.00
IPC send completion sync 229 33.19 0 0 0.00
latch: redo writing 55 0.00 0 1 0.00
latch: messages 377 0.00 0 0 0.00
kjbdrmcvtq lmon drm quiesce: ping completion 15 60.00 0 2 0.00
undo segment extension 11,839 99.94 0 0 0.02
latch: cache buffer handles 174 0.00 0 0 0.00
cursor: pin S 4,858 0.00 0 0 0.01
enq: TS - contention 34 0.00 0 0 0.00
enq: TD - KTF dump entries 36 0.00 0 0 0.00
gc current block congested 10 0.00 0 1 0.00
cursor: mutex S 119 0.00 0 0 0.00
enq: TA - contention 58 0.00 0 0 0.00
gc cr grant congested 9 0.00 0 1 0.00
latch: checkpoint queue latch 35 0.00 0 0 0.00
enq: PI - contention 22 0.00 0 0 0.00
latch: enqueue hash chains 52 0.00 0 0 0.00
latch: object queue header heap 27 0.00 0 0 0.00
gc cr failure 16 0.00 0 0 0.00
enq: MD - contention 9 0.00 0 0 0.00
direct path read 426 0.00 0 0 0.00
cursor: mutex X 77 0.00 0 0 0.00
enq: ST - contention 1 0.00 0 3 0.00
KJC: Wait for msg sends to complete 38 0.00 0 0 0.00
ges2 LMON to wake up LMD - mrcvr 11 0.00 0 0 0.00
gc cr disk read 4 0.00 0 0 0.00
direct path write 342 0.00 0 0 0.00
enq: DR - contention 2 0.00 0 1 0.00
latch: parallel query alloc buffer 5 0.00 0 0 0.00
enq: DL - contention 4 0.00 0 0 0.00
lock escalate retry 10 70.00 0 0 0.00
direct path write temp 38 0.00 0 0 0.00
kkdlgon 1 0.00 0 0 0.00
gc current block unknown 1 0.00 0 0 0.00
direct path read temp 6 0.00 0 0 0.00
SQL*Net message from client 12,465,449 0.00 2,652,587 213 24.41
pipe get 22,219 20.54 21,123 951 0.04
Streams AQ: waiting for messages in the queue 2,802 98.43 15,238 5438 0.01
gcs remote message 1,941,186 72.40 13,900 7 3.80
PX Idle Wait 4,138 83.25 8,786 2123 0.01
Streams AQ: qmn slave idle wait 141 2.13 3,687 26146 0.00
DIAG idle wait 17,877 0.00 3,524 197 0.04
Streams AQ: waiting for time management or cleanup tasks 2,054 45.23 3,518 1713 0.00
ges remote message 269,845 56.26 3,508 13 0.53
Streams AQ: qmn coordinator idle wait 257 49.42 3,492 13588 0.00
PL/SQL lock timer 208 95.67 2,277 10945 0.00
PX Deq: Execution Msg 15,470 8.69 221 14 0.03
PX Deq: Msg Fragment 8,934 0.08 11 1 0.02
PX Deq: Parse Reply 1,012 17.79 10 10 0.00
PX Deq: Execute Reply 10,994 29.91 5 0 0.02
PX Deq: Table Q Normal 7,625 28.25 4 1 0.01
PX Deq: Join ACK 1,194 35.01 0 0 0.00
PX Deq Credit: need buffer 1,233 34.79 0 0 0.00
class slave wait 5 0.00 0 1 0.00
Streams AQ: RAC qmn coordinator idle wait 259 100.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)
EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
log file parallel write 456,758 0.00 237 1 0.89
events in waitclass Other 737,552 37.99 120 0 1.44
db file parallel write 228,109 0.00 99 0 0.45
control file sequential read 7,409 0.00 5 1 0.01
control file parallel write 1,716 0.00 2 1 0.00
gc cr block 2-way 926 0.00 0 0 0.00
row cache lock 268 0.00 0 1 0.00
os thread startup 4 0.00 0 49 0.00
gc current block 2-way 356 0.00 0 0 0.00
library cache lock 51 0.00 0 2 0.00
gc cr multi block request 283 0.00 0 0 0.00
gc current grant busy 54 0.00 0 1 0.00
latch: redo writing 55 0.00 0 1 0.00
db file sequential read 2 0.00 0 13 0.00
latch: shared pool 2 0.00 0 12 0.00
buffer busy waits 105 2.86 0 0 0.00
latch: library cache 1 0.00 0 16 0.00
enq: RO - fast object reuse 17 0.00 0 1 0.00
enq: TM - contention 13 0.00 0 0 0.00
direct path read 426 0.00 0 0 0.00
latch: cache buffers chains 83 0.00 0 0 0.00
gc cr block busy 1 0.00 0 2 0.00
direct path write 242 0.00 0 0 0.00
gc current multi block request 9 0.00 0 0 0.00
enq: HW - contention 2 0.00 0 0 0.00
latch: row cache objects 8 0.00 0 0 0.00
gc cr grant 2-way 1 0.00 0 0 0.00
enq: TX - index contention 1 0.00 0 0 0.00
gc current grant 2-way 1 0.00 0 0 0.00
library cache pin 1 0.00 0 0 0.00
rdbms ipc message 1,143,813 5.37 37,923 33 2.24
gcs remote message 1,941,204 72.40 13,900 7 3.80
DIAG idle wait 17,878 0.00 3,524 197 0.04
pmon timer 2,430 99.01 3,521 1449 0.00
Streams AQ: waiting for time management or cleanup tasks 2,054 45.23 3,518 1713 0.00
ges remote message 269,760 56.25 3,508 13 0.53
smon timer 101,378 0.00 3,506 35 0.20
Streams AQ: qmn coordinator idle wait 257 49.42 3,492 13588 0.00
PX Deq: Parse Reply 25 36.00 0 1 0.00
PX Deq: Join ACK 27 40.74 0 1 0.00
PX Deq: Execute Reply 24 29.17 0 0 0.00
Streams AQ: RAC qmn coordinator idle wait 259 100.00 0 0 0.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
BUSY_TIME 1,989,631
IDLE_TIME 3,773,266
IOWAIT_TIME 61,643
NICE_TIME 0
SYS_TIME 133,821
USER_TIME 1,819,958
LOAD 3
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 25,328,188
NUM_CPUS 16
NUM_CPU_SOCKETS 4

Back to Wait Events Statistics
Back to Top

Service Statistics

  • ordered by DB Time
Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
VIS 19,265.60 17,790.40 126,243 1,302,448,964
SYS$USERS 128.90 68.60 39,955 3,562,982
SYS$BACKGROUND 0.00 0.00 434 1,192,657

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 NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
VIS 83391 31052 1501307 20743 0 0 15124631 6772
SYS$USERS 6336 1263 9114 155 0 0 5706 3
SYS$BACKGROUND 672 4 552 65 0 0 0 0

Back to Wait Events Statistics
Back to Top

SQL Statistics

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 IdSQL ModuleSQL Text
2,326 2,261 8 290.75 12.00 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
1,905 1,885 10 190.50 9.83 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
1,018 1,006 300 3.39 5.25 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
917 842 12 76.40 4.73 1g4jfpxpupdt6 OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
689 640 16 43.09 3.56 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
443 423 55,013 0.01 2.28 gksvm8wmzk8w0 WSHINTERFACE begin OE_INVOICE_WF.INVOICE_IN...
415 386 1,008 0.41 2.14 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
389 349 5,000 0.08 2.01 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
386 376 54,991 0.01 1.99 9wdrypwkpf607 WSHINTERFACE begin OE_FULFILL_WF.START_FULF...
351 335 55,051 0.01 1.81 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
337 334 4,205 0.08 1.74 a957tvxz9w038 icx.por.req.server.RequisitionAM BEGIN :1 := por_util_pkg.inter...
310 310 8 38.75 1.60 8vmj377jqyqwq WSHASCSRS BEGIN WSH_BATCH_PROCESS.CONFIR...
307 301 984 0.31 1.58 0qcpgw43yqj5r WSHPSRS ( SELECT DISTINCT WDD.SOURCE_L...
305 303 9 33.91 1.57 1du9v021t8m46 ARGLTP insert into gl_interface(creat...
293 160 10,000 0.03 1.51 64qc329wgjv39 PYUSLV select /*+ ORDERED INDEX(ASS...
277 270 61,047 0.00 1.43 g8102j8yynbwz OEXOETEL begin WF_STANDARD.CONTINUEFLOW...
247 246 45,724 0.01 1.28 9sf243fdjmdmh JDBC Thin Client begin fnd_global.apps_initiali...
195 193 55,055 0.00 1.01 gzkhp7cph992f INCTCW BEGIN INV_TXN_MANAGER_PUB.rel_...

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 IdSQL ModuleSQL Text
2,261 2,326 8 282.63 12.00 ghw9svn8x4vzu WSHPSRS BEGIN WSH_PICK_LIST.RELEASE_BA...
1,885 1,905 10 188.53 9.83 bkxrrs52m7d8p WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
1,006 1,018 300 3.35 5.25 0z318y6g3uagc INCTCW declare l_retstat varchar2 ( 3...
842 917 12 70.18 4.73 1g4jfpxpupdt6 OEHVIMP BEGIN OE_BULK_ORDER_IMPORT_PVT...
640 689 16 39.99 3.56 7g05xfvc5vp74 ARTERRPW BEGIN arp_run.revenue_recognit...
423 443 55,013 0.01 2.28 gksvm8wmzk8w0 WSHINTERFACE begin OE_INVOICE_WF.INVOICE_IN...
386 415 1,008 0.38 2.14 bt0w082dfrm8v WSHINTERFACE BEGIN WSH_SHIP_CONFIRM_ACTIONS...
376 386 54,991 0.01 1.99 9wdrypwkpf607 WSHINTERFACE begin OE_FULFILL_WF.START_FULF...
349 389 5,000 0.07 2.01 2fjd6cndjby9p ARTERRPW INSERT INTO RA_CUST_TRX_LINE_G...
335 351 55,051 0.01 1.81 4yak7x2zqz0dh WSHFRREL BEGIN inv_transfer_order_pvt.f...
334 337 4,205 0.08 1.74 a957tvxz9w038 icx.por.req.server.RequisitionAM BEGIN :1 := por_util_pkg.inter...
310 310 8 38.70 1.60 8vmj377jqyqwq WSHASCSRS BEGIN WSH_BATCH_PROCESS.CONFIR...