Oracle 10G RAC巡检脚本

Oracle 10G RAC巡检脚本

===========================SRVCTL srvctl check============================
mDd(y"o237577001.列出配置的所有数据库
*jf#XS3R[o23757700srvctl config database

2.显示指定集群数据库的所有服务ITPUB个人空间p U*pu9[~
srvctl config service -d GDTV

3.查看所有实例和服务的状态
,C$f%@9?3Ui0jxi23757700srvctl status database -d GDTV

ITPUB个人空间j-m)[!|p%d2M.O[,m
4.查看单个实例的状态
%i:N3R*dUZD~23757700srvctl status service -d GDTV -s

5.特定节点上节点应用程序的状态
7lU Y+P;Ht3H23757700srvctl status nodeapps -n DBSERVER1
I2_{H;T_ `e23757700srvctl status nodeapps -n DBSERVER2

6.列出RAC数据库的配置
;W3vf#TI1S ~23757700srvctl config database -d GDTV

O!|t9u7S7a U237577007.显示节点应用程序的配置 —（VIP、GSD、ONS、监听器）ITPUB个人空间u-} \G:}
srvctl config nodeapps -n DBSERVER1 -a -g -s -l
](oc'u9MbI?23757700srvctl config nodeapps -n DBSERVER2 -a -g -s -l

=========================================process check==================ITPUB个人空间;E;Z-z]6c{|,j.NB0h
8. Oracle进程检查

Ps –ef |grep ora_

9. CRS进程检查

ps -ef | grep oracm 

$ps –df |grep d.bin

       应有：crsd.bin ocssd.bin evmd.bin

crsctl check crs

crs_stat –t

crs_stat –ls

nK2{N@EA-\9qN*Ofmu23757700=================alert.log check====================================

8.查看各个instance的alert.log

=====================instance parameters check========================

9.查看spfile.ora  查看profile

T$d,r Yw,c23757700====================listener status check==============================
3xz,HVk0X2375770010.ITPUB个人空间!q,K0y `'fv h
$hostname
0a^D.Y!_1b C1M23757700$export ORACLE_SID=GDTV1ITPUB个人空间|;Y d} {(F1h1rt)S
$lsnrctl status

$hostnameITPUB个人空间)M-C3J(t VOTl
$export ORACLE_SID=GDTV2
+S[7K7^3D23757700$lsnrctl status

listener日志检查ITPUB个人空间KVu7]-\/k(m~
/u01/app/oracle/product/9.2.0/network/log/listener.log

/u01/app/oracle/product/9.2.0/network/log/listener.log

================oracrs status check===================================

    11.1 crs日志检查ITPUB个人空间G1}u0gNU9F
ocssd.logITPUB个人空间c&oJyub
$tail -20  cm.log

======================SQLcheck==============================ITPUB个人空间*O.\7h fO2lhY
$hostnameITPUB个人空间#o0M0yF{[*H
$export ORACLE_SID=GDTV1ITPUB个人空间!~"HP Y8r]k W(H
sqlplus "/as sysdba"

or sqlplus"/as sysdba"@GDTV1ITPUB个人空间9d{,@ gS.rX:[Pi
--run on db server as sysdba!!!ITPUB个人空间 P8MY#ay)P
--collect by lyf 200609ITPUB个人空间8P7h#OFXk D
set pagesize 1000
s K%b9O|-x23757700set linesize 120
g*fA/A Lyh^ y23757700set echo onITPUB个人空间o;t9b*C$d{bS6H
COLUMN name_col_plus_show_param FORMAT a36 HEADING NAMEITPUB个人空间MT-Uv1qWe:i*Q#X
COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE
MR Xk)y-m23757700col tablespace_name format a15ITPUB个人空间9YzvR1z-Fq_
host hostname

T N"zPO f3m`237577001.集群中所有正在运行的实例

SELECTITPUB个人空间:Nz6}x]2N
inst_id
S)U^D L4Tse23757700, instance_number inst_noITPUB个人空间/W]f {]pI|
, instance_name inst_name
Y5y,xRd6} ]!d23757700, parallelITPUB个人空间'H9i(l7]7reBT)V
, statusITPUB个人空间_OZO&SN&Q&q
, database_status db_statusITPUB个人空间mfD HMD.]
, active_state stateITPUB个人空间~ lw.Wsha
, host_name host
)c kw[+e%jH23757700FROM gv$instanceITPUB个人空间g |\w.j1Q
ORDER BY inst_id;

INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST
*cTA-nw r7j23757700-------- -------- ---------- --- ------- ------------ --------- -------
9I7m4w3nn,wAG237577001 1 orcl1 YES OPEN ACTIVE NORMAL rac1ITPUB个人空间(Zx&H"B.A X&gZO,F
2 2 orcl2 YES OPEN ACTIVE NORMAL rac2

7q!mu1xmA6ld23757700SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver, database_status FROM gv$instance;

 

2.检查参数ITPUB个人空间"w!X'?Fc0n9~[
show paramter

ITPUB个人空间'Ys N~`D U
3.检查SGA和PGA

show sga
9{Y*FK\23757700select name ,value/1024/1024/1024 from gv$sysstat where name like '%pga%';
qr7PteWx*z23757700select name ,value/1024/1024/1024 from v$sysstat where name like '%pga%';

4.检查查询服务器的运行模式和数据库安装选项
t5?^2V"X3s23757700   set linesize 200ITPUB个人空间n;j}*FOUcu
  select * from v$option;

5.用户检查
+Og)?6u P23757700col temporary_tablespace for a21ITPUB个人空间"J/Z3wW V
select username,account_status,default_tablespace,temporary_tablespace,created from dba_users;

select a.username , a.temporary_tablespace "Temporary Tablespace" , b.contentsITPUB个人空间W;u4m3n,q3y0s
from dba_users a , dba_tablespaces bITPUB个人空间d |l~ Kgy2a
where a.temporary_tablespace=b.tablespace_name
A#f Q2}`7[23757700and b.contents <> 'TEMPORARY';

ITPUB个人空间:k*C Y!s so.u
6、控制文件检查

col name for a60ITPUB个人空间#Pw&tYh0Q,u0h
select * from v$controlfile;

7、无效对象检查
*b#{0v6p&w(@`-d%B5T$t23757700   col OBJECT_NAME for a24ITPUB个人空间 k$p D4W#~xOX [
SELECT owner , object_name, object_type,status ,LAST_DDL_TIME  FROM dba_objects WHERE status like 'INVALID';

a*}\ ]1h)^\Y(`1M237577008、表空间和数据文件检查

1）数据文件
"X%bYP#r1S23757700col file_name for a56
*{y [&[/O6[wO9VS23757700set linesize 300ITPUB个人空间I#l:nD7I ]9o.z|
select file_id,file_name,tablespace_name,autoextensible from dba_data_files;

select count(*) from v$datafile;

show parameter db_files

J}6Jb1B-w\"a23757700select name from v$datafileITPUB个人空间 `$RO&L8R&zfO%B|
unionITPUB个人空间$|"|%D1[`,pZ#F
select member from v$logfileITPUB个人空间(|4}'p(`&J"V
unionITPUB个人空间 Fxi^7n+^
select name from v$controlfileITPUB个人空间l'KI9A{sV4Iv6a
unionITPUB个人空间TL h],s4z
select name from v$tempfile；

ITPUB个人空间$Jn} YvQ[*O
SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$datafile UNION ALL SELECT file#, ts#, NAME, status, BYTES / 1024 / 1024 size_mb FROM v$tempfile;

j/{wJTHW0n237577002）表空间ITPUB个人空间U1[6a&OpNc
set linesize 300
\3_G0`1a\6[8]D23757700col tablespace_name for a16
;q8m_e3~w.F9K"Qn-T23757700SELECT upper(f.tablespace_name) "tablespace_name",ITPUB个人空间X3L#z/P]
       d.Tot_grootte_Mb "tablespace(M)",
G!j#|u;Z2]$ev2}23757700       d.Tot_grootte_Mb - f.total_bytes "used(M)",ITPUB个人空间Hnj0\U0w7l5lF+p8Ai
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
[!Tq*z"hL23757700       f.total_bytes "free_space(M)",
6Q&e3v O/h(F/`'Bt Uh23757700       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%",ITPUB个人空间 UI7H g5K"u0?,L
       f.max_bytes "max_block(M)"
`p4Ln)I7|*{'W3N23757700FROM     
v{ge0O23757700    (SELECT tablespace_name,ITPUB个人空间wV` @!_^
            round(SUM(bytes)/(1024*1024),2) total_bytes,ITPUB个人空间t5S DzT!b,b v#pgx
            round(MAX(bytes)/(1024*1024),2) max_bytesITPUB个人空间 X mJ9N? q(Y-I_gZ)K
      FROM sys.dba_free_space
*X:AJ.rz1S23757700      GROUP BY tablespace_name) f,
L-a3a L2Vu.feN23757700      (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_MbITPUB个人空间_^%a@*yJ*{1@?Q
       FROM   sys.dba_data_files dd
)P N6t]];m {!b Y23757700      GROUP BY dd.tablespace_name) d
$aW-WuB4@%j23757700WHERE d.tablespace_name = f.tablespace_name   ITPUB个人空间%mJ:^Wx,k&n
ORDER BY 4 DESC;ITPUB个人空间%b,o/{Yo9Wn
 

ITPUB个人空间1MX_7}uE
表空间的空间使用情况ITPUB个人空间3GR5G\p7Kr8Tw+l
SELECT   df.tablespace_name, COUNT (*) datafile_count,
QHhA2F dG r23757700ROUND (SUM (df.BYTES) / 1048576) size_mb,ITPUB个人空间h&[KG^+Y&~U
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
x!Aa.n'ao1T*L0C23757700ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
%B+S |@u/g b23757700ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
`!~V[nu23757700100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,ITPUB个人空间,a!C0h)},U.U2p
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
;qML~Q wD23757700FROM dba_data_files df,
Q-d"LR8A23757700(SELECT   tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) freeITPUB个人空间 |9d+h0Kwq%QVQ
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
n:~\9kSg8L23757700GROUP BY df.tablespace_name ORDER BY ；

表空间可用性检查
[#_YP6MD\Z23757700select tablespace_name,status from dba_tablespaces;

1?MK0_)kb_2Bf'v23757700临时表空间使用情况和性能检查

SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE CONTENTS = 'TEMPORARY';
U ]OZ%nDWE;H23757700SELECT username, default_tablespace, temporary_tablespace FROM dba_users;

ITPUB个人空间;QaXLc3Y
    select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,status from dba_tablespaces order by  extent_management;

     select tablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

表：ITPUB个人空间4a-zs|aW4o
1、监控表的增长

select segment_name,segment_type,bytes/1024/1024 from dba_segments where wner='FOUNDER' ORDER BY bytes/1024/1024 desc；

2、表和索引分析信息

SELECT   'table', COUNT (*) FROM dba_tables WHERE last_analyzed IS NOT NULL GROUP BY 'table'

UNION ALL
5M7q4fk&jwCe23757700SELECT   'index', COUNT (*) FROM dba_indexes WHERE last_analyzed IS NOT NULL GROUP BY 'index';

3.未建索引的表

      SELECT   /*+ rule */
;q8{/Z _?23757700                owner, segment_name, segment_type, tablespace_name,
2B$S!OV9I~23757700                TRUNC (BYTES / 1024 / 1024, 1) size_mbITPUB个人空间5o Sp6djt9t
           FROM dba_segments t
~-Lv$Dnh8[1ihl23757700          WHERE NOT EXISTS (ITPUB个人空间k:GffQ;O
                      SELECT 'x'ITPUB个人空间w O+C\g/eEm
                       FROM dba_indexes i
)ZWT N z f`!eJ:_ g23757700                       WHERE t.owner = i.table_ownerITPUB个人空间qBe)v8C)m!o$Vt
                             AND t.segment_name = i.table_name)
!Gq.L*Bl p23757700            AND t.segment_type IN ('TABLE', 'TABLE PARTITION')
YW6p]-rGb3@23757700            AND t.owner NOT IN ('SYS', 'SYSTEM')ITPUB个人空间+m0X_6b/n8@0d4?,f
       ORDER BY 5 DESC;

升序用ASC

 

9.sort_segment检查
5U@;L-Xr}23757700    select tablespace_name,extent_size db_blocks_per_extent,total_extents,
8}d L3BREU2e23757700        used_extents,free_extents from v$sort_segment;

10.数据库总大小ITPUB个人空间Vy2WbT*C+B
    select round(sum(space)) all_space_M fromITPUB个人空间:lNv2U?Y8a
(
*c5v].l tebyr?&x23757700select sum(bytes)/1024/1024 space from dba_data_filesITPUB个人空间7CNCt3tV
union allITPUB个人空间p"Chh2f;{ w;\v%a
select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files
!C.t V"B(b0mnx23757700union allITPUB个人空间:z1WCH2r[
select sum(bytes)/1024/1024 space from v$logITPUB个人空间S5r g Io6_
);

11.检测连接数情况ITPUB个人空间^kUz!o8v9L\
（1）

select SW.Sid,S.Username,SW.Event,SW.Wait_Time,SW.State,SW.Seconds_In_Wait SEC_IN_WAIT
,h _ m7IuZI\ Q23757700from v$session S,v$session_wait SW where S.Username is not null and SW.Sid=S.Sid
1p\q+V[v#| w/Gl23757700and SW.event not like '%SQL*Net%' order by SW.Wait_Time Desc;

ITPUB个人空间f'h x6G;\VO3t;vM)_5Ug
（2）ITPUB个人空间1U U.nP$W
select count(*) from v$session;

ITPUB个人空间~e\X'y"ov
（3）
juNF?Xf23757700select sid,serial#,username,program,machine,status from v$session;

11.回滚段信息ITPUB个人空间#xx#|!JxBPc
1）信息1ITPUB个人空间6sT}?#`6w*r?{P
col segment_name format a20
9Z/r(J7Rn H"eU23757700col tablespace_name format a20
fN `K|}w23757700select segment_name,owner,tablespace_name,ITPUB个人空间a"`,O%_3]b XF
dba_rollback_segs.status
5B yjBW%H23757700from dba_rollback_segs,v$Datafile where file_id=file#;

ITPUB个人空间 {#?L8PhNl*B Un
  2）信息2ITPUB个人空间 bF+hloQ$ch1F,j]
select segment_name,initial_extent,next_extent,min_extents,
9RF)BeBh"HT#f23757700owner,dba_rollback_segs.status status,optsize
GX~a4G;p23757700from dba_rollback_segs,v$rollstat
m.F/CU-cd0rrQy23757700where dba_rollback_segs.segment_id=v$rollstat.usn;

3）信息3ITPUB个人空间%y6m-idXS
col Rollback_Name for a16
|8C*w!dv23757700select substr(V$rollname.NAME,1,20) "Rollback_Name",
%x8x_U QeQI x23757700        substr(V$rollstat.EXTENTS,1,6) "EXTENT",
&?Fg%M]Ei2m23757700        v$rollstat.RSSIZE, v$rollstat.WRITES,
:x4V7^5Qd~*_23757700        substr(v$rollstat.XACTS,1,6) "XACTS",ITPUB个人空间1rS4B6{xK| M c
        v$rollstat.GETS,ITPUB个人空间!s*Q!DX#OzE
        substr(v$rollstat.WAITS,1,6) "WAITS",
Pp%P2O3K23757700        v$rollstat.HWMSIZE, v$rollstat.SHRINKS,ITPUB个人空间qO2q'm!K/PA9c)R
        substr(v$rollstat.WRAPS,1,6) "WRAPS",
:j'x:ch `ZDs23757700        substr(v$rollstat.EXTENDS,1,6) "EXTEND",
j3A4f#g^\/u23757700        v$rollstat.AVESHRINK,
vv!f#v;VF!i.` Y23757700        v$rollstat.AVEACTIVEITPUB个人空间kkd-xJ2L*op
from v$rollname, v$rollstat
4PW-] Q S8S[ b1G1wA3Q23757700where v$rollname.USN = v$rollstat.USN
Q ?(g.f^&J5|LT23757700order by v$rollname.USN;

4）信息4ITPUB个人空间]4i?-Q[ZI+J&b
select  r.name Rollback_Name,
.b!{$k8I`f4P/Ssb23757700      p.pid Oracle_PID,
6L*O M-lr23757700        p.spid OS_PID,
;o7X({6UT?;A;Dmi23757700        nvl(p.username,'NO TRANSACTION') Transaction,
F@i.{$J K0v;Ag23757700        p.terminal TerminalITPUB个人空间LY#C)g I
from v$lock l, v$process p, v$rollname r
'`3nw?!Ax.?LG#p23757700where   l.addr = p.addr(+)
M?:o9v]s"@r$A/y~D23757700        and trunc(l.id1(+)/65536)=r.usnITPUB个人空间&oS;T4F'n4WDa;iQL
      and l.type(+) = 'TX'ITPUB个人空间*nx`/GP_
        and l.lmode(+) = 6
6T&Q3}LEaoh23757700order by r.name;

5）回滚段的争用情况ITPUB个人空间b3k4B\0C'}^)V
select name ,waits ,gets ,waits/gets "Ratio" from v$rollstat a ,v$rollname b where a.usn=b.usn;

6）rollback信息
`BI Q [roy:Z23757700select  substr(sys.dba_rollback_segs.SEGMENT_ID,1,5) "ID#",ITPUB个人空间,E(n;EZ[,TO9l
        substr(sys.dba_segments.OWNER,1,8) "Owner",
Z*P4dE7X7G+`T9R*pM23757700        substr(sys.dba_segments.TABLESPACE_NAME,1,17) "Tablespace Name",ITPUB个人空间:o}} N1}3zk
        substr(sys.dba_segments.SEGMENT_NAME,1,12) "Rollback Name",
0q g i*Vhnjpj23757700        substr(sys.dba_rollback_segs.INITIAL_EXTENT,1,10) "INI_Extent",ITPUB个人空间G6O qD+lU7LhI
        substr(sys.dba_rollback_segs.NEXT_EXTENT,1,10) "Next Exts",ITPUB个人空间h"L N e1@{#c2vB0g5]
        substr(sys.dba_segments.MIN_EXTENTS,1,5) "MinEx",
O.f!Y:p2Xk'i23757700        substr(sys.dba_segments.MAX_EXTENTS,1,5) "MaxEx",
h8PS_+W23757700        substr(sys.dba_segments.PCT_INCREASE,1,5) "%Incr",ITPUB个人空间Lt*@ Sp-JC.H-q
        substr(sys.dba_segments.BYTES,1,15) "Size (Bytes)",
Q1NXm-n&h*}C#t5n;R6E23757700        substr(sys.dba_segments.EXTENTS,1,6) "Extent#",
.Ju/f `m23757700        substr(sys.dba_rollback_segs.STATUS,1,10) "Status"
)hM9jZ(|\23757700from sys.dba_segments, sys.dba_rollback_segsITPUB个人空间m#Pq5p\j;~)i2n,Ih
where sys.dba_segments.segment_name = sys.dba_rollback_segs.segment_name and
X(y)NU]%B!@23757700      sys.dba_segments.segment_type = 'ROLLBACK'ITPUB个人空间+Y1Y%J+M} f G,W^ s
order by sys.dba_rollback_segs.segment_id;

 

12.Redo log信息检查

1）Redo Log 文件状态ITPUB个人空间1]d]KK N{7wD
   col member for a56ITPUB个人空间lk+tS4_D
select f.member "member",ITPUB个人空间a6\QVV Dj w/D
       f.group# "group",
v/s\Ap23757700       l.bytes/1024/1024 "size",
/m7S}f&S'v&f23757700       l.status
w'l(v*iq&Y I23757700from v$logfile f, v$log lITPUB个人空间ra/Q UU+M
where f.group#=l.group#ITPUB个人空间0nX Xm\U$QjM f
order by f.group#,f.member;

2）LogGroup信息ITPUB个人空间 aK&}1B2`b
SELECT group#, sequence#, bytes, members, status from v$log;

3）关于log_buffer
,]1R]/DX8v]23757700       select name,value from v$sysstat where name in('redo entries','redo buffer allocation retries');

4）查询LOG大小及频率
4d3R$L'PH%Y;F4A:r9F23757700set linesize 300ITPUB个人空间:x(nLsH!BW
set pages 100
%BEm4[.I LxHu23757700column d1 form. a20 heading "Date"
P.g'F Cq23757700column sw_cnt form. 99999 heading 'Number|of|Switches'ITPUB个人空间JQ b.|1['Qx9D
column Mb form. 999,999 heading "Redo Size"ITPUB个人空间5Z.x["Kc{l I
column redoMbytes form. 999,999,9999 heading "Redo Log File Size (Mb)"

break on report
jQg%j]23757700compute sum of sw_cnt on report
Vf,K L e23757700compute sum of Mb on report

var redoMbytes number;
:t2@C4^FO-X c23757700begin
iuDd)F+H0m:r23757700   select max(bytes)/1024/1024 into :redoMbytes from v$log;ITPUB个人空间:b#`Ijb2G,q+u
end;
Y&qm l}.g23757700/

print redoMbytes

select trunc(first_time) d1
S;fxyoR0nv23757700       , count(*) sw_cntITPUB个人空间/\f\\6O0lh
       , count(*) * :redoMbytes Mb
!B7t Ckc._/aF(f23757700from v$log_history
XEIn#{23757700group by trunc(first_time)ITPUB个人空间 U~+J3xg| l*TU
/

 

13. IO情况检查

1_KP6} M'dk;i.{JW23757700   col file_name for a46
,nlRA\:g#n![0S23757700selectITPUB个人空间x6ynZh7h
df.name file_name,ITPUB个人空间P6]W&\i,\4e~
fs.phyrds reads,ITPUB个人空间t~%ez!x Dt
fs.phywrts writes,ITPUB个人空间7fSn+wQ*nyn
(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,
eD.\6P&_T]23757700(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetimeITPUB个人空间?*t(J;b eq
from
(]H/^6N$tku23757700v$datafile df,v$filestat fs
-P0ep VR.a23757700where df.file#=fs.file#ITPUB个人空间Upx U/i
order by df.name;

select count(*) from v$session;

n1g|!s5fp^5ry2375770014.命中率相关检查

ITPUB个人空间 ]/@z&M"W y{ H guv
1）Shared Pool Size 命中率
6Gs k1dzP@(o/D @23757700  select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %"ITPUB个人空间H.aQF'kJa
from v$librarycache where namespaceITPUB个人空间 QxB3FLfhf)gO
in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');

2）数据字典命中率ITPUB个人空间5j G8S&\D3Rb0}(F
select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %"ITPUB个人空间B%|4yW6gL0o|
from v$rowcache;

3）锁竞争
F^6_8A@ yb23757700select  substr(ln.name,1,25) Name,
2pq+E_aNO23757700        l.gets, l.misses,ITPUB个人空间` XP(u)Pf2a
        100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"ITPUB个人空间Ml)P#q)N$z
from v$latch l, v$latchname ln
6~Z_Ic6w23757700where ln.name in ('cache buffers lru chain')ITPUB个人空间i S6RX'lC,uo
and ln.latch# = l.latch#;

4）排序命中率ITPUB个人空间)`MG{ G&^z
select a.value "Sort(Disk)", b.value "Sort(Memory)",ITPUB个人空间$C ~bt(q/^*FNx
round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)" ITPUB个人空间 m.A}[|
from v$sysstat a, v$sysstat b
ZM$IOS9Kz/KB23757700where a.name = 'sorts (disk)'
B~;S AG)o6F23757700and b.name = 'sorts (memory)';

5）数据缓冲区命中率
9x9X^ID+DE23757700select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratio
{!S1D K$s4V:QO23757700from v$sysstat phy,v$sysstat cur,v$sysstat con
%["qn`Wp23757700where phy.name='physical reads' and cur.name='db block gets' and con.name='consistent gets';

6）Miss LRU Hit命中率
U[%~a^J*Z23757700column "Miss LRU Hit%" format 99.9999999;ITPUB个人空间 z%yvr#e
col name format a40ITPUB个人空间3hQ o9BgJr
select name, (sleeps/gets) "Miss LRU Hit%"
h!Q#jc9{3Jw~v6J23757700from v$latch where name ='cache buffers lru chain';

7）检查内存排序性能ITPUB个人空间D+} zhRJ yEr!r-_
select a.name, to_char(value)
'UW]b:Z'^f7g23757700from v$statname a, v$sysstatITPUB个人空间({gm.dx)H]nkc
where a.statistic# = v$sysstat.statistic#ITPUB个人空间,U+^#a7J0~H
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');

8）redo log buffer retry ratioITPUB个人空间4f6d#L.m*[
select to_char(r.value/e.value) "redo log buffer retry ratio"ITPUB个人空间8I1v:J*gl#g1V9Y
from v$sysstat r,v$sysstat eITPUB个人空间!^"oQyp7Vsd
where r.name='redo buffer allocation retries'
^D+v9rdI!F23757700and e.name='redo entries';

9）wait等待检查
)i4nX']@X%Q23757700select count(*) total_in_wait from v$session_waitITPUB个人空间/t \&k9E2r.h4X c
where event='log buffer space';

select event,total_waits,time_waited,average_wait
]?M9JA B2G23757700from v$system_event
.C.ZiP@A"E(E#PVH23757700where event like '%undo%';

select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'ITPUB个人空间*xf%D1O3\"kL
and event not like 'rdbms%';

15、查询lock锁ITPUB个人空间
  SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
j%['X P9bU4[4xj23757700FROM V$LOCKITPUB个人空间z X Re6q A
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)ITPUB个人空间:\ R:Nh-R"Cc
ORDER BY id1, request;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23757700/viewspace-700625/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23757700/viewspace-700625/