日常監測分析資料庫的DBA_Monitor.sql程式

日常監測分析資料庫的DBA_Monitor.sql程式

[@more@]rem 日常監測分析資料庫的DBA_Monitor.sql程式
　　rem 我兩年前的實際經驗總結，以筆記奉獻於眾。
　　rem 主要參考《Oracle8 DBA Handbook》,《Oracle8 Tuning》。
　　rem 作者：丁聚崗 dingju@eastday.com
　　rem 參考
　　set echo on
　　spool user_DBA_report.txt
　　set pages 333 lin 96
　　rem　---------------------------------------------------
　　rem　 執行說明：本程式第一部分需要DBA許可權,
　　rem　 第二部分針對實際使用者,它們擁有表,索引,Source等。
　　　rem　　# cat> ding92sql
　　　rem　　sqlplus system/passwd@standax <　　　rem　　@dba_monitor.sql
　　　rem　　connect standaxxx/passwd@standax
　　　rem　　@getuser_objects.sql
　　　rem　　EOFa1
　　　rem　　exit
　　rem 使用時伺服器並不一定要設定為timed_statistics=true。
　　rem 《ORACLE資料庫情況統計分析程式》, 2001.05創作,2003.08整理 Ding Jugang
　　rem 資料庫效能調整包括三方面的內容:(硬體,軟體,資料庫)
　　rem 硬體，分別從CPU,Memory,Disk,NetWork提高；軟體，就是應用程式的結構最佳化。
　　rem 資料庫是我們DBA重點關心的，首先了解自己：
　　rem 表sys.DBA_tables : |名稱|行數.變化|列數|Initail|Cache|TSpace|
　　rem 索引sys.DBA_indexes:|名稱|表名|列數|型別|Initial|
　　rem 約束user_...:|名稱|表名|相關性|型別|
　　rem 查詢SQL的頻率和效率v_$sqlarea,跟蹤記錄到sqls表中
　　rem 定期重建索引,做成指令碼: index1rebld.sql, index2rebld.sql
　　　
　　rem 1、 引數dbwr_io_slave等三個從屬程式可以分別設定為 40:12:6
　　rem 2、 引數process 一般是實際程式資料的1.5倍, 是為限制程式總數.
　　rem　　 process過大則佔用系統資源，將降低系統的效能，可適當考慮降低該數值到600
　　rem 3、 當LOG FILE SWITCH時出現等待時，建議加大REDO LOG FILE,一般是30分鐘
　　rem　　 一次切換。目前是128MB, 配合4MB LOG_BUFFER已經可以了(8M也不顯效果).
　　rem 4、 在整個系統較繁忙時檢測SHARED_POOL(一般情況下應該空餘1/4)
　　rem 5、 SGA應該小於整個實體記憶體的一半，太大會導致OS記憶體換頁出現(PI/PO)
　　rem 6、 MTS對於網站應用是理想選擇，但在過分繁忙的客戶端壓力下，MTS會自動失敗
　　rem　　 而重啟專用程式(例如過多的PHP連線)，估計是應用型別不相容。
　　rem 7、 命令instat,vmstat,top,w 能從OS級評估系統負荷。
　　rem　　 監測下面語句的執行結果的變化率，能得出資料庫級的硬碟讀寫流量。
　　rem select count(FILE#),sum(PHYRDS),sum(PHYWRTS),sum(PHYBLKRD) from sys.V_$filestat;
　　
　　rem================================================================
　　rem 建立SQL跟蹤表SQLS
　　rem　create table SQLS as select * from sys.v_$sqlarea where disk_reads>500;
　　
　　rem當需要查詢效能時，設定為跟蹤模式，並執行下面的查詢：
　　rem insert into sqls select * from sys.v_$sqlarea where disk_reads>500 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 此二者應當達到95%，實際系統已經達到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中詳細說明 =====================
　　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 詳細列表,下面是幾個指標的演算法=======
　　rem 資料緩衝命中率：1- 40#/(39#+38#) = 99.8% > 95%
　　rem 記憶體排序成功率：1- 162#/(161#) = 99.4% > 92%
　　rem 髒緩衝區平均長度(oracle8i已廢除之): 41#/42#=0.06　　rem 應用效率：全表掃描<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);
　　
　　select name, value from v$parameter
　　where name in ('db_block_buffers',
　　'db_block_size', 'shared_pool_size','sort_area_size');
　　
　　rem DETERMINE IF THE DATA BLOCK BUFFERS IS SET HIGH ENOUGH
　　select 1-(sum(decode(name, 'physical reads', value,0))/
　　(sum(decode(name, 'db block gets', value,0)) +
　　sum(decode(name, 'consistent gets', value,0))))
　　"Read Hit Ratio" from v$sysstat;
　　
　　
　　rem 日誌快取要滿足空間請求極小,每日300次,還可更小：
　　rem LOG_BUFFER=4MB,還可再大。
　　rem select * from sys.v_$sysstat where name like 'redo%';
　　rem　
　　rem ==================回滾段1==============================================
　　rem 回滾段有效率：waits/gets<1%　即: 無等待命中率NoWait_Hit_Ratio接近於1
　　rem　回滾段資料量在4---100個,同樣規格大小,儘可能穩定不變。
　　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>=最優保留值optimal,shrinks是動態收縮次數,每小時2次是允許的。
　　rem 例如，統計資訊：rssize=8M,extents=8, waits和shrinks 小於每天2次
　　
　　select usn,extents,gets,writes,rssize,waits,shrinks
　　　from sys.v_$rollstat;
　　
　　rem =======****** 回滾段2 ****===========
　　rem　這裡是回滾段的彙總統計,分析其擴充套件段之定義。其命中率統計見v_$roolstat
　　rem dba_rollback_segs 下面驗證一致性。
　　
　　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 --&gt 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內部鎖，無等待命中率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內部鎖，命中率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 ================== 系統等待 ===========================================
　　rem　系統等待累計次