set echo on
spool user_DBA_report.txt
set pages 333 lin 96
rem 這是一個(gè)DBA_Monitor.sql程序，目的是日常監(jiān)測(cè)分析數(shù)據(jù)庫(kù)之用。
rem 使用時(shí)服務(wù)器并不一定要設(shè)置為timed_statistics=true。
rem 我希望在你們這里可以發(fā)表一個(gè)月后，我收集意見(jiàn)后再修改為正式版。
rem 看過(guò)的朋友一定提更改意見(jiàn)噢!
rem 主要參考《Oracle8 DBA Handbook》,《Oracle8 Tuning》。
rem
rem
rem ---------------------------------------------------
rem 執(zhí)行說(shuō)明：本程序第一部分需要DBA權(quán)限,
rem 第二部分針對(duì)實(shí)際用戶,它們擁有表,索引,Source等。
rem # cat> ding92sql
rem sqlplus system/passwd@stamex < rem @dba_monitor.sql
rem connect stamexxx/passwd@stamex
rem @getuser_objects.sql
rem EOFa1
rem exit
rem 數(shù)據(jù)庫(kù)性能調(diào)整包括三方面的內(nèi)容OS,DB,App)
rem OS=操作系統(tǒng)，分別從CPU,Memory,Disk,NetWork
rem 查詢數(shù)據(jù)表的:名稱行數(shù).變化列數(shù)InitailCacheTSpace
rem 查詢數(shù)據(jù)表索引的:名稱表名列數(shù)類型Initial
rem 查詢數(shù)據(jù)表約束:名稱表名相關(guān)性類型
rem 查詢SQL的頻率和效率v_$sqlarea,跟蹤到sqls表中
rem 生成并定期執(zhí)行:index1rebld.sql, index2rebld.sql
rem ORACLE數(shù)據(jù)庫(kù)情況統(tǒng)計(jì)分析程序, 2001.02--2001.05, Ding Jugang
rem 1、 參數(shù)dbwr_io_slave等三個(gè)從屬進(jìn)程可以分別設(shè)置為 40:12:6
rem 2、 參數(shù)PRocess 一般是實(shí)際進(jìn)程數(shù)據(jù)的1.5倍, 是為限制進(jìn)程總數(shù).
rem process過(guò)大將降低系統(tǒng)的性能，可適當(dāng)考慮降低該數(shù)值到600
rem 3、 當(dāng)LOG FILE SWITCH時(shí)出現(xiàn)等待時(shí)，建議加大REDO LOG FILE,一般是30分鐘
rem 一次切換。目前是128MB,配合4MB LOG_BUFFER已經(jīng)可以了(8M也沒(méi)用).
rem 4、 在整個(gè)系統(tǒng)較繁忙時(shí)檢測(cè)SHARED_POOL(一般情況下應(yīng)該空余1/4)
rem 5、 SGA應(yīng)該小于整個(gè)物理內(nèi)存的一半，太大會(huì)導(dǎo)致內(nèi)存換頁(yè)出現(xiàn)(PI/PO)
rem 6、 MTS對(duì)于網(wǎng)站應(yīng)用是理想選擇，但在過(guò)分繁忙的客戶端壓力下，MTS會(huì)自動(dòng)失敗
rem 而重啟專用進(jìn)程(例如過(guò)多的php連接)，估計(jì)是應(yīng)用類型不兼容。
rem 7、 命令instat,vmstat,top,w 能從OS級(jí)評(píng)估系統(tǒng)負(fù)荷。
rem 監(jiān)測(cè)下面語(yǔ)句的執(zhí)行結(jié)果的變化率，能得出數(shù)據(jù)庫(kù)級(jí)的硬盤(pán)讀寫(xiě)流量。
rem select count(FILE#),sum(PHYRDS),sum(PHYWRTS),sum(PHYBLKRD) from sys.V_$filestat;
rem================================================================
rem 創(chuàng)建SQL跟蹤表SQLS
rem create table SQLS as select * from sys.v_$sqlarea where disk_reads>100;
rem 擇機(jī)(有性能懷疑時(shí))執(zhí)行跟蹤, 并執(zhí)行隨后的查詢：
rem insert into sqls select * from sys.v_$sqlarea where disk_reads>10 and executions<10;
rem 1)最高頻率的SQL
rem select disk_reads,executions,rows_processed,sql_text from SQLS
rem where executions> 99 ;
rem 2)查詢性能最差的SQL：
rem select disk_reads,executions,rows_processed,first_load_time,sql_text from SQLS
rem order by first_load_time;
select disk_reads,executions,rows_processed,first_load_time,sql_text
from sys.v_$sqlarea where disk_reads>10 and executions <10 order by first_load_time;
rem ========================================================================
rem 567890123456789_1234567890123456789_1234567890123456789_1234567890123456
rem ==RowCache,LibraryCache 依靠于Shared_pool,參看sys.v_$sgastat ===
rem 此二者當(dāng)達(dá)到95%，現(xiàn)已經(jīng)達(dá)到99%
select (sum(pins - reloads))/ sum(pins) "lib cache" from sys.v_$librarycache;
select (sum(gets-getmisses-usage-fixed)) / sum(gets) from sys.v_$rowcache;
rem ==================== SGA ==============================================
rem ======= sys.v_$sgastat,SGA中具體說(shuō)明 =====================
rem 剩余共享池: 保留Free Memory 大于25%
column name format A46
column value format 999999,999,999
select * from sys.v_$sgastat where rownum<5;
rem =================== SYS ===============================================
rem ========= sys.v_$SYSstat 具體列表,下面是幾個(gè)指標(biāo)的算法=======
rem 數(shù)據(jù)緩沖命中率：1- 40#/(39#+38#) = 99.8% > 95%
rem 內(nèi)存排序成功率：1- 162#/(161#) = 99.4% > 92%
rem 臟緩沖區(qū)平均長(zhǎng)度(oracle8i已廢除之): 41#/42#=0.06 rem 應(yīng)用效率：全表掃描<1%, 140#long/(139#short+140#long)
column class format 99999
column value format 999999,999,999
select * from sys.v_$sysstat
where STATISTIC# in (38,39,40,41,42,43,139,140,141,106,161,162,163);
rem 日志緩存要滿足空間請(qǐng)求極小,每日300,還可更小：
rem LOG_BUFFER=4MB,還可再大。
rem select * from sys.v_$sysstat where name like 'redo%';
rem
rem ==================回滾段1==============================================
rem 回滾段有效率：waits/gets<1% 即: 無(wú)等待命中率NoWait_Hit_Ratio接近于1
rem 回滾段數(shù)據(jù)量在4---100個(gè),同樣規(guī)格大小,盡可能穩(wěn)定不變。
rem alter TABLESPACE RBS DEFAULT STORAGE
rem (INITIAL 1M NEXT 1M MINEXTENTS 8 PCTINCREASE 0);
rem CREATE PUBLIC ROLLBACK SEGMENT RB21 TABLESPACE RBS;
rem ALTER ROLLBACK SEGMENT RB21 storage( minextents 4 optimal 8M);
rem CREATE PUBLIC ROLLBACK SEGMENT RB55 TABLESPACE RBS2 storage(minextents 8 optimal 8M);
column Ratios? format 99.9999
select count(*), sum(waits)/sum(gets) from sys.v_$rollstat;
rem rssize>=最優(yōu)保留值optimal,shrinks是動(dòng)態(tài)收縮次數(shù),每小時(shí)2次是答應(yīng)的。
rem 例如，統(tǒng)計(jì)信息：rssize=8M,extents=8, waits和shrinks 小于天天2次
select usn,extents,gets,writes,rssize,waits,shrinks
from sys.v_$rollstat;
rem =======****** 回滾段2 ****===========
rem 這里是回滾段的匯總統(tǒng)計(jì),分析其擴(kuò)展段之定義。其命中率統(tǒng)計(jì)見(jiàn)v_$roolstat
rem dba_rollback_segs 下面驗(yàn)證一致性。
select owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,count(*),
TABLESPACE_NAME,STATUS
from dba_rollback_segs
group by owner,initial_extent,NEXT_EXTENT,min_extents,
MAX_EXTENTS,TABLESPACE_NAME,STATUS;
rem dba_rollback_segs --> detail 定義
column SEGMENT_name format a6
column TABLESPACE_NAME format a12
select SEGMENT_name,owner,initial_extent,NEXT_EXTENT,Min_EXTENTS,MAX_EXTENTS,
TABLESPACE_NAME,STATUS from dba_rollback_segs;
rem ======================== 閂 ===========================================
rem 閂-Oracle內(nèi)部鎖，無(wú)等待命中率NoWait_Hit_Ratio接近于1
column name format A30
select name, immediate_gets "Imme_gets", immediate_misses "Imme_Mis",
round(immediate_gets/(immediate_gets+immediate_misses),3) "nowait_hit_ratio"
from sys.v_$latch where immediate_gets+immediate_misses != 0 order by name;
rem 閂-Oracle內(nèi)部鎖，命中率HITRATIO(即misses/gets)接近于0
rem sleeps, immediate_gets "Imme_gets", 為了易于閱覽,不要換行
column Mis/Get? format 99.999;
select name, gets, misses, misses/gets "Mis/Get?",
immediate_misses "Imme_Mis"
from sys.v_$latch where gets > 0 order by name;
rem ================== 系統(tǒng)等待 ===========================================
rem 系統(tǒng)等待累計(jì)次數(shù)和累計(jì)時(shí)間
column class format A30
select * from sys.v_$waitstat ;
column event format A28
select * from sys.v_$system_event order by TOTAL_WAITS;
rem ==============================================================
rem ================ v_$session_wait ======================================
rem 會(huì)話正在等待的累計(jì)時(shí)間seconds
select event,sum(SECONDS_IN_WAIT),count(*)
from sys.v_$session_wait group by event ;
rem rdbms ipc message 3648 6
rem slave wait 31501 41 太大?
rem===================================================================rem 會(huì)話等待的累計(jì)次數(shù)和累計(jì)時(shí)間
column event format A32
select event,type, sum(total_waits) "Waits",
sum(time_waited) "SumTime",count(*) "Count"
from sys.v_$session s, sys.v_$session_event e
where s.sid = e.sid
group by event,type
order by type ;
rem =================================================================
rem 這里是日志文件和控制
