基於oracle的sql優化方法論
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。實際上，行遷移是在所難免的，我們也不能因噎廢食。
