1、數據庫性能檢查指導方案author: kamus date： 2004-9在系統穩定之后，應該按照本指導方案每個月檢查一次產品數據庫。該指導方案適用于0racle9i數據庫，因為有些腳本在9i屮才可以運行。檢查方式均為以sysdba身份背錄數據庫以后在sqlplus中執行命令腳本(毎小節的“檢查 方法”部分有詳細的命令腳本)。登陸數據庫的命令：sqlplus ''sys/password as sysd一.內存性能評估在內存性能評估的時候,我們使用內存性能指數(mp1, memory performance index) , b* 表列出了 mpi中的各項指數，這個評分系統并不

2、意味著對內存的使用和分配的全方位評估, 而只是代表一個晴雨表，反映當前系統內存的使用和分配狀況。mpi指數分類所需等級最高分緩沖區命中率(buffer cache)>98%30數據字典命中率(dictionary cache)>98%30庫緩存命中率(library cache)>98%30內存中的排序(sort in memory)>98%30空閑的數據緩沖區比例10-25%30使用最多的前10個sql占用的內存<5%60是否已經調整使用最多的詢25個sql是30是否嘗試固定高速緩存中經常使用的對象是10mpt指數總分2501.緩沖區命中率顯示了對于數據總讀取量

3、而言，非磁盤讀取(緩沖區命屮)的百分比。當然，十分高的命小 率并不代表數據庫性能一定優良，也有可能是糟糕的sql引起了大量的緩沖區讀操作，只有 在已經調整過首要的查詢之后，這個命中率才能更好地反映數據庫性能。檢杳方法：select (1 - (sum(decode(name, 1 physical reads1z value, 0) /(sum(decode(name, 1db block gets 1, value, 0) +sum(decode(name, 1 consistent gets 1, value, 0) * 100 nhit ratio"from v$sysstat

4、；評估準則:等級分數<90%090-94%1095-98%20>98%302.數據字典命中率顯示了對數據字典和其它對象的內存讀操作的百分比。檢查方法：select (1 - (sum (getmisses) / sum (gets) ) ) * 100 nhit ratio11from v$rowcache；評估準則:等級分數<85%086-92%1092-98%20>98%303.庫緩存命中率顯示了對sql和pl/sql對'象的內存讀操作的仃分比。同樣注意，很高的命中率并不總是反 映數據庫性能優秀。檢查方法：select sum (pins) / (sum (

5、pins) + sum (reloads) )* 100 nhit ratio11from v$librarycache；評估準則:等級分數<90%090-94%1094-98%20>98%304.內存中的排序根據初始化參數pga_aggregate_target或者sort_area_size的值，用戶的排序操作可能在 內存屮執行，也可能在臨時表空間中執行。這個檢查用以顯示在內存小排序占總排序的百分 比。檢査方法：select a .value "disk sorts'1,bvalue nmemory sortsn,round(100 * b value) /d

6、ecode(a value + b.value), 0, 1, (a.value + b.value),2 ) 11 pct memory sorts'1from v$sysstat az v$sysstat bwhere = 1 sorts (disk) 1and = 1 sorts (memory) 1；評估準則:等級分數<90%090-94%1094-98%20>98%305.空閑的數據緩沖區比例空閑的記錄數除以x$bii表中的記錄總數(即所分配的數據塊緩沖區的總數)得到的空閑緩 沖區百分比。同樣注意，擁有眾多空閑緩沖區的數據庫不一定是最住

7、壞境，因為可能是緩沖 區設置過人，浪費內存。檢查方法：select decode(state,0,'free',1,decode(lrba_seqz 0, 'available* z 'being used'),3,'being used',state) "block status"zcount(*)from x$bhgroup by decode(statez0,'free',1,decode(lrba-seq, 0, 'available'z 'being used'

8、)z3,'being used',state)；評估準則:等級分數<5%05-19%3020-25%20>25%06.最浪費內存的前10個語句占全部內存讀取量的比例通常一個沒冇優化系統屮，10個最常用的sql語句的訪問最會占到整個系統屮內存讀操作 的50%以上。這些sql是最需要進行優化的部分，也是優化工作中優先級很高的部分。檢杳方法：select sum(pct_bufgets)from (select rank () over (order by b uffer_gets desc) as rank_bufgets, to_char(100 * ratio_to

9、_report(buffer_gets) over(),'999.99 1 ) pct_bufgetsfrom v$sqlarea)where rank_bufgets < 11 ；評估準則:等級分數<5%605-19%5020-25%30>25%07.調整前25個最浪費內存的語句在沒冇調整的情況下，絕大多數系統中，訪問雖占前25位的語句的內存讀操作將占用整個 系統所冇內存讀操作的75%,對這部分語旬進行調幣是至關重要的。這部分腳本用于獲得訪 問量占前25位的sql語句。檢杳方法：set serveroutput on size 1000000declaretop25

10、 number；textl varchar2(4000);xnumber；lenl number；cursor cl isselect buffer_gets, substr(sql_text, 1, 4000)from v$sqlareaorder by buffer_gets desc；begindbms_output.put_line('gets'|1'|'text');dbms_output.put_line ( ''| |''| |'')；open cl；for i in 1 25 loopfet

11、ch clinto top25, textl；dbms_outlputl .putline (rpad 比o_char (top2 5) , 9)| |'1| |substr(textl, 1, 66)；lenl := length(textl)；x ：= 66 ;while lenl > x - 1 loopdbms_output.put_line ( 1 n1| | substr(textl, x, 66)；x ：= x + 66；end loop；end loop；end；/評估準則：本部分沒有評估準則，需耍開發人員或者dba去確認在這25個sql中屬于應用系統的語句 是否

12、都已經作過調優。8.固定緩存對象嘗試在內存中固定(pin)經常使用的對象，包括表，存儲過程等。檢索需要在共享池中要求大于100k連續空間的對象：select *from v$db_obj ect_cachewhere sharable_mem > 100000and type in ('package', 'package body', 'procedure', 'function');考察返回的結果，確認是否需要pin到共享池中，返回結果中的kept字段如果是yes, 那么表示該對彖已經固定在了共享池中，為no,則表示還沒

13、有固定。如果需要固定，使用下面的語句：exec dbms_shm:red_pool. keep ( ' sys . standard ');數據庫默認安裝的時候沒冇創建dbms_shared_pool包，所以需要先創建該包。cd $oracle_home/rdbms/adminsqlplus、'/ as sysdba"dbmspool.sql如果我們要固定表，那么可以在創建表的時候或者修改表屬性時使用cache關鍵字，將表 放置到buffer cache的lru列表的mru端。通常我們需要對于較小的但是頻繁使川的 表進行這種操作。alter table tab

14、le_name cache；我們也可以將需要頻繁使用的表放置到另外一個獨立的buffer cache屮，比如keep池。 這種操作可以使這些表的數據不至于很快被清除出default buffer cache。alter table table_name storage (buffer pool keep)；評佔準則：本部分沒有評估準則，需耍開發人員或者dba在系統分析以后謹慎執行。二.存儲性能評估在存儲性能評估的時候，我們使用磁盤性能指數(dpi, disk performance index),下表 列出了 dpt屮的各項指數，這個評分系統并不意味著對磁盤的使用和分配的全方位評估，而 只是代

15、表一個晴雨表，反映當麗磁盤的使用和分配上是否存在需要改進或者注意的地方。mpi指數分類所需等級最高分調整表和索引是30表的行連接問題無30分離關鍵的orac 1 e文件是30回滾段的平衡30臨時段的平衡30使用最多的前10個sql的磁盤使用率<5%60是否已經調整使用磁盤最多的前25個sql是40mpi指數總分2501.調整表和索引山于表和索引的數據塊通常是被同時讀取的，所以應該盡量將表和其相關聯的索引放置在不 同的磁盤上，以便減少文件的1/0沖突。檢查方法：select i. index_name, t. table_name, t. tablespace_namefrom user_

16、tables t, user_indexes iwhere t. table_name = i. table_nameand t. tablcspace_name = i. tablespace_name;返冋結果是創建在相同表空間屮的表和相關聯的索引。建議創建新的表空間用于專門存放索 引，并將當前的索引rebuild到新創建的表空間中。al ter index idx name rebui1d tablespace ts_name;評估準則:等級分數表和索引放在同一磁盤上0存儲使用了磁盤陣列，沒有進一步調整20存儲使用了磁盤陣列，對于ratd類型已經作 過調整30表和索引已經規劃在不同磁盤上

17、302.表的行鏈接問題當更新一張表，而數據塊中乂沒冇足夠的剩余空間來容納所作的修改時，就會發生“行鏈 接”現象，該記錄被鏈接到另外一個有-足夠空間的數據塊屮，也就是一條記錄跨越了多個數 據塊，這樣在讀取該記錄的時候就會消耗更多的i/o,當數據庫中有大量的“行鏈接”現象 存在時，數據庫的整體性能就會下降。檢查方法：sqlplus /nologcormect app user/passwordsql> $oracle home/rdbms/admin/ut1 chain.sqlsql> analyze table <table_name> list chained rows

18、;sql> select count(*) chained_rows, table_namefrom chained rowsgroup by table name;如果沒有返回任何行，則表示沒有“行鏈接”現象。否則將按照已經分析過的表顯示每張表 中有多少記錄出現了 “行鏈接”現彖。“行鏈接”現象的產生跟pctfree參數的設置不當有關系。pctfree值默認為10%,如果系 統小存在人量行鏈接，表示這個參數指定的塊保留空間過小，不足以容納塊小所有記錄的更 新操作。此時應該增大相應表的pctfree值。評估準則:等級分數存在行鏈接現象0不存在行鏈接現象303.分離關鍵的oracle文件無

19、論是出于安全性的考慮還是性能的考慮，都建議將關鍵的oracle文件分布在可用的獨立 磁盤上。首先在錯誤出現之后，用來被恢復的數據文件和用來恢復的控制文件，重作h志文件，歸檔 日志文件應該分離存放。如果有可能，將下列各個關鍵文件分布在不同的磁盤上。系統表空間(system)，臨吋表空間(temp),回滾表空間(uxdo),聯機重作日志文件(redo) 和歸檔日志文件(arch),經常訪問的用戶表空間，經常訪問的用戶索引表空間，操作系統 盤,$oracl_ebase中的關鍵oracle軟件文件。至少聯機巫作口志文件(redo)和歸檔口志文件(arch)應該跟其它文件存放在不同的磁盤 上，并且由于口

20、志文件的人部分時間為只寫屬性，所以需要考慮raid5在寫方血的弱孫，盡 量不要將h志文件存放在ratd5的陣列組上。檢杏方法：select file_name, tablespace_name, bytesfrom dba_data_fi lesunion al 1select fi1e_name, tablespacename, bytesfrom dba_temp_filesunion allselect name file_name, null, nullfrom v$controlfileunion al 1select member file_name, to_char(a. gro

21、up#) tablespace_name, b.bytes bytesfrom v$logfile a, v$log bwhere a. group# = b group#union all (select value file name, null, nullfrom v$parameterwhere name like，1og_archive_dest_%jand value is not nullminusselect value filename, null, nullfrom v$parameterwhere name like ，log_archive_dest_state%>

22、;); 返回數據庫中所冇關鍵文件存儲的位置，由dba和sa考察返回的結果，確認已經對于關鍵 文件的存儲位置作過符合實際情況的調整。評估準則:等級分數沒有調整，全部在單個磁盤上0沒有調整，全部在raid上20已經調整304.回滾段的平衡在oracle 9i和0racle9i z前如果沒冇使用回滾段自動管理，那么對于回滾段的性能仍然 是需耍監控并且調整的。檢查是否使用了回滾段自動管理：select name, value from v$parameter where name 1 ike ' %undo_% ；如果返回結果中undo_management的值是auto,則表示使用了回滾段白

23、動管理，同時 undo_tablespace值顯示了自動管理使丿ij的回滾表空間,undo_retention值顯示了在回滾表 空間中保超回滾數據的時限，以秒為單位。注意：如果undo_managemont的值是auto但是undo_tablcspace沒冇設置相應的值，那么 就會使用system表空間屮的system冋滾段，這個是絕對應該避免的現象。如果沒冇使用回滾段自動管理，那么需要監控用戶使用回滾段的頻度，原則上認為不應該冇 超過1個用戶同時使用1個回滾段。檢査方法：select a. name,b. extents,b rssize,b. xacts,b. waits,b. gets,

24、optsize,statusfrom v$rollnamc a, v$rollstat bwhere a. usn 二 b.usn;檢查輸出結果，對于所有回滾段而言，如果xacts (活動事務)和waits (段頭等待)經常 超出1,那么就表明需要增加回滾段數冃，以避免可能出現的爭用。增加回滾段的方法：create rollback segment rs name tablespace rbs storage(initial im next 2m);alter rollback segment rs name online;如果使用了回滾段自動管理，那么可以從v$undostat, v$rol

25、 lstat, dba undo extents等 視圖中查詢當前回滾段的使用和分配情況。評估準則:等級分數有回滾段等待現象0無回滾段等待現彖30使用了回滾段白動管理305.臨吋段的平衡當初始化參數中定義的sort_area_s1ze大小無法滿足排序要求的空間，就會使用臨吋表空 間小的臨時段進行排序，磁盤排序比內存排序要慢100-10000倍，所以盡量減少磁盤排序是 性能調整工作的一個重要部分。川能引起排序的操作create index, distinct, order by, group by 等。檢杏方法：select namc, value from v$sysstat where na

26、me 1 ike ' %sorts%'返回結果中的sorts (memory)表示內存排序，而sorts (disk)則表示磁盤排序，如果存在 大量的磁盤排序，則表明我們需要增加sort_area_stze或者hash_area_stze等排序區的大 小，或者需要檢查口前系統屮消耗大量磁盤的sql是否已經經過調整(檢杳前25位消耗磁 盤的sql在后面部分將提到)。檢查使用磁盤排序的會話信息，可以定位執行了大量磁盤排序的會話。檢杳方法：select b. name, a. sid, a. valuefrom v$sosstat a, v$statname bwhere a. st

27、atistic# = b.statistic#and b. name = ' sorts (disk)'and a. value > 0order by & value desc;如果冇可能我們應該將臨時表空間中的多個臨時數據文件分布在不同的磁盤匕以減少排序 時可能會產牛的磁盤沖突。在0racle9i中，我們可以設置pga_aggregate_size初始化參數來指定所有會話將使用的 pga大小，同時也必須設置workarea_s1ze_pol1cy參數為auto。具它詳細信息見內存性能 評估屮“4。內存屮的排序”部分。評估準則:等級分數對于存在的磁盤排序沒有評估

28、0已經就存在的磁盤排序進行過調整306.最浪費磁盤讀操作的詢10個語句占所有語句的比例通常一個沒冇優化系統中，10個最常用的sql語旬的訪問雖會占到整個系統中磁盤讀操作 的50%以上。這些sql是最需要進行優化的部分，也是優化工作小優先級很高的部分。通常 我們的優化目標是將這些sql的磁盤讀操作百分比降低到5-19%0檢查方法：select sum(pct_bufgets)from (select rank () over (order by disk_reads desc) as rank_bufgets,to char (100 * ratio to report(disk reads)

29、over(), ' 999. 99)pct bufgetsfrom v$sqlarea)where rank_bufgets < 11;評估準則:等級分數<5%605-19%5020-25%30>25%07.調整前25個最浪費磁盤讀操作的語句在沒有調整的情況下，絕大多數系統中，訪問量占前25位的語句的磁盤讀操作將占用整個 系統所有磁盤讀操作的75%,對這部分語句進行調整是至關重要的。這部分腳本用于獲得訪 問量占前25位的sql語句。輸出結果中的exec表示該sql被執行的次數。檢查方法：set serveroutput on size 1000000declareex

30、ecution number;top25 number;textl varchar2(4000);x rmmber;lenl number;cursor cl isselect executions, disk_reads, substr(sql_text, 1, 4000)from v$sqlareaorder by di sk reads desc;begindbms_output. put_line(，exec' i i ' ' | | reads* i i ' ' | | text*);dbms output. put_line(，h，| |，|

31、 |，| |，)；open cl;for i in 1 25 loopfetch clinto execution, top25, tcxtl;dbms_output. put line(rpad(to_char(execution), 5)rpad(to_char(top25), 8) i i ' ' | |substr (text 1, 1, 66);lenl := length(textl);x := 66;while lenl > x - 1 loopdbms_output< put_line c - ' | substr (textl, x, 66

32、);x := x + 66;end loop;end loop;end;/評估準則：本部分沒有具體的評估準則，需耍開發人員或者dba去確認在這25個sql中屬于應用系統 的語句是否都已經作過調優。8.其它存儲和關的調整1) 字典管理表空間中的extent總數不超過4096檢查方法：select a. tablespace_name, sum(a. extents)from dba segmcnts a, dba tablespaces bwhere a. tablespace name = b tablespace nameand b. extent management = ' dictionary' group by a. tablespace_name order by sum (a. exte ms);檢杳輸出結果，如果顯示某個表空間屮的extents總數超過了 4096,那么需要擴大這個表 空間的extent大小，過多的extent對于dmt的空間管理有負面影響。2）本地管理表空間中單個segement的extent數不超