基於oracle的sql最佳化方法論，oraclesql方法論

基於oracle的sql最佳化方法論，oraclesql方法論
Oracle資料庫裡SQL最佳化的終極目標就是要縮短目標SQL語句的執行時間。要達到上述目的，我們通常只有如下三種方法可以選擇：
1、降低目標SQL語句的資源消耗；
2、並存執行目標SQL語句；
3、平衡系統的資源消耗。
“方法1：降低目標SQL語句的資源消耗”以縮短執行時間，這是最常用的SQL最佳化方法。這種方法的核心是要麼通過在不更改商務邏輯的情況下改寫SQL來降低目標SQL語句的資源消耗，要麼不改SQL但通過調整執行計畫或相關表的資料來降低目標SQL語句的資源消耗。
方法2：並存執行目標SQL語句”，這實際上是以額外的資源消耗來換取執行時間的縮短，很多情況下使用並行是針對某些SQL的唯一最佳化手段。
“方法3：平衡系統的資源消耗” 可以避免不必要的資源爭用所導致的目標SQL語句執行時間的增長。對某些SQL而言，其執行時間的增長或者不穩定（時快時慢）不是由於其執行計畫的問題，而是由於在目標SQL執行時系統處於高負荷狀態，執行目標SQL所需要的硬體資源（CPU、記憶體、IO等）得不到保障，存在爭用的情況。在這樣的情況下，如果我們能平衡系統的資源消耗，把一些跟目標SQL同時執行的不重要但很消耗系統資源的操作（如報表、批處理等）挪到系統不忙的時候執行，比如把它們挪到晚上執行，那麼就可以釋放部分系統硬體資源以確保目標SQL執行時所需要的硬體資源不再存在爭用的情況，進而就避免了其執行時間的增長或者不穩定。


對於有問題的SQL做SQL最佳化的具體步驟一般為：
1、查看該SQL語句的執行計畫，並結合其資源消耗情況和相關統計資訊、Trace檔案來分析其執行計畫是否合理；
2、通過修正措施（如調整該SQL的執行計畫等）來對該SQL做調整以縮短其執行時間，這裡調整的指導原則就是之前剛介紹的Oracle資料庫裡做SQL最佳化通常會採用的三種方法。


 


統計資訊的收集如何來做比較合理?
答：在Oracle資料庫裡，收集統計資訊總的原則就是量體裁衣，即要找到適合自己系統的統計資訊收集策略，用盡量小的代價收集到能穩定跑出正確執行計畫的統計資訊即可，也就說收集到的統計資訊不一定要特別准，只要具備代表性，能穩定跑出正確的執行計畫就可以了。
根據上述指導原則，我們建議採用如下的收集策略：根據系統的類型及特點來決定是採用Oracle內建的自動統計資訊收集作業還是自己寫shell指令碼來收集統計資訊。對於那種資料量不大的OLTP類型的系統，建議是用前者。如果是資料量很大的OLAP或者DSS系統，建議自己寫shell指令碼來收集統計資訊，因為對於這種系統而言，很多表的訪問都是只有全表掃描這一種方式，這種情況下這些表的統計資訊是否準確就無所謂了，甚至無須浪費資源和時間來對這些表收集統計資訊，而是寫shell指令碼僅對那些需要收集統計資訊的表和相關對象收集統計資訊就可以了。
不管是採用自動統計資訊收集作業還是手工寫Shell指令碼來收集統計資訊，我們都需要特別注意如下注意事項：
（1）當系統中匯入了大量資料後，如果要馬上進行相關的業務處理，建議在進行相關的業務處理之前及時對相關表手工收集統計資訊，因為這些被匯入了大量資料的表已經來不及等到當天的自動統計資訊收集作業自動收集統計資訊了。
（2）某些新上線或新遷移的系統，其中的某些表在上線/遷移之初其資料量為0（所以其相關統計資訊也為0），接著在匯入少量資料（比如幾千條）後馬上進行相關的業務處理，建議在進行相關的業務處理之前及時對這些表手工收集統計資訊。因為一來這些被匯入了少量資料的表已經來不及等到當天的自動統計資訊收集作業自動收集統計資訊了，二來這些表的統計資訊的初始值為0，所以即便隨後匯入的資料量並不大，但如果不及時收集統計資訊的話也可能也會對相關SQL的執行計畫產生顛覆性的影響。
這裡需要注意的是：無論是用ANALYZE命令還是用DBMS_STATS包來收集統計資訊，它們均會提交當前事務。如果應用對事務有強一致性的要求，同時在當前事務中在匯入資料後又必須得在同一個事務中進行相關的後續業務處理，則可以在後續處理的相關SQL中加入Hint（或者使用SQL Profile/SPM來替換相關SQL的執行計畫）以讓Oracle走出理想執行計畫而不再受統計資訊正確與否的幹擾。
（3）建議及時對包含日期型欄位的表收集統計資訊，避免謂詞越界現象的出現。自動統計資訊收集作業選取收集對象的標準，在預設情況下是目標表的delete+insert+update操作所影響的記錄數已經超過了TAB$中記錄的目標表總記錄數的10%，或者是自上次自動統計資訊收集作業完成之後目標表被執行過truncate操作。這種標準對於有日期型欄位的大表而言，可能會顯得過於寬鬆。
比如一個有日期型欄位的表，每天向該表中匯入當天的資料，同時該表中的資料保持一年，那麼一年後該表新匯入的資料僅占該表總記錄數的1/365。這種情況下該表的那個日期型欄位的統計資訊很可能就是不準的，此時就非常容易出現謂詞越界的現象，進而可能會嚴重影響相關SQL的執行計畫。這種情況下我們的應對方法就是手工寫shell指令碼單獨對該表每天收集統計資訊，或者使用DBMS_STATS.SET_TABLE_PREFS來對自動統計資訊收集作業中針對該表的收集設定做修改，比如用DBMS_STATS.SET_TABLE_PREFS將針對該表的STALE_PERCENT的值設為0（僅適用於Oracle 11g及其以上的版本）。
（4）收集統計資訊的採樣比例到底設為多少合適是一個永恒的話題，實際上這個問題是沒有標準答案的，因為這個採樣比例取決於執行收集統計資訊作業被允許消耗的時間和系統的資料分布情況。如果時間允許，我們當然是希望將採樣比例設為100%，因為這時收集到的統計資訊肯定是最準的，但100%的採樣比例所帶來的問題就是收集統計資訊作業的執行時間可能會非常長，甚至在規定的視窗期內跑不完。如果採樣比例不能設為100%，那麼設為多少合適則取決於系統資料的分布情況。例如，如果系統的資料分布十分均勻，則很可能用10%的採樣比例所得到的統計資訊就已經能讓目標SQL跑出正確、穩定的執行計畫了。也就是說，這種情況下得到的執行計畫很可能和用100%的採樣比例得到的執行計畫是一樣的，既然如此，為什麼還要耗費額外的時間與資源去跑增加採樣比例後的統計資訊收集作業？但如果資料分布不均勻，此時同樣採用了10%的採樣比例，並且碰巧採樣的這10%的資料區塊大多數都是空塊或者是資料分布極不均衡、不具備代表性的資料區塊，則這種情況下得到的統計資訊很可能就是不準的，甚至是錯誤的，也就是說這個時候就需要增加採樣比例了。面對後一種情形，採樣比例增加到多少合適呢？是20%，30%，50%還是更多？沒有人可以告訴你答案，也許只能靠你自己不斷的摸索和調整，直到收集統計資訊作業既能在規定的視窗期內跑完同時目標SQL又能跑出正確的執行計畫為止。
儘管如此，我們還是推薦一個統計資訊收集作業採樣比例的初始值：對於Oracle 11g及其以上的版本，收集統計資訊的採樣比例建議採用DBMS_STATS.AUTO_SAMPLE_SIZE。Oracle 11g中的AUTO_SAMPLE_SIZE採用了全新的雜湊演算法，它既能保證在較短的時間內執行完又能保證收集到的統計資訊的品質（接近採樣比例為100%時的品質）。如果是Oracle 10g，繼續採用DBMS_STATS.AUTO_SAMPLE_SIZE就不太合適了，因為這個自動採樣比例在Oracle 10g裡是非常小的一個值，我們建議在Oracle 10g中將採樣比例的初始值設為30%，然後根據目標SQL的實際執行情況再做調整；如果是Oracle 9i，我們建議將採樣比例的初始值也設為30%，然後根據目標SQL的實際執行情況再做調整。
（5）建議使用DBMS_STATS包來對分區表收集全域統計資訊，並且收集分區表的統計資訊時應使用一致的GRANULARITY參數，全域統計資訊的收集方法和注意事項請見《基於Oracle的SQL最佳化》的5.6節“全域統計資訊”。
（6）建議要額外收集系統統計資訊，但系統統計資訊只收集一次就夠了，除非系統的硬體環境發生了變化，系統統計資訊的收集方法請見《基於Oracle的SQL最佳化》的5.9節“系統統計資訊”。
（7）建議要額外收集X$表的內部對象統計資訊，但僅僅是在明確診斷出系統已有的效能問題是因為X$表的內部對象統計資訊不準的情形下，對於其他情形就不要收集了。X$表的內部對象統計資訊的收集方法和注意事項請見《基於Oracle的SQL最佳化》的5.11節“內部對象統計資訊”。
如果是用寫shell指令碼的方式手工收集統計資訊，我們推薦採用如下的方式。
（1）對於單表的統計資訊收集，建議初始情況下採用如下的方式（然後根據目標SQL的實際執行情況再做調整）：
（a）適用於Oracle 11g及其以上的版本
EXEC DBMS_STATS.GATHER_TABLE_STATS(
       OWNNAME => 'SCHEMA_NAME',
       TABNAME => 'TABLE_NAME',
       ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
       CASCADE => TRUE,
       METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT' );


（b）適用於Oracle 9i/10g
EXEC DBMS_STATS.GATHER_TABLE_STATS(
       OWNNAME => 'SCHEMA_NAME',
       TABNAME => 'TABLE_NAME',
       ESTIMATE_PERCENT => 30,
       CASCADE => TRUE,
       METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT' );
（2）對於單個schema的統計資訊收集，建議初始情況下採用如下的方式（然後根據目標SQL的實際執行情況再做調整）：
(a) 適用於Oracle 11g及其以上的版本
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
       OWNNAME => 'SCHEMA_NAME', 
       ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
       CASCADE => TRUE, 
       METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT' );


(b) 適用於Oracle 9i/10g
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
       OWNNAME => 'SCHEMA_NAME', 
       ESTIMATE_PERCENT => 30
       CASCADE => TRUE, 
       METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT' );
無論是用Oracle內建的自動統計資訊收集作業還是用我們自己寫的shell指令碼來收集統計資訊，對於長條圖統計資訊的收集策略，我們建議都採用如下的方式：只對已經存在長條圖統計資訊的列重複收集長條圖統計資訊，而目標列的初次長條圖統計資訊的收集則是由瞭解系統的DBA手工來做。具體來說就是這樣：
（1）設定METHOD_OPT的值為‘FOR ALL COLUMNS SIZE 1’後先收集一次統計資訊，這意味著刪除了所有列上的長條圖統計資訊。
（2）在已經刪除了所有列上的長條圖統計資訊後，設定METHOD_OPT的值為‘FOR ALL COLUMNS SIZE REPEAT’，這意味著今後將只對已經存在長條圖統計資訊的列重複收集長條圖統計資訊。
（3）在系統使用或調優的過程中，目標列的初次長條圖統計資訊的收集是由瞭解系統的DBA手工來做。
上面所匯總的注意事項和推薦收集方法並沒有涵蓋所有的方面，完全可以在瞭解上述知識點的基礎上制訂出適合於自己系統的統計資訊收集策略。比如，如果某個表的資料變化得特別頻繁，我們完全可以將其統計資訊鎖住（使用DBMS_STATS.LOCK_TABLE_STATS，適用於Oracle 10g及其以上的版本），從此不再花時間對其收集統計資訊，當然這裡的前提條件是鎖住的統計資訊能使訪問該表的SQL走出理想的執行計畫。
總之，如何正確的收集統計資訊是一個複雜的話題，需要注意的知識點也有很多，但原則只有一個：量體裁衣，找到適合自己系統的統計資訊收集策略，用盡量小的代價收集到能穩定跑出正確執行計畫的統計資訊即可。


某個很耗資源的SQL依然執行很快，要麼是用了並行，要麼是用了類似於Exadata這樣的分散式運算架構。此時如果硬體資源還能撐住，那麼再最佳化的意義並不大（而且很多情況下，特別是在OLAP的環境下，並行和使用Exadata這樣的分散式運算架構是針對某些SQL的唯一最佳化手段）。


我的學習方法：
1、我在metalink上至少已經看過5000篇文章，所以對問題還是有一定的敏感度。
2、這個是基於我比較熟悉Oracle資料庫，另外就是metalink和DSI對我的協助非常大，有metalink和DSI的基礎，有一些問題我在類比的過程中確實可以做到信手拈來。
3、BBED只是一個工具，用好它的關鍵在於要瞭解Oracle資料庫的一些internal知識點和資料區塊結構，這才是我們需要深入鑽研的地方，深入研究BBED這個工具沒有太大意義。


如何通過metalink擷取協助:


第一、怎樣能夠通過metalink提高自己的水平？（如何通過metalink來學習呢）
如果研究Undo那麼就看一個系列的undo文章 包括ORA-1555處理等等， 通過系列文章來學習


第二、metalink中是否有  為查詢 各種指標而寫好的 SQL指令碼？我該如何才能找到這些？
使用script 做關鍵詞 可以獲得絕大多數這些指令碼
例如undo script




1、metalink是我最頻繁逛的網站，沒事我就在上面晃
2、不是，DSI我只是反覆的看，實驗我做的不多（我只有在覺得不太對的時候才會去做實驗驗證）
3、我用工具或指令碼不多，給不了你什麼推薦（推薦用sosi.txt看統計資訊，這個我強烈推薦，呵呵）。Tanel Poder的網站上有很多很有用的指令碼，你可以去看看


 


 




1、我個人是兩種方式兼有，先是在備份恢複方面鑽研的相對深入，後續又在最佳化器方面做了較深入的鑽研，同時從06年開始，我一直堅持在MOS上看Oracle資料庫各個方面的文章（這應該算是橫向的學習）。
2、Jonathan Lewis的《Cost Based Oracle Fundamentals》的第4章“Simple B-tree Access”中詳細說明了Effective Index Selectivity和Effective Table Selectivity的演算法，你可以去看一下。
3、IN-List Expansion / OR Expansion的好處是這樣改寫成以UNION ALL串連的各個分支後，各個分支就可以各自走索引、分區修剪（Partition Pruning）、表串連等相關的執行計畫而互不干擾；它的壞處是原先最佳化器只需要解析未做IN-List Expansion / OR Expansion之前的一個目標SQL並決定其執行計畫就好了，而一旦做了IN-List Expansion / OR Expansion，最佳化器就要對等價改寫後的每一個UNION ALL分支都執行同樣的解析、決定其執行計畫的工作，也就是說針對等價改寫後的目標SQL的解析時間會隨著UNION ALL分支的遞增而遞增。這意味著當IN後面的常量集合所包含的元素數量非常多的時候，IN-List Expansion / OR Expansion光解析的時間可能就會非常長，這就是我們在《基於Oracle的SQL最佳化》的“4.9.1 IN-List Iterator”中曾經提到過的IN-List Iterator的執行效率通常會比IN-List Expansion / OR Expansion的效率要好的原因。正是基於上述原因，做了IN-List Expansion / OR Expansion的等價改寫SQL的效率並不一定會比原SQL要好，這也就意味著IN-List Expansion / OR Expansion一定也是基於成本的，即只有當經過IN-List Expansion / OR Expansion後的等價改寫SQL的成本值小於原SQL的成本值時，Oracle才會對目標SQL執行IN-List Expansion / OR Expansion。
4、我在《基於Oracle的SQL最佳化》的“5.5.3.4.2 長條圖對可選擇率的影響”中詳細說明了各種情況下density的計算方法並全部給出了執行個體，你可以去看一下。
5、Oracle計算並行成本的公式我也不確定，似乎是沒有公開過。CBO在計算成本的時候本來就沒有考慮緩衝對物理I/O的影響，這也是CBO的局限性之一。
6、如果是因為聚簇因子的值而導致目標SQL沒有走相關的索引並且你又不能重建表，則你可以使用manual類型的sql profile或者SPM固定目標SQL的執行計畫；
7、沒有固定的SQL調優方法，這是我在《基於Oracle的SQL最佳化》這本書裡反覆強調的
8、最難調的是那種多表關聯並且SQL文本極其複雜的SQL，此時你必須要結合執行計畫和具體的業務知識來調整，也就是說面對這樣的SQL，首先你自己得知道應該走什麼樣的執行計畫。
9、先仔細的從頭到尾看一遍《基於Oracle的SQL最佳化》，然後有任何疑問都可以找我討論


 


1、你這個問題太大了。簡單來說就是這樣：對執行計畫的調整沒有固定的策略，調整的手段是什麼，能否奏效，均取決於你對CBO和執行計畫的瞭解程度。
我曾經在20#提到：“方法1：降低目標SQL語句的資源消耗”以縮短執行時間，這是最常用的SQL最佳化方法。這種方法的核心是要麼通過在不更改商務邏輯的情況下改寫SQL來降低目標SQL語句的資源消耗，要麼不改SQL但通過調整執行計畫或相關表的資料來降低目標SQL語句的資源消耗。
方法1所涉及到的這兩種最佳化手段在Oracle資料庫中能否奏效以及效果的好壞與否很大程度上取決於對CBO和執行計畫的理解程度，對CBO和執行計畫理解的越深，這兩種最佳化手段的應用就會越純熟，效果就會越好。這也是《基於Oracle的SQL最佳化》這本書所要提出的Oracle資料庫裡SQL最佳化方法論的第一點：Oracle裡SQL最佳化的本質是基於對CBO和執行計畫的深刻理解。
實際上，《基於Oracle的SQL最佳化》這本書裡基本上用了一整本書的篇幅來闡述上述SQL最佳化方法論的第一點內容。
2、有可能不需要你定位，因為你也許早就知道要調整的目標SQL是什麼。如果你不知道待調整的目標SQL是什麼，TOP SQL就是你首先要關注的調整目標。在Oracle資料庫裡，定位TOP SQL通常所採用的方法就是查看AWR報告或者Statspack報告，從AWR報告裡的“SQL ordered by Elapsed Time”、“SQL ordered by CPU Time”、“SQL ordered by Gets”等部分就能清晰的定位出在採樣的時間段內執行時間最長、消耗系統資源最多的Top SQL。


 


1、你的思路沒問題，SQL調優不能脫離實際的業務，是的，Oracle資料庫裡大部分SQL最佳化的問題都可以通過增加或者減少索引的方式來解決，但這絕不是全部！實際上，《基於Oracle的SQL最佳化》這本書裡列舉的SQL最佳化執行個體基本上沒有一個是簡單的通過增刪索引就能搞定的。SQL最佳化遇到困難時不知道如何繼續本質還是在於積累不夠，這方面沒有快速提高水準的方法。你可以試著認真看完《基於Oracle的SQL最佳化》，看看這本書能否緩解你這方面的問題，如果還是不行，你再來和我討論。
2、你這又是一個非常大的問題，而且表怎麼設計跟具體業務會緊密相關，我能想到的考慮因素包括但不限於：要滿足基本範式的要求（可能會局部反範式，以空間換時間）；要考慮具體欄位類型的設計（能用varchar2就不要用char等）；如果涉及到lob欄位，則要考慮lob欄位的平均長度以及如何儲存等因素；要考慮是否需要建約束和外鍵（子表的外鍵列上一定要建索引）；要考慮是否需要分區（不是說大表就一定要分區）；如果需要分區，資料分割配置是什麼；要設計適當的歸檔和拆分機制實現表資料的動靜分離，避免表內資料的無限增長，維持表內活動資料始終在一個可控的範圍內……




怎麼查看並行的執行計畫：
用select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'))，要指定SQL_ID和Child Cursor Number。
另外注意，當對並存執行的SQL使用GATHER_PLAN_STATISTICS hint時，FORMAT參數得是'ALLSTATS ALL'，不能是常規的'ALLSTATS LAST'。




提問：
再次諮詢您一個問題,我覺得最佳化好sql的話要對sql語句的背景(設計到業務)有瞭解,  我記得我們的系統中有很多sql語句中設計到了暫存資料表,由於暫存資料表沒有統計資訊,這時  有時暫存資料表中的資料量的不同可能導致相同的sql語句執行效率會有很大的不同,在設計到 暫存資料表時你對這條sql語句的最佳化思路是怎麼樣?
  還有一個實際工作中的問題,有時一條很簡單的sql查詢語句,有選擇性比較高的索引,  但是這樣的sql可能會返回10條以內的記錄數,我看到這樣的sql語句的邏輯讀會有幾十,我 重構表降低索引的聚簇因子後相應的邏輯讀會降下來,重構表降低索引的聚簇因子 你在實際的工作中會經常做嗎?


1、確實是這樣。對暫存資料表的使用我一般是這樣：1、如果業務對事務沒有強一致性要求，則我在使用暫存資料表之前會手工收集統計資訊；2、無論是用ANALYZE命令還是用DBMS_STATS包來對暫存資料表收集統計資訊，它們均會提交當前事務。所以如果應用對事務有強一致性的要求，同時在當前事務中在匯入資料後又必須得在同一個事務中進行相關的後續業務處理，則可以在後續處理的相關SQL中加入Hint（或者使用SQL Profile/SPM來替換相關SQL的執行計畫）以讓Oracle走出理想執行計畫而不再受統計資訊正確與否的幹擾；3、94#提到的使用動態採樣也是一種方法


2、通過重構表來降低聚簇因子的方法我不常用。在Oracle資料庫中，能夠降低目標索引的聚簇因子的唯一方法就是對錶中資料按照目標索引的索引鍵值排序後重新儲存。這裡需要注意的，這種按某一個目標索引的索引鍵值排序後重新儲存表中資料的方法確實可以降低該目標索引的聚簇因子的值，但可能會同時增加該表上存在的其他索引的聚簇因子的值。


表和索引的片段，在什麼時間點整理比較好，對效能改善比較大？
一般來說是這樣：如果你在建資料表空間時指定了uniform size，extent層級的片段基本上可以說就沒有了。如果不是uniform size，你可以將受片段問題困擾的表和索引遷移到uniform size的資料表空間（如用線上重定義等手段），遷移的時機應該是選擇在系統不那麼忙的時候做。


 




2009532140 發表於 2013-12-13 15:34 
你說的範式，這個我知道資料庫理論教材中看過..
但實際運用上，我咋感覺基本沒人關注這個問題呢？
再者 …


你的感覺不一定是事實。


char是定長的，varchar2是變長的，同樣定義一列，如果是用char(10)，則儲存'0'會佔用10個byte；如果是用varchar2(10),則儲存'0'只會佔用1個byte，你覺是用哪個更省儲存空間？
SQL> create table t1(c1 char(10),c2 varchar2(10));


Table created


SQL> insert into t1 values('0','0');


1 row inserted


SQL> commit;


Commit complete


SQL> select lengthb(c1),lengthb(c2) from t1;


LENGTHB(C1) LENGTHB(C2)
———– ———–
         10           1


是，長度變化確實有可能會導致行遷移，但不能因此就不用varchar2。實際上，行遷移是在所難免的，我們也不能因噎廢食。


 


 


以上摘自崔華解答