ORACLE傻瓜手册长篇连载--第六部分

6 資料庫優化
所有參見內容都在附件05_optimize下。
一個以資料庫為核心的應用系統，其80%以上的效率決定于應用軟體架構是否合理、簡潔、高效，支撐軟體和硬體終究是放在第二位考慮的問題，這是系統分析員和程式師們的價值所在和使命所系。
6.1 通用設置
對於每個資料庫系統，以下設置是固定的，企圖在此僅通過某種設置優化實現神奇的效果，不是解決問題的正確途徑，效果極為有限。
6.1.1 硬體配置
I/0負載均衡
按照一般要求，在沒有RAID的情況下，將I/O頻繁的資料檔案分配在不同磁片上，使磁片負載均衡。
臨時表空間、回滾表空間、線上日誌的I/O操作都比較密集，同時應用資料物件在表空間上的分佈也決定I/O操作的分佈。
RAID
關於RAID 5的寫效率一直存在爭論，在實際應用中表現並不突出。如果有條件，可使用RAID 0+1。RAID 5適用於磁碟空間有限，又要求保證一定容錯功能的中小型應用。
增加RAID卡緩存，並將Cache選項置為WriteBack而非WriteThrough。此兩項對系統I/0影響之大，令人印象極其深刻。
6.1.2 應用配置
表與索引分在不同表空間中，分配適宜的資料塊尺寸
為表和索引建立不同的表空間，並在創建表空間時指定于此建立的表或索引的缺省存儲參數，這樣此表空間上的所有物件使用統一的資料塊尺寸、擴展率和最大可用資料塊數量等參數，簡化了配置過程，減少了存儲空間的碎片
根據應用確定索引
檢查應用程式SQL語句，盡可能在where的查詢條件中使用整個索引，如不能滿足，至少用到索引首列。
如在emp中建立索引：create index emp_x01 on emp(name,age)。
如果查詢條件包含索引首列name：
select salary from emp where name like ‘Tom%’
這樣的查詢能夠利用索引emp_x01。
如果查詢條件不包含索引首列name，即使使用了索引的某個域，也不能利用到索引emp_x01:
select salary form emp where age=25;
索引首列的選擇應綜合考慮查詢條件的組合情況，同時為使索引B+樹盡可能產生多的分叉，應使用值變化較多的域，否則可考慮建立點陣圖索引（bitmap index）
查詢條件中對列的函數運算，列與列之間的比較會使執行效率大大降低，原因還是在於不能充分利用索引：
select salary from emp where substr(name,2,1)=’o’; #名字第二個字母是’o’
慎用order by，group by，它們會選出所有符合條件的記錄，在temp表空間上進行處理，再輸出。
評估應用程式中SQL語句的合理性
explain可以獲得SQL語句的執行策略，從而提供改進的思路，但由於執行環境的不同而無法準確推斷執行效率。
執行$ORACLE_HOME/rdbms/admin/utlxplan.sql建立plan_table
SQL>explain plan select salary from emp where age>25；
SQL>start $

ORACLE_HOME/rdbms/admin/utlxpls.sql或utlxplp.sql
查看分析結果，主要看是否出現表掃描、涉及的記錄條數和耗費時間
6.1.3 日常性能監控
utlbstat和utlestat
編寫以下兩個腳本：
run_utlbstat.sh
sqlplus “/ as sysdba” << EOF
@${ORACLE_HOME}/rdbms/admin/utlbstat.sql
exit
EOF
run_utlestat.sh
cd /export/oracle/tuning_report
sqlplus “/ as sysdba” << EOF
@$ORACLE_HOME/rdbms/admin/utlestat.sql
exit
EOF
選擇適當時間，如4:00運行run_utlbstat.sh，22:00運行run_utlestat.sh，可通過crontab方式執行。在/export/oracle/tuning_report下生成report.txt，並分析：
stats$lib PINHITRATIO應高
stats$waitstat 如undo header較高，則應加入更多回滾空間
如segment header較高，則應加入更多freelist(待查)
stats$roll 如TRANS_TBL_WAITS較高，則應加入更多回滾空間
stats$dc GET_MISS, SCAN_MISS應較低
stats$files I/O應平均分佈於各磁片
READS和BLKS_READ相差較大表明表掃描，相差較小表明index充分被利用
6.2 實戰分析
當應用系統出現聯機處理或批次處理程式執行時間變長、反應緩慢的現象，就應該積極尋找減少消耗增加效率的方法。硬體升級是最後的終極的手段，也是最不能體現人的能力的手段，矛盾的暫時平息不意味未來不會再次爆發。
6.2.1 總體分析
分析一個高負荷、低效率的資料庫系統先從分析作業系統的表現開始。
cpu利用率
使用sar 2 10發現：
a) %wio保持在50以上，%usr和%sys數值偏低
b) 或者%usr異常高，保持80以上，%wio幾乎為0
而%idle總是接近0。
情形a）說明資料的檢索量極大，無法從緩衝區中及時得到，oracle系統進程被迫從資料檔案上將資料讀入緩衝區替換掉一些陳舊的資料，從而使oracle服務進程處於等待狀態。這是緩衝區不足的表現，可以通過適當增加緩衝區大小來緩解矛盾，但不能根本上解決問題，首先記憶體區域有限，不可能一直增加下去；其次即使緩衝區足夠容納下所需資料，矛盾會向b）轉化。
情形b）說明oracle服務進程已能從緩衝區中得到幾乎所有需要的資料，但由於冗餘數據多，分析時間非常漫長，使cpu計算能力過多地被分配到oracle服務進程上。
兩者的發生多為檢索策略失當，特別在對索引的設計和利用上，從而導致表掃描。
必須結合應用程式的設計來進行改進，僅在資料庫層面上很難有所作為。
曾經發生過一種情形類似a），只是%wio不算太高，而%idle很高。無法用常理分析，後更換存儲設備後解決，推測是存儲設備的問題。
記憶體和交換空間
每個oracle服務進程大約佔用5M左右記憶體（mem），記憶體不足時，容易引起物理頁的頻繁換入換出，使系統I/O活動增加；還會佔用20M左右交換空間（swap），當交換空間不足時，就不能增加

新的oracle連接。
vmstat 2 10觀察記憶體和交換空間的空閒情況，注意pi（kilobytes page in），po(kilobytes page out)，結合sar –d 2 10觀察磁片的繁忙程度，及時調整硬體。
top也能對記憶體和交換空間進行監控。
改進措施無非就是增加記憶體和交換空間，以及控制oracle服務進程的數目。
聯機日誌
sar –d 2 10觀察磁片I/0，注意read和write的比例
如write比例過高，打開$ORACLE_BASE/admin/oradb/bdump/alert.log，檢查日誌檔切換間隔，如小於15分鐘，說明日誌檔太小，導致切換頻率過高，引起緩衝區和資料檔案的同步（checkpoint）發生過於頻繁。也可能由於資料庫同步寫進程（db_writer_processes）數量太少。
改進措施是創建新的日誌檔組，適當擴大日誌檔的尺寸，使同步間隔保持在30分鐘以上為宜。
參見《配置》中的log_checkpoint_interval和db_writer_processes，作綜合考慮。
6.2.2 詳細分析
排除了上述效率問題，就要開始對資料庫本身進行考察。
6.2.2.1 察看session當前執行的SQL語句
使用top觀察oracle服務進程cpu佔用情況，通常情況下單個進程不應超過5%，如果超過10%，一定發生了嚴重的檢索策略失當
在此情況下，可認為有問題的oracle服務進程代表的session當前執行的SQL語句耗費了大量的時間，通過分析此SQL語句，能獲得改進效率的線索
記錄下待分析的oracle服務進程的進程號PID
獲得oracle服務進程的session id，對應的應用進程資訊，如進程號、主機名、程式名，以及正執行的SQL語句在緩衝池中的編號
sqlplus “/ as sysdba”
SQL>select s.sid,s.serial#,s.process,s.machine,s.program,s.sql_hash_value from v$session s, v$process p where p.spid=’PID’ and s.paddr=p.addr;
獲得正執行的SQL語句的文本
SQL>select q.sql_text from v$session s, v$process p,v$sqlarea q where p.spid=’PID’ and s.paddr=p.addr and s.sql_hash_value=q.hash_value;
這個方法顯示此oracle服務進程的會話當前使用的sql命令，並不一定是導致效率低下的sql命令，但低效率的命令往往在會話中停留較長的時間，所以大部分情況還是能夠找出來的。
6.2.2.2 某段間隔session SQL語句執行情況
接上節，為更精確地分析此session中SQL語句的執行情況，需要獲得某段間隔各SQL語句所占的比重，從而可以針對耗時較長的語句進行改進
記錄下待分析的oracle服務進程的進程號PID
sqlplus “/ as sysdba”
SQL>select s.sid, s.serial# from v$session s, v$process p where p.spid=’PID’ and s.paddr=p.addr;
SQL>alter system set timed_statistics=true;
SQL>execute dbms_system.set_sql_trace_in_session(sid,serial#, TRUE);
或execute dbms_session.set_sql_trace(TRUE)或alter session set sql_trace=true #對當前session做trace分析，分析檔生成於initoradb.ora中user_dump_dest指定的目錄下
一段

時間後……
SQL>alter system set timed_statistics=false;
SQL> execute dbms_system.set_sql_trace_in_session(sid, serial#, FALSE);
利用工具tkprof將report.trc檔轉化為可讀形式
tkprof report.trc report.txt sort=EXECPU explain=system/manager ＃選擇CPU佔用時間進行排序
舉例：
call count cpu elapsed
parse 5 0.15 2.08
execute 12 6.30 41.04
fetch 0 0.00 0.00
update work_user_skill set work_level=:b0,skill_value=:b1 where (mb_loginname=:b2 and work_type=:b3) ＃mb_loginname和work_type恰好為主鍵
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF ‘WORK_USER_SKILL’
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF ‘WORK_USER_SKILL’
分析：
cpu與elapse相差很大說明I/O繁忙，請求等待時間長，有可能進行了表掃描
TABLE ACCESS出現FULL，即表掃描，說明：
可能一：未使用index。
可能二：使用了index，而未用首列。
可能三：使用了index，而由於其檢索資訊（主要是B+樹）過於陳舊，使oracle在決定檢索策略時，錯誤地選擇了表掃描。
解決：
對策一：根據應用的實際需要酌情建立索引。
對策二：修改查詢條件，或者修改索引的組成域和順序，確保索引首列被使用。
對策三：更新相關索引的陳舊檢索資訊
sqlplus dbuser/oracle
SQL>analyze index emp_x01 validate structure;
或者簡單些
SQL>analyze table emp validate structure cascade; #不僅重新檢查了table的存儲情況，還更新了它的所有index的檢索資訊
另外可重新組織index
SQL>alter index emp_x01 rebuild [online]; #建議使用online選項，可以與更新此索引的其他操作同時進行
參考命令：
生成table的統計資訊至dba_tables,user_tables
analyze table emp compute|estimate statistics for table [ all columns | columns col1,col2 | all indexed columns ]
生成index的統計資訊至dba_indexes,user_indexes
analyze index emp_x01 compute|estimate statistics
6.2.2.3 檢查被鎖的物件
由於某種原因操作的物件被加鎖，而又不能在短時間內解鎖，會在此物件上形成sql操作等待佇列，導致部分操作不得不超時退出，系統效率下降到難以接受的程度。這種現象可能是鎖死，也可能因為某些事務耗時太長，影響操作同一物件的短事務。通常是應用程式組織不當的結果。
檢查被長時間鎖的對象：
sqlplus “/ as sysdba”
SQL>select a.session_id,a.process,a.locked_mode,b.object_name,b.object_type,b.status from v$locked_object a,dba_objects b where a.object_id=b.object_id;
6.3 專題分析
6.3.1 巨表查詢
參見hugh,腳本都在script下，程式都在program下。。
問題的提出
在資料庫應用的開發中常常需要記錄用戶歷史、流水等這樣的資料，開發者的一般思路是僅建立一張表，以一個不重複的遞增的序列號作為主鍵，配合查詢的需要建

立若干索引。
然而在資料條數積累到相當多的時候，比如幾千萬這樣的級別，查詢的效率問題就會成為令人頭痛的瓶頸，因為查詢憑藉的索引本身也達到了相當複雜龐大的程度，任何一次查詢都會帶來很高的開銷。由此還給批次處理、資料備份、資料清理帶來相當大的困擾。
儘管資料庫系統在提高查詢效率上已經提供了相當強勁的功能，但單純依賴資料庫往往不能應對複雜多變的現實情況。不得不採用一些專用設計，但需要跟通用做法進行權衡。
試圖尋找解決巨表查詢的方法，同時考慮巨表備份、清理。為形象地說明，建立user_log表，這張表儲存典型的用戶歷史，包括序列號、日期、用戶名、歷史資訊等。
步驟：
在某資料庫實例中建立用戶dbuser，口令為oracle
進入script目錄
根據create_tablespace.sql建立各表空間
根據create_table.sql建立表和索引
運行genlog生成測試用資料
運行loadlog將資料上載到資料庫中
進入program目錄
運行make
資料組織方面，設想有一萬個用戶訪問，每天留下20萬條記錄，延續15天，總共300萬條記錄，平均每人300條，每天20條，訪問的資料隨機生成
硬體設備為PIII800EB+512M+Quantum FireBall 10代
分割建表
由於user_log中log_dt（記錄日期）是一個劃分資料範圍的天然標誌，所以很自然地想到把日期作為表名的一部分，從而將巨大的表分割成較小的表，期望從較小的表和索引中獲得較高的查詢效率。能否實現這個想法？於是有以下測試。
program目錄下有user_log_static.c和user_log_dyna.c，前者對整個user_log查詢，後者對已分割成user_log_yyyymmdd的表查詢，隨機生成某個用戶名，選出在log中這個用戶的全部記錄，共100次。運行7次，前2-3次由於資料緩衝的原因與後面的相差較大，可以忽略掉。
選出全部記錄 （以秒為單位）
未分割 198 122 118 71 53 46 46
分割 255 129 79 58 50 48 48

從結果上看，分割幾乎沒有任何效果。根據理論分析，表分割後，應用不得不使用動態SQL對每張表分別操作，原先的一次查詢被分成多次串列，儘管每次查詢的資料量大為減少，但疊加上去也很可觀。查詢的日期範圍越大，疊加的效應越強。所以在這種情況下，分割表的做法不能帶來效率上的提高，甚至可能更糟。
但在通常的應用中，不需要一下子選出全部的查詢所得，更多的是分批提供，比如顯示用戶歷史記錄，一屏可能就只有10條，向前或向後翻頁時再次向伺服器提交請求。試想在分割表的情況下，有可能在取到足夠記錄時，只需要查詢數目很少的表，這樣效率提高就很明顯。修改user_log_static.c和user_log_dyna.c，將Process函數中關於變數count的兩行注釋去掉，只選出10

0條記錄就中止查詢，大致估計訪問5張分割表。
100條記錄 （以秒為單位）
未分割 221 111 58 41 41 36 35
分割 96 51 30 25 18 15 14
可以作出這樣的結論：只要將訪問的表的數目控制在較小的範圍，分割表的查詢效率就能得到明顯的提升。
分區建表
在oracle8中可以使用partition選項將表或索引存放在不同的區域中，這樣的做法與上面的分割表方法似乎是殊途同歸。
program目錄下有user_log_partition.c，對user_log_partition進行查詢，user_log_partition的資料欄和user_log完全一致，資料也是使用同樣的規則隨產生，可以認為user_log_partition比起user_log差別僅在於partition選項。
索引的分區是否能帶來性能上的提升？先測試資料、查詢使用的索引都分區配置的情況，第一行選出全部記錄，第二行選出100條記錄
（以秒為單位）
全部 236 121 73 48 46 42 40
100條 47 45 44 40 38 38 37
資料分區配置，而查詢使用的索引非分區配置，第一行選出全部記錄，第二行選出100條記錄
（以秒為單位）
全部 238 120 69 48 45 42 41
100條 37 37 39 37 35 35 33
無論資料還是索引使用或不使用分區，都沒能帶來預期中的效率提升，也許是測試案例不夠全面，測試用機器不夠專業的原因吧。
總結
應用層次上的表分割，還是資料庫層次上的表分區，是值得考慮的選擇。前者以縮小單張表的規模為目標，企圖在較小的資料範圍內以較高的效率完成工作，同時資料備份、清理也能以表為單位進行處理，代價是應用必須圍繞著設計調整，通用化的程度弱，編程方面考慮得多，特別在應用某些成型軟體時，幾乎不能作這樣的調整。而表分區能在不改動應用的前提下，透明地提高查詢效率（見Oracle文檔，儘管在本次有限測試中未能體現），缺點在於配置過於繁瑣，資料庫管理員需要對應用的非常瞭解，根本上講只是在巨表內部作了有限的優化。
6.3.2 對比測試
參見benchmark,腳本都在script下，程式都在program下。
這組測試的目的是為了搞清楚對表的不同處理方式會導致效率上怎樣的差異，同時希望以數字代替長久以來的猜測。
結合筆者曾經參與過的一個專案，為測試設立了3張表，參見script/create_table.sql：
charge_fee：費用
charge_fee_dtl：費用明細
charge_fee_cfg：費用配置
charge_fee模擬了某種費用，如水、電、煤、電話等每月的繳付記錄，包括繳費狀態、費用月份、用戶名、繳費日期等資訊。生成1,000,000條記錄，分佈在10,000個用戶名上，平均每個用戶100條記錄。
charge_fee_dtl進一步說明某筆費用的具體情況，如電話費常常細分為固定費用、市話、長話、漫遊、國際等。對應charge_fee，每條費用記錄擁有5個分項（假定用戶不

會在每個分項上都產生費用，所以從10個分項中隨機選擇了5個），共有5,000,000條記錄。它採用charge_fee的序列號，也就是主鍵，作為分項和費用多對一關係的憑據。
charge_fee_cfg對charge­_fee_dtl中每個分項進行文字說明，是一張配置表，共有10條記錄，代表10個分項。
這3張表大致反映了大（charge_fee）－大（charge_fee_dtl）－小（charge_fee_cfg）的模式，下面分析在它們上面使用嵌入式SQL進行查詢的效率。
測試伺服器硬體配置：PII400，512M SDRAM，昆騰火球10代10G。
軟體配置：Solaris 8 Intel Platform Edition 10/01，Oracle 8iR3
Join vs Not Join
在開發程式的時候，表與表間的連接是經常的選擇，這樣做可以很簡潔地寫出代碼。然而，將涉及的表連接起來和順序選出之間的效率差異，哪些場合可以使用或者不適合使用連接，等等諸如此類的問題往往容易為開發者忽略。
大（charge_fee）－大（charge_fee_dtl）連接：
模擬需求：選取某個用戶的費用記錄，並選出每筆費用的分項記錄（常用於查詢、繳納費用的場合）。
這個需求將2張記錄都很多的表charge_fee和charg_fee_dtl聯繫起來，以比較採用連接與否的效率差異。
引入程式bb_join.c和bb_nojoin.c（bb—big table & big table），前者使用一個連接2張表的游標，直接選出費用和分項記錄，後者僅使用對charge_fee的游標，選出費用記錄後，再根據費用序列號，從charge_fee_dtl中選出分項記錄。兩者都使用了order by選項。
為更好地顯示查詢效果，每次程式運行隨機選取100個用戶，記錄總耗時。同種測試進行7次。
上面說過平均每個用戶擁有100條費用記錄，而每條費用記錄擁有5個分項。
測試一：選出用戶全部100條費用記錄
（以秒為單位）
連接（bb_join） 96 78 77 76 77 76 77
不連接（bb_nojoin） 92 96 93 97 95 95 95
結果有點出乎筆者的意料，因為筆者長久以來認為2張記錄都很多的表的連接會帶來很大的系統開銷，然而事實證明，連接和不連接（順序查詢）的效率近似，而且連接的表現更好一些！
聯繫到上一節《巨表查詢》的結果，筆者很快就想到：不連接方式由於對每筆費用記錄（charge_fee）都要重新展開一個對分項記錄（charge_fee_dtl）的游標，總計大約100（每個用戶費用記錄條數）*100（用戶數）=10,000次之多，耗費很大，而實際應用中很難有一次選出某用戶上百條記錄的情況，如果減少一點，比如每個用戶只選出20條，游標開啟次數降為約2,000次，結果會怎樣？
基於這個想法，在2個程式中加進了對費用記錄條數的控制，見注釋部分。
測試二：選出用戶前20條費用記錄
（以秒為單位）
連接（bb_join） 82 75 67 67 66 68 72
不連接（bb_nojoin） 24 17 1

3 10 9 8 8
結果同樣令筆者吃驚！性能提高得很明顯，由此可以得出與《巨表查詢》相似的結論：在涉及2張記錄都很多的表操作中，如果採用不連接的方式，只要將順序完成的查詢控制在一定數量內，一定能取得比連接方式高的效率。
大（charge_fee_dtl）－小（charge_fee_cfg）連接：
模擬需求：選取某筆費用的分項記錄，並顯示每筆分項的文字說明（常用於列印單據的場合）。
這個需求將1張記錄都很多的表charge_fee_dtl和另1張記錄極少的表charg_fee_cfg聯繫起來，以比較採用連接與否的效率差異。
引入程式bs_join.c和bs_nojoin.c（bs—big table & small table），前者使用一個連接2張表的游標，直接選出分項和配置記錄，後者事先將charge_fee_cfg全部讀取到記憶體中，僅使用對charge_fee_dtl的游標，選出分項記錄後，再根據分項類型，從記憶體中選出分項的文字說明。兩者都使用了order by選項。
為更好地顯示查詢效果，每次程式運行隨機選取30,000筆費用記錄，記錄總耗時。同種測試進行7次。
（以秒為單位）
連接（bs_join） 77 75 76 74 76 74 75
不連接（bs_nojoin） 67 66 66 65 67 65 66
結果差不多，可以得出結論：在涉及1張記錄很多的表和另1張記錄很少的表操作中，採用連接與否，效率相差不大。從靈活的角度考慮，建議對記錄很少的表（往往是靜態配置）多利用記憶體緩衝。
Static vs Dynamic
這裏比較靜態嵌入式SQL和動態嵌入式SQL語句的效率差異，動態方式多了一個對SQL語句的prepare過程。
引入程式bs_nojoin_dyna.c，其實就在bs_nojoin.c的基礎上，把靜態嵌入式SQL語句的部分代碼改成動態嵌入式SQ