Oracle Database Discussions

How do I interpret it ...And can anyone please suggest measures to improve my database performance from the available report.

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1184214567 orcl 1 03-Jun-08 16:02 10.2.0.1.0 NO

Host Name: KRISHNAF39E Num CPUs: 1 Phys Memory (MB): 512
~~~~

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 03-Jun-08 16:07:21 17 5.8
End Snap: 32 03-Jun-08 21:35:08 22 9.0
Elapsed: 327.78 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 76M 72M Std Block Size: 8K
Shared Pool Size: 72M 76M Log Buffer: 2,832K

Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 626.65 10,085.31
Logical reads: 18.57 298.82
Block changes: 2.72 43.79
Physical reads: 0.13 2.07
Physical writes: 0.34 5.40
User calls: 1.31 21.14
Parses: 1.56 25.10
Hard parses: 0.20 3.14
Sorts: 0.95 15.27
Logons: 0.01 0.17
Executes: 4.16 67.02
Transactions: 0.06

% Blocks changed per Read: 14.66 Recursive Call %: 95.81
Rollback per transaction %: 5.32 Rows per Sort: 9.86

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.99
Buffer Hit %: 99.31 In-memory Sort %: 100.00
Library Hit %: 92.48 Soft Parse %: 87.47
Execute to Parse %: 62.55 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 76.73 % Non-Parse CPU: 86.05

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 75.34 93.45
% SQL with executions>1: 64.78 93.71
% Memory for SQL w/exec>1: 85.34 95.57

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
control file parallel write 5,371 147 27 28.2
CPU time 108 20.7
db file parallel write 3,730 86 23 16.5
log file parallel write 2,080 45 22 8.7
control file sequential read 5,651 42 7 8.0
-------------------------------------------------------------
Host CPU (CPUs: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.89 3.04 96.07

Note: There is a 18% discrepancy between the OS Stat total CPU time and
the total CPU time estimated by Statspack
OS Stat CPU time: 16115(s) (BUSY_TIME + IDLE_TIME)
Statspack CPU time: 19667(s) (Elapsed time * num CPUs in end snap)

Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.92
% of busy CPU for Instance: 23.31
%DB time waiting for CPU - Resource Mgr:

Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 511.6 511.6
SGA use (MB): 160.0 160.0
PGA use (MB): 24.5 29.5
% Host Mem used for SGA+PGA: 36.1 37.1
-------------------------------------------------------------

Time Model System Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> Ordered by % of DB time desc, Statistic name

Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 162.4 76.3
DB CPU 103.7 48.7
PL/SQL execution elapsed time 50.2 23.6
parse time elapsed 36.3 17.1
hard parse elapsed time 34.0 16.0
PL/SQL compilation elapsed time 5.5 2.6
hard parse (sharing criteria) elaps 0.8 .4
connection management call elapsed 0.6 .3
repeated bind elapsed time 0.2 .1
hard parse (bind mismatch) elapsed 0.0 .0
sequence load elapsed time 0.0 .0
failed parse elapsed time 0.0 .0
DB time 212.8
background elapsed time 366.6
background cpu time 43.9
-------------------------------------------------------------
Wait Events DB/Inst: ORCL/orcl Snaps: 1-32
-> 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
--------------------------------- ------------ ------ ---------- ------ --------
control file parallel write 5,371 0 147 27 4.4
db file parallel write 3,730 0 86 23 3.1
log file parallel write 2,080 0 45 22 1.7
control file sequential read 5,651 0 42 7 4.6
db file sequential read 2,009 0 37 19 1.6
log file sync 910 1 35 38 0.7
Streams AQ: qmn coordinator waiti 2 100 10 5004 0.0
db file scattered read 111 0 4 35 0.1
os thread startup 71 0 3 41 0.1
Data file init write 16 0 1 72 0.0
library cache load lock 3 0 1 337 0.0
SQL*Net break/reset to client 1,232 0 1 1 1.0
log file switch completion 3 0 1 192 0.0
SGA: MMAN sleep for component shr 64 67 0 7 0.1
latch free 416 0 0 0 0.3
read by other session 6 0 0 20 0.0
rdbms ipc reply 62 0 0 2 0.1
library cache pin 4 0 0 16 0.0
log file sequential read 2 0 0 14 0.0
latch: library cache 6 0 0 3 0.0
LGWR wait for redo copy 24 0 0 1 0.0
log file single write 2 0 0 5 0.0
buffer busy waits 9 0 0 1 0.0
latch: shared pool 5 0 0 0 0.0
direct path write 21 0 0 0 0.0
SQL*Net message from client 23,572 0 49,054 2081 19.3
Streams AQ: qmn slave idle wait 705 16 18,942 26868 0.6
Streams AQ: qmn coordinator idle 1,015 76 16,085 15848 0.8
Streams AQ: waiting for messages 3,226 99 16,080 4985 2.6
virtual circuit status 538 100 16,071 29872 0.4
wait for unread message on broadc 15,873 100 15,891 1001 13.0
Streams AQ: waiting for time mana 87 47 10,271 ###### 0.1
jobq slave wait 1,263 99 3,780 2993 1.0
class slave wait 3 100 15 5004 0.0
SQL*Net message to client 23,577 0 0 0 19.3
SQL*Net more data from client 139 0 0 0 0.1
-------------------------------------------------------------
Background Wait Events DB/Inst: ORCL/orcl Snaps: 1-32
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)

Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
control file parallel write 5,368 0 147 27 4.4
db file parallel write 3,730 0 86 23 3.1
log file parallel write 2,080 0 45 22 1.7
events in waitclass Other 369 1 10 28 0.3
control file sequential read 783 0 8 10 0.6
os thread startup 71 0 3 41 0.1
db file sequential read 112 0 2 17 0.1
db file scattered read 70 0 1 20 0.1
log file switch completion 3 0 1 192 0.0
SGA: MMAN sleep for component shr 64 67 0 7 0.1
log file sequential read 2 0 0 14 0.0
log file single write 2 0 0 5 0.0
buffer busy waits 9 0 0 1 0.0
rdbms ipc message 53,282 96 139,790 2624 43.6
pmon timer 5,447 100 16,111 2958 4.5
Streams AQ: qmn coordinator idle 1,015 76 16,085 15848 0.8
smon timer 67 73 15,427 ###### 0.1
Streams AQ: qmn slave idle wait 456 11 12,775 28016 0.4
Streams AQ: waiting for time mana 87 47 10,271 ###### 0.1
-------------------------------------------------------------
Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 1-32
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Data file init write 16 37.5 18.8 31.3 12.5
LGWR wait for redo copy 24 95.8 4.2
SGA: MMAN sleep for compon 64 14.1 4.7 6.3 29.7 43.8 1.6
SQL*Net break/reset to cli 1232 91.2 6.9 .8 .6 .2 .2
SQL*Net more data to clien 12 100.0
Streams AQ: qmn coordinato 2 100.0
buffer busy waits 9 88.9 11.1
control file parallel writ 5371 .4 39.6 54.2 .8 .4 .8 3.3 .5
control file sequential re 5651 86.9 2.9 1.3 .9 1.7 3.1 3.0 .1
db file parallel write 3730 28.7 6.1 10.0 9.7 15.4 20.1 9.8 .2
db file scattered read 111 10.8 9.0 18.9 11.7 10.8 17.1 21.6
db file sequential read 2010 28.6 14.3 4.3 4.7 13.5 20.4 14.2
db file single write 1 100.0
direct path read 6 100.0
direct path write 21 100.0
latch free 413 96.9 1.7 .7 .7
latch: enqueue hash chains 1 100.0
latch: library cache 4 75.0 25.0
latch: library cache pin 1 100.0
latch: shared pool 5 80.0 20.0
library cache load lock 3 100.0
library cache pin 4 50.0 25.0 25.0
log file parallel write 2080 69.3 4.7 1.9 .9 2.9 10.2 9.8 .2
log file sequential read 2 50.0 50.0
log file single write 2 50.0 50.0
log file switch completion 3 33.3 33.3 33.3
log file sync 910 41.0 17.7 2.1 3.0 5.4 13.2 17.7
os thread startup 71 1.4 1.4 35.2 31.0 12.7 18.3
rdbms ipc reply 62 83.9 3.2 1.6 4.8 6.5
read by other session 6 33.3 50.0 16.7
undo segment extension 1 100.0
SQL*Net message from clien 23K 84.4 2.2 1.1 .7 .6 .5 .6 9.9
SQL*Net message to client 23K 99.9 .1
SQL*Net more data from cli 139 100.0
Streams AQ: qmn coordinato 1015 22.9 .9 .1 .2 .1 75.9
Streams AQ: qmn slave idle 705 4.1 .1 .1 .6 1.0 94.0
Streams AQ: waiting for me 3226 .0 .3 99.7
Streams AQ: waiting for ti 87 23.0 10.3 66.7
class slave wait 3 100.0
dispatcher timer 269 100.0
jobq slave wait 1263 .4 99.6
pmon timer 5447 1.4 .1 .1 98.4
rdbms ipc message 53K 1.0 .2 .2 .2 .3 .2 31.2 66.7
smon timer 67 9.0 1.5 89.6
virtual circuit status 538 100.0
wait for unread message on 15K .0 .0 99.9 .1
-------------------------------------------------------------

SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 1-32
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 104
-> Captured SQL accounts for 61.1% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
44.02 576 0.08 42.5 46.47 12,780 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

5.77 6,381 0.00 5.6 5.98 40,624 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;

1.81 1 1.81 1.7 2.08 13,786 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;

-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: ORCL/orcl Snaps: 1-32
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 213
-> Captured SQL accounts for 39.2% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
46.47 576 0.08 21.8 44.02 76 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

6.18 478 0.01 2.9 0.27 288 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#

5.98 6,381 0.00 2.8 5.77 6 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-32
-> 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: 365,154
-> Captured SQL accounts for 54.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
40,624 6,381 6.4 11.1 5.77 5.98 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;

20,280 4,090 5.0 5.6 0.38 0.38 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1

19,221 6,381 3.0 5.3 0.74 0.74 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 <=

13,786 1 13,786.0 3.8 1.81 2.08 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;

12,780 576 22.2 3.5 44.02 46.47 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

12,293 3,871 3.2 3.4 0.55 1.24 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),

11,781 860 13.7 3.2 0.28 1.33 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#

10,106 152 66.5 2.8 0.57 0.99 1405417040
Module: OEM.SystemPool
INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE, ME
TRIC_GUID, STRING_VALUE, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2
, ' '), :3, :4, :5, :6)

9,538 1,313 7.3 2.6 0.20 0.21 2889900621
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1

9,414 860 10.9 2.6 0.22 1.05 1198893840
select order#,columns,types from access$ where d_obj#=:1

8,535 1,002 8.5 2.3 0.48 0.88 3694268570
SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 1-32
-> 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: 365,154
-> Captured SQL accounts for 54.0% 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
--------------- ------------ -------------- ------ -------- --------- ----------
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i
.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.
distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
d(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(

8,516 3,330 2.6 2.3 0.23 1.51 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

7,836 194 40.4 2.1 0.06 0.06 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#

6,647 3,269 2.0 1.8 0.99 0.99 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job

6,643 860 7.7 1.8 0.18 0.20 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

6,152 112 54.9 1.7 0.62 1.05 1666200974
Module: OEM.SystemPool
BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END;

5,398 173 31.2 1.5 0.36 0.47 2384968239
Module: OEM.SystemPool
BEGIN :1 := MGMT_BLACKOUT_ENGINE.get_blackout_windows(:2,:3); EN
D;

5,037 1,247 4.0 1.4 0.52 0.54 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

-------------------------------------------------------------
SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 1-32
-> End Disk Reads Threshold: 1000 Total Disk Reads: 2,530
-> Captured SQL accounts for 28.4% 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
--------------- ------------ -------------- ------ -------- --------- ----------
288 478 0.6 11.4 0.27 6.18 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#

76 576 0.1 3.0 44.02 46.47 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

69 478 0.1 2.7 0.12 1.25 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#

47 3,330 0.0 1.9 0.23 1.51 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

43 860 0.1 1.7 0.28 1.33 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#

36 478 0.1 1.4 0.11 1.58 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#

35 860 0.0 1.4 0.22 1.05 1198893840
select order#,columns,types from access$ where d_obj#=:1

26 173 0.2 1.0 0.36 0.47 2384968239
Module: OEM.SystemPool
BEGIN :1 := MGMT_BLACKOUT_ENGINE.get_blackout_windows(:2,:3); EN
D;

-------------------------------------------------------------
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-32
-> End Executions Threshold: 100 Total Executions: 81,903
-> Captured SQL accounts for 76.9% 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
------------ --------------- ---------------- ----------- ---------- ----------
6,435 6,435 1.0 0.00 0.00 1667689875
Module: EM_PING
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL

6,381 6,381 1.0 0.00 0.00 1396250429
Module: OEM.SystemPool
begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end;

6,381 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 <=

4,090 6,051 1.5 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1

3,871 3,255 0.8 0.00 0.00 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),

3,330 1,856 0.6 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2

3,269 52 0.0 0.00 0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job

3,218 3,218 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))

1,313 3,457 2.6 0.00 0.00 2889900621
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1

1,247 15,878 12.7 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
SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-32
-> End Executions Threshold: 100 Total Executions: 81,903
-> Captured SQL accounts for 76.9% 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
------------ --------------- ---------------- ----------- ---------- ----------
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh

1,003 474 0.5 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#

1,002 1,313 1.3 0.00 0.00 3694268570
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i
.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.
distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
d(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(

999 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#

927 856 0.9 0.00 0.00 2703824309
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and remoteowner is null and linkname is null and subname i
s null

901 365 0.4 0.00 0.00 2018736380
select timestamp, flags from fixed_obj$ where obj#=:1

860 3,847 4.5 0.00 0.00 1198893840
select order#,columns,types from access$ where d_obj#=:1

860 4,090 4.8 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

860 4,216 4.9 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#

860 216 0.3 0.00 0.00 2065408759
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1

857 857 1.0 0.00 0.00 3047156589
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.int
cols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.i
nitrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,
t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degr
ee,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(

SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 1-32
-> End Executions Threshold: 100 Total Executions: 81,903
-> Captured SQL accounts for 76.9% 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
------------ --------------- ---------------- ----------- ---------- ----------
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 1-32
-> End Parse Calls Threshold: 1000 Total Parse Calls: 30,669
-> Captured SQL accounts for 57.7% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls

% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,388 616 4.53 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)

1,388 3,871 4.53 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),

901 901 2.94 2018736380
select timestamp, flags from fixed_obj$ where obj#=:1

860 860 2.80 1198893840
select order#,columns,types from access$ where d_obj#=:1

860 860 2.80 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#

595 595 1.94 1348827743
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2
and block#=:3

515 514 1.68 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn

478 478 1.56 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#

478 478 1.56 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#

478 478 1.56 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#

SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 1-32
-> End Parse Calls Threshold: 1000 Total Parse Calls: 30,669
-> Captured SQL accounts for 57.7% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls

% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
478 478 1.56 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#

469 576 1.53 1206412919
Module: OEM.SystemPool
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;

463 463 1.51 3468666020
select text from view$ where rowid=:1

442 442 1.44 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0

437 1,003 1.42 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#

433 999 1.41 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#

429 1,247 1.40 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

383 383 1.25 794436051
Module: OEM.SystemPool
SELECT INSTANTIABLE, supertype_owner, supertype_name, LOCAL_ATTR
IBUTES FROM all_types WHERE type_name = :1 AND owner = :2

320 860 1.04 2065408759
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1

316 860 1.03 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

-------------------------------------------------------------
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-32

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 10,750 0.6 8.8
CPU used when call started 8,740 0.4 7.2
CR blocks created 325 0.0 0.3
DB time 3,594,299 182.8 2,941.3
DBWR checkpoint buffers written 6,467 0.3 5.3
DBWR checkpoints 1 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR transaction table writes 341 0.0 0.3
DBWR undo block writes 1,453 0.1 1.2
IMU CR rollbacks 114 0.0 0.1
IMU Flushes 5,074 0.3 4.2
IMU Redo allocation size 654,720 33.3 535.8
IMU commits 712 0.0 0.6
IMU contention 1 0.0 0.0
IMU pool not allocated 3,388 0.2 2.8
IMU recursive-transaction flush 5 0.0 0.0
IMU undo allocation size 3,035,144 154.3 2,483.8
IMU- failed to get a private stra 3,388 0.2 2.8
SMON posted for undo segment shri 12 0.0 0.0
SQL*Net roundtrips to/from client 23,321 1.2 19.1
active txn count during cleanout 322 0.0 0.3
application wait time 79 0.0 0.1
background checkpoints completed 1 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 51,421 2.6 42.1
buffer is not pinned count 174,337 8.9 142.7
buffer is pinned count 50,933 2.6 41.7
bytes received via SQL*Net from c 2,725,918 138.6 2,230.7
bytes sent via SQL*Net to client 2,576,029 131.0 2,108.0
calls to get snapshot scn: kcmgss 116,448 5.9 95.3
calls to kcmgas 4,353 0.2 3.6
calls to kcmgcs 358 0.0 0.3
change write time 22 0.0 0.0
cleanout - number of ktugct calls 419 0.0 0.3
cleanouts only - consistent read 66 0.0 0.1
cluster key scan block gets 16,867 0.9 13.8
cluster key scans 9,001 0.5 7.4
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 63 0.0 0.1
commit batch/immediate requested 63 0.0 0.1
commit cleanout failures: callbac 42 0.0 0.0
commit cleanouts 7,347 0.4 6.0
commit cleanouts successfully com 7,305 0.4 6.0
commit immediate performed 63 0.0 0.1
commit immediate requested 63 0.0 0.1
commit txn count during cleanout 224 0.0 0.2
concurrency wait time 399 0.0 0.3
consistent changes 334 0.0 0.3
consistent gets 319,228 16.2 261.2
consistent gets - examination 120,857 6.2 98.9
consistent gets from cache 319,228 16.2 261.2
cursor authentications 928 0.1 0.8
data blocks consistent reads - un 334 0.0 0.3
db block changes 53,514 2.7 43.8
db block gets 45,926 2.3 37.6
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-32

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
db block gets direct 6 0.0 0.0
db block gets from cache 45,920 2.3 37.6
deferred (CURRENT) block cleanout 4,343 0.2 3.6
dirty buffers inspected 3 0.0 0.0
enqueue conversions 3,469 0.2 2.8
enqueue releases 171,287 8.7 140.2
enqueue requests 171,286 8.7 140.2
execute count 81,903 4.2 67.0
free buffer inspected 2,042 0.1 1.7
free buffer requested 4,152 0.2 3.4
heap block compress 26 0.0 0.0
hot buffers moved to head of LRU 2,424 0.1 2.0
immediate (CR) block cleanout app 66 0.0 0.1
immediate (CURRENT) block cleanou 1,169 0.1 1.0
index fast full scans (full) 24 0.0 0.0
index fetch by key 53,607 2.7 43.9
index scans kdiixs1 76,910 3.9 62.9
leaf node 90-10 splits 25 0.0 0.0
leaf node splits 127 0.0 0.1
lob reads 299 0.0 0.2
lob writes 265 0.0 0.2
lob writes unaligned 265 0.0 0.2
logons cumulative 208 0.0 0.2
messages received 5,731 0.3 4.7
messages sent 5,731 0.3 4.7
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 124,209 6.3 101.6
opened cursors cumulative 42,875 2.2 35.1
parse count (failures) 1 0.0 0.0
parse count (hard) 3,843 0.2 3.1
parse count (total) 30,669 1.6 25.1
parse time cpu 1,500 0.1 1.2
parse time elapsed 1,955 0.1 1.6
physical read IO requests 2,043 0.1 1.7
physical read bytes 20,725,760 1,053.8 16,960.5
physical read total IO requests 7,860 0.4 6.4
physical read total bytes 114,110,464 5,802.1 93,380.1
physical read total multi block r 163 0.0 0.1
physical reads 2,530 0.1 2.1
physical reads cache 2,524 0.1 2.1
physical reads cache prefetch 487 0.0 0.4
physical reads direct 6 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical write IO requests 3,745 0.2 3.1
physical write bytes 54,067,200 2,749.1 44,244.8
physical write total IO requests 22,112 1.1 18.1
physical write total bytes 341,971,968 17,388.1 279,846.1
physical write total multi block 2,744 0.1 2.3
physical writes 6,600 0.3 5.4
physical writes direct 12 0.0 0.0
physical writes direct (lob) 6 0.0 0.0
physical writes from cache 6,588 0.3 5.4
physical writes non checkpoint 3,981 0.2 3.3
process last non-idle time 17,544 0.9 14.4
recursive calls 591,275 30.1 483.9
recursive cpu usage 7,791 0.4 6.4
Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-32

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo blocks written 25,836 1.3 21.1
redo buffer allocation retries 1 0.0 0.0
redo entries 27,110 1.4 22.2
redo log space requests 3 0.0 0.0
redo log space wait time 57 0.0 0.1
redo size 12,324,244 626.7 10,085.3
redo synch time 3,512 0.2 2.9
redo synch writes 8,475 0.4 6.9
redo wastage 589,132 30.0 482.1
redo write time 4,543 0.2 3.7
redo writer latching time 2 0.0 0.0
redo writes 2,080 0.1 1.7
rollback changes - undo records a 106 0.0 0.1
rollbacks only - consistent read 325 0.0 0.3
rows fetched via callback 27,348 1.4 22.4
session connect time 6 0.0 0.0
session cursor cache hits 29,153 1.5 23.9
session logical reads 365,154 18.6 298.8
session pga memory 78,268,312 3,979.7 64,049.4
session pga memory max 96,324,768 4,897.8 78,825.5
session uga memory 262,001,112,732 13,321,864.7 ############
session uga memory max 116,009,500 5,898.7 94,934.1
shared hash latch upgrades - no w 65,452 3.3 53.6
sorts (memory) 18,654 1.0 15.3
sorts (rows) 183,882 9.4 150.5
sql area purged 41 0.0 0.0
summed dirty queue length 49 0.0 0.0
switch current to new buffer 246 0.0 0.2
table fetch by rowid 76,818 3.9 62.9
table fetch continued row 684 0.0 0.6
table scan blocks gotten 34,161 1.7 28.0
table scan rows gotten 2,017,936 102.6 1,651.3
table scans (long tables) 1 0.0 0.0
table scans (short tables) 3,180 0.2 2.6
total number of times SMON posted 18 0.0 0.0
transaction rollbacks 63 0.0 0.1
undo change vector size 3,993,928 203.1 3,268.4
user I/O wait time 4,256 0.2 3.5
user calls 25,832 1.3 21.1
user commits 1,157 0.1 1.0
user rollbacks 65 0.0 0.1
workarea executions - optimal 9,385 0.5 7.7
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 1 0.0 0.0
-------------------------------------------------------------

Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> Statistics with absolute values (should not be diffed)

Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 17 22
opened cursors current 98 199
session cursor cache count 168 2,568
-------------------------------------------------------------

Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 1 .18
-------------------------------------------------------------

OS Statistics DB/Inst: ORCL/orcl Snaps: 1-32
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic Total
------------------------- ----------------------
BUSY_TIME 63,332
IDLE_TIME 1,548,195
SYS_TIME 48,954
USER_TIME 14,378
VM_IN_BYTES 78,569,472
VM_OUT_BYTES 22,740,992
PHYSICAL_MEMORY_BYTES 536,399,872
NUM_CPUS 1
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 1-32
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
5,659 0 12.7 1.2 452 0 10 24.0
SYSAUX
1,057 0 16.6 1.1 1,217 0 0 0.0
PERFSTAT
22 0 30.0 1.0 1,381 0 0 0.0
UNDOTBS1
34 0 23.5 1.0 768 0 9 0.0
EXAMPLE
8 0 75.0 1.0 2 0 0 0.0
USERS
6 0 50.0 1.0 2 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: ORCL/orcl Snaps: 1-32
->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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
8 0 75.0 32 1.0 2 0 0

PERFSTAT C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERFSTAT.DBF
22 0 30.0 64 1.0 1,381 0 0

SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
1,057 0 16.6 ### 1.1 1,217 0 0

SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
5,659 0 12.7 ### 1.2 452 0 10 24.0

UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
34 0 23.5 32 1.0 768 0 9 0.0

USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
6 0 50.0 32 1.0 2 0 0

-------------------------------------------------------------
File Read Histogram Stats DB/Inst: ORCL/orcl Snaps: 1-32
->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
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
326 37 57 155 196 167

SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
497 48 35 100 177 98

USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
0 0 0 0 1 0

UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
2 1 0 0 4 0

EXAMPLE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
0 0 0 0 1 0

PERFSTAT C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERFSTAT.DBF
13 1 1 4 1 1

-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 1-32
-> 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 8982 99 366,134 2,521 6,588 0 0 15
-------------------------------------------------------------

Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> 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 17 2499 23208 184320 184320
E 0 14 215 23 2153 184320 2153
-------------------------------------------------------------

Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 32
-> 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 4 .1 0 3.4 28 345 121.1
D 8 .1 1 2.7 22 265 93.0
D 12 .2 1 2.5 20 245 86.0
D 16 .2 2 2.3 19 230 80.7
D 20 .3 2 2.2 18 212 74.4
D 24 .3 3 1.8 15 177 62.1
D 28 .4 3 1.7 14 158 55.4
D 32 .4 4 1.4 12 131 46.0
D 36 .5 4 1.3 10 118 41.4
D 40 .6 5 1.2 10 107 37.5
D 44 .6 5 1.1 9 104 36.5
D 48 .7 6 1.1 9 101 35.4
D 52 .7 6 1.1 9 100 35.1
D 56 .8 7 1.1 9 98 34.4
D 60 .8 7 1.1 9 95 33.3
D 64 .9 8 1.0 9 94 33.0
D 68 .9 8 1.0 9 94 33.0
D 72 1.0 9 1.0 8 89 31.2
D 76 1.1 9 1.0 8 88 30.9
D 80 1.1 10 1.0 8 87 30.5
-------------------------------------------------------------

Buffer wait Statistics DB/Inst: ORCL/orcl Snaps: 1-32
-> ordered by wait time desc, waits desc

Class Waits Total Wait Time (s) Avg Time (ms)
---------------------- ----------- ------------------- -------------
data block 6 0 20
undo header 9 0 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> 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 193 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 24.5 0.0 .0 .0 .0 3,276
E 16 4 29.5 0.0 .0 .0 .0 3,276
-------------------------------------------------------------

PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 1-32
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 9,075 9,075 0 0
64K 128K 27 27 0 0
512K 1024K 232 232 0 0
-------------------------------------------------------------

PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 32
-> 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 211.0 0.0 100.0 1
16 1.0 211.0 0.0 100.0 1
19 1.2 211.0 0.0 100.0 1
22 1.4 211.0 0.0 100.0 0
26 1.6 211.0 0.0 100.0 0
29 1.8 211.0 0.0 100.0 0
32 2.0 211.0 0.0 100.0 0
48 3.0 211.0 0.0 100.0 0
64 4.0 211.0 0.0 100.0 0
96 6.0 211.0 0.0 100.0 0
128 8.0 211.0 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 1-32
-> 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 -------- 24.8 12.4 .0 1.3 2.0 9 14 19
Other 24.1 1.3 2.0 9 10 19
SQL .6 .3 .1 .0 0 2 9
PL/SQL .2 .1 .0 .0 0 6 17
E -------- 29.7 16.1 .0 1.2 1.7 9 14 24
Other 28.6

null

Message was edited by:
user73673567

null