</p> 监控 SGA 中字典缓冲区的命中率
8 X% k, v: R" c7 T3 i select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
$ D o& Z! ~2 y& Z (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"9 p% N; I) {6 \. o6 ~! K9 h
from v$rowcache
! e/ }! P- H D where gets+getmisses 0' \1 _: ~% [' ]! [: X' q
group by parameter, gets, getmisses;! ~/ m H+ [; w
监控 SGA 中共享缓存区的命中率,应该小于1%6 Y, q/ o. K8 f( d, M+ f
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",3 A* v1 d5 c1 y
sum(reloads)/sum(pins) *100 libcache" S; R! d. U1 s- L$ f, d% D; d
from v$librarycache;
* \( J' F/ n) m7 ?" r+ q select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
" r3 ?5 K5 z% l" V! W3 n) W from v$librarycache;
* [0 u4 ?% H/ t 监控 SGA 的命中率
+ ^9 Z. V! e4 x' h" {$ _* [ select a.value + b.value "logical_reads", c.value "phys_reads",
- t( f! d* X4 }# g& }( I3 p round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" i, d9 m1 p2 k- G
from v$sysstat a, v$sysstat b, v$sysstat c9 i. B k0 a0 f
where a.statistic# = 38 and b.statistic# = 39+ g& A- B( \& N3 N+ \3 L
and c.statistic# = 40;/ q- J% R5 h! P3 t: ~0 U, h
监控 SGA 中重做日志缓存区的命中率,应该小于1%' |: G) e7 F( l9 E$ f; m1 I5 P+ {
SELECT name, gets, misses, immediate_gets, immediate_misses,. t) l, B, W9 j# _
Decode(gets,0,0,misses/gets*100) ratio1,6 P; X3 L+ l" n
Decode(immediate_gets+immediate_misses,0,0,
2 s ~6 `/ K; ]9 z- F immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
0 W8 Q4 b3 P8 H5 A5 ]) s1 c FROM v$latch WHERE name IN (redo allocation, redo copy);7 Q& ~# Y$ B, }( o& h0 u
显示所有数据库对象的类别和大小
2 {2 B+ G6 m# p7 P) _ select count(name) num_instances ,type ,sum(source_size) source_size ,: F8 R9 Y, S5 e3 @' z! I* R& S
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,$ y* k% W" e% }' [3 J
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
, N. R5 v1 L" x7 m& O from dba_object_size
- M( L) k; y7 @% F8 _8 K2 S3 L group by type order by 2;
) y% |9 U0 p( n; [) }: X# d; a 监控内存和硬盘的排序比率,最好使它小于 .10,增加 sort_area_size4 V" j5 T9 x' M# f; q
SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk));
* v$ Z5 S; _/ C/ q3 t, q 监控字典缓冲区# K+ G/ x4 ?7 h( P" M
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;0 C/ h; q) V4 u3 ]
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
* M8 R% r& p: P1 A) N9 w- A1 [& B SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;# [! Z: {! k9 e7 `2 K/ F! B# ^
后者除以前者,此比率小于1%,接近0%为好。; {2 [, T7 d1 X! }' w- a: R( w$ x
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
[5 S% H8 p# d% x% m FROM V$ROWCACHE# S+ v& x! i: s0 e' A7 l0 F, a# p! g( D
监控 MTS! F7 f, _/ i" R7 S, f
select busy/(busy+idle) "shared servers busy" from v$dispatcher;* a, _; B# h& J4 s: L# h9 L
此值大于0.5时,参数需加大
4 ^3 @3 K1 f# e7 F% e% S select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type=dispatcher;
" G- i; u" [, h/ F select count(*) from v$dispatcher;
0 F3 h7 t& I3 l8 G- a, O8 W, A select servers_highwater from v$mts;- g$ c: D. _; R2 m+ S6 t
servers_highwater接近mts_max_servers时,参数需加大# d8 T+ a s' |% I! w# n
碎片程度
$ I+ p, e) ^ C1 t select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name3 }3 e0 { D! x) ~2 U' P6 P
having count(tablespace_name)>10;
9 W1 `. D/ k8 k: ~2 p alter tablespace name coalesce;
$ L/ ^( H9 l/ C alter table name deallocate unused;
! t. k+ b6 D8 x J create or replace view ts_blocks_v as
! Q/ X9 l1 Q+ f0 T% m0 t select tablespace_name,block_id,bytes,blocks,free space segment_name from dba_free_space0 v9 C0 |; [+ I& v5 x x2 i; Q
union all9 H# J N# g v, @ r+ |% j; h2 J
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;+ {/ Z' a3 C: e, Q) a2 w
select * from ts_blocks_v;# K3 T/ q E7 |% T: H1 d2 f
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
e# [, e4 i6 `: ]& q( ? group by tablespace_name; |