STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1173970302 orcl 1 02-Jul-09 17:32 10.2.0.1.0 NO
Host Name: edrsr4p1.us.orac Num CPUs: 1 Phys Memory (MB): 0
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 41 17-Jul-09 13:34:52 33 10.3
End Snap: 42 17-Jul-09 13:35:42 33 10.3
Elapsed: 0.83 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 180M Std Block Size: 8K
Shared Pool Size: 80M Log Buffer: 2,859K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 36,818.80 141,610.77
Logical reads: 499.64 1,921.69
Block changes: 61.02 234.69
Physical reads: 16.16 62.15
Physical writes: 0.08 0.31
User calls: 2.84 10.92
Parses: 45.72 175.85
Hard parses: 3.84 14.77
Sorts: 38.82 149.31
Logons: 0.24 0.92
Executes: 53.26 204.85
Transactions: 0.26
% Blocks changed per Read: 12.21 Recursive Call %: 99.52
Rollback per transaction %: 0.00 Rows per Sort: 7.73
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 96.77 In-memory Sort %: 100.00
Library Hit %: 85.43 Soft Parse %: 91.60
Execute to Parse %: 14.16 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 41.01 % Non-Parse CPU: 75.67
Shared Pool Statistics Begin End
------ ------
Memory Usage %: ####### #######
% SQL with executions>1: 86.26 82.23
% Memory for SQL w/exec>1: 97.52 95.95
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 3 78.4
db file scattered read 155 0 2 7.4
control file sequential read 292 0 1 5.8
db file sequential read 163 0 1 5.2
log file parallel write 36 0 2 2.2
-------------------------------------------------------------
Host CPU (CPUs: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
1.33 1.65 91.33 8.67 0.00
Note: There is a 8% discrepancy between the OS Stat total CPU time and
the total CPU time estimated by Statspack
OS Stat CPU time: 46(s) (BUSY_TIME + IDLE_TIME)
Statspack CPU time: 50(s) (Elapsed time * num CPUs in end snap)
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 98.51
% of busy CPU for Instance: 98.51
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): .0 .0
SGA use (MB): 272.0 272.0
PGA use (MB): 145.7 146.0
% Host Mem used for SGA+PGA: 1468476.0 1493386.8
-------------------------------------------------------------
Time Model System Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 53.1 99.4
PL/SQL execution elapsed time 46.9 87.8
DB CPU 45.5 85.1
parse time elapsed 1.5 2.8
hard parse elapsed time 1.2 2.3
hard parse (sharing criteria) elaps 0.3 .6
PL/SQL compilation elapsed time 0.0 .1
connection management call elapsed 0.0 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 53.5
background elapsed time 0.3
background cpu time 0.2
-------------------------------------------------------------
Wait Events DB/Inst: ORCL/orcl Snaps: 41-42
-> 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
--------------------------------- ------------ ------ ---------- ------ --------
db file scattered read 155 0 0 2 11.9
control file sequential read 292 0 0 1 22.5
db file sequential read 163 0 0 1 12.5
log file parallel write 36 0 0 2 2.8
control file parallel write 16 0 0 2 1.2
log file sync 4 0 0 1 0.3
db file parallel write 4 0 0 1 0.3
SQL*Net break/reset to client 4 0 0 0 0.3
SQL*Net message from client 104 0 182 1753 8.0
class slave wait 1 100 117 ###### 0.1
ASM background timer 10 0 49 4884 0.8
wait for unread message on broadc 48 100 47 978 3.7
jobq slave wait 19 47 45 2386 1.5
Streams AQ: waiting for messages 9 100 44 4884 0.7
virtual circuit status 1 100 29 29294 0.1
Streams AQ: qmn slave idle wait 1 0 27 27347 0.1
Streams AQ: qmn coordinator idle 2 50 27 13673 0.2
-------------------------------------------------------------
Background Wait Events DB/Inst: ORCL/orcl Snaps: 41-42
-> %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
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 36 0 0 2 2.8
control file parallel write 16 0 0 2 1.2
db file parallel write 4 0 0 1 0.3
rdbms ipc message 260 79 469 1803 20.0
class slave wait 1 100 117 ###### 0.1
ASM background timer 10 0 49 4884 0.8
pmon timer 18 100 47 2605 1.4
Streams AQ: qmn slave idle wait 1 0 27 27347 0.1
Streams AQ: qmn coordinator idle 2 50 27 13673 0.2
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 41-42
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
SQL*Net break/reset to cli 4 100.0
control file parallel writ 16 81.3 18.8
control file sequential re 292 95.5 .3 2.1 1.7 .3
db file parallel write 4 100.0
db file scattered read 147 68.7 10.2 3.4 13.6 2.0 2.0
db file sequential read 157 83.4 4.5 1.9 6.4 3.8
latch free 10 100.0
log file parallel write 36 77.8 2.8 2.8 8.3 2.8 5.6
log file sync 4 75.0 25.0
ASM background timer 10 100.0
SQL*Net message from clien 104 78.8 4.8 2.9 1.9 11.5
SQL*Net message to client 104 100.0
Streams AQ: qmn coordinato 2 50.0 50.0
Streams AQ: qmn slave idle 1 100.0
Streams AQ: waiting for me 9 100.0
class slave wait 1 100.0
dispatcher timer 1 100.0
jobq slave wait 19 100.0
pmon timer 18 5.6 5.6 88.9
rdbms ipc message 259 1.2 1.9 .8 2.7 1.5 5.8 30.1 56.0
virtual circuit status 1 100.0
wait for unread message on 48 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 41-42
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 45
-> Captured SQL accounts for 7.0% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
2.78 1 2.78 6.1 5.63 24,195 2679188916
Module: SQL*Plus
BEGIN statspack.snap(10,134); END;
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: ORCL/orcl Snaps: 41-42
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 53
-> Captured SQL accounts for 11.3% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
5.63 1 5.63 10.5 2.78 799 2679188916
Module: SQL*Plus
BEGIN statspack.snap(10,134); END;
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 41-42
-> 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: 24,982
-> Captured SQL accounts for 27.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
--------------- ------------ -------------- ------ -------- --------- ----------
24,195 1 24,195.0 96.8 2.78 5.63 2679188916
Module: SQL*Plus
BEGIN statspack.snap(10,134); END;
2,398 560 4.3 9.6 0.11 0.11 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
748 160 4.7 3.0 0.02 0.02 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
640 20 32.0 2.6 0.02 0.02 3840591838
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1
) and privilege#>0
429 11 39.0 1.7 0.00 0.00 1129615673
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
from obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.ob
j#=o.obj# and o.owner#=u.user# and bitand(property,16)=0 and bi
tand(property,8)=0 order by o.obj#
384 32 12.0 1.5 0.01 0.01 2062595902
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
374 27 13.9 1.5 0.01 0.01 1937775682
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
274 32 8.6 1.1 0.01 0.01 1198893840
select order#,columns,types from access$ where d_obj#=:1
257 2 128.5 1.0 0.05 0.05 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 41-42
-> End Disk Reads Threshold: 1000 Total Disk Reads: 808
-> Captured SQL accounts for 0.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
799 1 799.0 98.9 2.78 5.63 2679188916
Module: SQL*Plus
BEGIN statspack.snap(10,134); END;
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 41-42
-> End Executions Threshold: 100 Total Executions: 2,663
-> Captured SQL accounts for 54.7% 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
------------ --------------- ---------------- ----------- ---------- ----------
560 828 1.5 0.00 0.00 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
160 214 1.3 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
38 0 0.0 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
34 0 0.0 0.00 0.00 204386021
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#
34 39 1.1 0.00 0.00 1980305124
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#
32 105 3.3 0.00 0.00 1198893840
select order#,columns,types from access$ where d_obj#=:1
32 111 3.5 0.00 0.00 2062595902
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
30 535 17.8 0.00 0.00 839312984
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
27 27 1.0 0.00 0.00 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
27 27 1.0 0.00 0.00 1667689875
Module: EM_PING
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
27 160 5.9 0.00 0.00 1937775682
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 41-42
-> End Executions Threshold: 100 Total Executions: 2,663
-> Captured SQL accounts for 54.7% 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
------------ --------------- ---------------- ----------- ---------- ----------
27 0 0.0 0.00 0.00 2065408759
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
27 0 0.0 0.00 0.00 2360644236
Module: OEM.SystemPool
SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITER
ATE_PARAM, ITERATE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FR
OM MGMT_JOB_EXECUTION J WHERE STEP_TYPE IN (:B7 , :B6 , :B5 ) AN
D STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND STEP_NAME LIKE :B
2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <=
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 41-42
-> End Parse Calls Threshold: 1000 Total Parse Calls: 2,286
-> Captured SQL accounts for 48.6% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
560 560 24.50 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
32 32 1.40 1198893840
select order#,columns,types from access$ where d_obj#=:1
32 32 1.40 2062595902
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
27 34 1.18 204386021
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#
27 34 1.18 1980305124
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#
26 26 1.14 2018736380
select timestamp, flags from fixed_obj$ where obj#=:1
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 41-42
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 300 6.0 23.1
CPU used when call started 345 6.9 26.5
CR blocks created 1 0.0 0.1
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DB time 16,146 322.9 1,242.0
DBWR checkpoint buffers written 4 0.1 0.3
DBWR checkpoints 0 0.0 0.0
DBWR object drop buffers written 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR tablespace checkpoint buffer 0 0.0 0.0
DBWR thread checkpoint buffers wr 0 0.0 0.0
DBWR transaction table writes 0 0.0 0.0
DBWR undo block writes 0 0.0 0.0
SMON posted for undo segment shri 0 0.0 0.0
SQL*Net roundtrips to/from client 98 2.0 7.5
active txn count during cleanout 87 1.7 6.7
application wait time 0 0.0 0.0
auto extends on undo tablespace 0 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 205 4.1 15.8
buffer is not pinned count 14,753 295.1 1,134.9
buffer is pinned count 6,192 123.8 476.3
bytes received via SQL*Net from c 10,664 213.3 820.3
bytes sent via SQL*Net to client 13,425 268.5 1,032.7
calls to get snapshot scn: kcmgss 3,333 66.7 256.4
calls to kcmgas 73 1.5 5.6
calls to kcmgcs 54 1.1 4.2
change write time 4 0.1 0.3
cleanout - number of ktugct calls 92 1.8 7.1
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 4,781 95.6 367.8
cluster key scans 2,507 50.1 192.9
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 0 0.0 0.0
commit batch/immediate requested 0 0.0 0.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 10 0.2 0.8
commit cleanouts 352 7.0 27.1
commit cleanouts successfully com 342 6.8 26.3
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
commit txn count during cleanout 42 0.8 3.2
concurrency wait time 0 0.0 0.0
consistent changes 1 0.0 0.1
consistent gets 21,536 430.7 1,656.6
consistent gets - examination 7,349 147.0 565.3
consistent gets direct 0 0.0 0.0
consistent gets from cache 21,536 430.7 1,656.6
cursor authentications 55 1.1 4.2
data blocks consistent reads - un 1 0.0 0.1
db block changes 3,051 61.0 234.7
db block gets 3,446 68.9 265.1
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 41-42
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
db block gets direct 0 0.0 0.0
db block gets from cache 3,446 68.9 265.1
deferred (CURRENT) block cleanout 180 3.6 13.9
enqueue conversions 81 1.6 6.2
enqueue releases 1,853 37.1 142.5
enqueue requests 1,853 37.1 142.5
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 2,663 53.3 204.9
flashback log writes 5 0.1 0.4
free buffer inspected 0 0.0 0.0
free buffer requested 821 16.4 63.2
global undo segment hints helped 0 0.0 0.0
heap block compress 7 0.1 0.5
immediate (CR) block cleanout app 0 0.0 0.0
immediate (CURRENT) block cleanou 109 2.2 8.4
index fast full scans (full) 1 0.0 0.1
index fetch by key 5,135 102.7 395.0
index scans kdiixs1 2,947 58.9 226.7
leaf node 90-10 splits 18 0.4 1.4
leaf node splits 37 0.7 2.9
lob reads 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logons cumulative 12 0.2 0.9
messages received 49 1.0 3.8
messages sent 49 1.0 3.8
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 10,964 219.3 843.4
opened cursors cumulative 2,447 48.9 188.2
parse count (failures) 0 0.0 0.0
parse count (hard) 192 3.8 14.8
parse count (total) 2,286 45.7 175.9
parse time cpu 73 1.5 5.6
parse time elapsed 178 3.6 13.7
physical read IO requests 300 6.0 23.1
physical read bytes 6,619,136 132,382.7 509,164.3
physical read total IO requests 592 11.8 45.5
physical read total bytes 11,403,264 228,065.3 877,174.2
physical read total multi block r 149 3.0 11.5
physical reads 808 16.2 62.2
physical reads cache 808 16.2 62.2
physical reads cache prefetch 508 10.2 39.1
physical reads direct 0 0.0 0.0
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical reads for flashback new 271 5.4 20.9
physical reads prefetch warmup 454 9.1 34.9
physical write IO requests 4 0.1 0.3
physical write bytes 32,768 655.4 2,520.6
physical write total IO requests 113 2.3 8.7
physical write total bytes 5,593,088 111,861.8 430,237.5
physical write total multi block 73 1.5 5.6
physical writes 4 0.1 0.3
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 41-42
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical writes from cache 4 0.1 0.3
physical writes non checkpoint 0 0.0 0.0
process last non-idle time 0 0.0 0.0
recursive calls 29,589 591.8 2,276.1
recursive cpu usage 278 5.6 21.4
redo blocks written 3,646 72.9 280.5
redo buffer allocation retries 0 0.0 0.0
redo entries 1,704 34.1 131.1
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 1,840,940 36,818.8 141,610.8
redo synch time 1 0.0 0.1
redo synch writes 73 1.5 5.6
redo wastage 7,384 147.7 568.0
redo write time 11 0.2 0.9
redo writer latching time 0 0.0 0.0
redo writes 36 0.7 2.8
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 1 0.0 0.1
rows fetched via callback 1,739 34.8 133.8
session connect time 0 0.0 0.0
session cursor cache hits 1,750 35.0 134.6
session logical reads 24,982 499.6 1,921.7
session pga memory 64,507,720 1,290,154.4 4,962,132.3
session pga memory max 126,177,096 2,523,541.9 9,705,930.5
session uga memory 42,949,911,624 858,998,232.5 ############
session uga memory max 4,826,608 96,532.2 371,277.5
shared hash latch upgrades - no w 2,766 55.3 212.8
shared hash latch upgrades - wait 0 0.0 0.0
sorts (memory) 1,941 38.8 149.3
sorts (rows) 15,002 300.0 1,154.0
sql area purged 0 0.0 0.0
switch current to new buffer 0 0.0 0.0
table fetch by rowid 4,677 93.5 359.8
table fetch continued row 60 1.2 4.6
table scan blocks gotten 1,798 36.0 138.3
table scan rows gotten 11,015 220.3 847.3
table scans (long tables) 0 0.0 0.0
table scans (short tables) 69 1.4 5.3
total number of times SMON posted 0 0.0 0.0
transaction rollbacks 0 0.0 0.0
undo change vector size 611,620 12,232.4 47,047.7
user I/O wait time 48 1.0 3.7
user calls 142 2.8 10.9
user commits 13 0.3 1.0
user rollbacks 0 0.0 0.0
workarea executions - optimal 1,219 24.4 93.8
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 33 33
opened cursors current 339 341
session cursor cache count 265,636 265,836
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 4,638
IDLE_TIME 0
NICE_TIME 0
SYS_TIME 402
USER_TIME 4,236
LOAD 2
NUM_CPUS 1
-------------------------------------------------------------
Session Time Model Stats DB/Inst: ORCL/orcl Snaps: 41-42
Session Id: 134 Serial#: 7228
-> Total Time in Database calls 53.5s (or 53454289us)
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
PL/SQL execution elapsed time 41.0 76.8
sql execute elapsed time 41.0 76.8
DB CPU 38.8 72.6
DB time 41.0
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 41-42
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSAUX
184 4 2.0 3.7 0 0 0 0.0
UNDOTBS1
112 2 0.4 1.0 1 0 0 0.0
SYSTEM
4 0 5.0 1.0 4 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: ORCL/orcl Snaps: 41-42
->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 +DISK1/orcl/datafile/sysaux.261.691078173
184 4 2.0 16 3.7 0 0 0
SYSTEM +DISK1/orcl/datafile/system.259.691078173
4 0 5.0 8 1.0 4 0 0
UNDOTBS1 +DISK1/orcl/datafile/undotbs1.260.691078173
112 2 0.4 16 1.0 1 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 41-42
->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
------------ ------------ ------------ ------------ ------------ ------------
UNDOTBS1 +DISK1/orcl/datafile/undotbs1.260.691078173
106 1 4 1 0 0
SYSTEM +DISK1/orcl/datafile/system.259.691078173
0 1 3 0 0 0
SYSAUX +DISK1/orcl/datafile/sysaux.261.691078173
31 1 5 4 0 0
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 41-42
-> 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 22K 97 25,961 813 4 0 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> 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 20 257 2293 12231 184320 12231
E 0 20 681 4477 15630 184320 15630
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 42
-> 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 16 .1 2 6.1 314 2,748 14.4
D 32 .2 4 3.8 196 1,658 8.7
D 48 .3 6 2.6 133 1,076 5.6
D 64 .4 8 2.1 106 831 4.4
D 80 .4 10 1.6 85 633 3.3
D 96 .5 12 1.3 68 481 2.5
D 112 .6 14 1.2 63 429 2.3
D 128 .7 16 1.1 57 372 2.0
D 144 .8 18 1.1 56 366 1.9
D 160 .9 20 1.0 52 333 1.7
D 176 1.0 22 1.0 52 329 1.7
D 180 1.0 22 1.0 52 328 1.7
D 192 1.1 24 1.0 52 326 1.7
D 208 1.2 26 1.0 51 318 1.7
D 224 1.2 28 1.0 50 312 1.6
D 240 1.3 30 0.9 48 298 1.6
D 256 1.4 32 0.9 47 284 1.5
D 272 1.5 34 0.9 46 273 1.4
D 288 1.6 36 0.9 45 261 1.4
D 304 1.7 38 0.8 43 250 1.3
D 320 1.8 40 0.7 36 186 1.0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> 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 23 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 145.7 0.0 .0 .0 .0 3,276
E 16 4 146.0 0.0 .0 .0 .0 3,276
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 41-42
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 1,042 1,042 0 0
64K 128K 2 2 0 0
128K 256K 2 2 0 0
512K 1024K 28 28 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 42
-> 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 4,186.2 46.8 99.0 34
16 1.0 4,186.2 0.0 100.0 34
19 1.2 4,186.2 0.0 100.0 34
22 1.4 4,186.2 0.0 100.0 34
26 1.6 4,186.2 0.0 100.0 34
29 1.8 4,186.2 0.0 100.0 34
32 2.0 4,186.2 0.0 100.0 34
48 3.0 4,186.2 0.0 100.0 34
64 4.0 4,186.2 0.0 100.0 32
96 6.0 4,186.2 0.0 100.0 3
128 8.0 4,186.2 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 41-42
-> 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 -------- 145.7 65.7 15.3 4.2 6.2 24 37 35
Other 128.4 3.7 6.2 24 32 35
Freeable 15.3 .0 .8 .4 2 20
SQL 1.5 .6 .1 .1 0 4 17
PL/SQL .6 .3 .0 .0 0 0 33
E -------- 146.0 65.8 15.7 4.2 6.2 24 37 35
Other 128.2 3.7 6.2 24 32 35
Freeable 15.7 .0 .8 .4 2 20
SQL 1.5 .6 .1 .1 0 4 17
PL/SQL .6 .3 .0 .0 0 0 33
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 6 LGWR -------- 23.7 11.1 .1 23.7 23.8
Other 23.6 23.6 23.7
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
21 ARC0 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
22 ARC1 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
15 J000 -------- 6.9 .1 .9 6.9 12.9
Other 5.9 5.9 8.2
Freeable .9 .0 .9
PL/SQL .1 .0 .1 .1
SQL .0 .0 .0 3.6
27 TNS V1-V3 --- 5.8 1.8 1.8 5.8 5.8
Other 3.9 3.9 3.9
Freeable 1.8 .0 1.8
SQL .1 .1 .1 2.2
PL/SQL .0 .0 .0 .0
11 MMON -------- 4.7 2.1 1.3 4.7 4.8
Other 3.3 3.3 3.3
Freeable 1.3 .0 1.3
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
23 TNS V1-V3 --- 4.4 1.7 1.0 4.4 5.9
Other 3.2 3.2 3.2
Freeable 1.0 .0 1.0
SQL .1 .0 .1 2.2
PL/SQL .0 .0 .0 .0
25 ------------ 4.3 1.7 1.0 4.3 5.7
Other 3.0 3.0 3.0
Freeable 1.0 .0 1.0
SQL .3 .1 .3 1.8
PL/SQL .0 .0 .0 .0
37 ------------ 4.3 2.0 1.0 4.3 4.7
Other 3.1 3.1 3.1
Freeable 1.0 .0 1.0
SQL .1 .0 .1 1.0
PL/SQL .1 .0 .1 .1
8 SMON -------- 4.2 1.5 .9 4.2 4.2
Other 3.2 3.2 3.2
Freeable .9 .0 .9
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
36 ------------ 3.7 2.4 .9 3.7 4.4
Other 2.6 2.6 2.6
Freeable .9 .0 .9
SQL .1 .0 .1 2.0
PL/SQL .1 .0 .1 .1
10 CJQ0 -------- 3.5 1.4 .9 3.5 3.5
Other 2.5 2.5 2.5
Freeable .9 .0 .9
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 10 SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
20 RVWR -------- 3.5 1.1 1.2 3.5 4.7
Other 2.3 2.3 3.5
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
32 q001 -------- 3.5 1.4 .3 3.5 3.5
Other 3.1 3.1 3.1
Freeable .3 .0 .3
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
28 ------------ 3.2 2.2 .8 3.2 5.3
Other 2.3 2.3 2.8
Freeable .8 .0 .8
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
9 RECO -------- 3.0 1.3 .3 3.0 3.0
Other 2.7 2.7 2.7
Freeable .3 .0 .3
SQL .1 .1 .1 .5
PL/SQL .0 .0 .0 .0
43 ------------ 3.0 1.9 .9 3.0 36.9
Other 2.0 2.0 32.3
Freeable .9 .0 .9
SQL .1 .0 .1 3.6
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 3.0 1.2 .4 3.0 3.3
Other 2.6 2.6 3.0
Freeable .4 .0 .4
PL/SQL .0 .0 .0 .0
7 CKPT -------- 2.9 1.1 .5 2.9 3.4
Other 2.4 2.4 2.8
Freeable .5 .0 .5
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
38 ------------ 2.7 2.2 .3 2.7 3.7
Other 2.2 2.2 2.7
Freeable .3 .0 .3
SQL .1 .1 .1 .6
PL/SQL .0 .0 .0 .0
E 6 LGWR -------- 23.7 11.1 .1 23.7 23.8
Other 23.6 23.6 23.7
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
21 ARC0 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
22 ARC1 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
15 J000 -------- 6.9 .1 .9 6.9 12.9
Other 5.9 5.9 8.2
Freeable .9 .0 .9
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 15 PL/SQL .1 .0 .1 .1
SQL .0 .0 .0 3.6
27 TNS V1-V3 --- 5.7 1.9 1.8 5.7 5.9
Other 3.8 3.8 3.8
Freeable 1.8 .0 1.8
SQL .1 .1 .1 2.2
PL/SQL .0 .0 .0 .0
11 MMON -------- 4.7 2.1 1.3 4.7 4.8
Other 3.3 3.3 3.3
Freeable 1.3 .0 1.3
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
23 TNS V1-V3 --- 4.4 1.7 1.0 4.4 5.9
Other 3.2 3.2 3.2
Freeable 1.0 .0 1.0
SQL .1 .0 .1 2.2
PL/SQL .0 .0 .0 .0
25 ------------ 4.3 1.7 1.0 4.3 5.7
Other 3.0 3.0 3.0
Freeable 1.0 .0 1.0
SQL .3 .1 .3 1.8
PL/SQL .0 .0 .0 .0
37 ------------ 4.3 2.0 1.0 4.3 4.7
Other 3.1 3.1 3.1
Freeable 1.0 .0 1.0
SQL .1 .0 .1 1.0
PL/SQL .1 .0 .1 .1
8 SMON -------- 4.2 1.5 .9 4.2 4.2
Other 3.2 3.2 3.2
Freeable .9 .0 .9
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
36 ------------ 3.7 2.4 .9 3.7 4.4
Other 2.6 2.6 2.6
Freeable .9 .0 .9
SQL .1 .0 .1 2.0
PL/SQL .1 .0 .1 .1
10 CJQ0 -------- 3.5 1.4 .9 3.5 3.5
Other 2.5 2.5 2.5
Freeable .9 .0 .9
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
20 RVWR -------- 3.5 1.1 1.2 3.5 4.7
Other 2.3 2.3 3.5
Freeable 1.2 .0 1.2
PL/SQL .0 .0 .0 .0
32 q001 -------- 3.5 1.4 .3 3.5 3.5
Other 3.1 3.1 3.1
Freeable .3 .0 .3
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
28 ------------ 3.2 2.2 .8 3.2 5.3
Other 2.3 2.3 2.8
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 28 Freeable .8 .0 .8
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
38 ------------ 3.1 2.2 .7 3.1 3.7
Other 2.2 2.2 2.2
Freeable .7 .0 .7
SQL .1 .1 .1 .9
PL/SQL .0 .0 .0 .0
9 RECO -------- 3.0 1.3 .3 3.0 3.0
Other 2.7 2.7 2.7
Freeable .3 .0 .3
SQL .1 .1 .1 .5
PL/SQL .0 .0 .0 .0
43 ------------ 3.0 1.9 1.0 3.0 36.9
Other 1.9 1.9 32.3
Freeable 1.0 .0 1.0
SQL .0 .0 .0 3.6
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 3.0 1.2 .4 3.0 3.3
Other 2.6 2.6 3.0
Freeable .4 .0 .4
PL/SQL .0 .0 .0 .0
7 CKPT -------- 2.9 1.1 .5 2.9 3.4
Other 2.4 2.4 2.8
Freeable .5 .0 .5
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Latch Activity DB/Inst: ORCL/orcl Snaps: 41-42
->"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 map operation freeli 4 0.0 0 0
ASM map operation hash t 1,500 0.0 0 0
ASM network background l 20 0.0 0 0
Consistent RBA 36 0.0 0 0
FOB s.o list latch 8 0.0 0 0
JS Sh mem access 20 50.0 1.0 0 0
JS mem alloc latch 20 0.0 0 0
JS queue access latch 20 0.0 0 0
JS queue state obj latch 1,204 0.0 0 0
JS slv state obj latch 50 0.0 0 0
KFK SGA context latch 34 0.0 0 0
KFMD SGA 3 0.0 0 0
KMG MMAN ready and start 16 0.0 0 0
KTF sga latch 0 0 10 0.0
KWQMN job cache list lat 1 0.0 0 0
KWQP Prop Status 1 0.0 0 0
MQL Tracking Latch 0 0 1 0.0
Memory Management Latch 0 0 16 0.0
OS process 6 0.0 0 0
OS process allocation 20 0.0 0 0
OS process: request allo 4 0.0 0 0
PL/SQL warning settings 31 0.0 0 0
SQL memory manager latch 59 0.0 0 15 0.0
SQL memory manager worka 5,080 0.0 0 0
Shared B-Tree 4 0.0 0 0
active checkpoint queue 20 0.0 0 0
active service list 424 0.0 0 18 0.0
archive control 11 0.0 0 0
archive process latch 15 0.0 0 0
cache buffer handles 158 0.0 0 0
cache buffers chains 49,321 0.0 0 2,091 0.0
cache buffers lru chain 1,959 0.0 0 786 0.0
cache table scan latch 0 0 5 0.0
channel handle pool latc 4 0.0 0 0
channel operations paren 281 0.0 0 0
checkpoint queue latch 322 0.0 0 430 0.0
client/application info 105 0.0 0 0
compile environment latc 134 0.0 0 0
dml lock allocation 478 0.0 0 0
dummy allocation 24 0.0 0 0
enqueue hash chains 3,779 0.0 0 0
enqueues 3,186 0.0 0 0
event group latch 2 0.0 0 0
flashback allocation 374 0.0 0 0
flashback copy 0 0 353 0.0
flashback mapping 5 0.0 0 0
global KZLD latch for me 2 0.0 0 0
hash table column usage 0 0 3,278 0.0
hash table modification 7 0.0 0 0
job workq parent latch 0 0 20 0.0
Latch Activity DB/Inst: ORCL/orcl Snaps: 41-42
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
job_queue_processes para 10 0.0 0 0
kks stats 419 0.0 0 0
ksuosstats global area 6 0.0 0 0
kwqbsn:qsga 4 0.0 0 0
lgwr LWN SCN 41 0.0 0 0
library cache 23,085 0.0 0 59 0.0
library cache load lock 1,328 0.0 0 0
library cache lock 8,292 0.0 0 0
library cache lock alloc 181 0.0 0 0
library cache pin 12,928 0.0 0 0
library cache pin alloca 163 0.0 0 0
list of block allocation 36 0.0 0 0
messages 576 0.0 0 0
mostly latch-free SCN 41 0.0 0 0
multiblock read objects 368 0.0 0 0
object queue header oper 1,772 0.0 0 0
object stats modificatio 390 0.0 0 0
parallel query alloc buf 8 0.0 0 0
parameter table allocati 22 0.0 0 0
post/wait queue 6 0.0 0 4 0.0
process allocation 4 0.0 0 2 0.0
process group creation 4 0.0 0 0
qmn task queue latch 4 0.0 0 0
redo allocation 77 0.0 0 1,751 0.0
redo copy 0 0 1,751 0.0
redo writing 160 0.0 0 0
resmgr group change latc 53 0.0 0 0
resmgr:actses active lis 35 0.0 0 0
resmgr:actses change gro 32 0.0 0 0
resmgr:free threads list 24 0.0 0 0
resmgr:schema config 1 0.0 0 0
row cache objects 16,486 0.0 0 152 0.0
rules engine rule set st 100 0.0 0 0
sequence cache 71 0.0 0 0
session allocation 35,167 0.0 0 0
session idle bit 406 0.0 0 0
session state list latch 24 0.0 0 0
session timer 18 0.0 0 0
shared pool 18,096 0.0 0 0
simulator hash latch 1,216 0.0 0 0
simulator lru latch 1,120 0.0 0 60 0.0
sort extent pool 1 0.0 0 0
threshold alerts latch 2 0.0 0 0
transaction allocation 42 0.0 0 0
undo global data 350 0.0 0 0
user lock 28 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: ORCL/orcl Snaps: 41-42
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
JS Sh mem access 20 10 10 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: ORCL/orcl Snaps: 41-42
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
JS Sh mem access jsksGetShMemLatch 0 10 10
-------------------------------------------------------------
Child Latch Statistics DB/Inst: ORCL/orcl Snaps: 41-42
-> only latches with sleeps/gets > 1/100000 are shown
-> ordered by name, gets desc
Child Get Spin
Latch Name Num Requests Misses Sleeps Gets
---------------------- ------- ------------ ------------ ---------- -----------
JS Sh mem access 2 18 9 9 0
-------------------------------------------------------------
Segments by Logical Reads DB/Inst: ORCL/orcl Snaps: 41-42
-> 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
---------- ---------- -------------------- ------------ ----- ------------ -----
SYS SYSTEM SYS_C00648 INDEX 144 17.6
SYS SYSTEM KOTTD$ TABLE 80 9.8
SYS SYSAUX SCHEDULER$_JOB_RUN_D TABLE 80 9.8
HR USERS X TABLE 64 7.8
SYSMAN SYSAUX MGMT_JOB_EXEC_IDX01 INDEX 64 7.8
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: ORCL/orcl Snaps: 41-42
->"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 1 0.0 0 0 1
dc_global_oids 69 0.0 0 0 71
dc_histogram_data 207 0.0 0 0 337
dc_histogram_defs 1,886 2.0 0 0 4,690
dc_object_grants 3 0.0 0 0 142
dc_object_ids 869 0.1 0 0 1,198
dc_objects 730 0.0 0 10 1,743
dc_profiles 24 0.0 0 0 2
dc_segments 293 0.0 0 0 954
dc_sequences 1 0.0 0 1 11
dc_tablespaces 670 0.0 0 0 7
dc_usernames 222 0.0 0 0 13
dc_users 1,275 0.0 0 0 55
-------------------------------------------------------------
Library Cache Activity DB/Inst: ORCL/orcl Snaps: 41-42
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 494 3.6 5,478 6.2 197 0
TABLE/PROCEDURE 780 2.8 2,760 31.4 403 0
CLUSTER 5 0.0 14 7.1 1 0
-------------------------------------------------------------
Rule Sets DB/Inst: ORCL/orcl Snaps: 41-42
-> * 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: ORCL/orcl End Snap: 42
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
8 .1 8 1,482 6,322 1.0 922 1.3 2,767,537
16 .2 16 2,649 6,359 1.0 885 1.3 2,784,019
24 .3 23 3,427 6,396 1.0 848 1.2 2,799,834
32 .4 30 4,725 6,429 1.0 815 1.2 2,813,524
40 .5 37 5,287 6,458 1.0 786 1.1 2,825,016
48 .6 44 6,572 6,482 1.0 762 1.1 2,834,080
56 .7 51 7,541 6,501 1.0 743 1.1 2,840,850
64 .8 58 8,206 6,516 1.0 728 1.0 2,846,028
72 .9 65 9,501 6,529 1.0 715 1.0 2,850,043
80 1.0 72 10,549 6,539 1.0 705 1.0 2,853,004
88 1.1 79 11,482 6,547 1.0 697 1.0 2,855,311
96 1.2 86 12,335 6,554 1.0 690 1.0 2,857,147
104 1.3 93 13,398 6,559 1.0 685 1.0 2,858,583
112 1.4 100 14,335 6,564 1.0 680 1.0 2,859,768
120 1.5 107 15,657 6,568 1.0 676 1.0 2,860,774
128 1.6 114 16,594 6,571 1.0 673 1.0 2,861,613
136 1.7 121 17,317 6,574 1.0 670 1.0 2,862,340
144 1.8 128 18,921 6,576 1.0 668 .9 2,862,973
152 1.9 132 19,242 6,578 1.0 666 .9 2,863,538
160 2.0 132 19,242 6,580 1.0 664 .9 2,864,078
-------------------------------------------------------------
SGA Target Advisory DB/Inst: ORCL/orcl End Snap: 42
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
68 .3 20,024 1.1 131,110
136 .5 19,323 1.0 67,418
204 .8 19,127 1.0 55,764
272 1.0 19,045 1.0 51,614
340 1.3 19,015 1.0 47,805
408 1.5 18,910 1.0 35,810
476 1.8 18,879 1.0 35,810
544 2.0 18,877 1.0 35,810
-------------------------------------------------------------
SGA Memory Summary DB/Inst: ORCL/orcl Snaps: 41-42
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 188,743,680
Fixed Size 1,218,992
Redo Buffers 2,973,696
Variable Size 92,276,304
-------------------- --------------------
sum 285,212,672
-------------------------------------------------------------
SGA breakdown difference DB/Inst: ORCL/orcl Snaps: 41-42
-> 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 ASM map operations hashta .2 .2 0.00
large PX msg pool .2 .2 0.00
large free memory 3.6 3.6 0.00
shared free memory 4,082.1 4,079.6 -0.06
buffer_cache 180.0 180.0 0.00
fixed_sga 1.2 1.2 0.00
log_buffer 2.8 2.8 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: ORCL/orcl Snaps: 41-42
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 60.78 60.39 -.64
Cursor to Parent ratio: 1.21 1.22 1.44
Total Cursors: 571 572 .17
Total Parents: 473 467 -1.28
-------------------------------------------------------------
init.ora Parameters DB/Inst: ORCL/orcl Snaps: 41-42
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/app/oracle/admin/orcl/adump
background_dump_dest /u01/app/oracle/admin/orcl/bdump
compatible 10.2.0.1.0
control_files +DISK1/orcl/controlfile/backup.25
core_dump_dest /u01/app/oracle/admin/orcl/cdump
db_block_size 8192
db_create_file_dest +DISK1
db_domain oracle.com
db_file_multiblock_read_count 16
db_name orcl
db_recovery_file_dest +DISK1
db_recovery_file_dest_size 2147483648
dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes 10
open_cursors 300
pga_aggregate_target 16777216
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 285212672
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/app/oracle/admin/orcl/udump
-------------------------------------------------------------
End of Report ( /home/oracle/sessionScottReport.txt )
댓글 없음:
댓글 쓰기