Oracle高階培訓 第7課 學習筆記

magus_yang發表於2020-04-04
Oracle筆記
 
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 file
ORACLE能夠跟蹤每一個後臺程式，跟蹤之後能夠產生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.SQL
UTLBSTAT.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_TABLES
DBA_TAB_COLUMNS
DBA_CLUSTERS
DBA_INDEXES
INDEX_STATS
 
 
在這些表裡面有些統計資料是沒有的，只有執行ANALYZE命令之後，才會有相應的統計資料。
 
X$tables 是ORACLE開發人員和ORACLE全球支援使用的。內容格式不公開。
 
在調優時會碰到的表/動態檢視，分以下幾類
和例項有關
和記憶體有關
和DISK有關
和爭用有關
和使用者的會話有關
 
最重要的5個檢視
V$STATNAME
包含所有引數的名稱
 
V$SYSSTAT
重中之重，整個系統的統計資料。從資料庫啟動時開始統計，累計的資料。statspack工具的源資料就是從這個動態檢視得到。
包含所有引數的統計值
statistic# 統計標號
name      引數名稱
class       類別
value       引數的值
 
V$SGASTAT
調整ORACLE記憶體引數
POOL     用於區分各種用途的POOL
name       相應的引數名稱
bytes       記憶體的大小
 
V$EVENT_NAME
對ORACLE資料庫進行調整，一個要看統計引數，另一個要看EVENT
每次等待會產生一個EVENT
找到EVENT，就找到了問題所在，定位問題。
event#
name
parameter1
parameter2
parameter3
 
 
V$SYSTEM_EVENT
total_waits     總共等待了多少時間
total_timeouts總共超時多少次
time_waited   
average_wait   平均等待的時間
 
 
同樣重要的幾個表
前面是SYSSTAT，這裡是SESSIONSTAT
V$SESSTAT
和V$STATNAME勾連起來能夠找到SESSION的name。
 
V$SESSION
 
 
V$SESSION_EVENT
 
 
V$EVENT_NAME
 
 
V$SESSION_WAIT
 
在剛才這些動態檢視中看到統計引數，就把TIMED_STATISTICS設定為true
oracle9i中這個引數預設為true，ORACLE8i中這個引數預設為false
8i到9i主要就是實現了管理的自動化。
 
statistics報告中可以看到的內容
 
 
ORACLE中很重要的一張表
stats$lib
 
decode(gethits,0,1,gethits)
gethits如果為0，就變成1，不為0就等於gethits。避免gethits為0。
 
 
實驗一：
實驗目的：效能調優
1.         dos> cd ora9lab
2.         dos> set ORACLE_SID=DB91
3.         dos> sqlplus /nolog
4.         SQL> connect sys/ora123 as sysdba
5.         SQL> startup
6.         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 ora9lab
12.     dos> sqlplus /nolog
13.     SQL> connect sys/ora123@DB91D as sysdba
14.     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
 
兩個快照之間執行應用程式，統計資料就比較準確了。
 

相關文章