首先进入$ORACLE_HOME/rdbms/admin目录
sqlplus / as sysdba
@awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
857742363 HXY 1 HXY
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 857742363 1 HXY HXY hxy.up.com
Using 857742363 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
HXY HXY 165 12 Oct 2013 11:11 1
166 12 Oct 2013 12:00 1
167 12 Oct 2013 13:00 1
168 12 Oct 2013 14:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 167
Begin Snapshot Id specified: 167
Enter value for end_snap: 168
End Snapshot Id specified: 168
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_167_168.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_167_168.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
HXY 857742363 HXY 1 10.2.0.1.0 NO hxy.up.com
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 167 12-Oct-13 13:00:33 19 2.6
End Snap: 168 12-Oct-13 14:00:36 18 2.8
Elapsed: 60.05 (mins)
DB Time: 0.06 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 64M 64M Std Block Size: 8K
Shared Pool Size: 76M 76M Log Buffer: 2,124K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 377.69 4,791.66
Logical reads: 15.12 191.77
Block changes: 1.73 21.99
Physical reads: 0.03 0.38
Physical writes: 0.23 2.95
User calls: 0.09 1.19
Parses: 1.28 16.28
Hard parses: 0.13 1.67
Sorts: 0.82 10.36
Logons: 0.03 0.39
Executes: 2.76 34.99
Transactions: 0.08
% Blocks changed per Read: 11.47 Recursive Call %: 99.62
Rollback per transaction %: 0.00 Rows per Sort: 6.92
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.80 In-memory Sort %: 100.00
Library Hit %: 88.67 Soft Parse %: 89.73
Execute to Parse %: 53.48 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 61.90 % Non-Parse CPU: 72.61
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.55 95.03
% SQL with executions>1: 82.53 86.55
% Memory for SQL w/exec>1: 92.99 94.03
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
control file parallel write 1,197 9 7 221.4 System I/O
os thread startup 55 5 87 124.1 Concurrenc
CPU time 3 74.2
log file parallel write 368 2 6 59.2 System I/O
db file sequential read 99 0 3 8.1 User I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> Total time in database user-calls (DB Time): 3.8s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 6.0 157.4
DB CPU 2.8 74.2
parse time elapsed 2.1 53.9
hard parse elapsed time 1.4 35.3
PL/SQL execution elapsed time 1.3 34.4
hard parse (sharing criteria) elapsed time 0.1 2.4
PL/SQL compilation elapsed time 0.1 1.9
repeated bind elapsed time 0.0 .1
sequence load elapsed time 0.0 .0
hard parse (bind mismatch) elapsed time 0.0 .0
DB time 3.8 N/A
background elapsed time 26.4 N/A
background cpu time 10.1 N/A
-------------------------------------------------------------
Wait Class DB/Inst: HXY/HXY Snaps: 167-168
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
System I/O 3,243 .0 11 3 11.4
Concurrency 55 .0 5 87 0.2
User I/O 104 .0 0 3 0.4
Commit 65 .0 0 4 0.2
Other 28 .0 0 6 0.1
-------------------------------------------------------------
Wait Events DB/Inst: HXY/HXY Snaps: 167-168
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
control file parallel write 1,197 .0 9 7 4.2
os thread startup 55 .0 5 87 0.2
log file parallel write 368 .0 2 6 1.3
db file sequential read 99 .0 0 3 0.3
log file sync 65 .0 0 4 0.2
latch free 5 .0 0 19 0.0
LGWR wait for redo copy 22 .0 0 4 0.1
control file sequential read 1,678 .0 0 0 5.9
db file scattered read 5 .0 0 0 0.0
latch: redo allocation 1 .0 0 0 0.0
Streams AQ: qmn coordinator 1,685 39.2 3,524 2091 5.9
Streams AQ: qmn slave idle w 1,025 .0 3,524 3438 3.6
virtual circuit status 120 100.0 3,516 29297 0.4
jobq slave wait 1,113 99.6 3,254 2924 3.9
Streams AQ: waiting for time 1 100.0 683 682964 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by 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 1,197 .0 9 7 4.2
os thread startup 55 .0 5 87 0.2
log file parallel write 370 .0 2 6 1.3
db file sequential read 36 .0 0 4 0.1
events in waitclass Other 30 .0 0 3 0.1
control file sequential read 1,024 .0 0 0 3.6
db file scattered read 4 .0 0 0 0.0
buffer busy waits 1 .0 0 1 0.0
rdbms ipc message 11,927 97.2 37,048 3106 42.0
Streams AQ: qmn coordinator 1,685 39.2 3,524 2091 5.9
Streams AQ: qmn slave idle w 1,025 .0 3,524 3438 3.6
smon timer 13 92.3 3,521 270838 0.0
pmon timer 1,260 100.0 3,519 2793 4.4
Streams AQ: waiting for time 1 100.0 683 682964 0.0
-------------------------------------------------------------
Operating System Statistics DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total
-------------------------------- --------------------
BUSY_TIME 2,693
IDLE_TIME 299,507
NICE_TIME 0
SYS_TIME 1,485
USER_TIME 1,208
LOAD 0
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,828
NUM_CPUS 1
-------------------------------------------------------------
Service Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 3.8 2.8 2 23,678
HXY 0.0 0.0 0 0
HXYXDB 0.0 0.0 0 0
SYS$BACKGROUND 0.0 0.0 107 30,532
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: HXY/HXY Snaps: 167-168
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
2 0 0 0 0 0 0 0
SYS$BACKGROUND
102 32 55 476 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
0 0 534 0.0 5.9 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
0 0 194 0.0 4.5 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
0 0 719 0.0 3.2 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
0 0 120 0.0 2.1 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
0 0 110 0.0 1.8 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
0 0 1 0.1 1.7 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice
0 0 8 0.0 1.7 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
0 0 690 0.0 1.5 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
0 0 664 0.0 1.3 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
0 0 140 0.0 1.2 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
0 0 204 0.0 1.2 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
0 0 120 0.0 1.0 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
SQL ordered by Elapsed Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
-------------------------------------------------------------
SQL ordered by CPU Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
0 0 534 0.00 5.9 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
0 0 194 0.00 4.5 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
0 0 719 0.00 3.2 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
0 0 120 0.00 2.1 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
0 0 110 0.00 1.8 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
0 0 1 0.06 1.7 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice
0 0 8 0.01 1.7 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
0 0 664 0.00 1.3 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
0 0 140 0.00 1.2 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
0 0 204 0.00 1.2 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
0 0 120 0.00 1.0 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
SQL ordered by CPU Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
0 0 690 0.00 1.5 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 54,463
-> Captured SQL account for 52.6% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
3,230 690 4.7 5.9 0.03 0.06 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
2,852 140 20.4 5.2 0.05 0.05 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
2,596 140 18.5 4.8 0.03 0.03 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
2,277 8 284.6 4.2 0.06 0.07 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
2,157 719 3.0 4.0 0.12 0.12 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
1,824 534 3.4 3.3 0.23 0.23 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
1,802 263 6.9 3.3 0.02 0.02 6769wyy3yf66f
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
1,641 194 8.5 3.0 0.17 0.17 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
1,636 128 12.8 3.0 0.02 0.02 a73wbv1yu8x5c
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro
wid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
1,119 85 13.2 2.1 0.03 0.03 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
841 204 4.1 1.5 0.05 0.05 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
743 181 4.1 1.4 0.03 0.03 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
664 664 1.0 1.2 0.05 0.05 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
SQL ordered by Gets DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 54,463
-> Captured SQL account for 52.6% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Disk Reads: 108
-> Captured SQL account for 5.6% of Total
Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
2 181 0.0 1.9 0.03 0.03 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
1 22 0.0 0.9 0.00 0.00 0m78skf1mudnb
select audit$,properties from type_misc$ where obj#=:1
1 6 0.2 0.9 0.00 0.00 52kfqq6q4ntj5
select o.obj#, t.flags from tab$ t, obj$ o, user$ u where u.user# = :1 and u.us
er# = o.owner# and o.name = :2 and o.obj# = t.obj#
1 690 0.0 0.9 0.03 0.06 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
1 14 0.1 0.9 0.01 0.01 c0agatqzq2jzr
insert into "SYS"."ALERT_QT" (q_name, msgid, corrid, priority, state, delay, ex
piration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_
uid, enq_tid, retry_count, exception_qschema, exception_queue, recipient_key,
dequeue_msgid, user_data, sender_name, sender_address, sender_protocol, user
0 6 0.0 0.0 0.00 0.00 04xtrk7uyhknh
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f
rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an
d linkname is null and subname is null
0 0 N/A 0.0 0.00 0.00 0cc4xv33r5uhm
select inst_id,sid_knst,serial_knst, applynum_knstacr, applyname_knstacr,decode(
state_knstacr,0,'INITIALIZING', 1,'APPLYING',2,'SHUTTING DOWN CLEANLY',3,
'ABORTING',4,'IDLE'),total_applied_knstacr, total_waitdeps_knstacr,total_waitcom
mits_knstacr,total_admin_knstacr,total_assigned_knstacr,total_received_knstacr,
0 2 0.0 0.0 0.03 0.03 0cjngzmtm4yv1
select reason_id, object_id, subobject_id, internal_instance_number, time_sugges
ted, context, reason_argument_1, reason_argument_2, reason_argument_3, reason_ar
gument_4, reason_argument_5, action_argument_1, action_argument_2, action_argume
nt_3, action_argument_4, action_argument_5, sequence_id, metric_value, instance_
0 26 0.0 0.0 0.00 0.00 0fr8zhn4ymu3v
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp
e$ where obj# = :1 order by intcol# asc
0 18 0.0 0.0 0.02 0.02 130dvvr5s8bgn
select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#, block#, pctfre
e$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt
, empcnt, avgspc, chncnt, avgrln, length(bhiboundval), bhiboundval from tabpart$
where bo# = :1 order by part#
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: HXY/HXY Snaps: 167-168
-> Total Executions: 9,937
-> Captured SQL account for 58.9% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
719 55 0.1 0.00 0.00 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
690 925 1.3 0.00 0.00 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
664 664 1.0 0.00 0.00 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
534 534 1.0 0.00 0.00 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
263 638 2.4 0.00 0.00 6769wyy3yf66f
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
204 2,417 11.8 0.00 0.00 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
196 99 0.5 0.00 0.00 asvzxj61dc5vs
select timestamp, flags from fixed_obj$ where obj#=:1
194 263 1.4 0.00 0.00 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
181 181 1.0 0.00 0.00 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
141 23 0.2 0.00 0.00 2q93zsrvbdw48
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w
here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
140 1,158 8.3 0.00 0.00 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
140 1,149 8.2 0.00 0.00 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
139 0 0.0 0.00 0.00 6aq34nj2zb2n7
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where
obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
SQL ordered by Executions DB/Inst: HXY/HXY Snaps: 167-168
-> Total Executions: 9,937
-> Captured SQL account for 58.9% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
128 690 5.4 0.00 0.00 a73wbv1yu8x5c
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro
wid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
128 3 0.0 0.00 0.00 f8pavn1bvsj7t
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
120 1,429 11.9 0.00 0.00 772s25v1y0x8k
select shared_pool_size_for_estimate s, shared_pool_size_factor * 100 f
, estd_lc_load_time l, 0 from v$shared_pool_advice
120 2,400 20.0 0.00 0.00 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
120 2,400 20.0 0.00 0.00 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
110 110 1.0 0.00 0.00 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: HXY/HXY Snaps: 167-168
-> Total Parse Calls: 4,623
-> Captured SQL account for 39.8% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
211 534 4.56 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
211 0 4.56 53btfq0dt9bs9
insert into sys.col_usage$ values ( :objn, :coln, decode(bitand(:flag,1),0,0
,1), decode(bitand(:flag,2),0,0,1), decode(bitand(: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), :time)
196 196 4.24 asvzxj61dc5vs
select timestamp, flags from fixed_obj$ where obj#=:1
140 140 3.03 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
140 140 3.03 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
110 110 2.38 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
85 85 1.84 39m4sx9k63ba2
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 c6awqs517jpj0
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w
here obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 ga9j9xk5cy9s0
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
59 59 1.28 b1wc53ddd6h3p
select audit$,options from procedure$ where obj#=:1
-------------------------------------------------------------
SQL ordered by Sharable Memory DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Version Count DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 766 0.2 2.7
CPU used when call started 148 0.0 0.5
CR blocks created 1 0.0 0.0
DB time 337,465 93.7 1,188.3
DBWR checkpoint buffers written 837 0.2 3.0
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 97 0.0 0.3
DBWR undo block writes 162 0.0 0.6
IMU CR rollbacks 0 0.0 0.0
IMU Flushes 19 0.0 0.1
IMU Redo allocation size 107,160 29.7 377.3
IMU commits 265 0.1 0.9
IMU contention 0 0.0 0.0
IMU undo allocation size 1,115,464 309.6 3,927.7
SMON posted for undo segment shr 1 0.0 0.0
SQL*Net roundtrips to/from clien 0 0.0 0.0
active txn count during cleanout 16 0.0 0.1
background timeouts 11,590 3.2 40.8
buffer is not pinned count 31,677 8.8 111.5
buffer is pinned count 7,723 2.1 27.2
bytes received via SQL*Net from 0 0.0 0.0
bytes sent via SQL*Net to client 0 0.0 0.0
calls to get snapshot scn: kcmgs 14,821 4.1 52.2
calls to kcmgas 662 0.2 2.3
calls to kcmgcs 46 0.0 0.2
change write time 34 0.0 0.1
cleanout - number of ktugct call 18 0.0 0.1
cleanouts and rollbacks - consis 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 5,165 1.4 18.2
cluster key scans 775 0.2 2.7
commit batch/immediate performed 0 0.0 0.0
commit batch/immediate requested 0 0.0 0.0
commit cleanout failures: callba 3 0.0 0.0
commit cleanouts 1,440 0.4 5.1
commit cleanouts successfully co 1,437 0.4 5.1
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
commit txn count during cleanout 12 0.0 0.0
concurrency wait time 476 0.1 1.7
consistent changes 1 0.0 0.0
consistent gets 48,897 13.6 172.2
consistent gets - examination 18,341 5.1 64.6
consistent gets from cache 48,897 13.6 172.2
cursor authentications 131 0.0 0.5
data blocks consistent reads - u 1 0.0 0.0
db block changes 6,246 1.7 22.0
db block gets 5,566 1.5 19.6
db block gets from cache 5,566 1.5 19.6
deferred (CURRENT) block cleanou 932 0.3 3.3
enqueue conversions 725 0.2 2.6
enqueue releases 32,678 9.1 115.1
enqueue requests 32,678 9.1 115.1
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 9,937 2.8 35.0
frame signature mismatch 0 0.0 0.0
free buffer inspected 0 0.0 0.0
free buffer requested 173 0.1 0.6
heap block compress 21 0.0 0.1
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
hot buffers moved to head of LRU 0 0.0 0.0
immediate (CR) block cleanout ap 0 0.0 0.0
immediate (CURRENT) block cleano 194 0.1 0.7
index fast full scans (full) 8 0.0 0.0
index fetch by key 7,248 2.0 25.5
index scans kdiixs1 6,371 1.8 22.4
leaf node 90-10 splits 7 0.0 0.0
leaf node splits 10 0.0 0.0
lob reads 79 0.0 0.3
lob writes 10 0.0 0.0
lob writes unaligned 10 0.0 0.0
logons cumulative 112 0.0 0.4
messages received 1,040 0.3 3.7
messages sent 1,040 0.3 3.7
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 21,380 5.9 75.3
opened cursors cumulative 6,119 1.7 21.6
parse count (hard) 475 0.1 1.7
parse count (total) 4,623 1.3 16.3
parse time cpu 78 0.0 0.3
parse time elapsed 126 0.0 0.4
physical read IO requests 97 0.0 0.3
physical read bytes 884,736 245.6 3,115.3
physical read total IO requests 1,780 0.5 6.3
physical read total bytes 28,418,048 7,887.3 100,063.6
physical read total multi block 5 0.0 0.0
physical reads 108 0.0 0.4
physical reads cache 108 0.0 0.4
physical reads cache prefetch 11 0.0 0.0
physical reads direct 0 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 680 0.2 2.4
physical write bytes 6,856,704 1,903.0 24,143.3
physical write total IO requests 4,641 1.3 16.3
physical write total bytes 67,203,072 18,651.9 236,630.5
physical write total multi block 410 0.1 1.4
physical writes 837 0.2 3.0
physical writes direct 0 0.0 0.0
physical writes from cache 837 0.2 3.0
physical writes non checkpoint 130 0.0 0.5
pinned buffers inspected 0 0.0 0.0
prefetch warmup blocks aged out 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 87,814 24.4 309.2
recursive cpu usage 386 0.1 1.4
redo blocks read for recovery 0 0.0 0.0
redo blocks written 2,952 0.8 10.4
redo entries 2,872 0.8 10.1
redo size 1,360,832 377.7 4,791.7
redo synch time 29 0.0 0.1
redo synch writes 985 0.3 3.5
redo wastage 94,148 26.1 331.5
redo write time 237 0.1 0.8
redo writer latching time 7 0.0 0.0
redo writes 370 0.1 1.3
rollback changes - undo records 0 0.0 0.0
rollbacks only - consistent read 1 0.0 0.0
rows fetched via callback 4,478 1.2 15.8
session cursor cache hits 3,811 1.1 13.4
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
session logical reads 54,463 15.1 191.8
session pga memory 80,028,480 22,211.5 281,790.4
session pga memory max 85,205,824 23,648.4 300,020.5
session uga memory 244,816,092,696 67,947,413.2 #############
session uga memory max 73,911,912 20,513.9 260,253.2
shared hash latch upgrades - no 6,488 1.8 22.9
sorts (memory) 2,941 0.8 10.4
sorts (rows) 20,362 5.7 71.7
sql area purged 0 0.0 0.0
switch current to new buffer 0 0.0 0.0
table fetch by rowid 13,136 3.7 46.3
table fetch continued row 227 0.1 0.8
table scan blocks gotten 4,541 1.3 16.0
table scan rows gotten 122,889 34.1 432.7
table scans (long tables) 1 0.0 0.0
table scans (short tables) 1,266 0.4 4.5
total number of times SMON poste 1 0.0 0.0
transaction rollbacks 0 0.0 0.0
undo change vector size 446,456 123.9 1,572.0
user I/O wait time 31 0.0 0.1
user calls 337 0.1 1.2
user commits 284 0.1 1.0
workarea executions - optimal 1,918 0.5 6.8
-------------------------------------------------------------
Instance Activity Stats - Absolute Values DB/Inst: HXY/HXY Snaps: 167-168
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
session cursor cache count 2,295 3,203
opened cursors current 50 50
workarea memory allocated 127 127
logons current 19 18
-------------------------------------------------------------
Instance Activity Stats - Thread Activity DB/Inst: HXY/HXY Snaps: 167-168
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: HXY/HXY Snaps: 167-168
-> 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
43 0 2.1 1.0 381 0 0 0.0
UNDOTBS1
0 0 0.0 .0 198 0 0 0.0
SYSTEM
55 0 4.2 1.2 101 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSAUX /u01/app/oracle/oradata/HXY/sysaux01.dbf
43 0 2.1 1.0 381 0 0 0.0
SYSTEM /u01/app/oracle/oradata/HXY/system01.dbf
55 0 4.2 1.2 101 0 0 0.0
UNDOTBS1 /u01/app/oracle/oradata/HXY/undotbs01.dbf
0 0 N/A N/A 198 0 0 0.0
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- -------------- ------------ ----------- ---- ---- ----------
D 7,918 100 54,363 109 837 0 0 1
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: HXY/HXY Snaps: 167-168
-> 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 34 221 640 996 184320 996 N/A
E 0 34 167 372 655 184320 655 N/A
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: HXY/HXY Snap: 168
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Est
Phys
Size for Size Buffers for Read Estimated
P Est (M) Factor Estimate Factor Physical Reads
--- -------- ------ ---------------- ------ ------------------
D 4 .1 495 3.1 28,889
D 8 .1 990 2.4 22,125
D 12 .2 1,485 2.0 18,221
D 16 .3 1,980 1.7 16,140
D 20 .3 2,475 1.7 15,499
D 24 .4 2,970 1.5 13,436
D 28 .4 3,465 1.4 12,621
D 32 .5 3,960 1.3 11,640
D 36 .6 4,455 1.2 11,099
D 40 .6 4,950 1.2 10,760
D 44 .7 5,445 1.1 10,586
D 48 .8 5,940 1.1 10,073
D 52 .8 6,435 1.1 9,798
D 56 .9 6,930 1.0 9,486
D 60 .9 7,425 1.0 9,294
D 64 1.0 7,920 1.0 9,257
D 68 1.1 8,415 1.0 9,257
D 72 1.1 8,910 1.0 9,257
D 76 1.2 9,405 1.0 9,257
D 80 1.3 9,900 1.0 9,037
-------------------------------------------------------------
PGA Aggr Summary DB/Inst: HXY/HXY Snaps: 167-168
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ------------------ --------------------------
100.0 21 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 Aggr Target Stats DB/Inst: HXY/HXY Snaps: 167-168
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> 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 %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 92.2 0.1 .1 100.0 .0 3,276
E 16 4 90.8 0.1 .1 100.0 .0 3,276
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: HXY/HXY Snaps: 167-168
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
2K 4K 1,895 1,895 0 0
64K 128K 3 3 0 0
512K 1024K 17 17 0 0
1M 2M 3 3 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: HXY/HXY Snap: 168
-> 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 107.7 0.0 100.0 1
16 1.0 107.7 0.0 100.0 1
19 1.2 107.7 0.0 100.0 1
22 1.4 107.7 0.0 100.0 1
26 1.6 107.7 0.0 100.0 1
29 1.8 107.7 0.0 100.0 1
32 2.0 107.7 0.0 100.0 1
48 3.0 107.7 0.0 100.0 1
64 4.0 107.7 0.0 100.0 0
96 6.0 107.7 0.0 100.0 0
128 8.0 107.7 0.0 100.0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: HXY/HXY Snap: 168
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
68 .9 8 887 194 1.0 42 1.1 50,392
76 1.0 15 1,523 196 1.0 40 1.0 51,301
84 1.1 22 2,423 197 1.0 39 1.0 52,090
92 1.2 29 3,109 198 1.0 38 1.0 52,814
100 1.3 36 3,525 200 1.0 36 .9 53,597
108 1.4 43 4,132 200 1.0 36 .9 53,857
116 1.5 50 5,016 200 1.0 36 .9 53,975
124 1.6 57 5,581 200 1.0 36 .9 53,997
132 1.7 64 6,144 200 1.0 36 .9 54,003
140 1.8 71 7,153 200 1.0 36 .9 54,009
148 1.9 78 8,059 200 1.0 36 .9 54,011
156 2.1 85 9,052 200 1.0 36 .9 54,011
-------------------------------------------------------------
SGA Target Advisory DB/Inst: HXY/HXY Snap: 168
SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
---------- ---------- ------------ ----------------
120 0.8 105 11,071
160 1.0 86 9,240
200 1.3 80 9,019
240 1.5 80 9,019
280 1.8 80 9,019
320 2.0 80 9,019
-------------------------------------------------------------
Streams Pool Advisory DB/Inst: HXY/HXY Snap: 168
Size for Size Est Spill Est Spill Est Unspill Est Unspill
Est (MB) Factor Count Time (s) Count Time (s)
---------- --------- ----------- ----------- ----------- -----------
4 0.5 0 0 0 0
8 1.0 0 0 0 0
12 1.5 0 0 0 0
16 2.0 0 0 0 0
20 2.5 0 0 0 0
24 3.0 0 0 0 0
28 3.5 0 0 0 0
32 4.0 0 0 0 0
36 4.5 0 0 0 0
40 5.0 0 0 0 0
44 5.5 0 0 0 0
48 6.0 0 0 0 0
52 6.5 0 0 0 0
56 7.0 0 0 0 0
60 7.5 0 0 0 0
64 8.0 0 0 0 0
68 8.5 0 0 0 0
72 9.0 0 0 0 0
76 9.5 0 0 0 0
80 10.0 0 0 0 0
-------------------------------------------------------------
Java Pool Advisory DB/Inst: HXY/HXY Snap: 168
No data exists for this section of the report.
-------------------------------------------------------------
Buffer Wait Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
------------------ ----------- ------------------- --------------
undo header 1 0 0
-------------------------------------------------------------
Enqueue Activity DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Undo Segment Summary DB/Inst: HXY/HXY Snaps: 167-168
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
1 .0 534 0 1 15/15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: HXY/HXY Snaps: 167-168
-> Most recent 35 Undostat rows, ordered by Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
12-Oct 13:50 5 43 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:40 2 61 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:30 3 45 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:20 13 327 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:10 3 58 0 1 15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "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 Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AWR Alerted Metric Eleme 12,742 0.0 N/A 0 0 N/A
Consistent RBA 370 0.0 N/A 0 0 N/A
FAL request queue 72 0.0 N/A 0 0 N/A
FAL subheap alocation 72 0.0 N/A 0 0 N/A
FOB s.o list latch 44 0.0 N/A 0 0 N/A
In memory undo latch 3,665 0.0 N/A 0 1,149 0.0
JS mem alloc latch 4 0.0 N/A 0 0 N/A
JS queue access latch 4 0.0 N/A 0 0 N/A
JS queue state obj latch 26,026 0.0 N/A 0 0 N/A
JS slv state obj latch 171 0.0 N/A 0 0 N/A
KGX 0 N/A N/A 0 12,215 0.0
KMG MMAN ready and start 1,200 0.0 N/A 0 0 N/A
KTF sga latch 7 0.0 N/A 0 1,145 0.0
KWQMN job cache list lat 17 0.0 N/A 0 0 N/A
KWQP Prop Status 405 0.0 N/A 0 0 N/A
MQL Tracking Latch 0 N/A N/A 0 72 0.0
Memory Management Latch 0 N/A N/A 0 1,200 0.0
Memory Queue 403 0.0 N/A 0 0 N/A
OS process 498 0.0 N/A 0 0 N/A
OS process allocation 1,531 0.0 N/A 0 0 N/A
OS process: request allo 111 0.0 N/A 0 0 N/A
PL/SQL warning settings 242 0.0 N/A 0 0 N/A
SGA IO buffer pool latch 1 0.0 N/A 0 1 0.0
SQL memory manager latch 460 0.0 N/A 0 1,197 0.0
SQL memory manager worka 111,941 0.0 N/A 0 0 N/A
Shared B-Tree 2,577 0.0 N/A 0 0 N/A
active checkpoint queue 1,880 0.0 N/A 0 0 N/A
active service list 7,693 0.0 N/A 0 1,260 0.0
archive control 68 0.0 N/A 0 0 N/A
archive process latch 1,269 0.0 N/A 0 0 N/A
bq:time manger info latc 133 0.0 N/A 0 0 N/A
bufq statistics 406 0.0 N/A 0 0 N/A
cache buffer handles 20 0.0 N/A 0 0 N/A
cache buffers chains 109,555 0.0 N/A 0 480 0.0
cache buffers lru chain 2,720 0.0 N/A 0 1,066 0.0
cache table scan latch 0 N/A N/A 0 5 0.0
cas latch 2,336 0.0 N/A 0 0 N/A
channel handle pool latc 111 0.0 N/A 0 0 N/A
channel operations paren 17,106 0.0 N/A 0 0 N/A
checkpoint queue latch 26,967 0.0 N/A 0 577 0.0
client/application info 1,445 0.0 N/A 0 0 N/A
commit callback allocati 28 0.0 N/A 0 0 N/A
compile environment latc 224 0.0 N/A 0 0 N/A
dml lock allocation 2,284 0.0 N/A 0 0 N/A
dummy allocation 225 0.0 N/A 0 0 N/A
enqueue hash chains 66,097 0.0 N/A 0 120 0.0
enqueues 61,835 0.0 N/A 0 0 N/A
event group latch 55 0.0 N/A 0 0 N/A
file cache latch 79 0.0 N/A 0 0 N/A
hash table column usage 426 0.0 N/A 0 5,173 0.0
hash table modification 63 0.0 N/A 0 0 N/A
job workq parent latch 0 N/A N/A 0 114 0.0
job_queue_processes para 117 0.0 N/A 0 0 N/A
kks stats 1,649 0.0 N/A 0 0 N/A
ksuosstats global area 242 0.0 N/A 0 0 N/A
ktm global data 15 0.0 N/A 0 0 N/A
kwqbsn:qsga 2,037 0.0 N/A 0 0 N/A
lgwr LWN SCN 1,424 0.0 N/A 0 0 N/A
library cache 65,439 0.0 N/A 0 203 0.0
library cache load lock 2,480 0.0 N/A 0 0 N/A
Latch Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "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 Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache lock 24,358 0.0 N/A 0 0 N/A
library cache lock alloc 1,296 0.0 N/A 0 0 N/A
library cache pin 37,828 0.0 N/A 0 0 N/A
library cache pin alloca 591 0.0 N/A 0 0 N/A
list of block allocation 133 0.0 N/A 0 0 N/A
logminer context allocat 1 0.0 N/A 0 0 N/A
messages 25,685 0.0 N/A 0 0 N/A
mostly latch-free SCN 2,089 0.0 N/A 0 0 N/A
multiblock read objects 38 0.0 N/A 0 0 N/A
ncodef allocation latch 60 0.0 N/A 0 0 N/A
object queue header heap 2 0.0 N/A 0 0 N/A
object queue header oper 4,571 0.0 N/A 0 0 N/A
object stats modificatio 1 0.0 N/A 0 0 N/A
parallel query alloc buf 480 0.0 N/A 0 0 N/A
parameter table allocati 115 0.0 N/A 0 0 N/A
post/wait queue 154 0.0 N/A 0 68 0.0
process allocation 111 0.0 N/A 0 55 0.0
process group creation 111 0.0 N/A 0 0 N/A
qmn task queue latch 3,707 0.1 1.0 0 0 N/A
redo allocation 4,093 0.0 1.0 0 2,910 0.1
redo copy 1 0.0 N/A 0 2,910 0.8
redo on-disk SCN 4,519 0.0 N/A 0 0 N/A
redo writing 5,504 0.0 N/A 0 0 N/A
resmgr group change latc 293 0.0 N/A 0 0 N/A
resmgr:actses active lis 222 0.0 N/A 0 0 N/A
resmgr:actses change gro 57 0.0 N/A 0 0 N/A
resmgr:free threads list 221 0.0 N/A 0 0 N/A
resmgr:schema config 1 0.0 N/A 0 0 N/A
rm cas latch 563 0.0 N/A 0 0 N/A
row cache objects 46,163 0.0 N/A 0 0 N/A
rules engine aggregate s 14 0.0 N/A 0 0 N/A
rules engine rule set st 128 0.0 N/A 0 0 N/A
sequence cache 49 0.0 N/A 0 0 N/A
session allocation 99,343 0.0 N/A 0 0 N/A
session idle bit 787 0.0 N/A 0 0 N/A
session state list latch 236 0.0 N/A 0 0 N/A
session switching 60 0.0 N/A 0 0 N/A
session timer 1,260 0.0 N/A 0 0 N/A
shared pool 47,741 0.0 N/A 0 0 N/A
simulator hash latch 4,020 0.0 N/A 0 0 N/A
simulator lru latch 3,903 0.0 N/A 0 11 0.0
slave class 2 0.0 N/A 0 0 N/A
slave class create 8 12.5 1.0 0 0 N/A
sort extent pool 91 0.0 N/A 0 0 N/A
state object free list 2 0.0 N/A 0 0 N/A
statistics aggregation 140 0.0 N/A 0 0 N/A
threshold alerts latch 253 0.0 N/A 0 0 N/A
transaction allocation 133 0.0 N/A 0 0 N/A
transaction branch alloc 60 0.0 N/A 0 0 N/A
undo global data 2,981 0.0 N/A 0 0 N/A
user lock 114 0.0 N/A 0 0 N/A
-------------------------------------------------------------
Latch Sleep Breakdown DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by misses desc
Latch Name
----------------------------------------
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
-------------- ----------- ----------- ---------- -------- -------- --------
qmn task queue latch
3,707 4 4 0 0 0 0
redo allocation
4,093 1 1 0 0 0 0
slave class create
8 1 1 0 0 0 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: HXY/HXY Snaps: 167-168
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
event range base latch No latch 0 4 4
redo allocation kcrfw_redo_gen: redo alloc 0 1 0
slave class create ksvcreate 0 1 0
-------------------------------------------------------------
Parent Latch Statistics DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Child Latch Statistics DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Segments by Logical Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Logical Reads: 54,463
-> Captured Segments account for 71.3% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM SMON_SCN_TIME TABLE 3,872 7.11
SYS SYSTEM JOB$ TABLE 2,448 4.49
SYS SYSTEM I_SYSAUTH1 INDEX 2,272 4.17
SYS SYSTEM I_CCOL1 INDEX 2,256 4.14
SYS SYSTEM SYS_C00650 INDEX 2,064 3.79
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Physical Reads: 108
-> Captured Segments account for 64.8% of Total
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM TAB$ TABLE 40 37.04
SYS SYSAUX WRH$_SQL_BIND_METADA TABLE 7 6.48
SYS SYSAUX WRH$_SYSMETRIC_SUMMA INDEX 4 3.70
SYS SYSAUX WRH$_ROWCACHE_SUMMAR 742363_147 INDEX 3 2.78
SYS SYSAUX WRH$_SYSMETRIC_SUMMA TABLE 3 2.78
-------------------------------------------------------------
Segments by Row Lock Waits DB/Inst: HXY/HXY Snaps: 167-168
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot
Row
Tablespace Subobject Obj. Lock % of
Owner Name Object Name Name Type Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM SMON_SCN_TIME TABLE 1 100.00
-------------------------------------------------------------
Segments by ITL Waits DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Segments by Buffer Busy Waits DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: HXY/HXY Snaps: 167-168
-> "Pct Misses" should be very low (< 2% in most cases)
-> "Final Usage" is the number of cache entries being used
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 66 0.0 0 N/A 2 1
dc_global_oids 192 0.0 0 N/A 0 32
dc_histogram_data 1,898 0.0 0 N/A 0 768
dc_histogram_defs 3,714 0.0 0 N/A 0 3,114
dc_object_grants 4 0.0 0 N/A 0 2
dc_object_ids 3,083 0.1 0 N/A 0 820
dc_objects 1,735 0.5 0 N/A 5 786
dc_profiles 59 0.0 0 N/A 0 1
dc_rollback_segments 646 0.0 0 N/A 0 22
dc_segments 854 0.0 0 N/A 1 532
dc_sequences 5 0.0 0 N/A 5 5
dc_tablespaces 1,676 0.0 0 N/A 0 6
dc_usernames 446 0.0 0 N/A 0 10
dc_users 2,370 0.0 0 N/A 0 12
outstanding_alerts 100 7.0 0 N/A 14 14
-------------------------------------------------------------
Library Cache Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 603 0.0 750 4.3 32 0
CLUSTER 33 0.0 58 6.9 4 0
INDEX 20 0.0 22 90.9 20 0
SQL AREA 1,573 0.1 12,855 6.6 461 0
TABLE/PROCEDURE 1,805 0.0 4,105 27.6 521 0
TRIGGER 10 0.0 164 1.2 2 0
-------------------------------------------------------------
Process Memory Summary DB/Inst: HXY/HXY Snaps: 167-168
-> 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
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist
Avg Std Dev Max Max
Alloc Used Alloc Alloc Alloc Alloc Num Num
Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B Other 83.6 N/A 4.0 7.5 22 37 21 21
Freeable 7.4 .0 .8 .3 1 N/A 9 9
SQL .9 .5 .1 .1 0 1 12 9
PL/SQL .2 .1 .0 .0 0 0 19 19
E Other 82.5 N/A 4.1 7.7 22 37 20 20
Freeable 7.3 .0 .9 .4 2 N/A 8 8
SQL .9 .5 .1 .1 0 1 11 9
PL/SQL .1 .1 .0 .0 0 0 18 18
-------------------------------------------------------------
SGA Memory Summary DB/Inst: HXY/HXY Snaps: 167-168
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ ------------------- -------------------
Database Buffers 67,108,864
Fixed Size 2,019,320
Redo Buffers 2,174,976
Variable Size 96,469,000
-------------------
sum 167,772,160
-------------------------------------------------------------
SGA breakdown difference DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by Pool, Name
-> N/A 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 free memory 4.0 4.0 0.00
large PX msg pool .2 .2 0.00
large free memory 3.8 3.8 0.00
shared ASH buffers 2.0 2.0 0.00
shared CCursor .9 .9 5.70
shared FileOpenBlock 1.4 1.4 0.00
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGLS heap .9 .8 -4.80
shared KQR L PO .8 .8 1.03
shared KQR M PO 2.2 2.2 0.04
shared KSFD SGA I/O b 3.8 3.8 0.00
shared KSXR pending messages que .8 .8 0.00
shared KSXR receive buffers 1.0 1.0 0.00
shared KTI-UNDO 1.2 1.2 0.00
shared event statistics per sess 1.5 1.5 0.00
shared free memory 3.4 3.8 11.69
shared kglsim hash table bkts 4.0 4.0 0.00
shared kglsim object batch .7 .8 13.57
shared kks stbkt 1.5 1.5 0.00
shared library cache 5.8 5.9 2.19
shared message pool freequeue .7 .7 0.00
shared private strands 2.3 2.3 0.00
shared returns from metrics req .7 .7 0.00
shared row cache 7.1 7.1 0.00
shared sessions .8 .8 0.00
shared sql area 3.2 3.0 -4.16
stream KGH: NO ACCESS 4.0 4.0 0.00
stream free memory 8.0 8.0 0.00
buffer_cache 64.0 64.0 0.00
fixed_sga 1.9 1.9 0.00
log_buffer 2.1 2.1 0.00
-------------------------------------------------------------
Streams CPU/IO Usage DB/Inst: HXY/HXY Snaps: 167-168
-> Streams processes ordered by CPU usage
-> CPU and I/O Time in micro seconds
Session Type CPU Time User I/O Time Sys I/O Time
------------------------- -------------- -------------- --------------
QMON Slaves 124,609 0 0
QMON Coordinator 116,713 0 0
-------------------------------------------------------------
Streams Capture DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Streams Apply DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Buffered Queues DB/Inst: HXY/HXY Snaps: 167-168
-> The Spill Rate should be very close to zero
-> The Diff in Percentage Spilled should be very close to zero or negative
Incoming Outgoing Spilled Diff
Queue Schema and Name per second per second per second Pct Spilled
--------------------- ----------- ----------- ----------- ------------
SYS.KUPC$C_1_20130628 0 0 0 0
-------------------------------------------------------------
Buffered Subscribers DB/Inst: HXY/HXY Snaps: 167-168
-> All Subscribers should have a zero spill rate
Incoming Outgoing Spilled
Subscriber Name per second per second per second
-------------------- ----------- ----------- -----------
MCP 0 0 0
-------------------------------------------------------------
Rule Set DB/Inst: HXY/HXY Snaps: 167-168
-> Rule Sets ordered by Evaluations
Fast SQL CPU Elapsed
Ruleset Name Evals Evals Execs Time Time
------------------------- -------- -------- -------- -------- --------
SYS.ALERT_QUE_R 14 0 0 4 4
-------------------------------------------------------------
Resource Limit Stats DB/Inst: HXY/HXY Snap: 168
No data exists for this section of the report.
-------------------------------------------------------------
init.ora Parameters DB/Inst: HXY/HXY Snaps: 167-168
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/app/oracle/admin/HXY/adump
background_dump_dest /u01/app/oracle/admin/HXY/bdump
compatible 10.2.0.1.0
control_files /u01/app/oracle/oradata/HXY/contr
core_dump_dest /u01/app/oracle/admin/HXY/cdump
db_block_size 8192
db_domain
db_file_multiblock_read_count 16
db_name HXY
db_recovery_file_dest /u01/app/oracle/flash_recovery_ar
db_recovery_file_dest_size 2147483648
dispatchers (PROTOCOL=TCP) (SERVICE=HXYXDB)
job_queue_processes 10
log_archive_dest_1 location=/u01/app/oracle/oradata/
open_cursors 300
pga_aggregate_target 16777216
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 167772160
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/app/oracle/admin/HXY/udump
-------------------------------------------------------------
End of Report
Report written to awrrpt_1_167_168.txt
SQL>
sqlplus / as sysdba
@awrrpt
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
857742363 HXY 1 HXY
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 857742363 1 HXY HXY hxy.up.com
Using 857742363 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
HXY HXY 165 12 Oct 2013 11:11 1
166 12 Oct 2013 12:00 1
167 12 Oct 2013 13:00 1
168 12 Oct 2013 14:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 167
Begin Snapshot Id specified: 167
Enter value for end_snap: 168
End Snapshot Id specified: 168
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_167_168.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrrpt_1_167_168.txt
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
HXY 857742363 HXY 1 10.2.0.1.0 NO hxy.up.com
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 167 12-Oct-13 13:00:33 19 2.6
End Snap: 168 12-Oct-13 14:00:36 18 2.8
Elapsed: 60.05 (mins)
DB Time: 0.06 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 64M 64M Std Block Size: 8K
Shared Pool Size: 76M 76M Log Buffer: 2,124K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 377.69 4,791.66
Logical reads: 15.12 191.77
Block changes: 1.73 21.99
Physical reads: 0.03 0.38
Physical writes: 0.23 2.95
User calls: 0.09 1.19
Parses: 1.28 16.28
Hard parses: 0.13 1.67
Sorts: 0.82 10.36
Logons: 0.03 0.39
Executes: 2.76 34.99
Transactions: 0.08
% Blocks changed per Read: 11.47 Recursive Call %: 99.62
Rollback per transaction %: 0.00 Rows per Sort: 6.92
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.80 In-memory Sort %: 100.00
Library Hit %: 88.67 Soft Parse %: 89.73
Execute to Parse %: 53.48 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 61.90 % Non-Parse CPU: 72.61
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.55 95.03
% SQL with executions>1: 82.53 86.55
% Memory for SQL w/exec>1: 92.99 94.03
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
control file parallel write 1,197 9 7 221.4 System I/O
os thread startup 55 5 87 124.1 Concurrenc
CPU time 3 74.2
log file parallel write 368 2 6 59.2 System I/O
db file sequential read 99 0 3 8.1 User I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> Total time in database user-calls (DB Time): 3.8s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 6.0 157.4
DB CPU 2.8 74.2
parse time elapsed 2.1 53.9
hard parse elapsed time 1.4 35.3
PL/SQL execution elapsed time 1.3 34.4
hard parse (sharing criteria) elapsed time 0.1 2.4
PL/SQL compilation elapsed time 0.1 1.9
repeated bind elapsed time 0.0 .1
sequence load elapsed time 0.0 .0
hard parse (bind mismatch) elapsed time 0.0 .0
DB time 3.8 N/A
background elapsed time 26.4 N/A
background cpu time 10.1 N/A
-------------------------------------------------------------
Wait Class DB/Inst: HXY/HXY Snaps: 167-168
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
Avg
%Time Total Wait wait Waits
Wait Class Waits -outs Time (s) (ms) /txn
-------------------- ---------------- ------ ---------------- ------- ---------
System I/O 3,243 .0 11 3 11.4
Concurrency 55 .0 5 87 0.2
User I/O 104 .0 0 3 0.4
Commit 65 .0 0 4 0.2
Other 28 .0 0 6 0.1
-------------------------------------------------------------
Wait Events DB/Inst: HXY/HXY Snaps: 167-168
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
---------------------------- -------------- ------ ----------- ------- ---------
control file parallel write 1,197 .0 9 7 4.2
os thread startup 55 .0 5 87 0.2
log file parallel write 368 .0 2 6 1.3
db file sequential read 99 .0 0 3 0.3
log file sync 65 .0 0 4 0.2
latch free 5 .0 0 19 0.0
LGWR wait for redo copy 22 .0 0 4 0.1
control file sequential read 1,678 .0 0 0 5.9
db file scattered read 5 .0 0 0 0.0
latch: redo allocation 1 .0 0 0 0.0
Streams AQ: qmn coordinator 1,685 39.2 3,524 2091 5.9
Streams AQ: qmn slave idle w 1,025 .0 3,524 3438 3.6
virtual circuit status 120 100.0 3,516 29297 0.4
jobq slave wait 1,113 99.6 3,254 2924 3.9
Streams AQ: waiting for time 1 100.0 683 682964 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by 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 1,197 .0 9 7 4.2
os thread startup 55 .0 5 87 0.2
log file parallel write 370 .0 2 6 1.3
db file sequential read 36 .0 0 4 0.1
events in waitclass Other 30 .0 0 3 0.1
control file sequential read 1,024 .0 0 0 3.6
db file scattered read 4 .0 0 0 0.0
buffer busy waits 1 .0 0 1 0.0
rdbms ipc message 11,927 97.2 37,048 3106 42.0
Streams AQ: qmn coordinator 1,685 39.2 3,524 2091 5.9
Streams AQ: qmn slave idle w 1,025 .0 3,524 3438 3.6
smon timer 13 92.3 3,521 270838 0.0
pmon timer 1,260 100.0 3,519 2793 4.4
Streams AQ: waiting for time 1 100.0 683 682964 0.0
-------------------------------------------------------------
Operating System Statistics DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total
-------------------------------- --------------------
BUSY_TIME 2,693
IDLE_TIME 299,507
NICE_TIME 0
SYS_TIME 1,485
USER_TIME 1,208
LOAD 0
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,828
NUM_CPUS 1
-------------------------------------------------------------
Service Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads Reads
-------------------------------- ------------ ------------ ---------- ----------
SYS$USERS 3.8 2.8 2 23,678
HXY 0.0 0.0 0 0
HXYXDB 0.0 0.0 0 0
SYS$BACKGROUND 0.0 0.0 107 30,532
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: HXY/HXY Snaps: 167-168
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in centisecond (100th of a second)
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
SYS$USERS
2 0 0 0 0 0 0 0
SYS$BACKGROUND
102 32 55 476 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
0 0 534 0.0 5.9 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
0 0 194 0.0 4.5 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
0 0 719 0.0 3.2 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
0 0 120 0.0 2.1 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
0 0 110 0.0 1.8 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
0 0 1 0.1 1.7 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice
0 0 8 0.0 1.7 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
0 0 690 0.0 1.5 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
0 0 664 0.0 1.3 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
0 0 140 0.0 1.2 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
0 0 204 0.0 1.2 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
0 0 120 0.0 1.0 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
SQL ordered by Elapsed Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
-------------------------------------------------------------
SQL ordered by CPU Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
0 0 534 0.00 5.9 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
0 0 194 0.00 4.5 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
0 0 719 0.00 3.2 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
0 0 120 0.00 2.1 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
0 0 110 0.00 1.8 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
0 0 1 0.06 1.7 bunssq950snhf
insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZ
E, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbi
d, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_R
EADS from v$sga_target_advice
0 0 8 0.01 1.7 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
0 0 664 0.00 1.3 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
0 0 140 0.00 1.2 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
0 0 204 0.00 1.2 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
0 0 120 0.00 1.0 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
SQL ordered by CPU Time DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ----------- ------- -------------
0 0 690 0.00 1.5 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 54,463
-> Captured SQL account for 52.6% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
3,230 690 4.7 5.9 0.03 0.06 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
2,852 140 20.4 5.2 0.05 0.05 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
2,596 140 18.5 4.8 0.03 0.03 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
2,277 8 284.6 4.2 0.06 0.07 g337099aatnuj
update smon_scn_time set orig_thread=0, time_mp=:1, time_dp=:2, scn=:3, scn_wrp
=:4, scn_bas=:5, num_mappings=:6, tim_scn_map=:7 where thread=0 and scn = (sel
ect min(scn) from smon_scn_time where thread=0)
2,157 719 3.0 4.0 0.12 0.12 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
1,824 534 3.4 3.3 0.23 0.23 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
1,802 263 6.9 3.3 0.02 0.02 6769wyy3yf66f
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
1,641 194 8.5 3.0 0.17 0.17 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
1,636 128 12.8 3.0 0.02 0.02 a73wbv1yu8x5c
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro
wid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
1,119 85 13.2 2.1 0.03 0.03 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
841 204 4.1 1.5 0.05 0.05 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
743 181 4.1 1.4 0.03 0.03 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
664 664 1.0 1.2 0.05 0.05 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
SQL ordered by Gets DB/Inst: HXY/HXY Snaps: 167-168
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 54,463
-> Captured SQL account for 52.6% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Disk Reads: 108
-> Captured SQL account for 5.6% of Total
Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
2 181 0.0 1.9 0.03 0.03 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
1 22 0.0 0.9 0.00 0.00 0m78skf1mudnb
select audit$,properties from type_misc$ where obj#=:1
1 6 0.2 0.9 0.00 0.00 52kfqq6q4ntj5
select o.obj#, t.flags from tab$ t, obj$ o, user$ u where u.user# = :1 and u.us
er# = o.owner# and o.name = :2 and o.obj# = t.obj#
1 690 0.0 0.9 0.03 0.06 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
1 14 0.1 0.9 0.01 0.01 c0agatqzq2jzr
insert into "SYS"."ALERT_QT" (q_name, msgid, corrid, priority, state, delay, ex
piration, time_manager_info, local_order_no, chain_no, enq_time, step_no, enq_
uid, enq_tid, retry_count, exception_qschema, exception_queue, recipient_key,
dequeue_msgid, user_data, sender_name, sender_address, sender_protocol, user
0 6 0.0 0.0 0.00 0.00 04xtrk7uyhknh
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f
rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an
d linkname is null and subname is null
0 0 N/A 0.0 0.00 0.00 0cc4xv33r5uhm
select inst_id,sid_knst,serial_knst, applynum_knstacr, applyname_knstacr,decode(
state_knstacr,0,'INITIALIZING', 1,'APPLYING',2,'SHUTTING DOWN CLEANLY',3,
'ABORTING',4,'IDLE'),total_applied_knstacr, total_waitdeps_knstacr,total_waitcom
mits_knstacr,total_admin_knstacr,total_assigned_knstacr,total_received_knstacr,
0 2 0.0 0.0 0.03 0.03 0cjngzmtm4yv1
select reason_id, object_id, subobject_id, internal_instance_number, time_sugges
ted, context, reason_argument_1, reason_argument_2, reason_argument_3, reason_ar
gument_4, reason_argument_5, action_argument_1, action_argument_2, action_argume
nt_3, action_argument_4, action_argument_5, sequence_id, metric_value, instance_
0 26 0.0 0.0 0.00 0.00 0fr8zhn4ymu3v
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp
e$ where obj# = :1 order by intcol# asc
0 18 0.0 0.0 0.02 0.02 130dvvr5s8bgn
select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#, block#, pctfre
e$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt
, empcnt, avgspc, chncnt, avgrln, length(bhiboundval), bhiboundval from tabpart$
where bo# = :1 order by part#
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: HXY/HXY Snaps: 167-168
-> Total Executions: 9,937
-> Captured SQL account for 58.9% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
719 55 0.1 0.00 0.00 803b7z0t84sq7
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_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, jo
b
690 925 1.3 0.00 0.00 53saa2zkr6wc3
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1
664 664 1.0 0.00 0.00 g2wr3u7s1gtf3
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysd
ate+5/86400))
534 534 1.0 0.00 0.00 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
263 638 2.4 0.00 0.00 6769wyy3yf66f
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#=:1
204 2,417 11.8 0.00 0.00 83taa7kaw59c1
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc
ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s
cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
196 99 0.5 0.00 0.00 asvzxj61dc5vs
select timestamp, flags from fixed_obj$ where obj#=:1
194 263 1.4 0.00 0.00 7ng34ruy5awxq
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
181 181 1.0 0.00 0.00 1gu8t96d0bdmu
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
141 23 0.2 0.00 0.00 2q93zsrvbdw48
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w
here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
140 1,158 8.3 0.00 0.00 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
140 1,149 8.2 0.00 0.00 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
139 0 0.0 0.00 0.00 6aq34nj2zb2n7
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where
obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
SQL ordered by Executions DB/Inst: HXY/HXY Snaps: 167-168
-> Total Executions: 9,937
-> Captured SQL account for 58.9% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
128 690 5.4 0.00 0.00 a73wbv1yu8x5c
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro
wid,cols,nvl(defer,0),mtime,nvl(spare1,0) from cdef$ where obj#=:1
128 3 0.0 0.00 0.00 f8pavn1bvsj7t
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
120 1,429 11.9 0.00 0.00 772s25v1y0x8k
select shared_pool_size_for_estimate s, shared_pool_size_factor * 100 f
, estd_lc_load_time l, 0 from v$shared_pool_advice
120 2,400 20.0 0.00 0.00 aykvshm7zsabd
select size_for_estimate, size_factor * 100 f,
estd_physical_read_time, estd_physical_reads
from v$db_cache_advice where id = '3'
120 2,400 20.0 0.00 0.00 g6gu1n3x0h1h4
select streams_pool_size_for_estimate s, streams_pool_size_factor * 10
0 f, estd_spill_time + estd_unspill_time, 0 from v$streams_pool_advic
e
110 110 1.0 0.00 0.00 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: HXY/HXY Snaps: 167-168
-> Total Parse Calls: 4,623
-> Captured SQL account for 39.8% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
211 534 4.56 3c1kubcdjnppq
update sys.col_usage$ set equality_preds = equality_preds + decode(bitan
d(:flag,1),0,0,1), equijoin_preds = equijoin_preds + decode(bitand(:flag
,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0
,1), range_preds = range_preds + decode(bitand(:flag,8),0,0,1),
211 0 4.56 53btfq0dt9bs9
insert into sys.col_usage$ values ( :objn, :coln, decode(bitand(:flag,1),0,0
,1), decode(bitand(:flag,2),0,0,1), decode(bitand(: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), :time)
196 196 4.24 asvzxj61dc5vs
select timestamp, flags from fixed_obj$ where obj#=:1
140 140 3.03 8swypbbr0m372
select order#,columns,types from access$ where d_obj#=:1
140 140 3.03 cqgv56fmuj63x
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#
(+) order by order#
110 110 2.38 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
85 85 1.84 39m4sx9k63ba2
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 c6awqs517jpj0
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w
here obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
85 85 1.84 ga9j9xk5cy9s0
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
59 59 1.28 b1wc53ddd6h3p
select audit$,options from procedure$ where obj#=:1
-------------------------------------------------------------
SQL ordered by Sharable Memory DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Version Count DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 766 0.2 2.7
CPU used when call started 148 0.0 0.5
CR blocks created 1 0.0 0.0
DB time 337,465 93.7 1,188.3
DBWR checkpoint buffers written 837 0.2 3.0
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 97 0.0 0.3
DBWR undo block writes 162 0.0 0.6
IMU CR rollbacks 0 0.0 0.0
IMU Flushes 19 0.0 0.1
IMU Redo allocation size 107,160 29.7 377.3
IMU commits 265 0.1 0.9
IMU contention 0 0.0 0.0
IMU undo allocation size 1,115,464 309.6 3,927.7
SMON posted for undo segment shr 1 0.0 0.0
SQL*Net roundtrips to/from clien 0 0.0 0.0
active txn count during cleanout 16 0.0 0.1
background timeouts 11,590 3.2 40.8
buffer is not pinned count 31,677 8.8 111.5
buffer is pinned count 7,723 2.1 27.2
bytes received via SQL*Net from 0 0.0 0.0
bytes sent via SQL*Net to client 0 0.0 0.0
calls to get snapshot scn: kcmgs 14,821 4.1 52.2
calls to kcmgas 662 0.2 2.3
calls to kcmgcs 46 0.0 0.2
change write time 34 0.0 0.1
cleanout - number of ktugct call 18 0.0 0.1
cleanouts and rollbacks - consis 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 5,165 1.4 18.2
cluster key scans 775 0.2 2.7
commit batch/immediate performed 0 0.0 0.0
commit batch/immediate requested 0 0.0 0.0
commit cleanout failures: callba 3 0.0 0.0
commit cleanouts 1,440 0.4 5.1
commit cleanouts successfully co 1,437 0.4 5.1
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
commit txn count during cleanout 12 0.0 0.0
concurrency wait time 476 0.1 1.7
consistent changes 1 0.0 0.0
consistent gets 48,897 13.6 172.2
consistent gets - examination 18,341 5.1 64.6
consistent gets from cache 48,897 13.6 172.2
cursor authentications 131 0.0 0.5
data blocks consistent reads - u 1 0.0 0.0
db block changes 6,246 1.7 22.0
db block gets 5,566 1.5 19.6
db block gets from cache 5,566 1.5 19.6
deferred (CURRENT) block cleanou 932 0.3 3.3
enqueue conversions 725 0.2 2.6
enqueue releases 32,678 9.1 115.1
enqueue requests 32,678 9.1 115.1
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 9,937 2.8 35.0
frame signature mismatch 0 0.0 0.0
free buffer inspected 0 0.0 0.0
free buffer requested 173 0.1 0.6
heap block compress 21 0.0 0.1
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
hot buffers moved to head of LRU 0 0.0 0.0
immediate (CR) block cleanout ap 0 0.0 0.0
immediate (CURRENT) block cleano 194 0.1 0.7
index fast full scans (full) 8 0.0 0.0
index fetch by key 7,248 2.0 25.5
index scans kdiixs1 6,371 1.8 22.4
leaf node 90-10 splits 7 0.0 0.0
leaf node splits 10 0.0 0.0
lob reads 79 0.0 0.3
lob writes 10 0.0 0.0
lob writes unaligned 10 0.0 0.0
logons cumulative 112 0.0 0.4
messages received 1,040 0.3 3.7
messages sent 1,040 0.3 3.7
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 21,380 5.9 75.3
opened cursors cumulative 6,119 1.7 21.6
parse count (hard) 475 0.1 1.7
parse count (total) 4,623 1.3 16.3
parse time cpu 78 0.0 0.3
parse time elapsed 126 0.0 0.4
physical read IO requests 97 0.0 0.3
physical read bytes 884,736 245.6 3,115.3
physical read total IO requests 1,780 0.5 6.3
physical read total bytes 28,418,048 7,887.3 100,063.6
physical read total multi block 5 0.0 0.0
physical reads 108 0.0 0.4
physical reads cache 108 0.0 0.4
physical reads cache prefetch 11 0.0 0.0
physical reads direct 0 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 680 0.2 2.4
physical write bytes 6,856,704 1,903.0 24,143.3
physical write total IO requests 4,641 1.3 16.3
physical write total bytes 67,203,072 18,651.9 236,630.5
physical write total multi block 410 0.1 1.4
physical writes 837 0.2 3.0
physical writes direct 0 0.0 0.0
physical writes from cache 837 0.2 3.0
physical writes non checkpoint 130 0.0 0.5
pinned buffers inspected 0 0.0 0.0
prefetch warmup blocks aged out 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 87,814 24.4 309.2
recursive cpu usage 386 0.1 1.4
redo blocks read for recovery 0 0.0 0.0
redo blocks written 2,952 0.8 10.4
redo entries 2,872 0.8 10.1
redo size 1,360,832 377.7 4,791.7
redo synch time 29 0.0 0.1
redo synch writes 985 0.3 3.5
redo wastage 94,148 26.1 331.5
redo write time 237 0.1 0.8
redo writer latching time 7 0.0 0.0
redo writes 370 0.1 1.3
rollback changes - undo records 0 0.0 0.0
rollbacks only - consistent read 1 0.0 0.0
rows fetched via callback 4,478 1.2 15.8
session cursor cache hits 3,811 1.1 13.4
Instance Activity Stats DB/Inst: HXY/HXY Snaps: 167-168
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
session logical reads 54,463 15.1 191.8
session pga memory 80,028,480 22,211.5 281,790.4
session pga memory max 85,205,824 23,648.4 300,020.5
session uga memory 244,816,092,696 67,947,413.2 #############
session uga memory max 73,911,912 20,513.9 260,253.2
shared hash latch upgrades - no 6,488 1.8 22.9
sorts (memory) 2,941 0.8 10.4
sorts (rows) 20,362 5.7 71.7
sql area purged 0 0.0 0.0
switch current to new buffer 0 0.0 0.0
table fetch by rowid 13,136 3.7 46.3
table fetch continued row 227 0.1 0.8
table scan blocks gotten 4,541 1.3 16.0
table scan rows gotten 122,889 34.1 432.7
table scans (long tables) 1 0.0 0.0
table scans (short tables) 1,266 0.4 4.5
total number of times SMON poste 1 0.0 0.0
transaction rollbacks 0 0.0 0.0
undo change vector size 446,456 123.9 1,572.0
user I/O wait time 31 0.0 0.1
user calls 337 0.1 1.2
user commits 284 0.1 1.0
workarea executions - optimal 1,918 0.5 6.8
-------------------------------------------------------------
Instance Activity Stats - Absolute Values DB/Inst: HXY/HXY Snaps: 167-168
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
session cursor cache count 2,295 3,203
opened cursors current 50 50
workarea memory allocated 127 127
logons current 19 18
-------------------------------------------------------------
Instance Activity Stats - Thread Activity DB/Inst: HXY/HXY Snaps: 167-168
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: HXY/HXY Snaps: 167-168
-> 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
43 0 2.1 1.0 381 0 0 0.0
UNDOTBS1
0 0 0.0 .0 198 0 0 0.0
SYSTEM
55 0 4.2 1.2 101 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSAUX /u01/app/oracle/oradata/HXY/sysaux01.dbf
43 0 2.1 1.0 381 0 0 0.0
SYSTEM /u01/app/oracle/oradata/HXY/system01.dbf
55 0 4.2 1.2 101 0 0 0.0
UNDOTBS1 /u01/app/oracle/oradata/HXY/undotbs01.dbf
0 0 N/A N/A 198 0 0 0.0
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- -------------- ------------ ----------- ---- ---- ----------
D 7,918 100 54,363 109 837 0 0 1
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: HXY/HXY Snaps: 167-168
-> 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 34 221 640 996 184320 996 N/A
E 0 34 167 372 655 184320 655 N/A
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: HXY/HXY Snap: 168
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Est
Phys
Size for Size Buffers for Read Estimated
P Est (M) Factor Estimate Factor Physical Reads
--- -------- ------ ---------------- ------ ------------------
D 4 .1 495 3.1 28,889
D 8 .1 990 2.4 22,125
D 12 .2 1,485 2.0 18,221
D 16 .3 1,980 1.7 16,140
D 20 .3 2,475 1.7 15,499
D 24 .4 2,970 1.5 13,436
D 28 .4 3,465 1.4 12,621
D 32 .5 3,960 1.3 11,640
D 36 .6 4,455 1.2 11,099
D 40 .6 4,950 1.2 10,760
D 44 .7 5,445 1.1 10,586
D 48 .8 5,940 1.1 10,073
D 52 .8 6,435 1.1 9,798
D 56 .9 6,930 1.0 9,486
D 60 .9 7,425 1.0 9,294
D 64 1.0 7,920 1.0 9,257
D 68 1.1 8,415 1.0 9,257
D 72 1.1 8,910 1.0 9,257
D 76 1.2 9,405 1.0 9,257
D 80 1.3 9,900 1.0 9,037
-------------------------------------------------------------
PGA Aggr Summary DB/Inst: HXY/HXY Snaps: 167-168
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ------------------ --------------------------
100.0 21 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 Aggr Target Stats DB/Inst: HXY/HXY Snaps: 167-168
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> 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 %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 92.2 0.1 .1 100.0 .0 3,276
E 16 4 90.8 0.1 .1 100.0 .0 3,276
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: HXY/HXY Snaps: 167-168
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
2K 4K 1,895 1,895 0 0
64K 128K 3 3 0 0
512K 1024K 17 17 0 0
1M 2M 3 3 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: HXY/HXY Snap: 168
-> 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 107.7 0.0 100.0 1
16 1.0 107.7 0.0 100.0 1
19 1.2 107.7 0.0 100.0 1
22 1.4 107.7 0.0 100.0 1
26 1.6 107.7 0.0 100.0 1
29 1.8 107.7 0.0 100.0 1
32 2.0 107.7 0.0 100.0 1
48 3.0 107.7 0.0 100.0 1
64 4.0 107.7 0.0 100.0 0
96 6.0 107.7 0.0 100.0 0
128 8.0 107.7 0.0 100.0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: HXY/HXY Snap: 168
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
68 .9 8 887 194 1.0 42 1.1 50,392
76 1.0 15 1,523 196 1.0 40 1.0 51,301
84 1.1 22 2,423 197 1.0 39 1.0 52,090
92 1.2 29 3,109 198 1.0 38 1.0 52,814
100 1.3 36 3,525 200 1.0 36 .9 53,597
108 1.4 43 4,132 200 1.0 36 .9 53,857
116 1.5 50 5,016 200 1.0 36 .9 53,975
124 1.6 57 5,581 200 1.0 36 .9 53,997
132 1.7 64 6,144 200 1.0 36 .9 54,003
140 1.8 71 7,153 200 1.0 36 .9 54,009
148 1.9 78 8,059 200 1.0 36 .9 54,011
156 2.1 85 9,052 200 1.0 36 .9 54,011
-------------------------------------------------------------
SGA Target Advisory DB/Inst: HXY/HXY Snap: 168
SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
---------- ---------- ------------ ----------------
120 0.8 105 11,071
160 1.0 86 9,240
200 1.3 80 9,019
240 1.5 80 9,019
280 1.8 80 9,019
320 2.0 80 9,019
-------------------------------------------------------------
Streams Pool Advisory DB/Inst: HXY/HXY Snap: 168
Size for Size Est Spill Est Spill Est Unspill Est Unspill
Est (MB) Factor Count Time (s) Count Time (s)
---------- --------- ----------- ----------- ----------- -----------
4 0.5 0 0 0 0
8 1.0 0 0 0 0
12 1.5 0 0 0 0
16 2.0 0 0 0 0
20 2.5 0 0 0 0
24 3.0 0 0 0 0
28 3.5 0 0 0 0
32 4.0 0 0 0 0
36 4.5 0 0 0 0
40 5.0 0 0 0 0
44 5.5 0 0 0 0
48 6.0 0 0 0 0
52 6.5 0 0 0 0
56 7.0 0 0 0 0
60 7.5 0 0 0 0
64 8.0 0 0 0 0
68 8.5 0 0 0 0
72 9.0 0 0 0 0
76 9.5 0 0 0 0
80 10.0 0 0 0 0
-------------------------------------------------------------
Java Pool Advisory DB/Inst: HXY/HXY Snap: 168
No data exists for this section of the report.
-------------------------------------------------------------
Buffer Wait Statistics DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
------------------ ----------- ------------------- --------------
undo header 1 0 0
-------------------------------------------------------------
Enqueue Activity DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Undo Segment Summary DB/Inst: HXY/HXY Snaps: 167-168
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concurcy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
1 .0 534 0 1 15/15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: HXY/HXY Snaps: 167-168
-> Most recent 35 Undostat rows, ordered by Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
12-Oct 13:50 5 43 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:40 2 61 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:30 3 45 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:20 13 327 0 1 15 0/0 0/0/0/0/0/0
12-Oct 13:10 3 58 0 1 15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "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 Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AWR Alerted Metric Eleme 12,742 0.0 N/A 0 0 N/A
Consistent RBA 370 0.0 N/A 0 0 N/A
FAL request queue 72 0.0 N/A 0 0 N/A
FAL subheap alocation 72 0.0 N/A 0 0 N/A
FOB s.o list latch 44 0.0 N/A 0 0 N/A
In memory undo latch 3,665 0.0 N/A 0 1,149 0.0
JS mem alloc latch 4 0.0 N/A 0 0 N/A
JS queue access latch 4 0.0 N/A 0 0 N/A
JS queue state obj latch 26,026 0.0 N/A 0 0 N/A
JS slv state obj latch 171 0.0 N/A 0 0 N/A
KGX 0 N/A N/A 0 12,215 0.0
KMG MMAN ready and start 1,200 0.0 N/A 0 0 N/A
KTF sga latch 7 0.0 N/A 0 1,145 0.0
KWQMN job cache list lat 17 0.0 N/A 0 0 N/A
KWQP Prop Status 405 0.0 N/A 0 0 N/A
MQL Tracking Latch 0 N/A N/A 0 72 0.0
Memory Management Latch 0 N/A N/A 0 1,200 0.0
Memory Queue 403 0.0 N/A 0 0 N/A
OS process 498 0.0 N/A 0 0 N/A
OS process allocation 1,531 0.0 N/A 0 0 N/A
OS process: request allo 111 0.0 N/A 0 0 N/A
PL/SQL warning settings 242 0.0 N/A 0 0 N/A
SGA IO buffer pool latch 1 0.0 N/A 0 1 0.0
SQL memory manager latch 460 0.0 N/A 0 1,197 0.0
SQL memory manager worka 111,941 0.0 N/A 0 0 N/A
Shared B-Tree 2,577 0.0 N/A 0 0 N/A
active checkpoint queue 1,880 0.0 N/A 0 0 N/A
active service list 7,693 0.0 N/A 0 1,260 0.0
archive control 68 0.0 N/A 0 0 N/A
archive process latch 1,269 0.0 N/A 0 0 N/A
bq:time manger info latc 133 0.0 N/A 0 0 N/A
bufq statistics 406 0.0 N/A 0 0 N/A
cache buffer handles 20 0.0 N/A 0 0 N/A
cache buffers chains 109,555 0.0 N/A 0 480 0.0
cache buffers lru chain 2,720 0.0 N/A 0 1,066 0.0
cache table scan latch 0 N/A N/A 0 5 0.0
cas latch 2,336 0.0 N/A 0 0 N/A
channel handle pool latc 111 0.0 N/A 0 0 N/A
channel operations paren 17,106 0.0 N/A 0 0 N/A
checkpoint queue latch 26,967 0.0 N/A 0 577 0.0
client/application info 1,445 0.0 N/A 0 0 N/A
commit callback allocati 28 0.0 N/A 0 0 N/A
compile environment latc 224 0.0 N/A 0 0 N/A
dml lock allocation 2,284 0.0 N/A 0 0 N/A
dummy allocation 225 0.0 N/A 0 0 N/A
enqueue hash chains 66,097 0.0 N/A 0 120 0.0
enqueues 61,835 0.0 N/A 0 0 N/A
event group latch 55 0.0 N/A 0 0 N/A
file cache latch 79 0.0 N/A 0 0 N/A
hash table column usage 426 0.0 N/A 0 5,173 0.0
hash table modification 63 0.0 N/A 0 0 N/A
job workq parent latch 0 N/A N/A 0 114 0.0
job_queue_processes para 117 0.0 N/A 0 0 N/A
kks stats 1,649 0.0 N/A 0 0 N/A
ksuosstats global area 242 0.0 N/A 0 0 N/A
ktm global data 15 0.0 N/A 0 0 N/A
kwqbsn:qsga 2,037 0.0 N/A 0 0 N/A
lgwr LWN SCN 1,424 0.0 N/A 0 0 N/A
library cache 65,439 0.0 N/A 0 203 0.0
library cache load lock 2,480 0.0 N/A 0 0 N/A
Latch Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "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 Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
library cache lock 24,358 0.0 N/A 0 0 N/A
library cache lock alloc 1,296 0.0 N/A 0 0 N/A
library cache pin 37,828 0.0 N/A 0 0 N/A
library cache pin alloca 591 0.0 N/A 0 0 N/A
list of block allocation 133 0.0 N/A 0 0 N/A
logminer context allocat 1 0.0 N/A 0 0 N/A
messages 25,685 0.0 N/A 0 0 N/A
mostly latch-free SCN 2,089 0.0 N/A 0 0 N/A
multiblock read objects 38 0.0 N/A 0 0 N/A
ncodef allocation latch 60 0.0 N/A 0 0 N/A
object queue header heap 2 0.0 N/A 0 0 N/A
object queue header oper 4,571 0.0 N/A 0 0 N/A
object stats modificatio 1 0.0 N/A 0 0 N/A
parallel query alloc buf 480 0.0 N/A 0 0 N/A
parameter table allocati 115 0.0 N/A 0 0 N/A
post/wait queue 154 0.0 N/A 0 68 0.0
process allocation 111 0.0 N/A 0 55 0.0
process group creation 111 0.0 N/A 0 0 N/A
qmn task queue latch 3,707 0.1 1.0 0 0 N/A
redo allocation 4,093 0.0 1.0 0 2,910 0.1
redo copy 1 0.0 N/A 0 2,910 0.8
redo on-disk SCN 4,519 0.0 N/A 0 0 N/A
redo writing 5,504 0.0 N/A 0 0 N/A
resmgr group change latc 293 0.0 N/A 0 0 N/A
resmgr:actses active lis 222 0.0 N/A 0 0 N/A
resmgr:actses change gro 57 0.0 N/A 0 0 N/A
resmgr:free threads list 221 0.0 N/A 0 0 N/A
resmgr:schema config 1 0.0 N/A 0 0 N/A
rm cas latch 563 0.0 N/A 0 0 N/A
row cache objects 46,163 0.0 N/A 0 0 N/A
rules engine aggregate s 14 0.0 N/A 0 0 N/A
rules engine rule set st 128 0.0 N/A 0 0 N/A
sequence cache 49 0.0 N/A 0 0 N/A
session allocation 99,343 0.0 N/A 0 0 N/A
session idle bit 787 0.0 N/A 0 0 N/A
session state list latch 236 0.0 N/A 0 0 N/A
session switching 60 0.0 N/A 0 0 N/A
session timer 1,260 0.0 N/A 0 0 N/A
shared pool 47,741 0.0 N/A 0 0 N/A
simulator hash latch 4,020 0.0 N/A 0 0 N/A
simulator lru latch 3,903 0.0 N/A 0 11 0.0
slave class 2 0.0 N/A 0 0 N/A
slave class create 8 12.5 1.0 0 0 N/A
sort extent pool 91 0.0 N/A 0 0 N/A
state object free list 2 0.0 N/A 0 0 N/A
statistics aggregation 140 0.0 N/A 0 0 N/A
threshold alerts latch 253 0.0 N/A 0 0 N/A
transaction allocation 133 0.0 N/A 0 0 N/A
transaction branch alloc 60 0.0 N/A 0 0 N/A
undo global data 2,981 0.0 N/A 0 0 N/A
user lock 114 0.0 N/A 0 0 N/A
-------------------------------------------------------------
Latch Sleep Breakdown DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by misses desc
Latch Name
----------------------------------------
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
-------------- ----------- ----------- ---------- -------- -------- --------
qmn task queue latch
3,707 4 4 0 0 0 0
redo allocation
4,093 1 1 0 0 0 0
slave class create
8 1 1 0 0 0 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: HXY/HXY Snaps: 167-168
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
event range base latch No latch 0 4 4
redo allocation kcrfw_redo_gen: redo alloc 0 1 0
slave class create ksvcreate 0 1 0
-------------------------------------------------------------
Parent Latch Statistics DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Child Latch Statistics DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Segments by Logical Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Logical Reads: 54,463
-> Captured Segments account for 71.3% of Total
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM SMON_SCN_TIME TABLE 3,872 7.11
SYS SYSTEM JOB$ TABLE 2,448 4.49
SYS SYSTEM I_SYSAUTH1 INDEX 2,272 4.17
SYS SYSTEM I_CCOL1 INDEX 2,256 4.14
SYS SYSTEM SYS_C00650 INDEX 2,064 3.79
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: HXY/HXY Snaps: 167-168
-> Total Physical Reads: 108
-> Captured Segments account for 64.8% of Total
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM TAB$ TABLE 40 37.04
SYS SYSAUX WRH$_SQL_BIND_METADA TABLE 7 6.48
SYS SYSAUX WRH$_SYSMETRIC_SUMMA INDEX 4 3.70
SYS SYSAUX WRH$_ROWCACHE_SUMMAR 742363_147 INDEX 3 2.78
SYS SYSAUX WRH$_SYSMETRIC_SUMMA TABLE 3 2.78
-------------------------------------------------------------
Segments by Row Lock Waits DB/Inst: HXY/HXY Snaps: 167-168
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot
Row
Tablespace Subobject Obj. Lock % of
Owner Name Object Name Name Type Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM SMON_SCN_TIME TABLE 1 100.00
-------------------------------------------------------------
Segments by ITL Waits DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Segments by Buffer Busy Waits DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: HXY/HXY Snaps: 167-168
-> "Pct Misses" should be very low (< 2% in most cases)
-> "Final Usage" is the number of cache entries being used
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 66 0.0 0 N/A 2 1
dc_global_oids 192 0.0 0 N/A 0 32
dc_histogram_data 1,898 0.0 0 N/A 0 768
dc_histogram_defs 3,714 0.0 0 N/A 0 3,114
dc_object_grants 4 0.0 0 N/A 0 2
dc_object_ids 3,083 0.1 0 N/A 0 820
dc_objects 1,735 0.5 0 N/A 5 786
dc_profiles 59 0.0 0 N/A 0 1
dc_rollback_segments 646 0.0 0 N/A 0 22
dc_segments 854 0.0 0 N/A 1 532
dc_sequences 5 0.0 0 N/A 5 5
dc_tablespaces 1,676 0.0 0 N/A 0 6
dc_usernames 446 0.0 0 N/A 0 10
dc_users 2,370 0.0 0 N/A 0 12
outstanding_alerts 100 7.0 0 N/A 14 14
-------------------------------------------------------------
Library Cache Activity DB/Inst: HXY/HXY Snaps: 167-168
-> "Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 603 0.0 750 4.3 32 0
CLUSTER 33 0.0 58 6.9 4 0
INDEX 20 0.0 22 90.9 20 0
SQL AREA 1,573 0.1 12,855 6.6 461 0
TABLE/PROCEDURE 1,805 0.0 4,105 27.6 521 0
TRIGGER 10 0.0 164 1.2 2 0
-------------------------------------------------------------
Process Memory Summary DB/Inst: HXY/HXY Snaps: 167-168
-> 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
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist
Avg Std Dev Max Max
Alloc Used Alloc Alloc Alloc Alloc Num Num
Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B Other 83.6 N/A 4.0 7.5 22 37 21 21
Freeable 7.4 .0 .8 .3 1 N/A 9 9
SQL .9 .5 .1 .1 0 1 12 9
PL/SQL .2 .1 .0 .0 0 0 19 19
E Other 82.5 N/A 4.1 7.7 22 37 20 20
Freeable 7.3 .0 .9 .4 2 N/A 8 8
SQL .9 .5 .1 .1 0 1 11 9
PL/SQL .1 .1 .0 .0 0 0 18 18
-------------------------------------------------------------
SGA Memory Summary DB/Inst: HXY/HXY Snaps: 167-168
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ ------------------- -------------------
Database Buffers 67,108,864
Fixed Size 2,019,320
Redo Buffers 2,174,976
Variable Size 96,469,000
-------------------
sum 167,772,160
-------------------------------------------------------------
SGA breakdown difference DB/Inst: HXY/HXY Snaps: 167-168
-> ordered by Pool, Name
-> N/A 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 free memory 4.0 4.0 0.00
large PX msg pool .2 .2 0.00
large free memory 3.8 3.8 0.00
shared ASH buffers 2.0 2.0 0.00
shared CCursor .9 .9 5.70
shared FileOpenBlock 1.4 1.4 0.00
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGLS heap .9 .8 -4.80
shared KQR L PO .8 .8 1.03
shared KQR M PO 2.2 2.2 0.04
shared KSFD SGA I/O b 3.8 3.8 0.00
shared KSXR pending messages que .8 .8 0.00
shared KSXR receive buffers 1.0 1.0 0.00
shared KTI-UNDO 1.2 1.2 0.00
shared event statistics per sess 1.5 1.5 0.00
shared free memory 3.4 3.8 11.69
shared kglsim hash table bkts 4.0 4.0 0.00
shared kglsim object batch .7 .8 13.57
shared kks stbkt 1.5 1.5 0.00
shared library cache 5.8 5.9 2.19
shared message pool freequeue .7 .7 0.00
shared private strands 2.3 2.3 0.00
shared returns from metrics req .7 .7 0.00
shared row cache 7.1 7.1 0.00
shared sessions .8 .8 0.00
shared sql area 3.2 3.0 -4.16
stream KGH: NO ACCESS 4.0 4.0 0.00
stream free memory 8.0 8.0 0.00
buffer_cache 64.0 64.0 0.00
fixed_sga 1.9 1.9 0.00
log_buffer 2.1 2.1 0.00
-------------------------------------------------------------
Streams CPU/IO Usage DB/Inst: HXY/HXY Snaps: 167-168
-> Streams processes ordered by CPU usage
-> CPU and I/O Time in micro seconds
Session Type CPU Time User I/O Time Sys I/O Time
------------------------- -------------- -------------- --------------
QMON Slaves 124,609 0 0
QMON Coordinator 116,713 0 0
-------------------------------------------------------------
Streams Capture DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Streams Apply DB/Inst: HXY/HXY Snaps: 167-168
No data exists for this section of the report.
-------------------------------------------------------------
Buffered Queues DB/Inst: HXY/HXY Snaps: 167-168
-> The Spill Rate should be very close to zero
-> The Diff in Percentage Spilled should be very close to zero or negative
Incoming Outgoing Spilled Diff
Queue Schema and Name per second per second per second Pct Spilled
--------------------- ----------- ----------- ----------- ------------
SYS.KUPC$C_1_20130628 0 0 0 0
-------------------------------------------------------------
Buffered Subscribers DB/Inst: HXY/HXY Snaps: 167-168
-> All Subscribers should have a zero spill rate
Incoming Outgoing Spilled
Subscriber Name per second per second per second
-------------------- ----------- ----------- -----------
MCP 0 0 0
-------------------------------------------------------------
Rule Set DB/Inst: HXY/HXY Snaps: 167-168
-> Rule Sets ordered by Evaluations
Fast SQL CPU Elapsed
Ruleset Name Evals Evals Execs Time Time
------------------------- -------- -------- -------- -------- --------
SYS.ALERT_QUE_R 14 0 0 4 4
-------------------------------------------------------------
Resource Limit Stats DB/Inst: HXY/HXY Snap: 168
No data exists for this section of the report.
-------------------------------------------------------------
init.ora Parameters DB/Inst: HXY/HXY Snaps: 167-168
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/app/oracle/admin/HXY/adump
background_dump_dest /u01/app/oracle/admin/HXY/bdump
compatible 10.2.0.1.0
control_files /u01/app/oracle/oradata/HXY/contr
core_dump_dest /u01/app/oracle/admin/HXY/cdump
db_block_size 8192
db_domain
db_file_multiblock_read_count 16
db_name HXY
db_recovery_file_dest /u01/app/oracle/flash_recovery_ar
db_recovery_file_dest_size 2147483648
dispatchers (PROTOCOL=TCP) (SERVICE=HXYXDB)
job_queue_processes 10
log_archive_dest_1 location=/u01/app/oracle/oradata/
open_cursors 300
pga_aggregate_target 16777216
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 167772160
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/app/oracle/admin/HXY/udump
-------------------------------------------------------------
End of Report
Report written to awrrpt_1_167_168.txt
SQL>