Oracle Technology Network
Articles
Articles
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2384218455 o102ee1 1 22-Mar-07 20:51 10.2.0.3.0 NO
Host Name: hpap0601 Num CPUs: 4 Phys Memory (MB): 0
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1109 02-Apr-07 20:01:08 24 12.5
End Snap: 1110 02-Apr-07 20:09:21 24 12.7
Elapsed: 8.22 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 92M Std Block Size: 8K
Shared Pool Size: 148M Log Buffer: 6,852K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 11,202.52 2,661.61
Logical reads: 109,025.27 25,903.35
Block changes: 49.34 11.72
Physical reads: 6,628.61 1,574.89
Physical writes: 4.18 0.99
User calls: 290.65 69.05
Parses: 116.82 27.75
Hard parses: 53.77 12.78
Sorts: 8.10 1.92
Logons: 0.04 0.01
Executes: 130.14 30.92
Transactions: 4.21
% Blocks changed per Read: 0.05 Recursive Call %: 57.52
Rollback per transaction %: 0.10 Rows per Sort: 11.93
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.01 Redo NoWait %: 100.00
Buffer Hit %: 93.92 In-memory Sort %: 100.00
Library Hit %: 60.87 Soft Parse %: 53.97
Execute to Parse %: 10.24 Latch Hit %: 98.31
Parse CPU to Parse Elapsd %: 89.83 % Non-Parse CPU: 96.16
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 70.13 72.57
% SQL with executions>1: 41.52 24.36
% Memory for SQL w/exec>1: 43.38 29.71
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1,543 82.9
read by other session 532,940 194 0 10.4
db file scattered read 464,025 56 0 3.0
db file sequential read 558,568 20 0 1.1
latch: cache buffers chains 3,042 13 4 .7
-------------------------------------------------------------
Host CPU (CPUs: 4)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.25 4.15 81.70 12.19 5.71 0.23
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 77.36
% of busy CPU for Instance: 82.05
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): .0 .0
SGA use (MB): 256.0 256.0
PGA use (MB): 86.5 85.4
% Host Mem used for SGA+PGA: 931312.2 741636.2
-------------------------------------------------------------
Time Model System Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 1,790.1 95.6
DB CPU 1,522.7 81.4
parse time elapsed 92.8 5.0
hard parse elapsed time 75.9 4.1
PL/SQL compilation elapsed time 27.9 1.5
PL/SQL execution elapsed time 1.2 .1
hard parse (sharing criteria) elaps 0.3 .0
connection management call elapsed 0.1 .0
repeated bind elapsed time 0.1 .0
hard parse (bind mismatch) elapsed 0.1 .0
sequence load elapsed time 0.0 .0
DB time 1,871.8
background elapsed time 25.6
background cpu time 2.7
-------------------------------------------------------------
Wait Events DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
read by other session 532,940 0 194 0 256.8
db file scattered read 464,025 0 56 0 223.6
db file sequential read 558,568 0 20 0 269.2
latch: cache buffers chains 3,042 0 13 4 1.5
latch free 2,100 0 10 5 1.0
control file parallel write 169 0 8 49 0.1
log file parallel write 2,244 0 8 3 1.1
log file sync 2,039 0 6 3 1.0
latch: shared pool 307 0 1 2 0.1
latch: library cache 72 0 0 3 0.0
SGA: MMAN sleep for component shr 16 75 0 11 0.0
library cache load lock 1 0 0 133 0.0
cursor: pin S wait on X 9 100 0 14 0.0
latch: cache buffers lru chain 42 0 0 3 0.0
buffer exterminate 1 0 0 99 0.0
SQL*Net break/reset to client 36 0 0 2 0.0
LGWR wait for redo copy 68 0 0 1 0.0
latch: row cache objects 8 0 0 5 0.0
SQL*Net more data to client 31 0 0 1 0.0
wait list latch free 2 0 0 13 0.0
control file sequential read 964 0 0 0 0.5
latch: library cache pin 1 0 0 10 0.0
latch: library cache lock 4 0 0 1 0.0
latch: object queue header operat 2 0 0 2 0.0
SQL*Net message from client 91,187 0 2,748 30 43.9
Streams AQ: qmn slave idle wait 18 0 498 27680 0.0
Streams AQ: qmn coordinator idle 39 54 498 12775 0.0
wait for unread message on broadc 491 100 481 980 0.2
jobq slave wait 164 95 479 2923 0.1
Streams AQ: waiting for messages 98 100 479 4885 0.0
SQL*Net message to client 91,187 0 1 0 43.9
-------------------------------------------------------------
Background Wait Events DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
control file parallel write 169 0 8 49 0.1
log file parallel write 2,244 0 8 3 1.1
SGA: MMAN sleep for component shr 16 75 0 11 0.0
events in waitclass Other 90 0 0 1 0.0
db file sequential read 20 0 0 0 0.0
rdbms ipc message 3,658 43 3,827 1046 1.8
smon timer 2 100 586 ###### 0.0
Streams AQ: qmn slave idle wait 18 0 498 27680 0.0
Streams AQ: qmn coordinator idle 39 54 498 12775 0.0
pmon timer 165 100 481 2916 0.1
-------------------------------------------------------------
Wait Event Histogram DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 68 94.1 1.5 1.5 1.5 1.5
SGA: MMAN sleep for compon 16 6.3 12.5 68.8 12.5
SQL*Net break/reset to cli 36 88.9 2.8 2.8 2.8 2.8
SQL*Net more data to clien 31 71.0 29.0
buffer exterminate 1 100.0
control file parallel writ 169 4.7 95.3
control file sequential re 964 100.0
cursor: pin S wait on X 9 88.9 11.1
db file scattered read 464K 99.8 .2 .0 .0 .0 .0 .0
db file sequential read 558K 100.0 .0 .0 .0 .0 .0 .0
latch free 1697 59.4 11.1 10.8 7.2 4.5 4.2 2.7
latch: cache buffers chain 2224 65.8 8.7 9.0 5.4 4.2 3.9 3.0
latch: cache buffers lru c 34 55.9 14.7 2.9 5.9 20.6
latch: library cache 55 38.2 9.1 12.7 25.5 10.9 3.6
latch: library cache lock 2 50.0 50.0
latch: library cache pin 1 100.0
latch: object queue header 2 50.0 50.0
latch: row cache objects 4 50.0 25.0 25.0
latch: shared pool 258 60.5 4.7 10.5 16.3 7.0 .8 .4
library cache load lock 1 100.0
log file parallel write 2244 .9 11.5 74.4 8.9 2.3 1.6 .3
log file sync 2039 .4 9.5 84.7 2.9 1.6 .5 .3
read by other session 532K 93.9 2.4 1.9 1.2 .4 .1 .1
wait list latch free 2 100.0
SQL*Net message from clien 91K 65.7 17.6 2.4 4.7 4.3 3.1 2.2 .2
SQL*Net message to client 91K 100.0 .0
SQL*Net more data from cli 2 100.0
Streams AQ: qmn coordinato 39 33.3 2.6 5.1 5.1 53.8
Streams AQ: qmn slave idle 18 100.0
Streams AQ: waiting for me 98 100.0
jobq slave wait 164 100.0
pmon timer 165 .6 99.4
rdbms ipc message 3658 1.1 1.1 50.1 3.2 .3 .1 14.6 29.7
smon timer 2 100.0
wait for unread message on 491 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 1,523
-> Captured SQL accounts for 5.4% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
56.72 1,000 0.06 3.7 77.97 1,557,001 528477046
Module: SQL*Plus
SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 1,872
-> Captured SQL accounts for 5.6% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
77.97 1,000 0.08 4.2 56.72 17 528477046
Module: SQL*Plus
SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 53,749,458
-> Captured SQL accounts for 3.5% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,557,001 1,000 1,557.0 2.9 56.72 77.97 528477046
Module: SQL*Plus
SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> End Executions Threshold: 100 Total Executions: 64,158
-> Captured SQL accounts for 55.6% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
10,000 9,999 1.0 0.00 0.00 1361892726
Module: SQL*Plus
SELECT ENAME FROM EMP2 WHERE EMP2NO = :num
10,000 10,000 1.0 0.00 0.00 4111966515
Module: SQL*Plus
select distinct emp1.deptno from emp1, dept where emp1.deptno =
dept.deptno and dept.deptno = 10
2,266 1,391 0.6 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
1,199 18,564 15.5 0.00 0.00 199663413
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
1,028 1,542 1.5 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
1,000 1,000 1.0 0.06 0.08 528477046
Module: SQL*Plus
SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
1,000 1,000 1.0 0.00 0.00 912059479
Module: SQL*Plus
SELECT COUNT(ENAME) FROM EMP3 WHERE EMP3NO = 5
1,000 1,000 1.0 0.00 0.00 2340347601
Module: SQL*Plus
update emp4 set ename = 'aaa' where emp4no = 1000000
1,000 1,000 1.0 0.00 0.00 4173520048
Module: SQL*Plus
update EMP5 set ename = 'aaa' where EMP5no = 100
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> End Parse Calls Threshold: 1000 Total Parse Calls: 57,590
-> Captured SQL accounts for 47.9% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
10,000 10,000 17.36 1361892726
Module: SQL*Plus
SELECT ENAME FROM EMP2 WHERE EMP2NO = :num
10,000 10,000 17.36 4111966515
Module: SQL*Plus
select distinct emp1.deptno from emp1, dept where emp1.deptno =
dept.deptno and dept.deptno = 10
1,000 1,000 1.74 528477046
Module: SQL*Plus
SELECT COUNT(ename) FROM emp3 WHERE emp3no = 1
1,000 1,000 1.74 912059479
Module: SQL*Plus
SELECT COUNT(ENAME) FROM EMP3 WHERE EMP3NO = 5
1,000 1,000 1.74 2340347601
Module: SQL*Plus
update emp4 set ename = 'aaa' where emp4no = 1000000
1,000 1,000 1.74 4173520048
Module: SQL*Plus
update EMP5 set ename = 'aaa' where EMP5no = 100
617 617 1.07 1348827743
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2
and block#=:3
-------------------------------------------------------------
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 154,277 312.9 74.4
CPU used when call started 153,996 312.4 74.2
CR blocks created 159 0.3 0.1
Cached Commit SCN referenced 1,488,000 3,018.3 717.1
Commit SCN cached 1 0.0 0.0
DB time 337,301 684.2 162.6
DBWR checkpoint buffers written 1,502 3.1 0.7
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 13 0.0 0.0
DBWR undo block writes 359 0.7 0.2
IMU CR rollbacks 56 0.1 0.0
IMU Flushes 121 0.3 0.1
IMU Redo allocation size 61,816 125.4 29.8
IMU commits 2,068 4.2 1.0
IMU contention 0 0.0 0.0
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 0 0.0 0.0
IMU recursive-transaction flush 0 0.0 0.0
IMU undo allocation size 1,991,312 4,039.2 959.7
IMU- failed to get a private stra 0 0.0 0.0
Misses for writing mapping 0 0.0 0.0
SMON posted for undo segment shri 0 0.0 0.0
SQL*Net roundtrips to/from client 91,169 184.9 43.9
active txn count during cleanout 196 0.4 0.1
application wait time 6 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 1,571 3.2 0.8
branch node splits 0 0.0 0.0
buffer is not pinned count 75,986 154.1 36.6
buffer is pinned count 20,362 41.3 9.8
bytes received via SQL*Net from c 12,512,013 25,379.3 6,029.9
bytes sent via SQL*Net to client 19,119,694 38,782.3 9,214.3
calls to get snapshot scn: kcmgss 177,339 359.7 85.5
calls to kcmgas 3,066 6.2 1.5
calls to kcmgcs 103 0.2 0.1
change write time 59 0.1 0.0
cleanout - number of ktugct calls 229 0.5 0.1
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 18 0.0 0.0
cluster key scan block gets 11,191 22.7 5.4
cluster key scans 6,073 12.3 2.9
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 2 0.0 0.0
commit batch/immediate requested 2 0.0 0.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 15 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 3,758 7.6 1.8
commit cleanouts successfully com 3,743 7.6 1.8
commit immediate performed 2 0.0 0.0
commit immediate requested 2 0.0 0.0
commit txn count during cleanout 100 0.2 0.1
concurrency wait time 1,407 2.9 0.7
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
consistent changes 159 0.3 0.1
consistent gets 53,724,430 108,974.5 25,891.3
consistent gets - examination 117,415 238.2 56.6
consistent gets direct 0 0.0 0.0
consistent gets from cache 53,724,430 108,974.5 25,891.3
cursor authentications 49 0.1 0.0
data blocks consistent reads - un 159 0.3 0.1
db block changes 24,326 49.3 11.7
db block gets 25,028 50.8 12.1
db block gets direct 0 0.0 0.0
db block gets from cache 25,028 50.8 12.1
deferred (CURRENT) block cleanout 3,034 6.2 1.5
dirty buffers inspected 223 0.5 0.1
enqueue conversions 148 0.3 0.1
enqueue releases 36,206 73.4 17.5
enqueue requests 36,206 73.4 17.5
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 64,158 130.1 30.9
failed probes on index block recl 0 0.0 0.0
frame signature mismatch 0 0.0 0.0
free buffer inspected 4,202,351 8,524.0 2,025.2
free buffer requested 3,268,499 6,629.8 1,575.2
heap block compress 8 0.0 0.0
hot buffers moved to head of LRU 2,834 5.8 1.4
immediate (CR) block cleanout app 18 0.0 0.0
immediate (CURRENT) block cleanou 303 0.6 0.2
index crx upgrade (positioned) 2,026 4.1 1.0
index fast full scans (full) 4 0.0 0.0
index fetch by key 43,803 88.9 21.1
index scans kdiixs1 19,533 39.6 9.4
leaf node 90-10 splits 13 0.0 0.0
leaf node splits 69 0.1 0.0
lob reads 0 0.0 0.0
lob writes 21 0.0 0.0
lob writes unaligned 21 0.0 0.0
logons cumulative 20 0.0 0.0
messages received 2,939 6.0 1.4
messages sent 2,939 6.0 1.4
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 53,486,821 108,492.5 25,776.8
opened cursors cumulative 63,906 129.6 30.8
parse count (failures) 0 0.0 0.0
parse count (hard) 26,509 53.8 12.8
parse count (total) 57,590 116.8 27.8
parse time cpu 5,925 12.0 2.9
parse time elapsed 6,596 13.4 3.2
physical read IO requests 1,022,578 2,074.2 492.8
physical read bytes 26,770,661,376 54,301,544.4 ############
physical read total IO requests 1,023,546 2,076.2 493.3
physical read total bytes 26,786,488,320 54,333,647.7 ############
physical read total multi block r 464,025 941.2 223.6
physical reads 3,267,903 6,628.6 1,574.9
physical reads cache 3,267,903 6,628.6 1,574.9
physical reads cache prefetch 2,245,325 4,554.4 1,082.1
physical reads direct 0 0.0 0.0
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 912 1.9 0.4
physical write bytes 16,891,904 34,263.5 8,140.7
physical write total IO requests 3,661 7.4 1.8
physical write total bytes 31,833,088 64,570.2 15,341.3
physical write total multi block 2,483 5.0 1.2
physical writes 2,062 4.2 1.0
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 0 0.0 0.0
physical writes from cache 2,062 4.2 1.0
physical writes non checkpoint 1,296 2.6 0.6
pinned buffers inspected 15,285 31.0 7.4
prefetch warmup blocks aged out b 0 0.0 0.0
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 494 1.0 0.2
recursive calls 194,037 393.6 93.5
recursive cpu usage 8,315 16.9 4.0
redo blocks written 12,958 26.3 6.2
redo buffer allocation retries 0 0.0 0.0
redo entries 10,778 21.9 5.2
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 266 0.5 0.1
redo size 5,522,844 11,202.5 2,661.6
redo subscn max counts 546 1.1 0.3
redo synch time 650 1.3 0.3
redo synch writes 2,474 5.0 1.2
redo wastage 980,100 1,988.0 472.3
redo write time 790 1.6 0.4
redo writer latching time 5 0.0 0.0
redo writes 2,244 4.6 1.1
rollback changes - undo records a 2 0.0 0.0
rollbacks only - consistent read 159 0.3 0.1
rows fetched via callback 18,912 38.4 9.1
session connect time 0 0.0 0.0
session cursor cache hits 34,529 70.0 16.6
session logical reads 53,749,458 109,025.3 25,903.4
session pga memory 11,301,536 22,924.0 5,446.5
session pga memory max 38,695,584 78,490.0 18,648.5
session uga memory 34,360,239,268 69,696,225.7 ############
session uga memory max 20,556,552 41,696.9 9,906.8
shared hash latch upgrades - no w 2,541 5.2 1.2
sorts (disk) 0 0.0 0.0
sorts (memory) 3,991 8.1 1.9
sorts (rows) 47,628 96.6 23.0
sql area evicted 1,089 2.2 0.5
sql area purged 0 0.0 0.0
summed dirty queue length 382 0.8 0.2
switch current to new buffer 20 0.0 0.0
table fetch by rowid 33,856 68.7 16.3
table fetch continued row 232 0.5 0.1
table scan blocks gotten 53,331,879 108,178.3 25,702.1
table scan rows gotten 8,653,903,739 17,553,557.3 4,170,556.0
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scans (long tables) 16,002 32.5 7.7
table scans (short tables) 1,280 2.6 0.6
total number of times SMON posted 0 0.0 0.0
transaction rollbacks 2 0.0 0.0
undo change vector size 1,803,480 3,658.2 869.2
user I/O wait time 27,090 55.0 13.1
user calls 143,288 290.7 69.1
user commits 2,073 4.2 1.0
user rollbacks 2 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 2,370 4.8 1.1
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 24 24
opened cursors current 299 304
session cursor cache count 323,078 323,468
-------------------------------------------------------------
Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 185,918
IDLE_TIME 11,254
IOWAIT_TIME 457
NICE_TIME 689
SYS_TIME 24,030
USER_TIME 161,085
LOAD 4
PHYSICAL_MEMORY_BYTES 48,264
NUM_CPUS 4
NUM_CPU_SOCKETS 2
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
USERS
1,021,641 2,072 0.1 3.2 3 0 532,785 0.4
SYSAUX
641 1 0.0 1.0 827 2 0 0.0
SYSTEM
243 0 0.0 1.0 21 0 0 0.0
UNDOTBS1
11 0 0.0 1.0 61 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf
641 1 0.0 8 1.0 827 2 0
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf
243 0 0.0 1 1.0 21 0 0
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf
11 0 0.0 1 1.0 61 0 0
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf
1,021,641 2,072 0.1 ### 3.2 3 0 532,785 0.4
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf
635 0 2 0 0 0
SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf
244 0 0 0 0 0
UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf
11 0 0 0 0 0
USERS /opt/app/oracle/oradata/o102ee1/users01.dbf
557,434 38 20 11 11 1
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 11K 94 53,671,862 3,267,827 2,062 0 0 532,936
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 10 1896 18534 34346 184320 34346
E 0 9 1171 10865 32322 184320 32322
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: O102EE1/o102ee1 End Snap: 1110
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 8 .1 1 1.0 35,825 4,663 14.5
D 16 .2 2 1.0 34,792 4,476 13.9
D 24 .3 3 1.0 34,779 4,474 13.9
D 32 .3 4 1.0 34,766 4,471 13.9
D 40 .4 5 1.0 34,745 4,468 13.9
D 48 .5 6 1.0 34,734 4,466 13.9
D 56 .6 7 1.0 34,730 4,465 13.9
D 64 .7 8 1.0 34,727 4,464 13.9
D 72 .8 9 1.0 34,725 4,464 13.9
D 80 .9 10 1.0 34,723 4,464 13.9
D 88 1.0 11 1.0 34,721 4,463 13.9
D 92 1.0 11 1.0 34,721 4,463 13.9
D 96 1.0 12 1.0 34,720 4,463 13.9
D 104 1.1 13 1.0 34,719 4,463 13.9
D 112 1.2 14 1.0 34,718 4,463 13.9
D 120 1.3 15 1.0 34,717 4,462 13.8
D 128 1.4 16 1.0 34,716 4,462 13.8
D 136 1.5 17 1.0 34,715 4,462 13.8
D 144 1.6 18 1.0 34,714 4,462 13.8
D 152 1.7 19 1.0 34,713 4,462 13.8
D 160 1.7 20 0.9 31,369 3,857 12.0
-------------------------------------------------------------
Buffer wait Statistics DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
---------------------- ----------- ------------------- -------------
data block 532,796 202 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> 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 Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 36 0
Warning: pga_aggregate_target was set too low for current workload, as this
value was exceeded during this interval. Use the PGA Advisory view
to help identify a different value for pga_aggregate_target.
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 16 4 86.5 0.0 .0 .0 .0 3,276
E 16 4 85.4 0.0 .0 .0 .0 3,276
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 2,313 2,313 0 0
64K 128K 5 5 0 0
128K 256K 1 1 0 0
256K 512K 2 2 0 0
512K 1024K 40 40 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: O102EE1/o102ee1 End Snap: 1110
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
12 0.8 12,542.1 662.4 95.0 134
16 1.0 12,542.1 27.3 100.0 134
19 1.2 12,542.1 27.3 100.0 134
22 1.4 12,542.1 27.3 100.0 134
26 1.6 12,542.1 27.3 100.0 133
29 1.8 12,542.1 27.3 100.0 128
32 2.0 12,542.1 25.9 100.0 104
48 3.0 12,542.1 0.0 100.0 19
64 4.0 12,542.1 0.0 100.0 1
96 6.0 12,542.1 0.0 100.0 0
128 8.0 12,542.1 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> 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
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 86.6 43.9 9.1 3.6 6.4 26 26 24
Other 75.1 3.1 6.5 26 26 24
Freeable 9.1 .0 .6 .3 1 14
SQL 1.7 .6 .1 .1 0 8 15
PL/SQL .7 .2 .0 .0 0 0 24
E -------- 85.4 43.8 8.1 3.6 6.5 26 26 24
Other 74.9 3.1 6.5 26 26 24
Freeable 8.1 .0 .7 .4 1 12
SQL 1.7 .6 .1 .1 0 8 15
PL/SQL .7 .2 .0 .0 0 0 24
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 5 DBW0 -------- 26.1 12.3 .1 26.1 26.2
Other 26.0 26.0 26.1
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
6 LGWR -------- 21.8 10.3 .0 21.8 21.8
Other 21.8 21.8 21.8
PL/SQL .0 .0 .0 .0
19 ------------ 5.8 4.4 1.1 5.8 6.9
Other 4.3 4.3 4.3
Freeable 1.1 .0 1.1
SQL .2 .1 .2 2.2
PL/SQL .1 .0 .1 .1
16 TNS V1-V3 --- 4.1 2.0 .7 4.1 4.1
Other 3.2 3.2 3.2
Freeable .7 .0 .7
SQL .1 .1 .1 1.4
PL/SQL .0 .0 .0 .0
28 ------------ 3.8 3.6 .0 3.8 11.1
Other 3.4 3.4 7.9
SQL .2 .1 .2 3.0
PL/SQL .2 .0 .2 .2
11 MMON -------- 2.9 1.3 1.1 2.9 3.0
Other 1.6 1.6 1.6
Freeable 1.1 .0 1.1
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
20 ------------ 2.4 1.3 1.1 2.4 2.9
Other 1.2 1.2 1.2
Freeable 1.1 .0 1.1
SQL .1 .0 .1 1.4
PL/SQL .0 .0 .0 .0
18 ------------ 2.2 1.4 .8 2.2 2.4
Other 1.3 1.3 1.3
Freeable .8 .0 .8
SQL .1 .1 .1 1.0
PL/SQL .0 .0 .0 .0
23 ------------ 2.1 1.1 .3 2.1 14.1
Other 1.7 1.7 5.6
Freeable .3 .0 .3
SQL .1 .0 .1 8.2
PL/SQL .0 .0 .0 .0
13 q000 -------- 1.8 .6 .6 1.8 1.8
Other 1.1 1.1 1.1
Freeable .6 .0 .6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
8 SMON -------- 1.8 .5 .7 1.8 2.3
Other 1.0 1.0 1.0
Freeable .7 .0 .7
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
25 ------------ 1.8 1.0 .6 1.8 2.1
Other .7 .7 .7
Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 25 Freeable .6 .0 .6
SQL .4 .1 .4 1.3
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 1.7 .6 .6 1.7 2.0
Other 1.0 1.0 1.0
Freeable .6 .0 .6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
15 J000 -------- 1.7 .2 .9 1.7 5.0
Freeable .9 .0 .9
Other .6 .6 1.7
PL/SQL .1 .0 .1 .1
SQL .0 .0 .0 2.3
7 CKPT -------- 1.5 .3 .0 1.5 1.5
Other 1.4 1.4 1.4
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
24 ------------ 1.3 1.1 .2 1.3 3.4
Other 1.0 1.0 1.5
Freeable .2 .0 .2
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
9 RECO -------- 1.1 .5 .3 1.1 1.1
Other .8 .8 .8
Freeable .3 .0 .3
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
E 5 DBW0 -------- 26.1 12.3 .1 26.1 26.2
Other 26.0 26.0 26.1
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
6 LGWR -------- 21.8 10.3 .0 21.8 21.8
Other 21.8 21.8 21.8
PL/SQL .0 .0 .0 .0
19 ------------ 5.8 4.4 1.1 5.8 6.9
Other 4.3 4.3 4.3
Freeable 1.1 .0 1.1
SQL .2 .1 .2 2.2
PL/SQL .1 .0 .1 .1
16 TNS V1-V3 --- 3.9 2.1 .6 3.9 3.9
Other 3.2 3.2 3.2
Freeable .6 .0 .6
SQL .1 .1 .1 1.3
PL/SQL .0 .0 .0 .0
28 ------------ 3.8 3.6 .0 3.8 11.1
Other 3.4 3.4 7.9
SQL .2 .1 .2 3.0
PL/SQL .2 .0 .2 .2
11 MMON -------- 2.9 1.3 1.1 2.9 3.0
Other 1.6 1.6 1.6
Freeable 1.1 .0 1.1
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 18 ------------ 2.2 1.4 .8 2.2 2.4
Other 1.3 1.3 1.3
Freeable .8 .0 .8
SQL .1 .1 .1 1.0
PL/SQL .0 .0 .0 .0
23 ------------ 2.1 1.0 1.1 2.1 14.1
Freeable 1.1 .0 1.1
Other .9 .9 4.7
SQL .1 .0 .1 8.2
PL/SQL .0 .0 .0 .0
13 q000 -------- 1.8 .6 .6 1.8 1.8
Other 1.1 1.1 1.1
Freeable .6 .0 .6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
8 SMON -------- 1.8 .5 .0 1.8 2.3
Other 1.7 1.7 1.7
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
25 ------------ 1.8 1.0 .6 1.8 2.1
Other .7 .7 .7
Freeable .6 .0 .6
SQL .4 .1 .4 1.3
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 1.7 .6 .6 1.7 2.0
Other 1.0 1.0 1.0
Freeable .6 .0 .6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
15 J000 -------- 1.7 .1 .9 1.7 5.0
Freeable .9 .0 .9
Other .6 .6 1.7
PL/SQL .1 .0 .1 .1
SQL .0 .0 .0 2.3
7 CKPT -------- 1.5 .3 .0 1.5 1.5
Other 1.4 1.4 1.4
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
20 ------------ 1.4 1.3 .0 1.4 2.9
Other 1.2 1.2 1.5
SQL .1 .0 .1 1.4
PL/SQL .0 .0 .0 .0
24 ------------ 1.3 1.1 .2 1.3 3.4
Other 1.0 1.0 1.5
Freeable .2 .0 .2
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
9 RECO -------- 1.1 .5 .3 1.1 1.1
Other .8 .8 .8
Freeable .3 .0 .3
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
-------------------------------------------------------------
Undo Segment Summary DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
1 .8 4,944 105 3 15/15.8 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
02-Apr 20:11 304 2,644 105 3 16 0/0 0/0/0/0/0/0
02-Apr 20:01 481 2,300 0 3 15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->"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
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 322 0.0 0 0
AWR Alerted Metric Eleme 1,753 0.0 0 0
Consistent RBA 2,244 0.0 0 0
FOB s.o list latch 46 0.0 0 0
In memory undo latch 9,439 0.0 0 2,310 0.0
JS queue state obj latch 2,970 0.0 0 0
KMG MMAN ready and start 180 0.0 0 0
KMG resize request state 8 0.0 0 0
KTF sga latch 2 0.0 0 150 0.0
KWQP Prop Status 1 0.0 0 0
MQL Tracking Latch 0 0 10 0.0
Memory Management Latch 96 0.0 0 180 0.0
OS process 36 0.0 0 0
OS process allocation 188 0.0 0 0
OS process: request allo 24 0.0 0 0
PL/SQL warning settings 10,096 0.0 0 0
SGA IO buffer pool latch 13 0.0 0 13 0.0
SQL memory manager latch 435 0.0 0 161 0.0
SQL memory manager worka 40,810 0.0 0 0
Shared B-Tree 22 0.0 0 0
active checkpoint queue 864 0.0 0 0
active service list 996 0.0 0 164 0.6
archive control 5 0.0 0 0
buffer pool 19 0.0 0 0
cache buffer handles 138 0.0 0 0
cache buffers chains 111,252,412 1.8 0.0 13 42,776,445 0.0
cache buffers lru chain 7,232 11.9 0.0 0 88,567,573 2.9
cache table scan latch 463,768 0.1 0.0 0 463,768 0.1
channel handle pool latc 24 0.0 0 0
channel operations paren 2,902 0.0 0 0
checkpoint queue latch 12,690 0.0 0 1,792 0.0
client/application info 295 0.0 0 0
compile environment latc 2,481 0.0 0 0
dml lock allocation 7,058 0.0 0 0
dummy allocation 40 0.0 0 0
enqueue hash chains 72,558 0.5 0.0 0 0
enqueues 62,579 1.6 0.0 0 0
event group latch 12 0.0 0 0
file cache latch 21 0.0 0 0
global KZLD latch for me 10 0.0 0 0
hash table column usage 376 0.0 0 34,992 0.9
job workq parent latch 0 0 16 0.0
job_queue_processes para 16 0.0 0 0
kks stats 171,269 0.4 0.0 0 0
ksuosstats global area 37 0.0 0 0
ktm global data 2 0.0 0 0
kwqbsn:qsga 22 0.0 0 0
lgwr LWN SCN 2,354 0.0 0 0
library cache 861,932 1.3 0.0 0 123,293 0.6
library cache load lock 3,170 0.0 0 0
Latch Activity DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->"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
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache lock 223,214 0.4 0.0 0 0
library cache lock alloc 677 0.0 0 0
library cache pin 211,158 0.3 0.0 0 0
library cache pin alloca 409 0.0 0 0
list of block allocation 100 0.0 0 0
messages 11,142 0.0 0.0 0 0
mostly latch-free SCN 2,364 0.0 0.0 0 0
multiblock read objects 2,016,942 0.3 0.0 0 0
ncodef allocation latch 8 0.0 0 0
object queue header heap 1,763 0.0 0 47 0.0
object queue header oper 6,543,456 0.0 0.0 0 468 0.0
object stats modificatio 685 0.0 0 0
parallel query alloc buf 64 0.0 0 0
parameter table allocati 28 0.0 0 0
post/wait queue 5,073 0.0 0 2,039 0.0
process allocation 24 0.0 0 12 0.0
process group creation 24 0.0 0 0
qmn task queue latch 72 0.0 0 0
redo allocation 11,568 0.0 0 10,789 0.0
redo copy 0 0 10,789 0.6
redo writing 7,938 0.0 0 0
resmgr group change latc 71 0.0 0 0
resmgr:actses active lis 42 0.0 0 0
resmgr:actses change gro 20 0.0 0 0
resmgr:free threads list 40 0.0 0 0
resmgr:schema config 2 0.0 0 0
row cache objects 429,914 4.8 0.0 0 2,141 0.0
rules engine rule set st 100 0.0 0 0
sequence cache 126 0.0 0 0
session allocation 279,683 0.0 0.0 0 0
session idle bit 289,035 0.1 0.0 0 0
session state list latch 36 0.0 0 0
session switching 8 0.0 0 0
session timer 165 0.0 0 0
shared pool 981,448 3.7 0.0 1 0
shared pool simulator 1,302,747 0.0 0.0 0 0
simulator hash latch 12,314,517 0.0 0.0 0 0
simulator lru latch 6,594,836 5.8 0.0 10 206,707 0.0
sort extent pool 16 0.0 0 0
state object free list 2 0.0 0 0
statistics aggregation 112 0.0 0 0
threshold alerts latch 34 0.0 0 0
transaction allocation 30 0.0 0 0
transaction branch alloc 8 0.0 0 0
undo global data 12,802 0.0 0 0
user lock 56 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
cache buffers chains 111,252,412 1,967,365 3,042 1,965,141
simulator lru latch 6,594,836 384,606 2,056 382,939
shared pool 981,448 36,195 307 35,937
row cache objects 429,914 20,635 8 20,631
library cache 861,932 11,175 72 11,120
multiblock read objects 2,016,942 5,524 32 5,502
simulator hash latch 12,314,517 963 7 958
library cache lock 223,214 884 4 882
cache buffers lru chain 7,232 864 42 830
kks stats 171,269 716 5 713
library cache pin 211,158 654 1 653
object queue header operat 6,543,456 45 2 43
-------------------------------------------------------------
Latch Miss Sources DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbgtcr: kslbegin excl 0 3,341 4,425
cache buffers chains kcbrls: kslbegin 0 1,252 228
cache buffers chains kcbzib: multi-block read: 0 103 0
cache buffers chains kcbzib: finish free bufs 0 89 24
cache buffers chains kcbzwb 0 50 186
cache buffers chains kcbzgb: scan from tail. no 0 18 0
cache buffers chains kcbbic2 0 1 0
cache buffers chains kcbbic1 0 1 0
cache buffers lru chain kcbzar: KSLNBEGIN 0 35 0
cache buffers lru chain kcbzgws 0 6 33
cache buffers lru chain kcbzgm 0 1 0
kks stats kks stats alloc/free 0 5 5
library cache kglic 0 11 1
library cache kgldti: 2child 0 7 4
library cache kglobpn: child: 0 5 12
library cache kglukp: child 0 5 8
library cache kglpndl: child: after proc 0 5 1
library cache kglpin 0 5 2
library cache kglhdgc: child: 0 2 0
library cache kglpndl: child: before pro 0 1 3
library cache lock kgllkdl: child: no lock ha 0 21 24
library cache lock kgllkal: child: multiinsta 0 2 4
library cache lock kgllkdl: child: cleanup 0 2 0
library cache pin kglpndl 0 1 0
multiblock read objects kcbzib: normal mbr free 0 24 6
multiblock read objects kcbzib: mbr get 0 8 26
object queue header oper kcbw_link_q 0 1 0
object queue header oper kcbw_unlink_q 0 1 0
row cache objects kqreqd: reget 0 4 0
row cache objects kqrpre: find obj 0 4 8
shared pool kghalo 0 141 106
shared pool kghupr1 0 90 109
shared pool kghfrunp: alloc: wait 0 43 1
shared pool kghalp 0 22 63
shared pool kghfrunp: clatch: nowait 0 7 0
shared pool kghfre 0 4 4
shared pool kgh: sim resz update 0 4 0
shared pool kghfrunp: clatch: wait 0 3 9
shared pool kghasp 0 3 24
simulator hash latch kcbsacc: lookup dba 0 5 3
simulator hash latch kcbsacc: insert dba 0 2 0
simulator lru latch kcbs_simulate: simulate se 0 2,055 2,045
simulator lru latch kcbs_simbufseq 0 1 11
-------------------------------------------------------------
Mutex Sleep DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> ordered by Wait Time desc
Wait
Mutex Type Location Sleeps Time (s)
------------------ -------------------------------- -------------- ------------
Cursor Pin kkslce [KKSCHLPIN2] 9 0.1
-------------------------------------------------------------
Segments by Logical Reads DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> End Segment Logical Reads Threshold: 10000
-> Pct Total shows % of logical reads for each top segment compared with total
logical reads for all segments captured by the Snapshot
Subobject Obj. Logical Pct
Owner Tablespace Object Name Name Type Reads Total
---------- ---------- -------------------- ------------ ----- ------------ -----
SCOTT USERS EMP1 TABLE 51,853,184 96.6
SCOTT USERS EMP3 TABLE 1,558,448 2.9
SCOTT USERS IND_EMP1_DEPTNO INDEX 132,416 .2
SCOTT USERS PK_EMP2 INDEX 29,264 .1
SCOTT USERS PK_DEPT INDEX 9,696 .0
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical Pct
Owner Tablespace Object Name Name Type Reads Total
---------- ---------- -------------------- ------------ ----- ------------ -----
SCOTT USERS EMP1 TABLE 3,266,706 100.0
SYS SYSAUX WRH$_SQL_PLAN TABLE 323 .0
SYS SYSTEM HISTGRM$ TABLE 81 .0
SYS SYSAUX WRH$_SQLTEXT TABLE 67 .0
SYS SYSAUX WRH$_SQL_PLAN_PK INDEX 60 .0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 12 0.0 0 2 1
dc_global_oids 769 3.5 0 0 32
dc_histogram_data 7,144 17.4 0 0 1,398
dc_histogram_defs 39,227 5.9 0 0 2,001
dc_object_grants 54 25.9 0 0 17
dc_object_ids 36,677 0.7 0 0 519
dc_objects 18,018 2.4 0 0 477
dc_profiles 18 0.0 0 0 1
dc_rollback_segments 54 0.0 0 0 12
dc_segments 33,972 1.8 0 3 659
dc_sequences 3 66.7 0 3 2
dc_tablespaces 5,081 0.1 0 0 5
dc_usernames 339 0.0 0 0 6
dc_users 5,439 0.2 0 0 25
outstanding_alerts 12 83.3 0 0 0
-------------------------------------------------------------
Library Cache Activity DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 93 3.2 828 2.2 15 0
CLUSTER 17 0.0 34 2.9 1 0
INDEX 38 50.0 82 23.2 0 0
SQL AREA 27,258 96.2 121,436 65.4 243 68
TABLE/PROCEDURE 1,476 7.7 84,705 1.8 888 0
TRIGGER 6 0.0 36 5.6 2 0
-------------------------------------------------------------
Rule Sets DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: O102EE1/o102ee1 End Snap: 1110
-> 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
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
84 .6 16 3,129 ####### .9 26,706 9.4 10,416,039
100 .7 31 4,889 ####### .9 20,081 7.1 10,428,762
116 .8 46 6,673 ####### .9 13,745 4.8 10,440,558
132 .9 61 8,400 ####### 1.0 7,938 2.8 10,451,497
148 1.0 76 10,147 ####### 1.0 2,835 1.0 10,461,600
164 1.1 91 11,874 ####### 1.0 1 .0 10,470,590
180 1.2 106 13,744 ####### 1.0 1 .0 10,478,287
196 1.3 121 15,945 ####### 1.1 1 .0 10,485,133
212 1.4 136 17,956 ####### 1.1 1 .0 10,492,114
228 1.5 151 19,926 ####### 1.1 1 .0 10,499,651
244 1.6 166 21,964 ####### 1.1 1 .0 10,507,531
260 1.8 181 24,544 ####### 1.1 1 .0 10,515,637
276 1.9 196 27,279 ####### 1.1 1 .0 10,524,767
292 2.0 211 30,219 ####### 1.1 1 .0 10,535,956
308 2.1 226 33,322 ####### 1.1 1 .0 10,550,676
-------------------------------------------------------------
SGA Target Advisory DB/Inst: O102EE1/o102ee1 End Snap: 1110
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
128 .5 54,452 1.7 35,821,040
192 .8 32,437 1.0 35,821,040
256 1.0 32,218 1.0 34,720,403
320 1.3 32,215 1.0 34,709,987
384 1.5 31,551 1.0 31,366,412
448 1.8 31,551 1.0 31,366,412
512 2.0 31,551 1.0 31,366,412
-------------------------------------------------------------
SGA Memory Summary DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 96,468,992
Fixed Size 1,261,248
Redo Buffers 7,127,040
Variable Size 163,578,176
-------------------- --------------------
sum 268,435,456
-------------------------------------------------------------
SGA breakdown difference DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null 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 p free memory 4.0 4.0 0.00
large PX msg pool 1.0 1.0 0.00
large free memory 3.0 3.0 0.00
shared ASH buffers 5.1 5.1 0.00
shared CCursor 6.1 8.4 36.67
shared Cursor Stats 1.8 1.8 0.00
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGH: NO ACCESS 23.8 23.8 0.00
shared KGLS heap 3.7 3.3 -11.04
shared KSFD SGA I/O b 3.8 3.8 0.00
shared KTI-UNDO 3.6 3.6 0.00
shared PCursor 4.5 5.8 28.03
shared event statistics per sess 3.8 3.8 0.00
shared free memory 44.2 40.6 -8.16
shared kglsim hash table bkts 2.0 2.0 0.00
shared kglsim heap 3.2 3.2 0.00
shared kglsim object batch 4.5 4.5 0.00
shared library cache 6.5 6.6 1.34
shared private strands 3.5 3.5 0.00
shared row cache 3.6 3.6 0.00
shared sessions 2.3 2.3 0.00
shared sql area 7.5 12.0 59.23
shared sql area:PLSQL 8.0 4.3 -46.96
buffer_cache 92.0 92.0 0.00
fixed_sga 1.2 1.2 0.00
log_buffer 6.8 6.8 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 13.56 10.60 -27.93
Cursor to Parent ratio: 1.12 1.09 -2.63
Total Cursors: 1,415 2,109 32.91
Total Parents: 1,267 1,938 34.62
-------------------------------------------------------------
init.ora Parameters DB/Inst: O102EE1/o102ee1 Snaps: 1109-1110
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /opt/app/oracle/admin/o102ee1/adu
background_dump_dest /opt/app/oracle/admin/o102ee1/bdu
compatible 10.2.0.3.0
control_files /opt/app/oracle/oradata/o102ee1/c
db_block_size 8192
db_domain
db_name o102ee1
db_recovery_file_dest /opt/app/oracle/flash_recovery_ar
db_recovery_file_dest_size 2147483648
java_pool_size 0
job_queue_processes 10
nls_language JAPANESE
nls_territory JAPAN
open_cursors 1024
pga_aggregate_target 16777216
processes 150
sessions 500
sga_target 268435456
undo_management AUTO
undo_tablespace UNDOTBS1
workarea_size_policy AUTO
-------------------------------------------------------------
End of Report ( sp_1109_1110.txt )
Printer View