WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORA11G 4193797614 ora11g 1 17-Oct-13 23:17 11.1.0.7.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
bj-ttttttt-db01 Linux x86 64-bit 8 4 1 15.57
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 56315 20-Nov-13 20:30:23 750 10.0
End Snap: 56317 20-Nov-13 20:50:38 1,699 4.9
Elapsed: 20.24 (mins)
DB Time: 30,226.41 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,248M 3,248M Std Block Size: 8K
Shared Pool Size: 800M 800M Log Buffer: 17,832K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 1,493.7 110.6 19.53 33.64
DB CPU(s): 8.7 0.6 0.11 0.20
Redo size: 35,641.2 2,639.8
Logical reads: 1,079.8 80.0
Block changes: 193.0 14.3
Physical reads: 318.0 23.6
Physical writes: 38.4 2.8
User calls: 44.4 3.3
Parses: 40.5 3.0
Hard parses: 0.6 0.0
W/A MB processed: 116,675.7 8,641.8
Logons: 0.9 0.1
Executes: 76.5 5.7
Rollbacks: 0.0 0.0
Transactions: 13.5
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 97.74 In-memory Sort %: 100.00
Library Hit %: 78.15 Soft Parse %: 98.56
Execute to Parse %: 47.01 Latch Hit %: 99.92
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.31
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 55.84 62.37
% SQL with executions>1: 77.86 79.03
% Memory for SQL w/exec>1: 88.15 84.23
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
library cache: mutex X 487,601,842 1,505,064 3 83.0 Concurrenc
library cache lock 2,047,628 212,285 104 11.7 Concurrenc
DB CPU 10,540 .6
log file sync 15,468 325 21 .0 Commit
db file sequential read 20,089 211 10 .0 User I/O
Host CPU (CPUs: 8 Cores: 4 Sockets: 1)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
16.51 ####### 87.1 12.6 0.0 0.2
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 98.9
% of busy CPU for Instance: 99.0
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 15,945.2 15,945.2
SGA use (MB): 4,160.0 4,160.0
PGA use (MB): 1,762.2 2,607.3
% Host Mem used for SGA+PGA: 37.14 37.14
Time Model Statistics DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> Total time in database user-calls (DB Time): 1813584.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
------------------------------------------ ------------------ ------------
parse time elapsed 1,809,581.3 99.8
failed parse elapsed time 11,147.5 .6
DB CPU 10,539.9 .6
sql execute elapsed time 1,854.6 .1
hard parse elapsed time 146.0 .0
connection management call elapsed time 47.6 .0
hard parse (sharing criteria) elapsed time 26.5 .0
PL/SQL execution elapsed time 13.7 .0
PL/SQL compilation elapsed time 8.1 .0
hard parse (bind mismatch) elapsed time 7.3 .0
sequence load elapsed time 0.4 .0
repeated bind elapsed time 0.0 .0
DB time 1,813,584.3
background elapsed time 1,838.3
background cpu time 24.1
-------------------------------------------------------------
Operating System Statistics DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> *TIME statistic values are diffed.
All others display actual values. End Value is displayed if different
-> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
Statistic Value End Value
------------------------- ---------------------- ----------------
BUSY_TIME 1,066,741
IDLE_TIME 1,787
IOWAIT_TIME 224
NICE_TIME 13
SYS_TIME 135,077
USER_TIME 931,136
LOAD 17 1,402
PHYSICAL_MEMORY_BYTES 16,719,749,120
NUM_CPUS 8
NUM_CPU_CORES 4
NUM_CPU_SOCKETS 1
GLOBAL_RECEIVE_SIZE_MAX 4,194,304
GLOBAL_SEND_SIZE_MAX 262,144
TCP_RECEIVE_SIZE_DEFAULT 4,194,304
TCP_RECEIVE_SIZE_MAX 4,194,304
TCP_RECEIVE_SIZE_MIN 4,194,304
TCP_SEND_SIZE_DEFAULT 262,144
TCP_SEND_SIZE_MAX 262,144
TCP_SEND_SIZE_MIN 262,144
-------------------------------------------------------------
Operating System Statistics - DetailDB/Inst: ORA11G/ora11g Snaps: 56315-56317
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
20-Nov 20:30:23 16.5 N/A N/A N/A N/A N/A
20-Nov 20:40:35 1,401.4 99.7 86.4 13.2 0.0 0.3
20-Nov 20:50:38 1,401.6 100.0 88.0 11.9 0.0 0.0
-------------------------------------------------------------
Foreground Wait Class DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> s - second, ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Captured Time accounts for 95.3% of Total DB time 1,813,584.32 (s)
-> Total FG Wait Time: 1,718,393.04 (s) DB CPU time: 10,539.91 (s)
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Concurrency 489,827,104 100 1,717,562 4 94.7
DB CPU 10,540 0.6
User I/O 22,986 0 411 18 0.0
Commit 15,468 0 325 21 0.0
System I/O 2,800 0 87 31 0.0
Other 14,379 100 4 0 0.0
Application 81 0 3 43 0.0
Configuration 2 50 1 501 0.0
Network 28,151 0 0 0 0.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> s - second, ms - millisecond - 1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
library cache: mutex X 487,601,842 100 1,505,064 3 3.0E+04 83.
library cache lock 2,047,628 0 212,285 104 124.9 11.7
log file sync 15,468 0 325 21 0.9 .0
db file sequential read 20,089 0 211 10 1.2 .0
direct path read 2,748 0 193 70 0.2 .0
cursor: pin S wait on X 10,147 100 133 13 0.6 .0
control file sequential re 2,785 0 74 27 0.2 .0
cursor: mutex S 167,315 100 59 0 10.2 .0
latch: row cache objects 16 0 14 864 0.0 .0
log file sequential read 15 0 13 836 0.0 .0
read by other session 118 0 6 51 0.0 .0
latch: In memory undo latc 36 0 6 165 0.0 .0
SQL*Net break/reset to cli 78 0 3 43 0.0 .0
latch: object queue header 3 0 2 652 0.0 .0
latch: session allocation 24 0 1 48 0.0 .0
db file scattered read 28 0 1 39 0.0 .0
log buffer space 1 100 1 1001 0.0 .0
latch: shared pool 49 0 1 20 0.0 .0
latch free 13 0 1 44 0.0 .0
kksfbc child completion 5 100 0 82 0.0 .0
cursor: pin S 14,328 100 0 0 0.9 .0
enq: TX - index contention 29 0 0 8 0.0 .0
db file parallel read 3 0 0 36 0.0 .0
enq: KO - fast object chec 2 0 0 53 0.0 .0
SQL*Net message to client 28,114 0 0 0 1.7 .0
enq: TX - row lock content 1 0 0 10 0.0 .0
buffer busy waits 27 0 0 0 0.0 .0
SQL*Net message from clien 28,128 0 2,830,268 100621 1.7
jobq slave wait 556 94 1,621 2916 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> ordered by wait time desc, waits desc (idle events last)
-> Only events with Total Wait Time (s) >= .001 are shown
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Avg
%Time Total Wait wait Waits % bg
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
os thread startup 46 0 192 4177 0.0 10.5
db file sequential read 759 0 93 122 0.0 5.0
control file sequential re 3,099 0 34 11 0.2 1.8
latch free 179 0 22 124 0.0 1.2
log file parallel write 16,026 0 16 1 1.0 .8
LGWR wait for redo copy 57 0 15 255 0.0 .8
direct path write 4 0 4 1120 0.0 .2
latch: cache buffers chain 2 0 4 1980 0.0 .2
enq: PR - contention 1 0 4 3939 0.0 .2
db file parallel write 4,945 0 2 0 0.3 .1
latch: session allocation 19 0 1 67 0.0 .1
control file parallel writ 622 0 1 2 0.0 .1
latch: shared pool 23 0 1 38 0.0 .0
log file sync 3 0 0 79 0.0 .0
SQL*Net message to client 2,124 0 0 0 0.1 .0
rdbms ipc reply 4 0 0 0 0.0 .0
rdbms ipc message 20,641 23 14,156 686 1.3
DIAG idle wait 1,747 100 2,211 1265 0.1
Streams AQ: qmn slave idle 92 0 1,289 14006 0.0
Streams AQ: qmn coordinato 138 33 1,289 9337 0.0
Space Manager: slave idle 257 99 1,280 4980 0.0
fbar timer 4 100 1,197 299255 0.0
smon timer 6 50 1,197 199448 0.0
pmon timer 352 42 483 1371 0.0
SQL*Net message from clien 2,124 0 46 22 0.1
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05 value of null is truly>
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 57 87.7 1.8 3.5 7.0
SQL*Net break/reset to cli 78 88.5 10.3 1.3
SQL*Net message to client 31K 100.0
SQL*Net more data from cli 4 100.0
SQL*Net more data to clien 34 100.0
buffer busy waits 29 89.7 3.4 6.9
buffer deadlock 4 100.0
control file parallel writ 707 68.3 28.6 1.1 .3 .7 .3 .7
control file sequential re 6027 38.8 .3 .3 2.2 38.1 12.3 7.9 .2
cursor: mutex S 155K 98.8 1.0 .2 .0 .0 .0 .0
cursor: pin S 14K 99.9 .0 .0 .0 .0
cursor: pin S wait on X 10K .1 .0 98.8 .7 .4 .0
db file parallel read 3 33.3 66.7
db file parallel write 5370 98.0 .8 .4 .2 .2 .1 .3
db file scattered read 26 3.8 7.7 15.4 26.9 46.2
db file sequential read 23K 42.7 .8 1.9 15.2 32.1 3.3 3.9 .2
direct path read 3468 81.4 1.0 3.5 .4 .1 .2 11.2 2.1
direct path write 4 50.0 50.0
enq: KO - fast object chec 3 33.3 66.7
enq: PR - contention 2 100.0
enq: SQ - contention 1 100.0
enq: TX - index contention 31 9.7 16.1 16.1 29.0 22.6 3.2 3.2
enq: TX - row lock content 1 100.0
kksfbc child completion 5 100.0
latch free 171 2.3 .6 1.2 17.0 8.8 69.0 1.2
latch: In memory undo latc 39 71.8 25.6 2.6
latch: cache buffers chain 16 87.5 6.3 6.3
latch: object queue header 2 50.0 50.0
latch: redo allocation 1 100.0
latch: row cache objects 16 25.0 12.5 18.8 6.3 25.0 12.5
latch: session allocation 46 6.5 4.3 8.7 10.9 6.5 4.3 58.7
latch: shared pool 61 18.0 8.2 8.2 14.8 13.1 8.2 24.6 4.9
library cache load lock 1 100.0
library cache lock 1918K .0 .0 .4 3.5 8.4 14.8 71.5 1.4
library cache: mutex X 527M 24.5 5.3 16.2 53.8 .1 .1 .0 .0
log buffer space 1 100.0
log file parallel write 18K 94.1 3.6 .9 .6 .6 .1 .0
log file sequential read 15 6.7 33.3 6.7 33.3 20.0
log file sync 17K 61.4 32.5 1.2 .7 .8 .9 1.8 .6
os thread startup 53 17.0 83.0
rdbms ipc reply 4 100.0
read by other session 118 34.7 3.4 5.9 11.9 5.9 4.2 33.9
reliable message 2 100.0
DIAG idle wait 1952 88.8 11.2
SQL*Net message from clien 31K 26.8 3.3 2.8 1.6 1.7 2.7 31.3 29.8
Space Manager: slave idle 289 .7 99.3
Streams AQ: qmn coordinato 156 65.4 .6 .6 33.3
Streams AQ: qmn slave idle 104 50.0 50.0
fbar timer 4 100.0
jobq slave wait 608 .2 1.5 98.4
pmon timer 357 46.5 .3 6.4 46.8
rdbms ipc message 23K 3.1 1.1 2.1 3.9 7.0 12.0 55.5 15.3
smon timer 6 100.0
-------------------------------------------------------------
Service Statistics DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads (K) Reads (K)
---------------------------- ------------ ------------ ------------ ------------
SYS$USERS 1,703,937 9,944 359 1,239
ora11g 6 0 0 2
SYS$BACKGROUND 0 0 1 76
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: ORA11G/ora11g Snaps: 56315-56317
-> 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 seconds
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
22853 404 4.9E+08 1703638 0 0 27999 0
ora11g
20 6 0 0 0 0 62 0
SYS$BACKGROUND
764 94 51 196 0 0 0 0
-------------------------------------------------------------