1.环境准备
--创建独立的表空间存储statspack数据
create tablespace perfstat datafile '/oracle/app/oracle/oradata/fermdg/perfstat.dbf' size 200M;
Tablespace created.
--执行安装statspack脚本文件,会提示输入perfstat用户的密码,默认表空间,临时表空间
SQL> start ?/rdbms/admin/spcreate.sql
Enter value for perfstat_password: perfstat
Enter value for default_tablespace:perfstat
Enter value for temporary_tablespace:temp 2.使用statspack用户登陆,生成数据
SQL> conn perfstat/perfstat
Connected.
--执行快照获取数据(多执行几次)
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
SQL> execute statspack.snap;
PL/SQL procedure successfully completed.
--查看有多少个报告
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
------------
6
1 row selected.
--生成报告
start ?/rdbms/admin/spreport.sql
Listing all Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
fermdg FERMDB 1 02 Feb 2024 15:45 5
2 02 Feb 2024 15:45 5
3 02 Feb 2024 15:45 5
4 02 Feb 2024 15:45 5
5 02 Feb 2024 15:45 5
6 02 Feb 2024 15:45 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Enter value for end_snap: 6
Enter value for report_name: sp_1_6(或者默认)3.vi sp_1_6.lst
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1185704947 fermdg 1 02-Feb-24 15:33 11.2.0.4.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
oracle11gdg Linux x86 64-bit 2 2 2 8.2
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 1 02-Feb-24 15:45:26 27 1.1
End Snap: 6 02-Feb-24 15:45:33 27 1.1
Elapsed: 0.12 (mins) Av Act Sess: 0.2
DB time: 0.03 (mins) DB CPU: 0.03 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 2,992M Std Block Size: 8K
Shared Pool: 688M Log Buffer: 12,856K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.2 0.3 0.00 0.16
DB CPU(s): 0.2 0.3 0.00 0.15
Redo size: 706,593.7 989,231.2
Logical reads: 2,467.9 3,455.0
Block changes: 1,622.6 2,271.6
Physical reads: 0.3 0.4
Physical writes: 0.0 0.0
User calls: 1.4 2.0
Parses: 80.4 112.6
Hard parses: 13.4 18.8
W/A MB processed: 19.4 27.2
Logons: 0.0 0.0
Executes: 222.1 311.0
Rollbacks: 0.0 0.0
Transactions: 0.7
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 Optimal W/A Exec %: 100.00
Library Hit %: 90.66 Soft Parse %: 83.30
Execute to Parse %: 63.79 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 50.00 % Non-Parse CPU: 99.28
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 30.83 31.24
% SQL with executions>1: 41.99 46.69
% Memory for SQL w/exec>1: 46.35 53.61
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1 95.7
log file parallel write 24 0 2 2.6
log file sync 5 0 3 .9
control file parallel write 11 0 1 .6
control file sequential read 1,558 0 0 .2
-------------------------------------------------------------
Host CPU (CPUs: 2 Cores: 2 Sockets: 2)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.06 0.05 4.09 1.73 94.10 0.08
Note: There is a 7% discrepancy between the OS Stat total CPU time and
the total CPU time estimated by Statspack
OS Stat CPU time: 13(s) (BUSY_TIME + IDLE_TIME)
Statspack CPU time: 14(s) (Elapsed time * num CPUs in end snap)
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 12.7
Host: Busy CPU time (s): 0.8
% of time Host is Busy: 5.9
Instance: Total CPU time (s): 1.6
% of Busy CPU used for Instance: 209.5
Instance: Total Database time (s): 1.7
%DB time waiting for CPU (Resource Mgr): 0.0
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 8,380.8 8,380.8
SGA use (MB): 3,743.2 3,743.2
PGA use (MB): 200.7 199.7
% Host Mem used for SGA+PGA: 47.1 47.0
-------------------------------------------------------------
Time Model System Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1.5 98.7
DB CPU 1.5 98.5
parse time elapsed 0.1 8.4
hard parse elapsed time 0.1 7.7
PL/SQL execution elapsed time 0.0 .4
repeated bind elapsed time 0.0 .0
DB time 1.6
background elapsed time 0.1
background cpu time 0.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync 5 0 0 3 1.0 .9
control file sequential read 1,545 0 0 0 309.0 .2
SQL*Net message from client 5 0 6 1102 1.0
-------------------------------------------------------------
Background Wait Events DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file parallel write 24 0 0 2 4.8 2.6
control file parallel write 11 0 0 1 2.2 .6
rdbms ipc message 58 66 74 1281 11.6
Streams AQ: qmn coordinator 2 50 28 14002 0.4
Streams AQ: qmn slave idle w 1 0 28 28004 0.2
DIAG idle wait 14 100 14 1001 2.8
pmon timer 2 100 6 3001 0.4
Space Manager: slave idle wa 2 50 6 2792 0.4
smon timer 1 0 1 583 0.2
-------------------------------------------------------------
Wait Events (fg and bg) DB/Inst: FERMDB/fermdg Snaps: 1-6
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file parallel write 24 0 0 2 4.8 2.6
log file sync 5 0 0 3 1.0 .9
control file parallel write 11 0 0 1 2.2 .6
control file sequential read 1,558 0 0 0 311.6 .2
rdbms ipc message 58 66 74 1281 11.6
Streams AQ: qmn coordinator 2 50 28 14002 0.4
Streams AQ: qmn slave idle w 1 0 28 28004 0.2
DIAG idle wait 14 100 14 1001 2.8
pmon timer 2 100 6 3001 0.4
Space Manager: slave idle wa 2 50 6 2792 0.4
SQL*Net message from client 5 0 6 1102 1.0
smon timer 1 0 1 583 0.2
-------------------------------------------------------------
Wait Event Histogram DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 6 100.0
control file parallel writ 11 81.8 18.2
control file sequential re 1558 100.0
db file sequential read 2 100.0
log file parallel write 24 50.0 29.2 12.5 8.3
log file sync 5 60.0 20.0 20.0
DIAG idle wait 14 100.0
SQL*Net message from clien 5 60.0 40.0
SQL*Net message to client 5 100.0
Space Manager: slave idle 2 50.0 50.0
Streams AQ: qmn coordinato 2 50.0 50.0
Streams AQ: qmn slave idle 1 100.0
pmon timer 2 100.0
rdbms ipc message 58 8.6 3.4 1.7 1.7 56.9 27.6
smon timer 1 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Total DB CPU (s): 2
-> Captured SQL accounts for 99.9% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
1.52 5 0.30 99.8 1.53 21,272 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: FERMDB Instance: fermdg Snaps: 1 -6
-> Total DB Time (s): 2
-> Captured SQL accounts for 99.0% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
1.53 5 0.31 98.9 1.52 10 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: FERMDB/fermdg Snaps: 1-6
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 17,275
-> Captured SQL accounts for 0.7% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
21,272 5 4,254.4 123.1 1.52 1.53 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: FERMDB/fermdg Snaps: 1-6
-> End Disk Reads Threshold: 1000 Total Disk Reads: 2
-> Captured SQL accounts for 0.0% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
10 5 2.0 500.0 1.52 1.53 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: FERMDB/fermdg Snaps: 1-6
-> End Parse Calls Threshold: 1000 Total Parse Calls: 563
-> Captured SQL accounts for 1.4% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
6 5 1.07 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO single block count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
Block Cleanout Optim referenced 0 0.0 0.0
CCursor + sql area evicted 8 1.1 1.6
CPU used by this session 138 19.7 27.6
CPU used when call started 152 21.7 30.4
CR blocks created 2 0.3 0.4
Commit SCN cached 9 1.3 1.8
DB time 153 21.9 30.6
DBWR checkpoint buffers written 0 0.0 0.0
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 0 0.0 0.0
DBWR undo block writes 0 0.0 0.0
HSC Heap Segment Block Changes 634 90.6 126.8
Heap Segment Array Inserts 352 50.3 70.4
Heap Segment Array Updates 69 9.9 13.8
IMU Flushes 5 0.7 1.0
IMU Redo allocation size 92,468 13,209.7 18,493.6
IMU commits 0 0.0 0.0
IMU ktichg flush 0 0.0 0.0
IMU undo allocation size 35,368 5,052.6 7,073.6
Requests to/from client 6 0.9 1.2
RowCR attempts 5 0.7 1.0
RowCR hits 5 0.7 1.0
SQL*Net roundtrips to/from client 5 0.7 1.0
active txn count during cleanout 236 33.7 47.2
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 38 5.4 7.6
buffer is not pinned count 1,889 269.9 377.8
buffer is pinned count 758 108.3 151.6
bytes received via SQL*Net from c 1,495 213.6 299.0
bytes sent via SQL*Net to client 855 122.1 171.0
calls to get snapshot scn: kcmgss 1,895 270.7 379.0
calls to kcmgas 524 74.9 104.8
calls to kcmgcs 981 140.1 196.2
cell physical IO interconnect byt 31,332,864 4,476,123.4 6,266,572.8
change write time 4 0.6 0.8
cleanout - number of ktugct calls 262 37.4 52.4
cleanouts only - consistent read 2 0.3 0.4
cluster key scan block gets 110 15.7 22.0
cluster key scans 103 14.7 20.6
commit batch/immediate performed 7 1.0 1.4
commit batch/immediate requested 7 1.0 1.4
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 30 4.3 6.0
commit cleanouts 1,121 160.1 224.2
commit cleanouts successfully com 1,091 155.9 218.2
commit immediate performed 7 1.0 1.4
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
commit immediate requested 7 1.0 1.4
commit txn count during cleanout 134 19.1 26.8
concurrency wait time 0 0.0 0.0
consistent changes 2 0.3 0.4
consistent gets 4,546 649.4 909.2
consistent gets - examination 2,464 352.0 492.8
consistent gets from cache 4,546 649.4 909.2
consistent gets from cache (fastp 1,922 274.6 384.4
cursor authentications 58 8.3 11.6
data blocks consistent reads - un 0 0.0 0.0
db block changes 11,358 1,622.6 2,271.6
db block gets 12,729 1,818.4 2,545.8
db block gets from cache 12,729 1,818.4 2,545.8
db block gets from cache (fastpat 4,868 695.4 973.6
deferred (CURRENT) block cleanout 575 82.1 115.0
enqueue conversions 1 0.1 0.2
enqueue releases 1,573 224.7 314.6
enqueue requests 1,573 224.7 314.6
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 1,555 222.1 311.0
file io service time 0 0.0 0.0
file io wait time 27 3.9 5.4
free buffer requested 1,780 254.3 356.0
heap block compress 12 1.7 2.4
immediate (CR) block cleanout app 2 0.3 0.4
immediate (CURRENT) block cleanou 410 58.6 82.0
in call idle wait time 15,715 2,245.0 3,143.0
index crx upgrade (positioned) 0 0.0 0.0
index fast full scans (full) 5 0.7 1.0
index fetch by key 1,026 146.6 205.2
index scans kdiixs1 611 87.3 122.2
leaf node 90-10 splits 5 0.7 1.0
leaf node splits 105 15.0 21.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logical read bytes from cache 141,516,800 20,216,685.7 ############
logons cumulative 0 0.0 0.0
max cf enq hold time 0 0.0 0.0
messages received 25 3.6 5.0
messages sent 25 3.6 5.0
min active SCN optimization appli 0 0.0 0.0
no work - consistent read gets 1,299 185.6 259.8
non-idle wait count 1,625 232.1 325.0
non-idle wait time 7 1.0 1.4
opened cursors cumulative 1,524 217.7 304.8
parse count (failures) 0 0.0 0.0
parse count (hard) 94 13.4 18.8
parse count (total) 563 80.4 112.6
parse time cpu 1 0.1 0.2
parse time elapsed 2 0.3 0.4
physical read IO requests 2 0.3 0.4
physical read bytes 16,384 2,340.6 3,276.8
physical read total IO requests 1,560 222.9 312.0
physical read total bytes 25,542,656 3,648,950.9 5,108,531.2
physical read total multi block r 0 0.0 0.0
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical reads 2 0.3 0.4
physical reads cache 2 0.3 0.4
physical reads cache prefetch 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 0 0.0 0.0
physical write bytes 0 0.0 0.0
physical write total IO requests 49 7.0 9.8
physical write total bytes 5,790,208 827,172.6 1,158,041.6
physical write total multi block 9 1.3 1.8
physical writes 0 0.0 0.0
physical writes from cache 0 0.0 0.0
physical writes non checkpoint 0 0.0 0.0
pinned cursors current 0 0.0 0.0
process last non-idle time 0 0.0 0.0
recursive calls 3,070 438.6 614.0
recursive cpu usage 137 19.6 27.4
redo KB read 0 0.0 0.0
redo blocks checksummed by FG (ex 6,700 957.1 1,340.0
redo blocks written 10,605 1,515.0 2,121.0
redo entries 6,839 977.0 1,367.8
redo ordering marks 1 0.1 0.2
redo size 4,946,156 706,593.7 989,231.2
redo subscn max counts 3 0.4 0.6
redo synch long waits 0 0.0 0.0
redo synch time 1 0.1 0.2
redo synch time (usec) 13,724 1,960.6 2,744.8
redo synch time overhead (usec) 324 46.3 64.8
redo synch time overhead count (< 0 0.0 0.0
redo synch time overhead count (< 5 0.7 1.0
redo synch time overhead count (< 0 0.0 0.0
redo synch writes 5 0.7 1.0
redo wastage 7,052 1,007.4 1,410.4
redo write info find 5 0.7 1.0
redo write time 4 0.6 0.8
redo writes 25 3.6 5.0
rollback changes - undo records a 6 0.9 1.2
rollbacks only - consistent read 0 0.0 0.0
rows fetched via callback 143 20.4 28.6
session connect time 0 0.0 0.0
session cursor cache hits 1,222 174.6 244.4
session logical reads 17,275 2,467.9 3,455.0
session pga memory 655,360 93,622.9 131,072.0
session pga memory max 6,684,672 954,953.1 1,336,934.4
session uga memory 1,017,296 145,328.0 203,459.2
session uga memory max 5,997,272 856,753.1 1,199,454.4
shared hash latch upgrades - no w 55 7.9 11.0
sorts (memory) 142 20.3 28.4
sorts (rows) 17,824 2,546.3 3,564.8
sql area evicted 8 1.1 1.6
sql area purged 0 0.0 0.0
switch current to new buffer 104 14.9 20.8
table fetch by rowid 303 43.3 60.6
table fetch continued row 0 0.0 0.0
table scan blocks gotten 256 36.6 51.2
table scan rows gotten 1,220 174.3 244.0
table scans (short tables) 11 1.6 2.2
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
total cf enq hold time 0 0.0 0.0
total number of cf enq holders 1 0.1 0.2
total number of times SMON posted 1 0.1 0.2
transaction rollbacks 3 0.4 0.6
undo change vector size 1,736,384 248,054.9 347,276.8
user I/O wait time 0 0.0 0.0
user calls 10 1.4 2.0
user commits 5 0.7 1.0
user logons cumulative 0 0.0 0.0
user logouts cumulative 0 0.0 0.0
workarea executions - optimal 172 24.6 34.4
write clones created in backgroun 0 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 27 27
opened cursors current 30 30
session cursor cache count 1,032 1,032
-------------------------------------------------------------
Instance Activity Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: FERMDB/fermdg Snaps: 1-6
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 75
IDLE_TIME 1,196
IOWAIT_TIME 1
SYS_TIME 22
USER_TIME 52
PHYSICAL_MEMORY_BYTES 8,787,902,464
NUM_CPUS 2
NUM_CPU_CORES 2
NUM_CPU_SOCKETS 2
GLOBAL_RECEIVE_SIZE_MAX 4,194,304
GLOBAL_SEND_SIZE_MAX 1,048,576
TCP_RECEIVE_SIZE_DEFAULT 87,380
TCP_RECEIVE_SIZE_MAX 6,291,456
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 4,194,304
TCP_SEND_SIZE_MIN 4,096
-------------------------------------------------------------
OS Statistics - detail DB/Inst: FERMDB/fermdg Snaps: 1-6
Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
1 Fri 02 15:45:26 .1
2 Fri 02 15:45:29 .1 5.7 3.3 2.5 0.0
3 Fri 02 15:45:30 .1 3.9 3.1 .8 0.0
4 Fri 02 15:45:31 .0 7.5 5.4 1.6 0.5
5 Fri 02 15:45:32 .0 7.0 5.4 1.6 0.0
6 Fri 02 15:45:33 .0 6.4 5.1 1.3 0.0
-------------------------------------------------------------
IO Stat by Function - summary DB/Inst: FERMDB/fermdg Snaps: 1-6
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc
---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
Others 24M 222.6 3.4M 3.1 1580 0.0
Buffer Cache Re .4 3 0.0
-------------------------------------------------------------
IO Stat by Function - detail DB/Inst: FERMDB/fermdg Snaps: 1-6
->ordered by Data Volume (Read+Write) desc
----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
Others 1558 24M 22
Buffer Cache Reads 3
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
3 0 0.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSTEM /oracle/app/oracle/oradata/fermdg/system01.dbf
3 0 0.0 1 1.0 0 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM /oracle/app/oracle/oradata/fermdg/system01.dbf
3 0 0 0 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> 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 10 2574 20242 165888 165888
E 0 11 3709 28620 165888 165888
-------------------------------------------------------------
Memory Dynamic Components DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 2,992 0 GROW/DEF 02-Feb 15:34:14
PGA Target 1,264 0 STATIC
SGA Target 3,760 0 STATIC
java pool 16 0 STATIC
large pool 32 0 SHRINK/DEF 02-Feb 15:34:14
shared pool 688 0 STATIC
-------------------------------------------------------------
--缓冲池建议
Buffer Pool Advisory DB/Inst: FERMDB/fermdg End Snap: 6
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 288 .1 35 1.0 11 2 9.5
D 576 .2 71 1.0 11 2 9.5
D 864 .3 106 1.0 11 2 9.5
D 1,152 .4 142 1.0 11 2 9.5
D 1,440 .5 177 1.0 11 2 9.5
D 1,728 .6 213 1.0 11 2 9.5
D 2,016 .7 248 1.0 11 2 9.5
D 2,304 .8 284 1.0 11 2 9.5
D 2,592 .9 319 1.0 11 2 9.5
D 2,880 1.0 354 1.0 11 2 9.5
D 2,992 1.0 368 1.0 11 2 9.5
D 3,168 1.1 390 1.0 11 2 9.5
D 3,456 1.2 425 1.0 11 2 9.5
D 3,744 1.3 461 1.0 11 2 9.5
D 4,032 1.3 496 1.0 11 2 9.5
D 4,320 1.4 532 1.0 11 2 9.5
D 4,608 1.5 567 1.0 11 2 9.5
D 4,896 1.6 603 1.0 11 2 9.5
D 5,184 1.7 638 1.0 11 2 9.5
D 5,472 1.8 673 1.0 11 2 9.5
D 5,760 1.9 709 1.0 11 2 9.5
-------------------------------------------------------------
--缓冲统计信息
Buffer Pool Statistics DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 368K 100 18,683 3 0 0 0 0
--PGA 统计信息。
PGA Aggr Target Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control
--PGA缓冲命中率
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 136 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 1,250 1,003 200.7 0.0 .0 .0 .0 128,000
E 1,250 1,001 199.7 0.0 .0 .0 .0 128,000
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 61 61 0 0
64K 128K 10 10 0 0
128K 256K 7 7 0 0
512K 1024K 70 70 0 0
1M 2M 10 10 0 0
4M 8M 10 10 0 0
-------------------------------------------------------------
--PGA内存建议
PGA Memory Advisory DB/Inst: FERMDB/fermdg End Snap: 6
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
156 0.1 196 0 0.0 100.0 0
313 0.3 196 0 0.0 100.0 0
625 0.5 196 0 0.0 100.0 0
938 0.8 196 0 0.0 100.0 0
1,250 1.0 196 0 0.0 100.0 0
1,500 1.2 196 0 0.0 100.0 0
1,750 1.4 196 0 0.0 100.0 0
2,000 1.6 196 0 0.0 100.0 0
2,250 1.8 196 0 0.0 100.0 0
2,500 2.0 196 0 0.0 100.0 0
3,750 3.0 196 0 0.0 100.0 0
5,000 4.0 196 0 0.0 100.0 0
7,500 6.0 196 0 0.0 100.0 0
10,000 8.0 196 0 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 200.7 137.8 50.6 6.9 11.3 45 45 29
Other 149.0 5.1 8.0 28 28 29
Freeable 50.6 .0 4.6 6.7 18 11
PL/SQL .6 .5 .0 .1 0 0 27
SQL .5 .1 .0 .1 0 7 9
E -------- 199.8 137.8 49.6 6.9 11.3 45 45 29
Other 149.1 5.1 8.0 28 28 29
Freeable 49.6 .0 4.5 6.7 18 11
PL/SQL .6 .5 .0 .1 0 0 27
SQL .5 .1 .0 .1 0 7 9
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: FERMDB/fermdg Snaps: 1-6
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 20 ARC0 -------- 45.2 25.9 18.0 45.2 45.2
Other 27.2 27.2 27.2
Freeable 18.0 .0 18.0
PL/SQL .0 .0 .0 .0
23 ARC3 -------- 34.9 16.9 16.9 34.9 34.9
Other 18.0 18.0 18.0
Freeable 16.9 .0 16.9
PL/SQL .0 .0 .0 .0
22 ARC2 -------- 28.3 26.0 .0 28.3 28.3
Other 28.3 28.3 28.3
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 19.0 16.9 1.1 19.0 19.0
Other 18.0 18.0 18.0
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
24 NSA2 -------- 11.7 11.0 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.6 11.0 .0 11.6 11.6
Other 11.6 11.6 11.6
PL/SQL .0 .0 .0 .0
19 TNS V1-V3 --- 10.6 2.9 7.4 10.6 10.6
Freeable 7.4 .0 7.4
Other 2.9 2.9 2.9
PL/SQL .2 .2 .2 .2
SQL .2 .0 .2 7.0
10 DBW0 -------- 7.4 6.8 .1 7.4 7.4
Other 7.4 7.4 7.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
13 SMON -------- 5.0 1.0 2.8 5.0 5.0
Freeable 2.8 .0 2.8
Other 2.2 2.2 2.2
SQL .0 .0 .0 2.1
PL/SQL .0 .0 .0 .0
27 CJQ0 -------- 4.2 1.3 2.6 4.2 4.2
Freeable 2.6 .0 2.6
Other 1.6 1.6 1.6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
15 MMON -------- 3.7 2.1 1.4 3.7 3.7
Other 2.1 2.1 2.1
Freeable 1.4 .0 1.4
SQL .2 .0 .2 1.1
PL/SQL .1 .1 .1 .1
14 RECO -------- 1.5 .9 .3 1.5 1.5
Other 1.3 1.3 1.3
Freeable .3 .0 .3
SQL .0 .0 .0 .5
PL/SQL .0 .0 .0 .0
34 Q000 -------- 1.5 1.3 .1 1.5 1.5
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
Top Process Memory (by component) DB/Inst: FERMDB/fermdg Snaps: 1-6
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 34 Freeable .1 .0 .1
SQL .0 .0 .0 .2
17 D000 -------- 1.4 1.3 .0 1.4 1.4
Other 1.4 1.4 1.4
8 DIA0 -------- 1.4 1.2 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
35 Q001 -------- 1.2 1.0 .0 1.2 1.2
Other 1.2 1.2 1.2
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
12 CKPT -------- 1.1 .9 .0 1.1 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
7 DBRM -------- 1.0 .9 .0 1.0 1.0
Other 1.0 1.0 1.0
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
28 W000 -------- 1.0 .9 .0 1.0 1.0
Other 1.0 1.0 1.0
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
E 20 ARC0 -------- 45.2 25.9 18.0 45.2 45.2
Other 27.2 27.2 27.2
Freeable 18.0 .0 18.0
PL/SQL .0 .0 .0 .0
23 ARC3 -------- 34.9 16.9 16.9 34.9 34.9
Other 18.0 18.0 18.0
Freeable 16.9 .0 16.9
PL/SQL .0 .0 .0 .0
22 ARC2 -------- 28.3 26.0 .0 28.3 28.3
Other 28.3 28.3 28.3
PL/SQL .0 .0 .0 .0
21 ARC1 -------- 19.0 16.9 1.1 19.0 19.0
Other 18.0 18.0 18.0
Freeable 1.1 .0 1.1
PL/SQL .0 .0 .0 .0
24 NSA2 -------- 11.7 11.0 .0 11.7 11.7
Other 11.7 11.7 11.7
PL/SQL .0 .0 .0 .0
11 LGWR -------- 11.6 11.0 .0 11.6 11.6
Other 11.6 11.6 11.6
PL/SQL .0 .0 .0 .0
19 TNS V1-V3 --- 9.7 2.9 6.4 9.7 10.7
Freeable 6.4 .0 6.4
Other 2.9 2.9 2.9
PL/SQL .2 .2 .2 .2
SQL .1 .0 .1 7.0
10 DBW0 -------- 7.4 6.8 .1 7.4 7.4
Other 7.4 7.4 7.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
Top Process Memory (by component) DB/Inst: FERMDB/fermdg Snaps: 1-6
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 13 SMON -------- 5.0 1.0 2.8 5.0 5.0
Freeable 2.8 .0 2.8
Other 2.2 2.2 2.2
SQL .0 .0 .0 2.1
PL/SQL .0 .0 .0 .0
27 CJQ0 -------- 4.2 1.3 2.6 4.2 4.2
Freeable 2.6 .0 2.6
Other 1.6 1.6 1.6
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
15 MMON -------- 3.7 2.1 1.4 3.7 3.7
Other 2.1 2.1 2.1
Freeable 1.4 .0 1.4
SQL .2 .0 .2 1.1
PL/SQL .1 .1 .1 .1
14 RECO -------- 1.5 .9 .3 1.5 1.5
Other 1.3 1.3 1.3
Freeable .3 .0 .3
SQL .0 .0 .0 .5
PL/SQL .0 .0 .0 .0
34 Q000 -------- 1.5 1.3 .1 1.5 1.5
Other 1.2 1.2 1.2
PL/SQL .2 .2 .2 .2
Freeable .1 .0 .1
SQL .0 .0 .0 .2
17 D000 -------- 1.4 1.3 .0 1.4 1.4
Other 1.4 1.4 1.4
8 DIA0 -------- 1.4 1.2 .1 1.4 1.4
Other 1.3 1.3 1.3
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
35 Q001 -------- 1.2 1.0 .0 1.2 1.2
Other 1.2 1.2 1.2
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
12 CKPT -------- 1.1 .9 .0 1.1 1.1
Other 1.1 1.1 1.1
PL/SQL .0 .0 .0 .0
7 DBRM -------- 1.0 .9 .0 1.0 1.0
Other 1.0 1.0 1.0
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
28 W000 -------- 1.0 .9 .0 1.0 1.0
Other 1.0 1.0 1.0
SQL .0 .0 .0 .0
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Latch Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
AQ deq hash table latch 5 0.0 0 0
ASM db client latch 14 0.0 0 0
ASM map operation hash t 5 0.0 0 0
Change Notification Hash 2 0.0 0 0
Consistent RBA 25 0.0 0 0
DML lock allocation 1,264 0.0 0 0
Event Group Locks 5 0.0 0 0
File State Object Pool P 5 0.0 0 0
I/O Staticstics latch 5 0.0 0 0
IPC stats buffer allocat 5 0.0 0 0
In memory undo latch 151 0.0 0 6 0.0
JS Sh mem access 5 0.0 0 0
JS queue access latch 5 0.0 0 0
JS queue state obj latch 36 0.0 0 0
JS slv state obj latch 5 0.0 0 0
KFC FX Hash Latch 5 0.0 0 0
KFC Hash Latch 5 0.0 0 0
KFCL LE Freelist 5 0.0 0 0
KGNFS-NFS:SHM structure 5 0.0 0 0
KGNFS-NFS:SVR LIST 5 0.0 0 0
KJC message pool free li 5 0.0 0 0
KJCT flow control latch 5 0.0 0 0
KMG MMAN ready and start 2 0.0 0 0
KTF sga latch 0 0 2 0.0
KWQP Prop Status 5 0.0 0 0
Locator state objects po 5 0.0 0 0
Lsod array latch 5 0.0 0 0
Memory Management Latch 5 0.0 0 2 0.0
Memory Queue 5 0.0 0 0
Memory Queue Message Sub 5 0.0 0 0
Memory Queue Message Sub 5 0.0 0 0
Memory Queue Message Sub 5 0.0 0 0
Memory Queue Message Sub 5 0.0 0 0
Memory Queue Subscriber 5 0.0 0 0
Mutex 5 0.0 0 0
Mutex Stats 5 0.0 0 0
OS process 5 0.0 0 0
OS process allocation 14 0.0 0 0
PL/SQL warning settings 5 0.0 0 0
PX hash array latch 5 0.0 0 0
QMT 5 0.0 0 0
SGA blob parent 5 0.0 0 0
SGA bucket locks 5 0.0 0 0
SGA heap locks 5 0.0 0 0
SGA pool locks 5 0.0 0 0
SQL memory manager latch 5 0.0 0 7 0.0
SQL memory manager worka 1,121 0.0 0 0
Shared B-Tree 21 0.0 0 0
Streams Generic 5 0.0 0 0
Testing 5 0.0 0 0
Latch Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
Token Manager 5 0.0 0 0
WCR: sync 5 0.0 0 0
Write State Object Pool 5 0.0 0 0
XDB NFS Security Latch 5 0.0 0 0
XDB unused session pool 5 0.0 0 0
XDB used session pool 5 0.0 0 0
active checkpoint queue 2 0.0 0 0
active service list 10 0.0 0 646 0.0
buffer pool 5 0.0 0 0
business card 5 0.0 0 0
cache buffer handles 896 0.0 0 0
cache buffers chains 50,276 0.0 0 1,597 0.0
cache buffers lru chain 331 0.0 0 1,594 0.0
call allocation 97 0.0 0 0
cas latch 5 0.0 0 0
change notification clie 5 0.0 0 0
channel handle pool latc 5 0.0 0 0
channel operations paren 79 0.0 0 0
checkpoint queue latch 340 0.0 0 1,567 0.0
client/application info 5 0.0 0 0
corrupted undo seg latch 21 0.0 0 0
cp cmon/server latch 5 0.0 0 0
cp pool latch 5 0.0 0 0
cp server hash latch 5 0.0 0 0
cvmap freelist lock 5 0.0 0 0
dml lock allocation 124 0.0 0 0
done queue latch 5 0.0 0 0
dummy allocation 5 0.0 0 0
eighth spare latch - X p 5 0.0 0 0
eleventh spare latch - c 5 0.0 0 0
enqueue freelist latch 5 0.0 0 906 0.0
enqueue hash chains 2,972 0.0 0 0
enqueues 37 0.0 0 0
fifteenth spare latch - 5 0.0 0 0
file cache latch 69 0.0 0 0
flashback copy 5 0.0 0 0
fourteenth spare latch - 5 0.0 0 0
fourth Audit Vault latch 5 0.0 0 0
gc element 5 0.0 0 0
gcs commit scn state 5 0.0 0 0
gcs partitioned table ha 5 0.0 0 0
gcs pcm hashed value buc 5 0.0 0 0
gcs resource freelist 5 0.0 0 0
gcs resource hash 5 0.0 0 0
gcs resource scan list 5 0.0 0 0
gcs resource validate li 5 0.0 0 0
gcs shadows freelist 5 0.0 0 0
ges domain table 5 0.0 0 0
ges enqueue table freeli 5 0.0 0 0
ges group table 5 0.0 0 0
Latch Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
ges process hash list 5 0.0 0 0
ges process parent latch 5 0.0 0 0
ges resource hash list 5 0.0 0 0
ges resource scan list 5 0.0 0 0
ges resource table freel 5 0.0 0 0
ges value block free lis 5 0.0 0 0
global tx hash mapping 5 0.0 0 0
granule operation 5 0.0 0 0
hash table column usage 0 0 8,046 0.0
hash table modification 24 0.0 0 0
heartbeat check 5 0.0 0 0
intra txn parallel recov 5 0.0 0 0
io pool granule metadata 5 0.0 0 0
job workq parent latch 5 0.0 0 0
job_queue_processes para 1 0.0 0 0
k2q lock allocation 5 0.0 0 0
kdlx hb parent latch 5 0.0 0 0
kgb parent 5 0.0 0 0
kgnfs mount latch 5 0.0 0 0
ksfv messages 5 0.0 0 0
ksim group membership ca 5 0.0 0 0
kss move lock 5 0.0 0 0
ksuosstats global area 15 0.0 0 0
ksv class latch 5 0.0 0 0
ksv msg queue latch 5 0.0 0 0
ktm global data 4 0.0 0 0
kwqbsn:qsga 16 0.0 0 0
lgwr LWN SCN 25 0.0 0 0
list of block allocation 39 0.0 0 0
loader state object free 62 0.0 0 0
lob segment dispenser la 5 0.0 0 0
lob segment hash table l 5 0.0 0 0
lob segment query latch 5 0.0 0 0
lock DBA buffer during m 5 0.0 0 0
logical standby cache 5 0.0 0 0
logminer context allocat 5 0.0 0 0
logminer local 5 0.0 0 0
logminer work area 5 0.0 0 0
longop free list parent 5 0.0 0 0
mapped buffers lru chain 5 0.0 0 0
message pool operations 5 0.0 0 0
messages 151 0.0 0 0
mostly latch-free SCN 26 0.0 0 0
msg queue latch 5 0.0 0 0
name-service namespace b 5 0.0 0 0
nineth spare latch - X p 5 0.0 0 0
object queue header heap 133 0.0 0 0
object queue header oper 3,339 0.0 0 0
object stats modificatio 226 0.0 0 0
parallel query alloc buf 5 0.0 0 0
Latch Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
parallel query stats 5 0.0 0 0
peshm 5 0.0 0 0
pesom_free_list 5 0.0 0 0
pesom_hash_node 5 0.0 0 0
post/wait queue 22 0.0 0 10 0.0
process queue 5 0.0 0 0
process queue reference 5 0.0 0 0
qmn task queue latch 4 0.0 0 0
query server freelists 5 0.0 0 0
queuing load statistics 5 0.0 0 0
recovery domain hash lis 5 0.0 0 0
redo allocation 92 3.3 0.0 0 6,641 0.0
redo copy 5 0.0 0 6,641 0.1
redo writing 93 0.0 0 0
resmgr:active threads 5 0.0 0 0
resmgr:actses change gro 5 0.0 0 0
resmgr:actses change sta 5 0.0 0 0
resmgr:plan CPU method 5 0.0 0 0
resmgr:resource group CP 5 0.0 0 0
resmgr:schema config 5 0.0 0 0
resmgr:session queuing 5 0.0 0 0
rm cas latch 5 0.0 0 0
row cache objects 34,013 0.0 0 0
rules engine rule set st 500 0.0 0 0
second Audit Vault latch 5 0.0 0 0
sequence cache 15 0.0 0 0
session allocation 18 0.0 0 12 0.0
session idle bit 38 0.0 0 0
session queue latch 5 0.0 0 0
session switching 5 0.0 0 0
session timer 2 0.0 0 0
seventh spare latch - X 5 0.0 0 0
shared pool 8,563 0.0 0 0
shared pool sim alloc 11 0.0 0 0
shared pool simulator 191 0.0 0 0
sim partition latch 5 0.0 0 0
simulator hash latch 979 0.0 0 0
simulator lru latch 5 0.0 0 788 0.0
sixth spare latch - X pa 5 0.0 0 0
sort extent pool 1 0.0 0 0
space background task la 8 0.0 0 5 0.0
tablespace key chain 5 0.0 0 0
tenth spare latch - X pa 5 0.0 0 0
test excl. parent l0 5 0.0 0 0
test excl. parent2 l0 5 0.0 0 0
thirteenth spare latch - 5 0.0 0 0
transaction allocation 316 0.0 0 0
twelfth spare latch - ch 5 0.0 0 0
twenty-fifth spare latch 5 0.0 0 0
twenty-first spare latch 5 0.0 0 0
Latch Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
twenty-fourth spare latc 5 0.0 0 0
twenty-second spare latc 5 0.0 0 0
twenty-third spare latch 5 0.0 0 0
undo global data 1,980 0.0 0 0
virtual circuit buffers 5 0.0 0 0
virtual circuit holder 5 0.0 0 0
virtual circuit queues 5 0.0 0 0
write info latch 0 0 25 0.0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: FERMDB/fermdg Snaps: 1-6
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_histogram_defs 3,043 15.4 0 0 4,253
dc_objects 1,220 0.0 0 0 1,891
dc_rollback_segments 1 0.0 0 0 22
dc_segments 836 9.6 0 201 560
dc_tablespace_quotas 488 0.0 0 0 2
dc_tablespaces 3,152 0.0 0 0 8
dc_users 3,875 0.0 0 0 242
global database name 4 0.0 0 0 1
-------------------------------------------------------------
Library Cache Activity DB/Inst: FERMDB/fermdg Snaps: 1-6
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 14 28.6 2,221 11.7 53 48
TABLE/PROCEDURE 967 0.0 2,416 4.0 43 0
INDEX 43 0.0 43 100.0 0 0
SQL AREA STATS 101 47.5 101 47.5 0 0
SQL AREA BUILD 101 47.5 0 0 0
-------------------------------------------------------------
Rule Sets DB/Inst: FERMDB/fermdg Snaps: 1-6
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
--共享池建议
Shared Pool Advisory DB/Inst: FERMDB/fermdg End Snap: 6
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
208 .3 48 3,612 143 1.0 3 1.0 30,778
288 .4 56 4,069 143 1.0 3 1.0 30,778
368 .5 56 4,069 143 1.0 3 1.0 30,778
448 .7 56 4,069 143 1.0 3 1.0 30,778
528 .8 56 4,069 143 1.0 3 1.0 30,778
544 .8 56 4,069 143 1.0 3 1.0 30,778
560 .8 56 4,069 143 1.0 3 1.0 30,778
576 .8 56 4,069 143 1.0 3 1.0 30,778
592 .9 56 4,069 143 1.0 3 1.0 30,778
608 .9 56 4,069 143 1.0 3 1.0 30,778
624 .9 56 4,069 143 1.0 3 1.0 30,778
640 .9 56 4,069 143 1.0 3 1.0 30,966
656 1.0 56 4,069 143 1.0 3 1.0 31,232
672 1.0 56 4,069 143 1.0 3 1.0 31,497
688 1.0 56 4,069 143 1.0 3 1.0 31,783
704 1.0 56 4,069 143 1.0 3 1.0 31,794
720 1.0 56 4,069 143 1.0 3 1.0 31,794
736 1.1 56 4,069 143 1.0 3 1.0 31,794
752 1.1 56 4,069 143 1.0 3 1.0 31,794
768 1.1 56 4,069 143 1.0 3 1.0 31,794
784 1.1 56 4,069 143 1.0 3 1.0 31,794
800 1.2 56 4,069 143 1.0 3 1.0 31,794
816 1.2 56 4,069 143 1.0 3 1.0 31,794
832 1.2 56 4,069 143 1.0 3 1.0 31,794
848 1.2 56 4,069 143 1.0 3 1.0 31,794
928 1.3 56 4,069 143 1.0 3 1.0 31,794
1,008 1.5 56 4,069 143 1.0 3 1.0 31,794
1,088 1.6 56 4,069 143 1.0 3 1.0 31,794
1,168 1.7 56 4,069 143 1.0 3 1.0 31,794
1,248 1.8 56 4,069 143 1.0 3 1.0 31,794
1,328 1.9 56 4,069 143 1.0 3 1.0 31,794
1,408 2.0 56 4,069 143 1.0 3 1.0 31,794
-------------------------------------------------------------
SGA Target Advisory DB/Inst: FERMDB/fermdg End Snap: 6
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
940 .3 21 1.0 10,506
1,880 .5 21 1.0 10,506
2,820 .8 21 1.0 10,506
3,760 1.0 21 1.0 10,506
4,700 1.3 21 1.0 10,506
5,640 1.5 21 1.0 10,506
6,580 1.8 21 1.0 10,506
7,520 2.0 21 1.0 10,506
-------------------------------------------------------------
SGA Memory Summary DB/Inst: FERMDB/fermdg Snaps: 1-6
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 3,137,339,392
Fixed Size 2,259,240
Redo Buffers 13,680,640
Variable Size 771,753,688
-------------------- --------------------
sum 3,925,032,960
-------------------------------------------------------------
SGA breakdown difference DB/Inst: FERMDB/fermdg Snaps: 1-6
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 16.0 16.0 0.00
large PX msg pool .5 .5 0.00
large free memory 31.5 31.5 0.00
shared KGLH0 22.8 23.5 2.85
shared KGLS 12.1 12.3 1.34
shared SQLA 29.6 31.1 5.11
shared db_block_hash_buckets 22.3 22.3 0.00
shared file state object 7.5 7.5 0.00
shared free memory 475.9 473.1 -0.59
shared row cache 8.2 8.2 0.00
shared write state object 13.4 13.4 0.00
buffer_cache 2,992.0 2,992.0 0.00
fixed_sga 2.2 2.2 0.00
log_buffer 13.0 13.0 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: FERMDB/fermdg Snaps: 1-6
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 25.27 25.38 .43
Cursor to Parent ratio: 1.08 1.08 -.08
Total Cursors: 1,191 1,204 1.08
Total Parents: 1,105 1,118 1.16
-------------------------------------------------------------
--参数文件
init.ora Parameters DB/Inst: FERMDB/fermdg Snaps: 1-6
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /oracle/app/oracle/admin/fermdg/adump
audit_trail DB
compatible 11.2.0.4.0
control_files /oracle/app/oracle/oradata/fermdg
/control01.ctl, /oracle/app/oracl
e/fast_recovery_area/fermdg/contr
ol02.ctl
db_block_size 8192
db_create_file_dest /oracle/app/oracle/oradata/fermdg
db_domain
db_file_name_convert /oracle/app/oracle/oradata/fermdb
, /oracle/app/oracle/oradata/ferm
dg
db_name fermdb
db_recovery_file_dest /oracle/app/oracle/fast_recovery_
area
db_recovery_file_dest_size 4385144832
db_unique_name fermdg
diagnostic_dest /oracle/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=fermdgXDB
)
fal_client fermdg
fal_server fermdg
log_archive_config DG_CONFIG=(fermdb,fermdg)
log_archive_dest_1 LOCATION=/oracle/app/arch VALID_F
OR=(ALL_LOGFILES,ALL_ROLES) DB_UN
IQUE_NAME=fermdg
log_archive_dest_2 SERVICE=fermdb LGWR ASYNC VALID_F
OR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=fermdb
log_file_name_convert /oracle/app/oracle/oradata/fermdb
, /oracle/app/oracle/oradata/ferm
dg
open_cursors 300
optimizer_capture_sql_plan_ba FALSE
optimizer_use_sql_plan_baseli FALSE
pga_aggregate_target 1310720000
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 3942645760
undo_tablespace UNDOTBS1
-------------------------------------------------------------
End of Report ( sp_1_6.lst )
4.statspack管理
--删除历史数据,可以清理某个区间的数据。
SQL> start ?/rdbms/admin/sppurge.sql
Enter value for losnapid:1 --开始SNAPID;
Enter value for hisnapid:6 --结束SNAPID;
SQL> start ?/rdbms/admin/sptrunc.sql --清空表中的数据
Enter value for begin_or_exit: --回车
--卸载statspack工具
--会删除表，视图，同义词。
SQL> start ?/rdbms/admin/spdrop.sql 5.总结
STATSPACK可以收集如下信息。不包含SQL;
(1)缓存
(2)可以收集LOAD PROFILE,
(3)参数文件
(4)SGA,PGA的建议值
(5)内存，数据库运行时间
(6)Top 5 Timed Events :前5事件。
(7)Memory Statistics :内存统计
(8)等待事件
(9)OS Statistics:OS ,IO,CPU的使用情况。
(10)进程内存情况。
(11)LATCH统计信息。
(12)库缓存统计信息
扫一扫
686
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
