監(jiān)控事例的等待
selectevent,sum??? (decode(wait_Time,0,0,1))"Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*)"Tot" from v$session_Wait???group by event order by 4;
回滾段的爭(zhēng)用情況
selectname, waits, gets, waits/gets "Ratio"??? fromv$rollstat C, v$rollname D?? where C.usn= D.usn;
監(jiān)控表空間的 I/O 比例
selectB.tablespace_name nam,
????? ??A.phyblkrd pbr,
????? ??A.phywrts pyw,
????? ??B.file_name "file",
????? ??A.phyrds pyr,
?????? ?A.PHYBLKWRTpbw
fromv$filestat A, dba_data_files B??
whereA.file# = B.file_id?? order byB.tablespace_name;????
監(jiān)控文件系統(tǒng)的 I/O 比例
selectsubstr(C.file#,1,2) "#",substr(C.name,1,30) "Name",C.bytes, D.phyrds, D.PHYWRTS, C.status from v$datafile C, v$filestat D??????????
whereC.file# = D.file#;
監(jiān)控 SGA 的命中率
selecta.value +??? b.value"logical_reads", c. value"phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))"BUFFER HIT RATIO"
fromv$sysstat a, v$sysstat b, v$sysstat c
wherea.statistic# = 38 and b.statistic# = 39and c.statistic# = 40;
監(jiān)控 SGA 中字典緩沖區(qū)的命中率
selectparameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "missratio",
(1-(sum(getmisses)/(sum(getmisses)+sum(getmisses))))*100 "Hit ratio"
fromv$rowcache
wheregets+getmisses <>0
groupby parameter, gets, getmisses;
監(jiān)控 SGA 中共享緩存區(qū)的命中率窿凤，應(yīng)該小于1%
selectsum(pins)??? "Total Pins", sum(reloads)?"Total Reloads",
sum(reloads)/sum(pins)*100 libcache
fromv$librarycache;????????????????????
selectsum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins)"reload percent" from v$librarycache;
監(jiān)控 SGA 中重做日志緩存區(qū)的命中率，應(yīng)該小于1%
SELECTname, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,?? misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROMv$latch WHERE name IN ('redo allocation??? ','redo copy');
數(shù)據(jù)庫(kù)表空間使用情況監(jiān)控(字典管理表空間)
數(shù)據(jù)庫(kù)運(yùn)行了一段時(shí)間后，由于不斷的在表空間上創(chuàng)建和刪除對(duì)象捎迫，會(huì)在表空間上產(chǎn)生大量的碎片踢星，DBA應(yīng)該及時(shí)了解表空間的碎片和可用空間情況澳叉，以決定是否要對(duì)碎片進(jìn)行整理或?yàn)楸砜臻g增加數(shù)據(jù)文件。以下為引用的內(nèi)容：
selecttablespace_name,
count(*)chunks ,
max(bytes/1024/1024)max_chunk
fromdba_free_space
groupby tablespace_name;
上面的SQL列出了數(shù)據(jù)庫(kù)中每個(gè)表空間的空閑塊情況,如下所示:
以下為引用的內(nèi)容：
TABLESPACE_NAME??????? ?CHUNKS?????MAX_CHUNK
--------------------?----------? ?----------
INDX??????????????? ?1??????????57.9921875
RBS???????????????? ?3??????????490.992188
RMAN_TS??????????? ?1???????????16.515625
SYSTEM??????????????1??????????207.296875
TEMP????????????????20?????????70.8046875
TOOLS?????????????? ?1??????????11.8359375
USERS???????????????67?????????71.3671875
其中，CHUNKS列表示表空間中有多少可用的空閑塊(每個(gè)空閑塊是由一些連續(xù)的Oracle數(shù)據(jù)塊組成)成洗，如果這樣的空閑塊過(guò)多五督，比如平均到每個(gè)數(shù)據(jù)文件上超過(guò)了100個(gè)，那么該表空間的碎片狀況就比較嚴(yán)重了瓶殃，可以嘗試用以下的SQL命令進(jìn)行表空間相鄰碎片的接合:
alter tablespace 表空間名coalesce;
然后再執(zhí)行查看表空間碎片的SQL語(yǔ)句充包，看表空間的碎片有沒(méi)有減少。如果沒(méi)有效果遥椿，并且表空間的碎片已經(jīng)嚴(yán)重影響到了數(shù)據(jù)庫(kù)的運(yùn)行基矮，則考慮對(duì)該表空間進(jìn)行重建。
MAX_CHUNK列的結(jié)果是表空間上最大的可用塊大小冠场，如果該表空間上的對(duì)象所需分配的空間(NEXT值)大于可用塊的大小的話愈捅，就會(huì)提示ORA-1652、ORA-1653慈鸠、ORA-1654的錯(cuò)誤信息蓝谨，DBA應(yīng)該及時(shí)對(duì)表空間的空間進(jìn)行擴(kuò)充，以避免這些錯(cuò)誤發(fā)生青团。
查看數(shù)據(jù)庫(kù)的連接情況
DBA要定時(shí)對(duì)數(shù)據(jù)庫(kù)的連接情況進(jìn)行檢查譬巫，看與數(shù)據(jù)庫(kù)建立的會(huì)話數(shù)目是不是正常，如果建立了過(guò)多的連接督笆，會(huì)消耗數(shù)據(jù)庫(kù)的資源芦昔。同時(shí)，對(duì)一些“掛死”的連接娃肿，可能會(huì)需要DBA手工進(jìn)行清理咕缎。
以下的SQL語(yǔ)句列出當(dāng)前數(shù)據(jù)庫(kù)建立的會(huì)話情況:以下為引用的內(nèi)容：
selectsid,serial#,username,program,machine,status
fromv$session;
輸出結(jié)果為:以下為引用的內(nèi)容：
SID??SERIAL#???USERNAME?? ?PROGRAM???MACHINE???STATUS
----?-------?---------- ----------- ---------------?--------
1??????1???????????????????ORACLE.EXE??WORK3?????ACTIVE
2??????1?????????????????? ?ORACLE.EXE??WORK3???? ?ACTIVE
3?????? 1???????????????????ORACLE.EXE??WORK3?????ACTIVE
4????? ?1?????????????????? ?ORACLE.EXE??WORK3?????ACTIVE
5????? ?3?????????????????? ?ORACLE.EXE??WORK3?????ACTIVE
6????? ?1???????????????????ORACLE.EXE??WORK3?????ACTIVE
7?????? 1???????????????????ORACLE.EXE? ?WORK3???? ?ACTIVE
8??????27?????? ?SYS???????SQLPLUS.EXE?WORKGROUP\WORK3?ACTIVE
11?????5?????? ?DBSNMP???dbsnmp.exe??WORKGROUP\WORK3? ?INACTIVE
注：
SID會(huì)話(session)的ID號(hào)；
SERIAL#會(huì)話的序列號(hào)料扰，和SID一起用來(lái)唯一標(biāo)識(shí)一個(gè)會(huì)話凭豪；
USERNAME建立該會(huì)話的用戶名；
PROGRAM這個(gè)會(huì)話是用什么工具連接到數(shù)據(jù)庫(kù)的晒杈；
STATUS當(dāng)前這個(gè)會(huì)話的狀態(tài)嫂伞，ACTIVE表示會(huì)話正在執(zhí)行某些任務(wù)，INACTIVE表示當(dāng)前會(huì)話沒(méi)有執(zhí)行任何操作拯钻。
如果DBA要手工斷開(kāi)某個(gè)會(huì)話帖努，則執(zhí)行:
altersystem kill session 'SID,SERIAL#';
注意，上例中SID為1到7(USERNAME列為空)的會(huì)話粪般，是Oracle的后臺(tái)進(jìn)程拼余，不要對(duì)這些會(huì)話進(jìn)行任何操作。
查看undo回滾率
SELECT NAME, VALUE
FROM v$sysstat WHERE NAME IN ('user commits', 'transaction rollbacks');
