说明：Buffer Cache: 40M = db_cache_size(32m) + db_16k_cache_size(8m)

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
SDUSUN 3686818209 sdusun 1 9.2.0.7.0 NO SUN

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 4 12-3月 -07 13:38:29 10 3.6
End Snap: 5 12-3月 -07 13:42:33 10 5.1
Elapsed: 4.07 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 40M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K

第2部分：数据库加载信息
这一部分显示了数据库加载统计信息。比如，它提供了精确到每秒的平均数量和其他的相关统计数据。

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,282.62 556,960.00
Logical reads: 17.55 4,281.00
Block changes: 11.06 2,698.00
Physical reads: 0.00 0.00
Physical writes: 0.08 19.00
User calls: 0.07 17.00
Parses: 0.68 167.00
Hard parses: 0.00 0.00
Sorts: 0.45 110.00
Logons: 0.00 0.00
Executes: 1.52 372.00
Transactions: 0.00

% Blocks changed per Read: 63.02 Recursive Call %: 97.38
Rollback per transaction %: 0.00 Rows per Sort: 72.16

第3部分：使用率
本部分非常有用，因为它显示了实例内存结构的使用率，并简要概述了内存是否被最优化地配置了。

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 100.00
Execute to Parse %: 55.11 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 63.64

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 52.13 52.14
% SQL with executions>1: 71.37 71.37
% Memory for SQL w/exec>1: 69.48 69.49

第4部分：顶部的5个事件
本部分显示了消耗了大部分时间的顶部的5个事件。

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 0 40.53
control file sequential read 82 0 24.72
control file parallel write 82 0 22.99
log file parallel write 328 0 9.94
db file parallel write 8 0 1.55
-------------------------------------------------------------
Wait Events for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> 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
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file sequential read 82 0 0 1 82.0
control file parallel write 82 0 0 1 82.0
log file parallel write 328 0 0 0 328.0
db file parallel write 8 0 0 1 8.0
log file sync 1 0 0 1 1.0
LGWR wait for redo copy 4 0 0 0 4.0
wakeup time manager 9 9 261 28999 9.0
SQL*Net message from client 11 0 244 22183 11.0
virtual circuit status 8 8 240 29999 8.0
SQL*Net message to client 11 0 0 0 11.0
-------------------------------------------------------------
Background Wait Events for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write 82 0 0 1 82.0
control file sequential read 32 0 0 1 32.0
log file parallel write 328 0 0 0 328.0
db file parallel write 8 0 0 1 8.0
LGWR wait for redo copy 4 0 0 0 4.0
rdbms ipc message 545 246 736 1350 545.0
pmon timer 81 81 243 3000 81.0
-------------------------------------------------------------

第5部分：顶部SQL
本部分显示了作为资源顶部用户的SQL查询，因此，它对于识别有问题的查询非常有帮助。

SQL ordered by Gets for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
3,444 9 382.7 80.4 0.14 0.15 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n

1,266 180 7.0 29.6 0.06 0.04 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked

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

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

9 9 1.0 0.2 0.00 0.00 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked

9 9 1.0 0.2 0.00 0.00 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_

9 9 1.0 0.2 0.00 0.00 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1

9 9 1.0 0.2 0.00 0.00 3637162254
select q_name, state, delay, expiration, rowid, msgid, dequeue_
msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr
iority, exception_qschema, exception_queue, retry_count, corrid,
time_manager_info, sender_name, sender_address, sender_protoco
l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st
SQL ordered by Gets for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100

CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

9 9 1.0 0.2 0.00 0.00 3920324236
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1

6 2 3.0 0.1 0.00 0.00 3293499221
Module: PL/SQL Developer
select 'x' from dual

0 4 0.0 0.0 0.00 0.00 2707513792
Module: PL/SQL Developer
begin :id := sys.dbms_transaction.local_transaction_id; end;

-------------------------------------------------------------
SQL ordered by Executions for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
180 180 1.0 0.00 0.00 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked

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

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

9 0 0.0 0.00 0.00 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked

9 0 0.0 0.00 0.00 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_

9 0 0.0 0.00 0.00 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1

9 180 20.0 0.02 0.02 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n

9 0 0.0 0.00 0.00 3637162254
select q_name, state, delay, expiration, rowid, msgid, dequeue_
msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr
iority, exception_qschema, exception_queue, retry_count, corrid,
time_manager_info, sender_name, sender_address, sender_protoco
l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st

9 0 0.0 0.00 0.00 3920324236
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
SQL ordered by Executions for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1

4 4 1.0 0.00 0.00 2707513792
Module: PL/SQL Developer
begin :id := sys.dbms_transaction.local_transaction_id; end;

2 2 1.0 0.00 0.00 3293499221
Module: PL/SQL Developer
select 'x' from dual

-------------------------------------------------------------
SQL ordered by Parse Calls for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
9 9 5.39 633914867
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
_manager_info <= :1 and state != :2 for update skip locked

9 9 5.39 994140048
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from QS_CBADM.QS_CBADM_ORDERS_SQTAB where time_manager_

9 9 5.39 1824912791
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1

9 9 5.39 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n

9 180 5.39 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked

9 9 5.39 3637162254
select q_name, state, delay, expiration, rowid, msgid, dequeue_
msgid, chain_no, local_order_no, enq_time, enq_tid, step_no, pr
iority, exception_qschema, exception_queue, retry_count, corrid,
time_manager_info, sender_name, sender_address, sender_protoco
l from QS.QS_ORDERS_SQTAB where time_manager_info <= :1 and st

9 9 5.39 3920324236
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1

4 4 2.40 2707513792
Module: PL/SQL Developer
begin :id := sys.dbms_transaction.local_transaction_id; end;

2 2 1.20 3293499221
Module: PL/SQL Developer
SQL ordered by Parse Calls for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
select 'x' from dual

0 0 0.00 204386021
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#

0 0 0.00 336764478
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#

0 0 0.00 363903461
insert into argument$( obj#,procedure$,procedure#,overload#,posi
tion#,sequence#,level#,argument,type#,default#,in_out,length,pre
cision#,scale,radix,charsetid,charsetform,properties,type_owner,
type_name,type_subname,type_linkname,pls_type) values (:1,:2,:3,
:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:2

0 0 0.00 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#

0 0 0.00 431456802
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1

0 0 0.00 655789632
insert into con$(owner#,name,con#)values(:1,:2,:3)

0 0 0.00 701589132
Module: PL/SQL Developer
BEGIN /* NOP UNLESS A TABLE OBJECT */ IF dictionary_
obj_type = 'TABLE' AND sys.dbms_cdc_publish.active > 0 THE
N sys.dbms_cdc_publish.change_table_trigger(dictionary_o
bj_owner,dictionary_obj_name,sysevent); END IF; END;

0 0 0.00 761440199
insert into idl_ub2$(obj#,part,version,piece#,length,piece) valu
es(:1,:2,:3,:4,:5,:6)

0 0 0.00 839312984
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti

-------------------------------------------------------------

第6部分：系统统计数据
本部分提供了全面的系统统计数据。

Instance Activity Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 11 0.1 11.0
CPU used when call started 11 0.1 11.0
CR blocks created 9 0.0 9.0
DBWR checkpoint buffers written 18 0.1 18.0
DBWR transaction table writes 0 0.0 0.0
DBWR undo block writes 10 0.0 10.0
SQL*Net roundtrips to/from client 11 0.1 11.0
active txn count during cleanout 15 0.1 15.0
background timeouts 294 1.2 294.0
buffer is not pinned count 1,382 5.7 1,382.0
buffer is pinned count 342 1.4 342.0
bytes received via SQL*Net from c 2,229 9.1 2,229.0
bytes sent via SQL*Net to client 1,940 8.0 1,940.0
calls to get snapshot scn: kcmgss 1,642 6.7 1,642.0
calls to kcmgas 566 2.3 566.0
calls to kcmgcs 10 0.0 10.0
change write time 3 0.0 3.0
cleanout - number of ktugct calls 19 0.1 19.0
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 0 0.0 0.0
cluster key scan block gets 0 0.0 0.0
cluster key scans 0 0.0 0.0
commit cleanout failures: callbac 1 0.0 1.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 618 2.5 618.0
commit cleanouts successfully com 617 2.5 617.0
commit txn count during cleanout 11 0.1 11.0
consistent changes 423 1.7 423.0
consistent gets 2,264 9.3 2,264.0
consistent gets - examination 1,208 5.0 1,208.0
cursor authentications 16 0.1 16.0
data blocks consistent reads - un 423 1.7 423.0
db block changes 2,698 11.1 2,698.0
db block gets 2,017 8.3 2,017.0
deferred (CURRENT) block cleanout 387 1.6 387.0
dirty buffers inspected 1 0.0 1.0
enqueue conversions 25 0.1 25.0
enqueue releases 1,387 5.7 1,387.0
enqueue requests 1,387 5.7 1,387.0
execute count 372 1.5 372.0
free buffer inspected 1 0.0 1.0
free buffer requested 230 0.9 230.0
immediate (CR) block cleanout app 0 0.0 0.0
immediate (CURRENT) block cleanou 14 0.1 14.0
index fetch by key 611 2.5 611.0
index scans kdiixs1 329 1.4 329.0
leaf node 90-10 splits 0 0.0 0.0
leaf node splits 7 0.0 7.0
logons cumulative 0 0.0 0.0
messages received 328 1.3 328.0
messages sent 328 1.3 328.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 263 1.1 263.0
opened cursors cumulative 167 0.7 167.0
parse count (failures) 0 0.0 0.0
parse count (hard) 0 0.0 0.0
Instance Activity Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
parse count (total) 167 0.7 167.0
parse time cpu 4 0.0 4.0
parse time elapsed 4 0.0 4.0
physical reads 0 0.0 0.0
physical reads direct 0 0.0 0.0
physical writes 19 0.1 19.0
physical writes direct 0 0.0 0.0
physical writes non checkpoint 17 0.1 17.0
prefetched blocks 0 0.0 0.0
process last non-idle time 0 0.0 0.0
recursive calls 633 2.6 633.0
recursive cpu usage 13 0.1 13.0
redo blocks written 1,243 5.1 1,243.0
redo entries 1,370 5.6 1,370.0
redo size 556,960 2,282.6 556,960.0
redo synch time 0 0.0 0.0
redo synch writes 1 0.0 1.0
redo wastage 45,592 186.9 45,592.0
redo write time 8 0.0 8.0
redo writer latching time 0 0.0 0.0
redo writes 328 1.3 328.0
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 45 0.2 45.0
rows fetched via callback 559 2.3 559.0
session connect time 0 0.0 0.0
session logical reads 4,281 17.6 4,281.0
session pga memory 0 0.0 0.0
session pga memory max 186,680 765.1 186,680.0
session uga memory 0 0.0 0.0
session uga memory max 0 0.0 0.0
shared hash latch upgrades - no w 329 1.4 329.0
sorts (memory) 110 0.5 110.0
sorts (rows) 7,938 32.5 7,938.0
summed dirty queue length 1 0.0 1.0
switch current to new buffer 180 0.7 180.0
table fetch by rowid 560 2.3 560.0
table fetch continued row 0 0.0 0.0
table scan blocks gotten 193 0.8 193.0
table scan rows gotten 2,395 9.8 2,395.0
table scans (long tables) 0 0.0 0.0
table scans (short tables) 193 0.8 193.0
user calls 17 0.1 17.0
user commits 1 0.0 1.0
workarea executions - optimal 77 0.3 77.0
-------------------------------------------------------------

第7部分：表空间统计数据
本部分显示了关于经常被使用的表空间的统计信息，这意味着哪些表空间有许多读取/写入行为

Tablespace IO Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS1
0 0 0.0 10 0 0 0.0
SYSTEM
0 0 0.0 9 0 0 0.0
-------------------------------------------------------------

第8部分：文件统计
本部分提供了关于拥有I/O行为（读取/写入）的最高等级数据的统计信息。

File IO Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM F:ORACLEORADATASDUSUNSYSTEM01.DBF
0 0 9 0 0

UNDOTBS1 F:ORACLEORADATASDUSUNUNDOTBS01.DBF
0 0 10 0 0

-------------------------------------------------------------

第9部分：缓冲池统计信息

Buffer Pool Statistics for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 4,004 100.0 4,054 0 19 0 0 0
16k 506 0 0 0 0 0 0
-------------------------------------------------------------

第10部分：
本部分显示了关于恢复所需平均时间（MTTR）的统计信息。

Instance Recovery Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> 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 44 18 1155 22477 21924 184320 21924
E 44 18 1180 23014 22548 184320 22548
-------------------------------------------------------------

第11部分：缓冲池咨询统计信息
引导我们适当配置缓冲缓存器大小的缓冲池咨询统计的列表。

Buffer Pool Advisory for DB: SDUSUN Instance: sdusun End Snap: 5
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size first)

Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 8 .3 1,001 1.86 5,341
D 16 .5 2,002 1.32 3,785
D 24 .8 3,003 1.00 2,872
D 32 1.0 4,004 1.00 2,872
D 40 1.3 5,005 1.00 2,872
D 48 1.5 6,006 1.00 2,872
D 56 1.8 7,007 1.00 2,872
D 64 2.0 8,008 1.00 2,872
D 72 2.3 9,009 1.00 2,872
D 80 2.5 10,010 1.00 2,872
D 88 2.8 11,011 1.00 2,872
D 96 3.0 12,012 1.00 2,872
D 104 3.3 13,013 1.00 2,872
D 112 3.5 14,014 1.00 2,872
D 120 3.8 15,015 1.00 2,872
D 128 4.0 16,016 1.00 2,872
D 136 4.3 17,017 1.00 2,872
D 144 4.5 18,018 1.00 2,872
D 152 4.8 19,019 1.00 2,872
D 160 5.0 20,020 1.00 2,872
-------------------------------------------------------------

第12部分：PGA聚集咨询统计信息
显示了识别PDA是否被最优化的配置的咨询统计信息。

PGA Aggr Target Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 1 0

%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 24 16 10.5 0.0 .0 .0 .0 1,228
E 24 16 10.5 0.0 .0 .0 .0 1,228
-------------------------------------------------------------

PGA Aggr Target Histogram for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 66 66 0 0
16K 32K 8 8 0 0
32K 64K 1 1 0 0
64K 128K 1 1 0 0
256K 512K 1 1 0 0
-------------------------------------------------------------

PGA Memory Advisory for DB: SDUSUN Instance: sdusun End Snap: 5
-> 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.5 24.3 0.0 100.0 0
18 0.8 24.3 0.0 100.0 0
24 1.0 24.3 0.0 100.0 0
29 1.2 24.3 0.0 100.0 0
34 1.4 24.3 0.0 100.0 0
38 1.6 24.3 0.0 100.0 0
43 1.8 24.3 0.0 100.0 0
48 2.0 24.3 0.0 100.0 0
72 3.0 24.3 0.0 100.0 0
96 4.0 24.3 0.0 100.0 0
144 6.0 24.3 0.0 100.0 0
192 8.0 24.3 0.0 100.0 0
-------------------------------------------------------------

第13部分：回滚段统计信息

Rollback Segment Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed

Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 1.0 0.00 0 0 0 0
1 1.0 0.00 0 0 0 0
2 7.0 0.00 564 0 0 0
3 279.0 0.00 17,816 0 0 0
4 1.0 0.00 0 0 0 0
5 42.0 0.00 115,038 0 0 0
6 280.0 0.00 17,870 0 0 0
7 1.0 0.00 0 0 0 0
8 9.0 0.00 752 0 0 0
9 280.0 0.00 17,758 0 0 0
10 1.0 0.00 0 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 3,268,608 104,857 3,268,608
2 3,268,608 0 3,268,608
3 39,968,768 0 39,968,768
4 2,220,032 96,648 3,268,608
5 2,351,104 0 2,351,104
6 1,171,456 0 1,171,456
7 2,220,032 0 2,220,032
8 2,220,032 104,857 2,220,032
9 3,268,608 104,857 3,268,608
10 3,268,608 104,857 3,268,608
-------------------------------------------------------------
Undo Segment Summary for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 76 32,993 3 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------

Undo Segment Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
-> ordered by Time desc

Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-3月 13:5 39 16,909 2 1 0 0 0/0/0/0/0/0
1
12-3月 13:4 37 16,084 3 1 0 0 0/0/0/0/0/0
1
-------------------------------------------------------------

第14部分：锁存器行为统计信息
显示了可以用于识别争用的锁存器行为。

Latch Activity for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 328 0.0 0 0
SQL memory manager latch 1 0.0 0 81 0.0
SQL memory manager worka 5,498 0.0 0 0
active checkpoint queue 89 0.0 0 0
cache buffer handles 26 0.0 0 0
cache buffers chains 13,445 0.0 0 51 0.0
cache buffers lru chain 260 0.0 0 189 0.0
channel operations paren 164 0.0 0 0
checkpoint queue latch 4,378 0.0 0 50 0.0
dml lock allocation 1,715 0.0 0 0
enqueue hash chains 2,805 0.0 0 0
enqueues 324 0.0 0 0
job_queue_processes para 5 0.0 0 0
kwqit: protect wakeup ti 9 0.0 0 0
lgwr LWN SCN 394 0.0 0 0
library cache 7,131 0.0 0 0
library cache pin 1,848 0.0 0 0
library cache pin alloca 1,434 0.0 0 0
list of block allocation 4 0.0 0 0
messages 1,623 0.0 0 0
mostly latch-free SCN 396 0.3 0.0 0 0
ncodef allocation latch 3 0.0 0 0
post/wait queue 1 0.0 0 1 0.0
redo allocation 2,126 0.0 0.0 0 0
redo copy 0 0 1,404 0.4
redo writing 920 0.0 0 0
row cache enqueue latch 451 0.0 0 0
row cache objects 451 0.0 0 0
sequence cache 3 0.0 0 0
session allocation 722 0.0 0 0
session idle bit 44 0.0 0 0
session switching 3 0.0 0 0
session timer 81 0.0 0 0
shared pool 2,037 0.0 0 0
simulator hash latch 4 0.0 0 0
simulator lru latch 0 0 4 0.0
sort extent pool 3 0.0 0 0
spilled msgs queues list 9 0.0 0 0
transaction branch alloc 3 0.0 0 0
undo global data 1,640 0.0 0 0
-------------------------------------------------------------

第15部分：内存统计信息
油5部分组成：
1 数据字典缓存统计信息 2 库缓存统计信息 3 共享池统计信息
4 SGA配置 5 SGA分解统计信息

Dictionary Cache Stats for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache

Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_objects 44 0.0 0 0 852
dc_segments 169 0.0 0 0 293
-------------------------------------------------------------

Library Cache Activity for DB: SDUSUN Instance: sdusun Snaps: 4 -5
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 1 0.0 1 0.0 0 0
INDEX 44 0.0 44 0.0 0 0
SQL AREA 145 0.0 504 0.0 0 0
TABLE/PROCEDURE 167 0.0 283 0.0 0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: SDUSUN Instance: sdusun End Snap: 5
-> 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

Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
24 .5 10 3,251 464 1.0 44,289
32 .7 10 3,251 464 1.0 44,289
40 .8 10 3,251 464 1.0 44,289
48 1.0 10 3,251 464 1.0 44,289
56 1.2 10 3,251 464 1.0 44,289
64 1.3 10 3,251 464 1.0 44,289
72 1.5 10 3,251 464 1.0 44,289
80 1.7 10 3,251 464 1.0 44,289
88 1.8 10 3,251 464 1.0 44,289
96 2.0 10 3,251 464 1.0 44,289
-------------------------------------------------------------
SGA Memory Summary for DB: SDUSUN Instance: sdusun Snaps: 4 -5

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 41,943,040
Fixed Size 454,220
Redo Buffers 667,648
Variable Size 109,051,904
----------------
sum 152,116,812
-------------------------------------------------------------

SGA breakdown difference for DB: SDUSUN Instance: sdusun Snaps: 4 -5

Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large free memory 8,388,608 8,388,608 0.00
shared 1M buffer 2,098,176 2,098,176 0.00
shared Checkpoint queue 282,304 282,304 0.00
shared FileIdentificatonBlock 323,292 323,292 0.00
shared FileOpenBlock 695,504 695,504 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 2,038,636 2,038,636 0.00
shared KQR M PO 731,700 731,700 0.00
shared KQR S PO 164,640 164,640 0.00
shared KQR S SO 1,280 1,280 0.00
shared KSXR pending messages que 841,036 841,036 0.00
shared KSXR receive buffers 1,033,000 1,033,000 0.00
shared MTTR advisory 12,692 12,692 0.00
shared PL/SQL DIANA 3,446,596 3,446,596 0.00
shared PL/SQL MPCODE 677,124 677,124 0.00
shared PL/SQL PPCODE 17,032 17,032 0.00
shared PL/SQL SOURCE 6,660 6,660 0.00
shared PLS non-lib hp 2,884 2,884 0.00
shared VIRTUAL CIRCUITS 265,840 265,840 0.00
shared character set object 274,508 274,508 0.00
shared dictionary cache 2,137,216 2,137,216 0.00
shared enqueue 189,780 189,780 0.00
shared errors 36,360 36,360 0.00
shared event statistics per sess 1,918,280 1,918,280 0.00
shared fixed allocation callback 228 228 0.00
shared free memory 32,125,176 32,118,236 -0.02
shared joxs heap init 4,220 4,220 0.00
shared ksm_file2sga region 370,496 370,496 0.00
shared library cache 4,359,516 4,362,256 0.06
shared message pool freequeue 665,792 665,792 0.00
shared miscellaneous 7,055,372 7,059,572 0.06
shared parameters 19,836 19,836 0.00
shared sessions 410,720 410,720 0.00
shared sim memory hea 59,288 59,288 0.00
shared sql area 4,832,988 4,832,988 0.00
shared table definiti 2,016 2,016 0.00
shared trigger defini 2,548 2,548 0.00
shared trigger inform 1,144 1,144 0.00
shared trigger source 1,228 1,228 0.00
buffer_cache 41,943,040 41,943,040 0.00
fixed_sga 454,220 454,220 0.00
log_buffer 656,384 656,384 0.00
-------------------------------------------------------------

第16部分：修改的初始化参数
本部分列出了通过修改他们的默认值而得出的所有初始化参数。

init.ora Parameters for DB: SDUSUN Instance: sdusun Snaps: 4 -5

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest F:oracleadminsdusunbdump
compatible 9.2.0.0.0
control_files F:oracleoradatasdusunCONTROL0
core_dump_dest F:oracleadminsdusuncdump
db_16k_cache_size 8388608
db_block_size 8192
db_cache_size 33554432
db_domain
db_file_multiblock_read_count 16
db_name sdusun
dispatchers (PROTOCOL=TCP) (SERVICE=sdusunXDB
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name sdusun
java_pool_size 33554432
job_queue_processes 6
large_pool_size 8388608
open_cursors 300
pga_aggregate_target 25165824
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
sga_max_size 152116812
shared_pool_size 50331648
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest F:oracleadminsdusunudump
-------------------------------------------------------------

End of Report