Oracle進階培訓 第7課 學習筆記 作者：JackYang (JackYang.sh@gmail.com)日期：2006-10-20 本課實驗已通過上機驗證。 效能調優以下筆記內容與老師提供的檔案ORA_TRAIN6.PPT相對應 效能調優到底由誰調優，不僅是DBA的事情，和系統開發人員密切相關。在應用設計和編程階段對效能調優很重視，那麼很多效能問題不會出現。 誤解：開發完畢再調優。開發完畢再調優很困難。 和系統管理員也有關，UNIX和WINDOWS系統參數是否調優。 如何調優？調優哪些東西？ 調優目標包括：回應時間資料庫的可用性資料庫的命中率。ORACLE裡面很多事情在記憶體中進行。如果所要的資料在記憶體中找不到。那麼就要從檔案中讀取。效率就會受到影響。 調優目標包括：儘可能少的訪問硬碟上的資料區塊。盡量訪問記憶體中的資料。在記憶體中緩衝資料區塊。希望執行一條SQL語句時，不要編譯和解析。而是從共用記憶體中訪問。屬於命中率的一種。在記憶體中找到SQL已經解析過的代碼。在記憶體中找不到叫脫靶。如果一定要進行I/O操作，那麼希望I/O操作能盡量快。任何使用者的操作，不需要等待資源。通過調優調整資源的爭用。希望備份操作和日常的管理操作能夠對ORACLE資料庫的正常運行產生最小的影響。盡量在晚上做。 主要調整的項目：最佳化執行個體提高SQL語句重用的機率。希望在記憶體中找到已經編譯的代碼。空間的管理。包括，記憶體空間管理和資料檔案的管理最佳化對資料的訪問。 調優步驟調整設計調整應用 即調整SQA調整記憶體 調整redo log buffer，shared buffer，large buffer調整IO 調整爭用(衝突) 調整鎖，latch(鎖存器)調整作業系統。 UNIX，WINDOWS ORACLE中和效能調整密切相關的檔案：Alert log file 警告檔案，ORACLE的重要操作和內部錯誤，會在ALERT LOG FILE中產生一些資訊。非預設的參數也會顯示。DBA每天應該看一次ALERT LOG FILE，不管資料庫是否正常。因為有些問題時積累的，越早發現越容易解決。ALERT log file會不斷增長。每天檢查後，覺得沒問題，就清空。也可以進行備份。 通過BACKGROUP_DUMP_DEST參數設定ALERT_LOG_FILE的位置。對每一個資料庫都應該設定一個此參數，保證每個資料庫的ALERT檔案在不同地方，找起來比較方便。 在ALERT log file中可以看到不同進程產生的資訊。 BACKGROUND TRACE fileORACLE能夠跟蹤每一個後台進程，跟蹤之後能夠產生TRACE file。不同進程產生的跟蹤資訊，放在不同的trace檔案。trace檔案和每一個後台進程對應。每一個今後台進程有很多個trace檔案。 user trace file使用者也可以產生TRACE檔案，記載SQL語句。使用者把TRACE設為TRUE，每執行一條SQL語句，都會在TRACE檔案中記載。需要通過ORACLE的工具來對這些TRACE檔案進行翻譯。這個功能影響效能，所以一般都關閉這個功能，只有在調試的時候才開啟。可以有3級初始化參數檔案中SQL_TRACE參數為TRUE，就開啟使用者TRACE使用ALTER SESSION命令，在目前使用者會話中，啟用使用者TRACE功能ORACLE的程式包中有一個procedure SET_SQL_TRACE_IN_SESSION,可以在應用程式中調用。 USER_DUMP_DEST這個參數，在初始化參數檔案中設定，指定使用者TRACE檔案的路徑。 調優的工具：動態視圖 V$打頭的視圖DBA_打頭的視圖UTLBSTAT.SQL和UTLESTAT.SQLUTLBSTAT.SQL開始收集ORACLE的資料。UTLESTAT.SQL結束收集ORACLE的資料。oracle8之前用這兩個SQL檔案，不推薦使用，產生的報表不方便看，對結果不儲存。 ORACLE9i之後使用statspack來代替這兩個SQL檔案，不需要開始和結束的時候都運行。可以在任何時刻運行這個statspack，運行這個程式，就會產生當時資料庫的快照，包括：CPU、記憶體、IO、鎖、鎖存器、爭用的各種情況。而且可以產生statspack的報表，這個報表易讀。 Enterprise Manager，圖形介面好用。ORACLE的診斷調整包。可以通過ENTERPRISE MANAGER來調用。www.oracle.com.cn上常會有些ORACLE專家提供的工具下載。 一些用的較多的視圖DBA_TABLESDBA_TAB_COLUMNSDBA_CLUSTERSDBA_INDEXESINDEX_STATS 在這些表裡面有些統計資料是沒有的，只有運行ANALYZE命令之後，才會有相應的統計資料。 X$tables 是ORACLE開發人員和ORACLE全球支援使用的。內容格式不公開。 在調優時會碰到的表/動態視圖，分以下幾類和執行個體有關和記憶體有關和DISK有關和爭用有關和使用者的會話有關 最重要的5個視圖V$STATNAME包含所有參數的名稱 V$SYSSTAT重中之重，整個系統的統計資料。從資料庫啟動時開始統計，累計的資料。statspack工具的來源資料就是從這個動態視圖得到。包含所有參數的統計值statistic# 統計標號name 參數名稱class 類別value 參數的值 V$SGASTAT調整ORACLE記憶體參數POOL 用於區分各種用途的POOLname 相應的參數名稱bytes 記憶體的大小 V$EVENT_NAME對ORACLE資料庫進行調整，一個要看統計參數，另一個要看EVENT每次等待會產生一個EVENT找到EVENT，就找到了問題所在，定位問題。event#nameparameter1parameter2parameter3 V$SYSTEM_EVENTtotal_waits 總共等待了多少時間total_timeouts總共逾時多少次time_waited average_wait 平均等待的時間 同樣重要的幾個表前面是SYSSTAT，這裡是SESSIONSTATV$SESSTAT和V$STATNAME勾連起來能夠找到SESSION的name。 V$SESSION V$SESSION_EVENT V$EVENT_NAME V$SESSION_WAIT 在剛才這些動態視圖中看到統計參數，就把TIMED_STATISTICS設定為trueoracle9i中這個參數預設為true，ORACLE8i中這個參數預設為false8i到9i主要就是實現了管理的自動化。 statistics報告中可以看到的內容 ORACLE中很重要的一張表stats$lib decode(gethits,0,1,gethits)gethits如果為0，就變成1，不為0就等於gethits。避免gethits為0。
實驗一：實驗目的：效能調優1. dos> cd ora9lab2. dos> set ORACLE_SID=DB913. dos> sqlplus /nolog4. SQL> connect sys/ora123 as sysdba5. SQL> startup6. SQL> select name from v$datafile; 看看資料檔案在哪個目錄下 7. SQL> create tablespace perfstat datafile ‘D:/perfstat/perfstat1.dbf’ size 200m autoextend on; 建立資料表空間 之前先在D盤下建立perfstat子目錄 8. SQL> @?/rdbms/admin/spcreate 輸入perfstat_password的值：輸入ora123 輸入default_tablespace的值：輸入perfstat 輸入 temporary_tablespace的值：輸入TEMP，一個暫存資料表空間 相對應的命令是@?/rdbms/admin/spdrop 9. SQL> exec statspack.snap 開始統計，照張快照。做些操作之後，再照張快照。 進入了perfstat目錄 10. 再開一個視窗11. dos> cd ora9lab12. dos> sqlplus /nolog13. SQL> connect sys/ora123@DB91D as sysdba14. SQL> select * from scott.dept; 15. 回來原來視窗16. SQL> exec statspack.snap 再做一張快照 17. SQL> select username from v$session 查看目前使用者 18. SQL> @?/rdbms/admin/spreport 建立一個報表 可以看到做了幾張快照 輸入begin_snap的值：輸入1 輸入end_snap的值：輸入2 輸入report_name的值：可以之間按斷行符號，報表名稱產生在預設目錄下。 19. SQL> host dir 查看報表在哪個目錄下 名稱是sp_1_2.LST sp_起始快照序號_結束快照序號.LST redo nowait 要大於90%以上，小於的時候要進行調整。 in-memory sort要大於90%,70%以下就有問題 Execute to Parse 執行解析的次數 Top 5 Timed Events 等待時間最長的5個事件，是發現效能問題的關鍵。 log file parallel write 從redo log buffer寫入redo log file Background Wait Events 後台進程的等待 Waits一般是指次數 對這張報告要仔細研究。 20. SQL> @?/rdbms/admin/sppurge 刪除一個範圍內的快照 輸入 losnapid的值：範圍開始的id號，1 輸入 hisnapid的值：範圍結束的id號，2 兩個快照之間運行應用程式，統計資料就比較準確了。
