现象，系统不定时的出现无法登陆的问题（登陆时有写数据库安全日志的过程），系统压力非常小，并有时还报17002错误，大约20多分钟系统能自己恢复正常，分析报告前五个等待事件全和写IO有关.在系统出问题期间，俺 自己写的java测试程序能轻易获取到数据库连接，且写数据到库中速度也很快
STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
EIDC 2575538572 EIDC 1 9.2.0.5.0 NO db2 Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 12-Mar-08 11:28:29 24 8.7
End Snap: 2 12-Mar-08 11:40:05 41 5.3
Elapsed: 11.60 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 304M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 512KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 9,661.41 40,508.07
Logical reads: 71.79 301.00
Block changes: 29.97 125.66
Physical reads: 4.00 16.78
Physical writes: 4.07 17.08
User calls: 19.74 82.75
Parses: 3.45 14.46
Hard parses: 0.03 0.13
Sorts: 1.49 6.24
Logons: 0.05 0.22
Executes: 4.59 19.25
Transactions: 0.24 % Blocks changed per Read: 41.75 Recursive Call %: 47.77
Rollback per transaction %: 0.00 Rows per Sort: 79.44Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 99.90
Library Hit %: 98.89 Soft Parse %: 99.08
Execute to Parse %: 24.87 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 83.33 % Non-Parse CPU: 97.22 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.95 91.02
% SQL with executions>1: 52.11 52.26
% Memory for SQL w/exec>1: 53.70 54.04Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 4 65.38
log file sync 166 1 19.84
db file parallel write 46 0 5.35
control file parallel write 227 0 2.81
log file parallel write 363 0 2.01
-------------------------------------------------------------
Wait Events for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 166 1 1 7 1.0
db file parallel write 46 0 0 6 0.3
control file parallel write 227 0 0 1 1.4
log file parallel write 363 260 0 0 2.2
direct path read 65 0 0 2 0.4
process startup 4 0 0 18 0.0
local write wait 30 0 0 1 0.2
control file sequential read 166 0 0 0 1.0
LGWR wait for redo copy 6 0 0 1 0.0
direct path write 4 0 0 2 0.0
SQL*Net more data to client 17 0 0 0 0.1
SQL*Net message from client 13,685 0 7,657 560 82.4
jobq slave wait 699 667 2,037 2914 4.2
wakeup time manager 23 23 665 28913 0.1
SQL*Net more data from clien 2 0 0 141 0.0
SQL*Net message to client 13,702 0 0 0 82.5
-------------------------------------------------------------
Background Wait Events for DB: EIDC Instance: EIDC Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 46 0 0 6 0.3
control file parallel write 227 0 0 1 1.4
log file parallel write 363 260 0 0 2.2
control file sequential read 92 0 0 0 0.6
LGWR wait for redo copy 6 0 0 1 0.0
rdbms ipc message 1,305 933 3,837 2940 7.9
pmon timer 231 231 677 2930 1.4
smon timer 2 2 600 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
33,090 1 33,090.0 66.2 3.45 4.78 864547796
Module: SQL*Plus
BEGIN statspack.snap(i_snap_level=>6,i_modify_parameter=>'true')
; END; 24,691 1 24,691.0 49.4 0.50 0.48 2389506893
Module: SQL*Plus
INSERT INTO STATS$SQL_PLAN ( PLAN_HASH_VALUE , ID , OPERATION ,
OPTIONS , OBJECT_NODE , OBJECT# , OBJECT_OWNER , OBJECT_NAME , O
PTIMIZER , PARENT_ID , DEPTH , POSITION , SEARCH_COLUMNS , COST
, CARDINALITY , BYTES , OTHER_TAG , PARTITION_START , PARTITION_
STOP , PARTITION_ID , OTHER , DISTRIBUTION , CPU_COST , IO_COST
STATSPACK report forDB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
EIDC 2575538572 EIDC 1 9.2.0.5.0 NO db2 Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 12-Mar-08 11:28:29 24 8.7
End Snap: 2 12-Mar-08 11:40:05 41 5.3
Elapsed: 11.60 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 304M Std Block Size: 8K
Shared Pool Size: 512M Log Buffer: 512KLoad Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 9,661.41 40,508.07
Logical reads: 71.79 301.00
Block changes: 29.97 125.66
Physical reads: 4.00 16.78
Physical writes: 4.07 17.08
User calls: 19.74 82.75
Parses: 3.45 14.46
Hard parses: 0.03 0.13
Sorts: 1.49 6.24
Logons: 0.05 0.22
Executes: 4.59 19.25
Transactions: 0.24 % Blocks changed per Read: 41.75 Recursive Call %: 47.77
Rollback per transaction %: 0.00 Rows per Sort: 79.44Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 99.90
Library Hit %: 98.89 Soft Parse %: 99.08
Execute to Parse %: 24.87 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 83.33 % Non-Parse CPU: 97.22 Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.95 91.02
% SQL with executions>1: 52.11 52.26
% Memory for SQL w/exec>1: 53.70 54.04Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 4 65.38
log file sync 166 1 19.84
db file parallel write 46 0 5.35
control file parallel write 227 0 2.81
log file parallel write 363 0 2.01
-------------------------------------------------------------
Wait Events for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 166 1 1 7 1.0
db file parallel write 46 0 0 6 0.3
control file parallel write 227 0 0 1 1.4
log file parallel write 363 260 0 0 2.2
direct path read 65 0 0 2 0.4
process startup 4 0 0 18 0.0
local write wait 30 0 0 1 0.2
control file sequential read 166 0 0 0 1.0
LGWR wait for redo copy 6 0 0 1 0.0
direct path write 4 0 0 2 0.0
SQL*Net more data to client 17 0 0 0 0.1
SQL*Net message from client 13,685 0 7,657 560 82.4
jobq slave wait 699 667 2,037 2914 4.2
wakeup time manager 23 23 665 28913 0.1
SQL*Net more data from clien 2 0 0 141 0.0
SQL*Net message to client 13,702 0 0 0 82.5
-------------------------------------------------------------
Background Wait Events for DB: EIDC Instance: EIDC Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 46 0 0 6 0.3
control file parallel write 227 0 0 1 1.4
log file parallel write 363 260 0 0 2.2
control file sequential read 92 0 0 0 0.6
LGWR wait for redo copy 6 0 0 1 0.0
rdbms ipc message 1,305 933 3,837 2940 7.9
pmon timer 231 231 677 2930 1.4
smon timer 2 2 600 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
33,090 1 33,090.0 66.2 3.45 4.78 864547796
Module: SQL*Plus
BEGIN statspack.snap(i_snap_level=>6,i_modify_parameter=>'true')
; END; 24,691 1 24,691.0 49.4 0.50 0.48 2389506893
Module: SQL*Plus
INSERT INTO STATS$SQL_PLAN ( PLAN_HASH_VALUE , ID , OPERATION ,
OPTIONS , OBJECT_NODE , OBJECT# , OBJECT_OWNER , OBJECT_NAME , O
PTIMIZER , PARENT_ID , DEPTH , POSITION , SEARCH_COLUMNS , COST
, CARDINALITY , BYTES , OTHER_TAG , PARTITION_START , PARTITION_
STOP , PARTITION_ID , OTHER , DISTRIBUTION , CPU_COST , IO_COST
Instance Activity Stats for DB: EIDC Instance: EIDC Snaps: 1 -2Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 360 0.5 2.2
CPU used when call started 360 0.5 2.2
CR blocks created 25 0.0 0.2
DBWR checkpoint buffers written 20 0.0 0.1
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 4 0.0 0.0
DBWR undo block writes 8 0.0 0.1
SQL*Net roundtrips to/from client 13,655 19.6 82.3
SQL*Net roundtrips to/from dblink 0 0.0 0.0
active txn count during cleanout 267 0.4 1.6
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 816 1.2 4.9
branch node splits 0 0.0 0.0
buffer is not pinned count 24,066 34.6 145.0
buffer is pinned count 96,439 138.6 581.0
bytes received via SQL*Net from c 1,138,828 1,636.3 6,860.4
bytes received via SQL*Net from d 0 0.0 0.0
bytes sent via SQL*Net to client 1,039,678 1,493.8 6,263.1
bytes sent via SQL*Net to dblink 0 0.0 0.0
calls to get snapshot scn: kcmgss 6,890 9.9 41.5
calls to kcmgas 630 0.9 3.8
calls to kcmgcs 177 0.3 1.1
change write time 10 0.0 0.1
cleanout - number of ktugct calls 270 0.4 1.6
cluster key scan block gets 3,382 4.9 20.4
cluster key scans 3,368 4.8 20.3
commit cleanout failures: callbac 9 0.0 0.1
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 1,202 1.7 7.2
commit cleanouts successfully com 1,193 1.7 7.2
commit txn count during cleanout 122 0.2 0.7
consistent changes 78 0.1 0.5
consistent gets 31,851 45.8 191.9
consistent gets - examination 12,596 18.1 75.9
cursor authentications 0 0.0 0.0
data blocks consistent reads - un 48 0.1 0.3
db block changes 20,859 30.0 125.7
db block gets 18,115 26.0 109.1
deferred (CURRENT) block cleanout 585 0.8 3.5
enqueue conversions 26 0.0 0.2
enqueue deadlocks 0 0.0 0.0
enqueue releases 1,684 2.4 10.1
enqueue requests 1,684 2.4 10.1
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 3,196 4.6 19.3
free buffer inspected 0 0.0 0.0
free buffer requested 900 1.3 5.4
hot buffers moved to head of LRU 0 0.0 0.0
immediate (CR) block cleanout app 2 0.0 0.0
immediate (CURRENT) block cleanou 247 0.4 1.5
index fast full scans (full) 0 0.0 0.0
index fetch by key 11,083 15.9 66.8
index scans kdiixs1 3,714 5.3 22.4
leaf node 90-10 splits 17 0.0 0.1
Instance Activity Stats for DB: EIDC Instance: EIDC Snaps: 1 -2Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
leaf node splits 116 0.2 0.7
logons cumulative 37 0.1 0.2
messages received 422 0.6 2.5
messages sent 422 0.6 2.5
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 14,531 20.9 87.5
opened cursors cumulative 2,341 3.4 14.1
parse count (failures) 0 0.0 0.0
parse count (hard) 22 0.0 0.1
parse count (total) 2,401 3.5 14.5
parse time cpu 10 0.0 0.1
parse time elapsed 12 0.0 0.1
physical reads 2,785 4.0 16.8
physical reads direct 2,785 4.0 16.8
physical writes 2,835 4.1 17.1
physical writes direct 2,785 4.0 16.8
physical writes non checkpoint 2,815 4.0 17.0
pinned buffers inspected 0 0.0 0.0
prefetched blocks 0 0.0 0.0
process last non-idle time 39,774,663,015 57,147,504.3 ############
recovery blocks read 0 0.0 0.0
recursive calls 12,563 18.1 75.7
recursive cpu usage 320 0.5 1.9
redo blocks written 13,725 19.7 82.7
redo entries 10,906 15.7 65.7
redo ordering s 0 0.0 0.0
redo size 6,724,340 9,661.4 40,508.1
redo synch time 111 0.2 0.7
redo synch writes 166 0.2 1.0
redo wastage 96,356 138.4 580.5
redo write time 33 0.1 0.2
redo writer latching time 0 0.0 0.0
redo writes 363 0.5 2.2
rollback changes - undo records a 4 0.0 0.0
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP
173 0 1.7 16.1 141 0 0 0.0
UNDOTBS1
0 0 0.0 12 0 0 0.0
EIDC_DATA
0 0 0.0 5 0 0 0.0
SYSTEM
0 0 0.0 3 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: EIDC Instance: EIDC Snaps: 1 -2
->ordered by Tablespace, FileTablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
EIDC_DATA /dev/reidc_data17
0 0 1 0 0
/dev/reidc_data32
0 0 2 0 0
/dev/reidc_data37
0 0 2 0 0SYSTEM /dev/rora9_system
0 0 3 0 0TEMP /dev/rora9_temp
173 0 1.7 16.1 141 0 0UNDOTBS1 /dev/rora9_undo
0 0 12 0 0 -------------------------------------------------------------
Buffer Pool Statistics for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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 37,715 100.0 50,247 0 50 0 0 0
-------------------------------------------------------------Instance Recovery Stats for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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 144 18 2719 48138 46918 471852 46918
E 144 21 3411 60813 60274 471852 60274
-------------------------------------------------------------Buffer Pool Advisory for DB: EIDC Instance: EIDC End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 32 .1 3,970 6.72 1,174,629
D 64 .2 7,940 3.03 529,762
D 96 .3 11,910 2.03 355,368
D 128 .4 15,880 1.41 245,956
D 160 .5 19,850 1.18 206,109
D 192 .6 23,820 1.06 185,262
D 224 .7 27,790 1.00 175,503
D 256 .8 31,760 1.00 174,986
D 288 .9 35,730 1.00 174,838
D 304 1.0 37,715 1.00 174,690
D 320 1.1 39,700 1.00 174,542
D 352 1.2 43,670 1.00 174,025
D 384 1.3 47,640 0.99 173,359
D 416 1.4 51,610 0.99 173,359
D 448 1.5 55,580 0.99 173,359
D 480 1.6 59,550 0.99 173,359
D 512 1.7 63,520 0.99 173,359
D 544 1.8 67,490 0.99 173,359
D 576 1.9 71,460 0.99 173,359
D 608 2.0 75,430 0.99 173,359
D 640 2.1 79,400 0.99 173,359
-------------------------------------------------------------
-> 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 controlPGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
82.9 59 12 %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 200 173 44.5 0.0 .0 .0 .0 10,240
E 200 170 52.8 0.0 .0 .0 .0 10,240
-------------------------------------------------------------PGA Aggr Target Histogram for DB: EIDC Instance: EIDC Snaps: 1 -2
-> Optimal Executions are purely in-memory operations Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 1,100 1,100 0 0
16K 32K 4 4 0 0
32K 64K 1 1 0 0
64K 128K 2 2 0 0
512K 1024K 4 4 0 0
1M 2M 23 23 0 0
2M 4M 2 2 0 0
8M 16M 1 0 1 0
-------------------------------------------------------------PGA Memory Advisory for DB: EIDC Instance: EIDC End Snap: 2
-> 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
---------- ------- ---------------- ---------------- -------- ----------
25 0.1 180,424.6 63.9 100.0 172
50 0.3 180,424.6 27.4 100.0 2
100 0.5 180,424.6 12.1 100.0 0
150 0.8 180,424.6 12.1 100.0 0
200 1.0 180,424.6 12.1 100.0 0
240 1.2 180,424.6 12.1 100.0 0
280 1.4 180,424.6 0.0 100.0 0
320 1.6 180,424.6 0.0 100.0 0
360 1.8 180,424.6 0.0 100.0 0
400 2.0 180,424.6 0.0 100.0 0
600 3.0 180,424.6 0.0 100.0 0
800 4.0 180,424.6 0.0 100.0 0
1,200 6.0 180,424.6 0.0 100.0 0
1,600 8.0 180,424.6 0.0 100.0 0
-------------------------------------------------------------
->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 3.0 0.00 0 0 0 0
1 90.0 0.00 7,856 0 0 0
2 689.0 0.00 2,039,154 6 0 5
3 105.0 0.00 18,298 0 0 1
4 108.0 0.00 12,606 0 0 0
5 79.0 0.00 5,938 0 0 0
6 91.0 0.00 11,242 0 0 0
7 70.0 0.00 8,408 0 0 0
8 110.0 0.00 9,880 0 0 0
9 103.0 0.00 11,450 0 0 0
10 74.0 0.00 5,224 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: EIDC Instance: EIDC Snaps: 1 -2
->Optimal Size should be larger than Avg ActiveRBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 909,312 73,715 2,220,032
2 4,186,112 1,183,376 4,186,112
3 712,704 73,809 3,268,608
4 2,088,960 149,633 3,268,608
5 2,220,032 356,636 2,220,032
6 2,220,032 356,636 3,268,608
7 1,171,456 356,636 3,268,608
8 2,220,032 356,636 3,268,608
9 1,171,456 356,636 3,268,608
10 2,220,032 356,636 3,268,608
-------------------------------------------------------------
Undo Segment Summary for DB: EIDC Instance: EIDC Snaps: 1 -2
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsedUndo 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 960 7,811,070 2 2 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: EIDC Instance: EIDC Snaps: 1 -2
-> 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-Mar 11:35 960 ######## 2 2 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: EIDC Instance: EIDC Snaps: 1 -2
->"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 362 0.0 0 0
FOB s.o list latch 12 0.0 0 0
SQL memory manager latch 1 0.0 0 227 0.0
SQL memory manager worka 15,768 0.0 0 0
active checkpoint queue 301 0.0 0 0
cache buffer handles 248 0.0 0 0
cache buffers chains 125,506 0.0 0 788 0.0
cache buffers lru chain 143 0.0 0 2,996 0.0
channel handle pool latc 33 0.0 0 0
channel operations paren 522 0.0 0 0
checkpoint queue latch 27,040 0.0 0 792 0.0
child cursor hash table 303 0.0 0 0
dml lock allocation 1,015 0.0 0 0
dummy allocation 57 3.5 0.0 0 0
enqueue hash chains 3,366 0.0 0 0
enqueues 2,979 0.1 0.0 0 0
event group latch 25 0.0 0 0
hash table column usage 12 0.0 0 52 0.0
job workq parent latch 0 0 25 0.0
job_queue_processes para 44 0.0 0 0
ktm global data 2 0.0 0 0
kwqit: protect wakeup ti 23 0.0 0 0
lgwr LWN SCN 509 0.0 0 0
library cache 86,884 0.0 0.0 0 205 0.0
library cache load lock 54 0.0 0 0
library cache pin 19,236 0.0 0 0
library cache pin alloca 10,649 0.0 0 0
list of block allocation 54 0.0 0 0
loader state object free 6 0.0 0 0
messages 3,437 0.0 0.0 0 0
mostly latch-free SCN 510 0.0 0 0
ncodef allocation latch 11 0.0 0 0
post/wait queue 249 0.0 0 165 0.0
process allocation 25 0.0 0 25 0.0
process group creation 33 0.0 0 0
redo allocation 11,754 0.0 0.0 0 0
redo copy 0 0 10,888 0.1
redo writing 1,865 0.0 0 0
row cache enqueue latch 3,031 0.0 0 0
row cache objects 3,700 0.0 0 0
sequence cache 499 0.0 0 0
session allocation 4,011 0.1 0.0 0 0
session idle bit 29,139 0.0 0.0 0 0
session switching 11 0.0 0 0
session timer 232 0.0 0 0
shared pool 14,987 0.0 0.0 0 0
simulator hash latch 1,149 0.0 0 0
simulator lru latch 51 0.0 0 8 0.0
sort extent pool 70 0.0 0 0
spilled msgs queues list 23 0.0 0 0
Latch Activity for DB: EIDC Instance: EIDC Snaps: 1 -2
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
transaction allocation 50 0.0 0 0
transaction branch alloc 11 0.0 0 0
undo global data 2,024 0.0 0 0
user lock 74 0.0 0 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: EIDC Instance: EIDC Snaps: 1 -2
->"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_histogram_defs 552 78.6 0 0 3,911
dc_object_ids 116 0.0 0 0 1,630
dc_objects 222 0.0 0 0 2,232
dc_profiles 33 0.0 0 0 1
dc_rollback_segments 22 0.0 0 0 12
dc_segments 133 0.0 0 1 1,673
dc_sequences 8 0.0 0 8 14
dc_tablespace_quotas 1 0.0 0 1 4
dc_tablespaces 161 0.0 0 0 17
dc_user_grants 296 0.0 0 0 23
dc_usernames 68 0.0 0 0 21
dc_users 584 0.0 0 0 22
-------------------------------------------------------------
Library Cache Activity for DB: EIDC Instance: EIDC Snaps: 1 -2
->"Pct Misses" should be very low Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
CLUSTER 2 0.0 3 0.0 0 0
INDEX 92 0.0 92 0.0 0 0
SQL AREA 2,369 0.1 9,137 0.7 0 0
TABLE/PROCEDURE 475 0.0 996 5.2 0 0
TRIGGER 49 0.0 49 0.0 0 0
-------------------------------------------------------------
-> 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
----------- ----- ---------- ------------ ------------ ------- ---------------
256 .5 257 55,034 849,225 1.0 161,910,743
320 .6 320 71,646 849,226 1.0 161,914,329
384 .8 383 88,625 849,228 1.0 161,917,890
448 .9 446 105,309 849,231 1.0 161,924,416
512 1.0 511 119,051 849,235 1.0 161,928,535
576 1.1 574 131,661 849,235 1.0 161,929,665
640 1.3 637 143,765 849,236 1.0 161,930,531
704 1.4 700 154,818 849,236 1.0 161,930,851
768 1.5 757 163,753 849,239 1.0 161,931,873
832 1.6 757 163,753 849,239 1.0 161,931,873
896 1.8 757 163,753 849,239 1.0 161,931,873
960 1.9 757 163,753 849,239 1.0 161,931,873
1,024 2.0 757 163,753 849,239 1.0 161,931,873
-------------------------------------------------------------
SGA Memory Summary for DB: EIDC Instance: EIDC Snaps: 1 -2SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 318,767,104
Fixed Size 743,792
Redo Buffers 798,720
Variable Size 603,979,776
----------------
sum 924,289,392
-------------------------------------------------------------
SGA breakdown difference for DB: EIDC Instance: EIDC Snaps: 1 -2Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 16,777,216 16,777,216 0.00
large free memory 16,777,216 16,777,216 0.00
shared 1M buffer 1,056,768 1,056,768 0.00
shared Checkpoint queue 1,026,560 1,026,560 0.00
shared FileOpenBlock 1,191,104 1,191,104 0.00
shared KGK heap 7,000 7,000 0.00
shared KGLS heap 3,344,112 3,348,560 0.13
shared KQR L PO 3,958,232 3,958,232 0.00
shared KQR M PO 2,999,880 3,193,688 6.46
shared KQR S SO 5,888 5,888 0.00
shared KQR X PO 2,576 2,576 0.00
shared KSXR pending messages que 853,952 853,952 0.00
shared KSXR receive buffers 1,034,000 1,034,000 0.00
shared MTTR advisory 85,680 85,680 0.00
shared PL/SQL DIANA 5,787,560 5,787,560 0.00
shared PL/SQL MPCODE 2,283,312 2,290,248 0.30
shared PL/SQL PPCODE 49,664 49,664 0.00
shared PL/SQL SOURCE 5,160 5,160 0.00
shared PLS non-lib hp 2,088 2,088 0.00
shared dictionary cache 3,229,952 3,229,952 0.00
shared errors 441,744 441,744 0.00
shared event statistics per sess 1,913,520 1,913,520 0.00
shared fixed allocation callback 552 552 0.00
shared free memory 51,596,928 51,234,208 -0.70
shared joxs heap init 4,240 4,240 0.00
shared ksm_file2sga region 370,496 370,496 0.00
shared library cache 162,347,408 162,115,200 -0.14
shared message pool freequeue 771,984 771,984 0.00
shared miscellaneous 44,048,664 44,513,784 1.06
shared parameters 86,984 87,384 0.46
shared partitioning d 110,456 110,456 0.00
shared sessions 459,680 459,680 0.00
shared sim memory hea 328,304 328,304 0.00
shared sql area 280,677,384 280,600,536 -0.03
shared table definiti 7,224 9,352 29.46
shared transaction 327,536 327,536 0.00
shared trigger defini 6,800 5,736 -15.65
shared trigger inform 960 960 0.00
shared trigger source 992 992 0.00
buffer_cache 318,767,104 318,767,104 0.00
fixed_sga 743,792 743,792 0.00
log_buffer 787,456 787,456 0.00
-------------------------------------------------------------
init.ora Parameters for DB: EIDC Instance: EIDC Snaps: 1 -2 End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest /opt/oracle/admin/EIDC/bdump
compatible 9.2.0.0.0
control_files /dev/rora9_ctl1, /dev/rora9_ctl2,
core_dump_dest /opt/oracle/admin/EIDC/cdump
db_block_size 8192
db_cache_size 318767104
db_domain
db_file_multiblock_read_count 16
db_name EIDC
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name EIDC
java_pool_size 16777216
job_queue_processes 10
large_pool_size 16777216
log_archive_dest /opt/oracle/product/dbs/arch
open_cursors 350
optimizer_mode FIRST_ROWS
pga_aggregate_target 209715200
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 536870912
sort_area_size 524288
spfile /dev/rora9_spfile
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest /opt/oracle/admin/EIDC/udump
-------------------------------------------------------------End of Report
两点：
1。log buffer 只有512k， 太小，所以 log file sync 等待很多，建议加大， 开到10m吧。2. Execute to Parse %: 24.87 ， 语句重用率太低，可能是缺少绑定变量