Hi Folks,
Web application server version - 10.1.2.0.2
Database version - 10.1.0.4.0
OS version - AIX 5.3
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
xxxx 3419432639 xxxx 1 10.1.0.4.0 NO hostname
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 16598 07-Jul-09 16:30:54 23 60.7
End Snap: 16599 07-Jul-09 17:30:46 36 94.1
Elapsed: 59.88 (mins)
DB Time: 44.99 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,536M Std Block Size: 8K
Shared Pool Size: 1,248M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 30,495.86 138,156.74
Logical reads: 1,735.49 7,862.39
Block changes: 197.66 895.45
Physical reads: 564.70 2,558.28
Physical writes: 54.64 247.52
User calls: 2.00 9.04
Parses: 14.32 64.87
Hard parses: 1.37 6.22
Sorts: 5.83 26.42
Logons: 0.12 0.54
Executes: 72.71 329.38
Transactions: 0.22
% Blocks changed per Read: 11.39 Recursive Call %: 99.64
Rollback per transaction %: 17.53 Rows per Sort: 5933.99
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 71.24 In-memory Sort %: 99.79
Library Hit %: 96.22 Soft Parse %: 90.41
Execute to Parse %: 80.31 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 21.82 % Non-Parse CPU: 96.80
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 13.13 27.55
% SQL with executions>1: 61.04 15.52
% Memory for SQL w/exec>1: 60.96 12.31
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ------------ ----------- --------- --------------
db file scattered read 135,977 1,204 44.62 User I/O
CPU time 547 20.26
db file sequential read 99,521 187 6.92 User I/O
log file parallel write 10,995 16 .59 System I/O
direct path write temp 7,225 13 .47 User I/O
-------------------------------------------------------------
Wait Events DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> 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)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
--------------------------- ------------ ---------- ---------- ------- ---------
db file scattered read 135,977 0 1,204 9 171.5
db file sequential read 99,521 0 187 2 125.5
log file parallel write 10,995 0 16 1 13.9
direct path write temp 7,225 0 13 2 9.1
process startup 117 1 7 58 0.1
latch: library cache 306 0 5 15 0.4
direct path read temp 152,490 0 4 0 192.3
db file parallel write 342 0 4 11 0.4
log file sync 309 3 4 13 0.4
db file parallel read 359 0 3 8 0.5
latch: cache buffers lru ch 80 0 2 19 0.1
control file parallel write 1,325 0 1 1 1.7
latch: row cache objects 26 0 1 51 0.0
latch: cache buffers chains 138 138 1 9 0.2
log file switch completion 3 0 1 399 0.0
buffer busy waits 94 0 1 10 0.1
latch: shared pool 44 0 0 6 0.1
control file sequential rea 1,258 0 0 0 1.6
latch: object queue header 3 0 0 41 0.0
SQL*Net message to client 5,866 0 0 0 7.4
LGWR wait for redo copy 495 1 0 0 0.6
direct path read 58 0 0 1 0.1
latch: session allocation 6 0 0 6 0.0
latch: In memory undo latch 3 0 0 10 0.0
SQL*Net break/reset to clie 62 0 0 0 0.1
control file single write 40 0 0 1 0.1
latch: redo allocation 12 0 0 2 0.0
latch free 2 0 0 11 0.0
enq: TX - index contention 1 0 0 21 0.0
latch: messages 8 0 0 2 0.0
log file single write 2 0 0 4 0.0
direct path write 99 0 0 0 0.1
SQL*Net more data to client 237 0 0 0 0.3
log file sequential read 2 0 0 2 0.0
latch: library cache lock 1 0 0 3 0.0
latch: enqueue hash chains 3 0 0 0 0.0
SQL*Net more data from clie 2 0 0 0 0.0
SQL*Net message from client 5,855 0 23,607 4032 7.4
jobq slave wait 3,441 3,334 9,925 2884 4.3
Queue Monitor Wait 1,537 623 3,532 2298 1.9
Queue Monitor Slave Wait 503 0 3,532 7022 0.6
wakeup time manager 12 0 3,527 293898 0.0
virtual circuit status 120 120 3,512 29266 0.2
class slave wait 2 2 10 4883 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
--------------------------- ------------ ---------- ---------- ------- ---------
log file parallel write 10,995 0 16 1 13.9
process startup 117 1 7 58 0.1
db file sequential read 1,255 0 4 3 1.6
db file parallel write 342 0 4 11 0.4
control file parallel write 1,329 0 1 1 1.7
db file scattered read 151 0 1 7 0.2
latch: cache buffers lru ch 32 0 0 15 0.0
latch: library cache 5 0 0 69 0.0
control file sequential rea 213 0 0 1 0.3
latch: cache buffers chains 2 2 0 34 0.0
LGWR wait for redo copy 495 1 0 0 0.6
control file single write 40 0 0 1 0.1
latch: redo allocation 12 0 0 2 0.0
latch: messages 8 0 0 2 0.0
latch: object queue header 2 0 0 8 0.0
log file single write 2 0 0 4 0.0
direct path write 25 0 0 0 0.0
buffer busy waits 1 0 0 7 0.0
latch: shared pool 2 0 0 2 0.0
log file sequential read 2 0 0 2 0.0
direct path read 22 0 0 0 0.0
rdbms ipc message 15,461 10,346 26,675 1725 19.5
Queue Monitor Wait 1,537 623 3,532 2298 1.9
Queue Monitor Slave Wait 503 0 3,532 7022 0.6
wakeup time manager 12 0 3,527 293898 0.0
smon timer 175 8 3,418 19530 0.2
-------------------------------------------------------------
Time Model Statistics DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> ordered by Time (seconds) desc
Time % Total
Statistic Name (seconds) DB Time
--------------------------------------------- -------------- -----------
sql execute elapsed time 2,708.75 100.35
DB time 2,699.22 100.00
DB CPU 546.94 20.26
background elapsed time 113.64 4.21
parse time elapsed 90.88 3.37
hard parse elapsed time 59.03 2.19
PL/SQL execution elapsed time 56.46 2.09
Java execution elapsed time 8.99 .33
background cpu time 8.18 .30
PL/SQL compilation elapsed time 4.55 .17
connection management call elapsed time 1.08 .04
failed parse elapsed time .73 .03
hard parse (sharing criteria) elapsed time .70 .03
hard parse (bind mismatch) elapsed time .61 .02
sequence load elapsed time .41 .02
inbound PL/SQL rpc elapsed time .00 .00
failed parse (out of shared memory) elapsed t .00 .00
-------------------------------------------------------------
Operating System Statistics DB/Inst: xxxx/xxxx Snaps: 16598-16599
Statistic Name Value
----------------------------------- ------------------
AVG_BUSY_TICKS 771,364
AVG_IDLE_TICKS 346,024
AVG_IN_BYTES 3,812,632,576
AVG_IOWAIT_TICKS 38,470
AVG_OUT_BYTES 1,141,137,408
AVG_SYS_TICKS 2,786
AVG_USER_TICKS 13,588
BUSY_TICKS 3,085,837
IDLE_TICKS 1,384,395
IN_BYTES 15,250,530,304
IOWAIT_TICKS 154,103
OS_CPU_WAIT_TIME 70,200
OUT_BYTES 4,564,549,632
RSRC_MGR_CPU_WAIT_TIME 0
SYS_TICKS 11,439
USER_TICKS 54,613
-------------------------------------------------------------
Service Statistics DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
xxxx 2,032.9 519.7 1,804,948 5,709,611
SYS$USERS 666.3 27.2 219,320 501,732
SYS$BACKGROUND 0.0 0.0 4,565 28,708
xxxxXDB 0.0 0.0 0 0
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> 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 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
--------- --------- --------- --------- --------- --------- --------- ---------
xxxx
374696 127922 0 0 0 0 3701 7
SYS$USERS
18820 12406 172 222 0 0 2129 0
SYS$BACKGROUND
2218 783 0 0 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: xxxx/xxxx Snaps: 16598-16599
-> 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
<<<<<<<<<<<<<<<<<<<<<<<<<<<SQLs Deleted >>>>>>>>>>>>
-------------------------------------------------------------
SQL ordered by Sharable Memory DB/Inst: xxxx/xxxx Snaps: 16598-16599
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Version Count DB/Inst: xxxx/xxxx Snaps: 16598-16599
No data exists for this section of the report.
-------------------------------------------------------------
Instance Activity Stats DB/Inst: xxxx/xxxx Snaps: 16598-16599
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 53,470 14.9 67.4
CPU used when call started 51,928 14.5 65.5
CR blocks created 5,298 1.5 6.7
Cached Commit SCN referenced 192,226 53.5 242.4
Commit SCN cached 3,613 1.0 4.6
DB time 1,253,838 349.0 1,581.1
DBWR checkpoint buffers written 4,304 1.2 5.4
DBWR checkpoints 1 0.0 0.0
DBWR transaction table writes 59 0.0 0.1
DBWR undo block writes 5,557 1.6 7.0
IMU CR rollbacks 1 0.0 0.0
IMU Flushes 191 0.1 0.2
IMU Redo allocation size 860,820 239.6 1,085.5
IMU commits 381 0.1 0.5
IMU contention 5 0.0 0.0
IMU ktichg flush 1 0.0 0.0
IMU undo allocation size 1,839,872 512.1 2,320.1
SQL*Net roundtrips to/from clien 5,591 1.6 7.1
active txn count during cleanout 948 0.3 1.2
application wait time 4 0.0 0.0
background checkpoints completed 1 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 10,366 2.9 13.1
buffer is not pinned count 2,387,595 664.6 3,010.8
buffer is pinned count 792,051 220.5 998.8
bytes received via SQL*Net from 333,467 92.8 420.5
bytes sent via SQL*Net to client 2,449,972 682.0 3,089.5
calls to get snapshot scn: kcmgs 319,869 89.0 403.4
calls to kcmgas 17,576 4.9 22.2
calls to kcmgcs 1,912 0.5 2.4
change write time 2,175 0.6 2.7
cleanout - number of ktugct call 4,589 1.3 5.8
cluster key scan block gets 579,340 161.3 730.6
cluster key scans 283,013 78.8 356.9
commit cleanout failures: block 1 0.0 0.0
commit cleanout failures: buffer 1 0.0 0.0
commit cleanout failures: callba 1 0.0 0.0
commit cleanout failures: cannot 5 0.0 0.0
commit cleanouts 25,713 7.2 32.4
commit cleanouts successfully co 25,705 7.2 32.4
commit txn count during cleanout 4,110 1.1 5.2
concurrency wait time 650 0.2 0.8
consistent changes 7,352 2.1 9.3
consistent gets 5,509,829 1,533.7 6,948.1
consistent gets - examination 1,061,141 295.4 1,338.1
consistent gets direct 39 0.0 0.1
consistent gets from cache 5,509,790 1,533.7 6,948.0
cursor authentications 310 0.1 0.4
data blocks consistent reads - u 6,747 1.9 8.5
db block changes 710,092 197.7 895.5
db block gets 725,044 201.8 914.3
db block gets direct 14,789 4.1 18.7
db block gets from cache 710,255 197.7 895.7
deferred (CURRENT) block cleanou 14,036 3.9 17.7
dirty buffers inspected 9,309 2.6 11.7
enqueue conversions 926 0.3 1.2
enqueue releases 92,643 25.8 116.8
enqueue requests 92,643 25.8 116.8
enqueue waits 1 0.0 0.0
execute count 261,199 72.7 329.4
Instance Activity Stats DB/Inst: xxxx/xxxx Snaps: 16598-16599
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
free buffer inspected 1,719,681 478.7 2,168.6
free buffer requested 1,804,893 502.4 2,276.0
heap block compress 1,617 0.5 2.0
hot buffers moved to head of LRU 295,841 82.4 373.1
immediate (CR) block cleanout ap 3,664 1.0 4.6
immediate (CURRENT) block cleano 6,108 1.7 7.7
index fast full scans (full) 533 0.2 0.7
index fetch by key 582,804 162.2 734.9
index scans kdiixs1 328,534 91.5 414.3
leaf node 90-10 splits 190 0.1 0.2
leaf node splits 353 0.1 0.5
logons cumulative 426 0.1 0.5
messages received 5,699 1.6 7.2
messages sent 5,699 1.6 7.2
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 4,088,134 1,137.9 5,155.3
opened cursors cumulative 46,867 13.1 59.1
parse count (failures) 27 0.0 0.0
parse count (hard) 4,933 1.4 6.2
parse count (total) 51,443 14.3 64.9
parse time cpu 1,748 0.5 2.2
parse time elapsed 8,012 2.2 10.1
physical read IO requests 390,490 108.7 492.4
physical reads 2,028,713 564.7 2,558.3
physical reads cache 1,793,229 499.2 2,261.3
physical reads cache prefetch 1,557,452 433.5 1,964.0
physical reads direct 235,484 65.6 297.0
physical reads direct (lob) 36 0.0 0.1
physical reads direct temporary 235,426 65.5 296.9
physical reads prefetch warmup 6,031 1.7 7.6
physical write IO requests 15,092 4.2 19.0
physical writes 196,287 54.6 247.5
physical writes direct 179,731 50.0 226.7
physical writes direct (lob) 39 0.0 0.1
physical writes direct temporary 179,664 50.0 226.6
physical writes from cache 16,556 4.6 20.9
physical writes non checkpoint 194,791 54.2 245.6
pinned buffers inspected 2 0.0 0.0
prefetch warmup blocks aged out 1,292 0.4 1.6
prefetched blocks aged out befor 293,795 81.8 370.5
process last non-idle time 1,954 0.5 2.5
recursive calls 1,997,982 556.1 2,519.5
recursive cpu usage 50,834 14.2 64.1
redo blocks written 224,755 62.6 283.4
redo buffer allocation retries 3 0.0 0.0
redo entries 357,063 99.4 450.3
redo log space requests 3 0.0 0.0
redo log space wait time 123 0.0 0.2
redo ordering marks 5,391 1.5 6.8
redo size 109,558,292 30,495.9 138,156.7
redo synch time 398 0.1 0.5
redo synch writes 300 0.1 0.4
redo wastage 1,486,252 413.7 1,874.2
redo write time 1,657 0.5 2.1
redo writer latching time 7 0.0 0.0
redo writes 5,499 1.5 6.9
rollback changes - undo records 5,688 1.6 7.2
rows fetched via callback 96,659 26.9 121.9
session connect time 0 0.0 0.0
session logical reads 6,234,873 1,735.5 7,862.4
Instance Activity Stats DB/Inst: xxxx/xxxx Snaps: 16598-16599
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
session pga memory 162,220,520 45,154.5 204,565.6
session pga memory max 341,641,032 95,096.7 430,821.0
session uga memory 790,286,168,264 219,978,374.3 #############
session uga memory max 267,584,080 74,482.8 337,432.6
shared hash latch upgrades - no 327,865 91.3 413.5
shared hash latch upgrades - wai 47 0.0 0.1
sorts (disk) 44 0.0 0.1
sorts (memory) 20,909 5.8 26.4
sorts (rows) 124,334,820 34,609.0 156,790.4
summed dirty queue length 10,849 3.0 13.7
switch current to new buffer 125 0.0 0.2
table fetch by rowid 362,785 101.0 457.5
table fetch continued row 1,811 0.5 2.3
table scan blocks gotten 2,989,662 832.2 3,770.1
table scan rows gotten 102,945,486 28,655.2 129,817.8
table scans (long tables) 65 0.0 0.1
table scans (short tables) 8,082 2.3 10.2
transaction rollbacks 72 0.0 0.1
undo change vector size 40,524,316 11,280.1 51,102.5
user I/O wait time 140,959 39.2 177.8
user calls 7,168 2.0 9.0
user commits 654 0.2 0.8
user rollbacks 139 0.0 0.2
workarea executions - onepass 30 0.0 0.0
workarea executions - optimal 13,486 3.8 17.0
write clones created in foregrou 6 0.0 0.0
-------------------------------------------------------------
contd on next ...
Edited by: LazyDBA10g on Jul 8, 2009 4:50 AM