STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1173970302 orcl 1 02-Jul-09 22:46 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: 1 16-Jul-09 11:04:13 34 10.9
End Snap: 11 16-Jul-09 11:05:19 34 12.3
Elapsed: 1.10 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 172M 168M Std Block Size: 8K
Shared Pool Size: 88M 92M Log Buffer: 2,859K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 13,398.91 126,332.57
Logical reads: 217.71 2,052.71
Block changes: 21.30 200.86
Physical reads: 4.12 38.86
Physical writes: 5.17 48.71
User calls: 2.24 21.14
Parses: 9.82 92.57
Hard parses: 2.08 19.57
Sorts: 5.98 56.43
Logons: 0.06 0.57
Executes: 26.06 245.71
Transactions: 0.11
% Blocks changed per Read: 9.78 Recursive Call %: 99.32
Rollback per transaction %: 0.00 Rows per Sort: 25.47
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.11 In-memory Sort %: 100.00
Library Hit %: 83.64 Soft Parse %: 78.86
Execute to Parse %: 62.33 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.88 % Non-Parse CPU: 73.45
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.43 90.98
% SQL with executions>1: 76.99 85.40
% Memory for SQL w/exec>1: 93.60 95.29
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 3 63.8
db file sequential read 285 1 3 17.3
SGA: MMAN sleep for component shrink 45 0 5 5.4
db file parallel write 40 0 5 4.7
control file sequential read 401 0 0 3.8
-------------------------------------------------------------
Host CPU (CPUs: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.25 0.22 2.94 8.92 88.01
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 6.07
% of busy CPU for Instance: 50.66
%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): 162.8 163.3
% Host Mem used for SGA+PGA: 1931643.9 2129147.1
-------------------------------------------------------------
Time Model System Stats DB/Inst: ORCL/orcl Snaps: 1-11
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 4.3 95.9
DB CPU 3.5 78.3
parse time elapsed 1.2 26.1
hard parse elapsed time 1.1 24.2
hard parse (sharing criteria) elaps 0.2 5.0
PL/SQL compilation elapsed time 0.1 3.3
PL/SQL execution elapsed time 0.1 1.7
repeated bind elapsed time 0.1 1.6
connection management call elapsed 0.0 1.0
sequence load elapsed time 0.0 .0
DB time 4.5
background elapsed time 0.7
background cpu time 0.4
-------------------------------------------------------------
Wait Events DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 sequential read 285 0 1 3 40.7
SGA: MMAN sleep for component shr 45 20 0 5 6.4
db file parallel write 40 0 0 5 5.7
control file sequential read 401 0 0 0 57.3
log file parallel write 15 0 0 8 2.1
os thread startup 1 0 0 54 0.1
control file parallel write 29 0 0 2 4.1
SQL*Net break/reset to client 4 0 0 0 0.6
SQL*Net message from client 136 0 1,074 7897 19.4
class slave wait 1 100 117 ###### 0.1
ASM background timer 13 0 64 4885 1.9
Streams AQ: waiting for messages 13 100 63 4884 1.9
wait for unread message on broadc 64 100 63 978 9.1
jobq slave wait 20 100 59 2930 2.9
virtual circuit status 2 100 59 29294 0.3
Streams AQ: qmn slave idle wait 2 0 55 27346 0.3
Streams AQ: qmn coordinator idle 4 50 55 13673 0.6
SQL*Net message to client 136 0 0 0 19.4
-------------------------------------------------------------
Background Wait Events DB/Inst: ORCL/orcl Snaps: 1-11
-> %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
--------------------------------- ------------ ------ ---------- ------ --------
SGA: MMAN sleep for component shr 45 20 0 5 6.4
db file parallel write 40 0 0 5 5.7
log file parallel write 15 0 0 8 2.1
control file parallel write 30 0 0 2 4.3
os thread startup 1 0 0 54 0.1
control file sequential read 21 0 0 2 3.0
rdbms ipc message 261 95 684 2622 37.3
class slave wait 1 100 117 ###### 0.1
pmon timer 22 100 64 2931 3.1
ASM background timer 12 0 59 4885 1.7
Streams AQ: qmn slave idle wait 2 0 55 27346 0.3
Streams AQ: qmn coordinator idle 4 50 55 13673 0.6
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 1-11
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
SGA: MMAN sleep for compon 45 20.0 8.9 22.2 17.8 31.1
SQL*Net break/reset to cli 4 100.0
SQL*Net more data to clien 2 100.0
control file parallel writ 29 34.5 31.0 24.1 10.3
control file sequential re 401 94.8 1.0 1.7 2.5
db file parallel write 40 32.5 17.5 17.5 17.5 10.0 2.5 2.5
db file sequential read 293 51.5 13.0 15.7 12.3 7.5
log file parallel write 15 80.0 6.7 6.7 6.7
os thread startup 1 100.0
ASM background timer 12 100.0
SQL*Net message from clien 136 64.7 11.8 1.5 4.4 2.9 .7 14.0
SQL*Net message to client 136 100.0
Streams AQ: qmn coordinato 4 50.0 50.0
Streams AQ: qmn slave idle 2 100.0
Streams AQ: waiting for me 13 100.0
class slave wait 1 100.0
dispatcher timer 1 100.0
jobq slave wait 19 100.0
pmon timer 22 100.0
rdbms ipc message 262 .4 .4 28.2 71.0
virtual circuit status 2 100.0
wait for unread message on 65 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 1-11
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 3
-> Captured SQL accounts for 10.6% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
0.09 210 0.00 2.6 0.09 5,647 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
by sequence# desc
0.05 36 0.00 1.4 0.05 144 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: ORCL/orcl Snaps: 1-11
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 4
-> Captured SQL accounts for 8.5% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
0.09 210 0.00 2.1 0.09 0 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
by sequence# desc
0.05 36 0.00 1.1 0.05 0 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 1-11
-> 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: 14,369
-> Captured SQL accounts for 54.1% 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
--------------- ------------ -------------- ------ -------- --------- ----------
5,647 210 26.9 39.3 0.09 0.09 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
by sequence# desc
336 21 16.0 2.3 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#
290 21 13.8 2.0 0.01 0.01 1198893840
select order#,columns,types from access$ where d_obj#=:1
226 9 25.1 1.6 0.01 0.01 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
194 47 4.1 1.4 0.01 0.01 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
144 36 4.0 1.0 0.05 0.05 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-11
-> End Executions Threshold: 100 Total Executions: 1,720
-> Captured SQL accounts for 41.2% 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
------------ --------------- ---------------- ----------- ---------- ----------
210 2,623 12.5 0.00 0.00 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
by sequence# desc
47 50 1.1 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
39 39 1.0 0.00 0.00 3468666020
select text from view$ where rowid=:1
37 37 1.0 0.00 0.00 1667689875
Module: EM_PING
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
36 36 1.0 0.00 0.00 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;
36 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 <=
21 124 5.9 0.00 0.00 1198893840
select order#,columns,types from access$ where d_obj#=:1
21 130 6.2 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#
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 1-11
-> End Parse Calls Threshold: 1000 Total Parse Calls: 648
-> Captured SQL accounts for 39.5% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
39 39 6.02 3468666020
select text from view$ where rowid=:1
21 21 3.24 1198893840
select order#,columns,types from access$ where d_obj#=:1
21 21 3.24 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#
15 15 2.31 2018736380
select timestamp, flags from fixed_obj$ where obj#=:1
10 10 1.54 340778183
select audit$,options from procedure$ where obj#=:1
9 9 1.39 336764478
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#
9 9 1.39 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
9 9 1.39 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
9 9 1.39 2954231783
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fr
om idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
7 13 1.08 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
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-11
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 275 4.2 39.3
CPU used when call started 349 5.3 49.9
CR blocks created 2 0.0 0.3
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DB time 18,966 287.4 2,709.4
DBWR checkpoint buffers written 4 0.1 0.6
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 19 0.3 2.7
SMON posted for dropping temp seg 0 0.0 0.0
SMON posted for undo segment shri 0 0.0 0.0
SQL*Net roundtrips to/from client 130 2.0 18.6
active txn count during cleanout 35 0.5 5.0
application wait time 1 0.0 0.1
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 248 3.8 35.4
buffer is not pinned count 11,792 178.7 1,684.6
buffer is pinned count 1,169 17.7 167.0
bytes received via SQL*Net from c 12,673 192.0 1,810.4
bytes sent via SQL*Net to client 23,441 355.2 3,348.7
calls to get snapshot scn: kcmgss 2,026 30.7 289.4
calls to kcmgas 34 0.5 4.9
calls to kcmgcs 22 0.3 3.1
change write time 5 0.1 0.7
cleanout - number of ktugct calls 38 0.6 5.4
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 259 3.9 37.0
cluster key scans 212 3.2 30.3
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: buffer 0 0.0 0.0
commit cleanout failures: callbac 6 0.1 0.9
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 150 2.3 21.4
commit cleanouts successfully com 144 2.2 20.6
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
commit txn count during cleanout 21 0.3 3.0
concurrency wait time 6 0.1 0.9
consistent changes 2 0.0 0.3
consistent gets 12,472 189.0 1,781.7
consistent gets - examination 2,516 38.1 359.4
consistent gets direct 0 0.0 0.0
consistent gets from cache 12,472 189.0 1,781.7
cursor authentications 20 0.3 2.9
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-11
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
data blocks consistent reads - un 2 0.0 0.3
db block changes 1,406 21.3 200.9
db block gets 1,897 28.7 271.0
db block gets direct 0 0.0 0.0
db block gets from cache 1,897 28.7 271.0
deferred (CURRENT) block cleanout 30 0.5 4.3
dirty buffers inspected 0 0.0 0.0
enqueue conversions 58 0.9 8.3
enqueue releases 993 15.1 141.9
enqueue requests 993 15.1 141.9
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 1,720 26.1 245.7
flashback log writes 3 0.1 0.4
frame signature mismatch 0 0.0 0.0
free buffer inspected 0 0.0 0.0
free buffer requested 276 4.2 39.4
heap block compress 0 0.0 0.0
hot buffers moved to head of LRU 0 0.0 0.0
immediate (CR) block cleanout app 0 0.0 0.0
immediate (CURRENT) block cleanou 46 0.7 6.6
index fast full scans (full) 1 0.0 0.1
index fetch by key 965 14.6 137.9
index scans kdiixs1 1,307 19.8 186.7
leaf node 90-10 splits 2 0.0 0.3
leaf node splits 17 0.3 2.4
lob reads 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logons cumulative 4 0.1 0.6
messages received 98 1.5 14.0
messages sent 98 1.5 14.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 8,443 127.9 1,206.1
opened cursors cumulative 1,322 20.0 188.9
parse count (failures) 0 0.0 0.0
parse count (hard) 137 2.1 19.6
parse count (total) 648 9.8 92.6
parse time cpu 73 1.1 10.4
parse time elapsed 85 1.3 12.1
physical read IO requests 272 4.1 38.9
physical read bytes 2,228,224 33,761.0 318,317.7
physical read total IO requests 679 10.3 97.0
physical read total bytes 8,805,376 133,414.8 1,257,910.9
physical read total multi block r 4 0.1 0.6
physical reads 272 4.1 38.9
physical reads cache 272 4.1 38.9
physical reads cache prefetch 0 0.0 0.0
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 230 3.5 32.9
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 239 3.6 34.1
physical write bytes 2,793,472 42,325.3 399,067.4
physical write total IO requests 316 4.8 45.1
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-11
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical write total bytes 6,031,360 91,384.2 861,622.9
physical write total multi block 75 1.1 10.7
physical writes 341 5.2 48.7
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 341 5.2 48.7
physical writes non checkpoint 326 4.9 46.6
pinned buffers inspected 0 0.0 0.0
prefetch warmup blocks aged out b 0 0.0 0.0
prefetch warmup blocks flushed ou 0 0.0 0.0
process last non-idle time 0 0.0 0.0
recursive calls 21,649 328.0 3,092.7
recursive cpu usage 246 3.7 35.1
redo blocks written 1,772 26.9 253.1
redo buffer allocation retries 0 0.0 0.0
redo entries 885 13.4 126.4
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 884,328 13,398.9 126,332.6
redo synch time 0 0.0 0.0
redo synch writes 59 0.9 8.4
redo wastage 4,444 67.3 634.9
redo write time 16 0.2 2.3
redo writer latching time 0 0.0 0.0
redo writes 15 0.2 2.1
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 2 0.0 0.3
rows fetched via callback 304 4.6 43.4
session connect time 0 0.0 0.0
session cursor cache hits 1,061 16.1 151.6
session logical reads 14,369 217.7 2,052.7
session pga memory 1,240,660 18,797.9 177,237.1
session pga memory max 1,961,556 29,720.6 280,222.3
session uga memory 4,295,282,184 65,080,033.1 ############
session uga memory max 2,649,664 40,146.4 378,523.4
shared hash latch upgrades - no w 1,307 19.8 186.7
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 395 6.0 56.4
sorts (rows) 10,062 152.5 1,437.4
sql area purged 1 0.0 0.1
summed dirty queue length 0 0.0 0.0
switch current to new buffer 2 0.0 0.3
table fetch by rowid 4,185 63.4 597.9
table fetch continued row 60 0.9 8.6
table scan blocks gotten 867 13.1 123.9
table scan rows gotten 57,726 874.6 8,246.6
table scans (long tables) 0 0.0 0.0
table scans (short tables) 42 0.6 6.0
total number of times SMON posted 0 0.0 0.0
transaction rollbacks 0 0.0 0.0
undo change vector size 297,196 4,503.0 42,456.6
user I/O wait time 65 1.0 9.3
user calls 148 2.2 21.1
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-11
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
user commits 7 0.1 1.0
user rollbacks 0 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 280 4.2 40.0
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: 1-11
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 34 34
opened cursors current 372 419
session cursor cache count 210,988 211,028
-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-11
-> 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: 1-11
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 759
IDLE_TIME 5,572
NICE_TIME 8
SYS_TIME 565
USER_TIME 186
RSRC_MGR_CPU_WAIT_TIME 0
NUM_CPUS 1
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 1-11
->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
7,790 118 14.2 1.9 16,923 256 57 19.8
UNDOTBS1
14,239 216 10.3 1.0 5,942 90 85 6.4
SYSTEM
13,009 197 18.6 1.2 3,309 50 19 76.3
USERS
2,129 32 88.7 2.0 641 10 0 0.0
EXAMPLE
55 1 335.1 1.6 16 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: ORCL/orcl Snaps: 1-11
->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)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
EXAMPLE +DISK1/orcl/datafile/example.258.691078173
55 1 ##### 1.6 16 0 0
SYSAUX +DISK1/orcl/datafile/sysaux.261.691078173
7,790 118 14.2 64 1.9 16,923 256 57 19.8
SYSTEM +DISK1/orcl/datafile/system.259.691078173
13,009 197 18.6 16 1.2 3,309 50 19 76.3
UNDOTBS1 +DISK1/orcl/datafile/undotbs1.260.691078173
14,239 216 10.3 16 1.0 5,942 90 85 6.4
USERS +DISK1/orcl/datafile/users.262.691078191
2,129 32 88.7 2.0 641 10 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 1-11
->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
32 9 7 1 0 0
SYSTEM +DISK1/orcl/datafile/system.259.691078173
0 3 3 3 0 0
SYSAUX +DISK1/orcl/datafile/sysaux.261.691078173
210 42 35 23 0 1
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 20K 98 15,069 341 341 0 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 28 2516 26515 29486 184320 29486
E 0 27 2412 28264 31193 184320 31193
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 11
-> 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 7.2 358 4,877 41.8
D 32 .2 4 4.5 225 2,997 25.7
D 48 .3 6 3.0 149 1,919 16.5
D 64 .4 8 2.2 108 1,340 11.5
D 80 .5 10 1.7 82 968 8.3
D 96 .6 12 1.4 71 818 7.0
D 112 .7 14 1.3 63 702 6.0
D 128 .8 16 1.2 58 632 5.4
D 144 .9 18 1.1 55 591 5.1
D 160 1.0 20 1.0 50 520 4.5
D 168 1.0 21 1.0 50 512 4.4
D 176 1.0 22 1.0 49 503 4.3
D 192 1.1 24 1.0 49 497 4.3
D 208 1.2 26 1.0 48 495 4.2
D 224 1.3 28 1.0 48 494 4.2
D 240 1.4 30 1.0 48 482 4.1
D 256 1.5 32 0.9 47 468 4.0
D 272 1.6 34 0.9 46 456 3.9
D 288 1.7 36 0.9 45 443 3.8
D 304 1.8 38 0.9 44 430 3.7
D 320 1.9 40 0.8 40 373 3.2
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 30 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 162.8 0.0 .0 .0 .0 3,276
E 16 4 163.3 0.0 .0 .0 .0 3,276
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 1-11
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 188 188 0 0
64K 128K 4 4 0 0
512K 1024K 40 40 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 11
-> 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,664.2 331.6 93.0 78
16 1.0 4,664.2 28.7 99.0 78
19 1.2 4,664.2 28.7 99.0 78
22 1.4 4,664.2 28.7 99.0 78
26 1.6 4,664.2 28.7 99.0 78
29 1.8 4,664.2 28.7 99.0 78
32 2.0 4,664.2 28.7 99.0 78
48 3.0 4,664.2 28.7 99.0 78
64 4.0 4,664.2 28.7 99.0 78
96 6.0 4,664.2 25.5 99.0 11
128 8.0 4,664.2 0.0 100.0 2
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 -------- 162.8 77.4 16.3 4.5 6.2 24 42 36
Other 144.5 4.0 6.2 24 38 36
Freeable 16.3 .0 .7 .4 2 22
SQL 1.5 .6 .1 .0 0 3 18
PL/SQL .6 .3 .0 .0 0 5 34
E -------- 163.3 77.6 16.3 4.5 6.2 24 42 36
Other 144.8 4.0 6.2 24 38 36
Freeable 16.3 .0 .7 .4 2 22
SQL 1.5 .6 .1 .0 0 3 18
PL/SQL .6 .3 .0 .0 0 5 34
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-11
-> 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
20 ARC0 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
36 TNS V1-V3 --- 13.4 5.9 .9 13.4 25.2
Other 12.4 12.4 18.1
Freeable .9 .0 .9
SQL .0 .0 .0 .7
PL/SQL .0 .0 .0 5.5
27 TNS V1-V3 --- 5.6 1.8 1.7 5.6 5.6
Other 3.8 3.8 3.8
Freeable 1.7 .0 1.7
SQL .1 .1 .1 2.2
PL/SQL .0 .0 .0 .0
25 ------------ 5.1 2.3 .9 5.1 41.8
Other 4.0 4.0 37.5
Freeable .9 .0 .9
SQL .1 .0 .1 3.2
PL/SQL .1 .0 .1 .1
34 ------------ 4.8 2.6 .8 4.8 41.3
Other 3.8 3.8 37.3
Freeable .8 .0 .8
SQL .1 .0 .1 3.1
PL/SQL .1 .0 .1 .1
11 MMON -------- 4.8 2.4 1.1 4.8 4.9
Other 3.6 3.6 3.6
Freeable 1.1 .0 1.1
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
40 ------------ 4.6 2.2 .9 4.6 4.6
Other 3.6 3.6 3.6
Freeable .9 .0 .9
SQL .1 .0 .1 1.3
PL/SQL .1 .0 .1 .1
8 SMON -------- 4.3 1.5 .9 4.3 4.3
Other 3.4 3.4 3.4
Freeable .9 .0 .9
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 3.8 1.5 1.0 3.8 3.8
Other 2.8 2.8 2.8
Freeable 1.0 .0 1.0
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
15 ------------ 3.8 1.5 1.0 3.8 5.1
Other 2.8 2.8 2.8
Freeable 1.0 .0 1.0
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-11
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 15 SQL .0 .0 .0 2.1
PL/SQL .0 .0 .0 .0
31 q001 -------- 3.7 1.5 .8 3.7 3.7
Other 2.8 2.8 2.8
Freeable .8 .0 .8
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
19 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
33 ------------ 3.4 1.9 1.0 3.4 36.8
Other 2.3 2.3 32.7
Freeable 1.0 .0 1.0
SQL .0 .0 .0 3.1
PL/SQL .0 .0 .0 .1
26 ------------ 3.3 2.2 .9 3.3 5.2
Other 2.2 2.2 2.6
Freeable .9 .0 .9
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
29 ------------ 3.2 2.2 .8 3.2 4.4
Other 2.3 2.3 2.4
Freeable .8 .0 .8
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
35 ------------ 3.1 1.3 .4 3.1 3.1
Other 2.6 2.6 2.6
Freeable .4 .0 .4
SQL .1 .0 .1 .4
PL/SQL .0 .0 .0 .0
9 RECO -------- 3.0 1.3 .2 3.0 3.0
Other 2.7 2.7 2.7
Freeable .2 .0 .2
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
7 CKPT -------- 3.0 1.2 .5 3.0 3.5
Other 2.4 2.4 2.9
Freeable .5 .0 .5
SQL .0 .0 .0 .1
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
20 ARC0 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 23.1 11.1 .0 23.1 23.1
Other 23.1 23.1 23.1
PL/SQL .0 .0 .0 .0
36 TNS V1-V3 --- 13.4 5.9 .9 13.4 25.2
Other 12.4 12.4 18.1
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-11
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 36 Freeable .9 .0 .9
SQL .0 .0 .0 .7
PL/SQL .0 .0 .0 5.5
15 ------------ 5.8 1.9 1.8 5.8 6.1
Other 3.9 3.9 3.9
Freeable 1.8 .0 1.8
SQL .1 .1 .1 2.9
PL/SQL .0 .0 .0 .0
25 ------------ 5.1 2.3 .9 5.1 41.8
Other 4.0 4.0 37.5
Freeable .9 .0 .9
SQL .1 .0 .1 3.2
PL/SQL .1 .0 .1 .1
34 ------------ 4.8 2.6 .8 4.8 41.3
Other 3.8 3.8 37.3
Freeable .8 .0 .8
SQL .1 .0 .1 3.1
PL/SQL .1 .0 .1 .1
11 MMON -------- 4.8 2.4 1.1 4.8 4.9
Other 3.6 3.6 3.6
Freeable 1.1 .0 1.1
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
40 ------------ 4.6 2.2 .9 4.6 4.6
Other 3.6 3.6 3.6
Freeable .9 .0 .9
SQL .1 .0 .1 1.3
PL/SQL .1 .0 .1 .1
8 SMON -------- 4.3 1.5 .9 4.3 4.3
Other 3.4 3.4 3.4
Freeable .9 .0 .9
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
27 TNS V1-V3 --- 4.0 1.6 1.0 4.0 5.6
Other 2.8 2.8 2.8
Freeable 1.0 .0 1.0
SQL .1 .0 .1 2.2
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 3.8 1.5 1.0 3.8 3.8
Other 2.8 2.8 2.8
Freeable 1.0 .0 1.0
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
31 q001 -------- 3.7 1.5 .8 3.7 3.7
Other 2.8 2.8 2.8
Freeable .8 .0 .8
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
19 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
33 ------------ 3.4 1.9 1.0 3.4 36.8
Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 1-11
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 33 Other 2.3 2.3 32.7
Freeable 1.0 .0 1.0
PL/SQL .0 .0 .0 .1
SQL .0 .0 .0 3.1
26 ------------ 3.3 2.2 .9 3.3 5.2
Other 2.2 2.2 2.6
Freeable .9 .0 .9
SQL .1 .0 .1 1.7
PL/SQL .0 .0 .0 .0
29 ------------ 3.2 2.2 .8 3.2 4.4
Other 2.3 2.3 2.4
Freeable .8 .0 .8
SQL .1 .0 .1 1.2
PL/SQL .0 .0 .0 .0
35 ------------ 3.1 1.3 .4 3.1 3.1
Other 2.6 2.6 2.6
Freeable .4 .0 .4
SQL .1 .0 .1 .4
PL/SQL .0 .0 .0 .0
9 RECO -------- 3.0 1.3 .2 3.0 3.0
Other 2.7 2.7 2.7
Freeable .2 .0 .2
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
7 CKPT -------- 3.0 1.2 .5 3.0 3.5
Other 2.4 2.4 2.9
Freeable .5 .0 .5
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-11
->"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 hash t 1,984 0.0 0 0
ASM network background l 24 0.0 0 0
Consistent RBA 15 0.0 0 0
FAL request queue 1 0.0 0 0
FAL subheap alocation 1 0.0 0 0
FIB s.o chain latch 4 0.0 0 0
FOB s.o list latch 7 0.0 0 0
JS queue state obj latch 468 0.0 0 0
JS slv state obj latch 3 0.0 0 0
KFK SGA context latch 40 0.0 0 0
KMG MMAN ready and start 66 0.0 0 0
KMG resize request state 2 0.0 0 0
KTF sga latch 0 0 19 0.0
KWQP Prop Status 1 0.0 0 0
MQL Tracking Latch 0 0 1 0.0
Memory Management Latch 65 0.0 0 66 0.0
OS process 15 0.0 0 0
OS process allocation 31 0.0 0 0
OS process: request allo 6 0.0 0 0
PL/SQL warning settings 15 0.0 0 0
SGA IO buffer pool latch 14 0.0 0 14 0.0
SQL memory manager latch 64 0.0 0 22 0.0
SQL memory manager worka 5,914 0.0 0 0
Shared B-Tree 5 0.0 0 0
active checkpoint queue 62 0.0 0 0
active service list 150 0.0 0 22 0.0
archive control 2 0.0 0 0
archive process latch 23 0.0 0 0
buffer pool 2 0.0 0 0
cache buffer handles 130 0.0 0 0
cache buffers chains 30,571 0.0 0 6,062 0.0
cache buffers lru chain 2,128 0.0 0 4 0.0
channel handle pool latc 6 0.0 0 0
channel operations paren 392 0.0 0 0
checkpoint queue latch 939 0.0 0 478 0.0
client/application info 45 0.0 0 0
compile environment latc 65 0.0 0 0
dml lock allocation 359 0.0 0 0
dummy allocation 8 0.0 0 0
enqueue hash chains 2,018 0.0 0 0
enqueues 1,609 0.0 0 0
event group latch 3 0.0 0 0
file cache latch 1 0.0 0 0
flashback allocation 264 0.0 0 0
flashback copy 0 0 239 0.0
flashback mapping 3 0.0 0 0
global KZLD latch for me 2 0.0 0 0
hash table column usage 0 0 6,191 0.0
hash table modification 22 0.0 0 0
job workq parent latch 0 0 2 0.0
Latch Activity DB/Inst: ORCL/orcl Snaps: 1-11
->"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 2 0.0 0 0
kks stats 418 0.0 0 0
ksuosstats global area 7 0.0 0 0
kwqbsn:qsga 5 0.0 0 0
lgwr LWN SCN 27 0.0 0 0
library cache 13,492 0.0 0 45 0.0
library cache load lock 692 0.0 0 0
library cache lock 4,174 0.0 0 0
library cache lock alloc 146 0.0 0 0
library cache pin 7,080 0.0 0 0
library cache pin alloca 136 0.0 0 0
list of block allocation 10 0.0 0 0
messages 708 0.0 0 0
mostly latch-free SCN 27 0.0 0 0
ncodef allocation latch 1 0.0 0 0
object queue header heap 10 0.0 0 28 0.0
object queue header oper 1,797 0.0 0 0
parallel query alloc buf 12 0.0 0 0
parameter table allocati 6 0.0 0 0
process allocation 6 0.0 0 3 0.0
process group creation 6 0.0 0 0
qmn task queue latch 8 0.0 0 0
redo allocation 42 0.0 0 898 0.0
redo copy 0 0 898 0.0
redo writing 154 0.0 0 0
resmgr group change latc 9 0.0 0 0
resmgr:actses active lis 22 0.0 0 0
resmgr:actses change gro 3 0.0 0 0
resmgr:free threads list 8 0.0 0 0
resmgr:resource group CP 4 0.0 0 0
resmgr:schema config 14 0.0 0 0
row cache objects 18,514 0.0 0 268 0.0
rules engine rule set st 100 0.0 0 0
sequence cache 11 0.0 0 0
session allocation 29,578 0.0 0 0
session idle bit 357 0.0 0 0
session state list latch 7 0.0 0 0
session switching 1 0.0 0 0
session timer 22 0.0 0 0
shared pool 12,634 0.0 0 0
simulator hash latch 808 0.0 0 0
simulator lru latch 772 0.0 0 23 0.0
sort extent pool 1 0.0 0 0
threshold alerts latch 1 0.0 0 0
transaction allocation 2 0.0 0 0
transaction branch alloc 1 0.0 0 0
undo global data 168 0.0 0 0
user lock 10 0.0 0 0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: ORCL/orcl Snaps: 1-11
->"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 87 0.0 0 0 81
dc_histogram_data 338 0.0 0 0 825
dc_histogram_defs 1,604 24.8 0 0 5,618
dc_object_grants 6 0.0 0 0 208
dc_object_ids 621 0.0 0 0 1,636
dc_objects 347 0.3 0 1 2,152
dc_profiles 5 0.0 0 0 2
dc_segments 296 0.0 0 0 1,086
dc_sequences 1 0.0 0 1 13
dc_tablespaces 1,424 0.0 0 0 7
dc_usernames 34 0.0 0 0 20
dc_users 1,718 0.0 0 0 57
-------------------------------------------------------------
Library Cache Activity DB/Inst: ORCL/orcl Snaps: 1-11
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 200 4.5 2,672 10.1 99 1
TABLE/PROCEDURE 703 0.4 1,826 26.3 242 0
BODY 11 0.0 106 2.8 3 0
TRIGGER 4 0.0 4 100.0 4 0
CLUSTER 8 0.0 12 0.0 0 0
-------------------------------------------------------------
Rule Sets DB/Inst: ORCL/orcl Snaps: 1-11
-> * 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.SCHED_RULESET$2 0 0 0 0
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: ORCL/orcl End Snap: 11
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
80 .9 13 1,921 15,000 1.0 1,118 1.1 2,225,731
92 1.0 24 3,091 15,110 1.0 1,008 1.0 2,241,243
104 1.1 35 4,892 15,209 1.0 909 .9 2,254,634
116 1.3 46 6,031 15,291 1.0 827 .8 2,265,478
128 1.4 57 7,466 15,355 1.0 763 .8 2,273,713
140 1.5 68 8,774 15,404 1.0 714 .7 2,279,884
152 1.7 79 10,222 15,442 1.0 676 .7 2,284,458
164 1.8 90 11,738 15,472 1.0 646 .6 2,287,835
176 1.9 101 13,300 15,496 1.0 622 .6 2,290,351
188 2.0 112 14,581 15,515 1.0 603 .6 2,292,241
-------------------------------------------------------------
Cache Size Changes DB/Inst: ORCL/orcl Snaps: 1-11
-> Not all cache size changes may be captured. Only cache changes which are
evident at snapshot time are shown
Prior New Difference
Snap Id Cache Size (MB) Size (MB) (MB)
--------- ------------ --------- --------- ----------
11 Buffer Cache 172 168 -4
Shared Pool 88 92 4
-------------------------------------------------------------
SGA Target Advisory DB/Inst: ORCL/orcl End Snap: 11
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
136 .5 14,326 1.2 176,643
204 .8 12,111 1.0 49,466
272 1.0 11,664 1.0 49,466
340 1.3 11,439 1.0 38,880
408 1.5 11,379 1.0 37,970
476 1.8 11,327 1.0 35,081
544 2.0 11,256 1.0 31,213
-------------------------------------------------------------
SGA Memory Summary DB/Inst: ORCL/orcl Snaps: 1-11
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 180,355,072 176,160,768
Fixed Size 1,218,992
Redo Buffers 2,973,696
Variable Size 100,664,912 104,859,216
-------------------- --------------------
sum 285,212,672 281,019,984
-------------------------------------------------------------
SGA breakdown difference DB/Inst: ORCL/orcl Snaps: 1-11
-> 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 ASH buffers 2.0 2.0 0.00
shared ASM extent pointer array 36.7 36.7 0.00
shared CCursor 1.8 1.9 4.97
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGH: NO ACCESS 23.8 23.8 0.00
shared KGLS heap 1.8 2.1 19.66
shared KQR M PO 4.5 4.7 3.87
shared KSFD SGA I/O b 3.8 3.8 0.00
shared PL/SQL DIANA 2.1 2.1 0.38
shared PL/SQL MPCODE 1.5 ########
shared flashback generation buff 3.8 3.8 0.00
shared free memory 6.7 8.3 24.62
shared kglsim hash table bkts 2.0 2.0 0.00
shared library cache 8.1 8.1 -0.56
shared row cache 3.6 3.6 0.00
shared sql area 5.4 6.6 21.45
buffer_cache 172.0 168.0 -2.33
fixed_sga 1.2 1.2 0.00
log_buffer 2.8 2.8 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: ORCL/orcl Snaps: 1-11
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 49.63 53.29 6.86
Cursor to Parent ratio: 1.07 1.12 4.62
Total Cursors: 578 570 -1.40
Total Parents: 539 507 -6.31
-------------------------------------------------------------
init.ora Parameters DB/Inst: ORCL/orcl Snaps: 1-11
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_cache_size 104857600
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
resource_manager_plan MYPLAN
sga_target 285212672
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/app/oracle/admin/orcl/udump
-------------------------------------------------------------
End of Report ( /home/oracle/report.txt )
댓글 없음:
댓글 쓰기