Introduction to the Optimizer

Overview of SQL Processing
1. parser 進行語意和語法分析
2. Optimizer 利用RBO,CBO等方法決定產生查詢結果的最有效路徑
3. Row Source Generator 從2中接受最佳化後的方案，併產生SQL的Execution Plan
4. SQL Execution Engine執行該執行計劃，併產生查詢結果

Features that Require the CBO
1. Partitioned tables and indexes
2. Index-organized tables
3. Reverse key indexes
4. Function-based indexes
5. SAMPLE clauses in a SELECT statement
6. Parallel query and parallel DML
7. Star transformations and star joins
8. Extensible optimizer
9. Query rewrite with materialized views
10. Enterprise Manager progress meter
11. Hash joins
12. Bitmap indexes and bitmap join indexes
13. Index skip scans
即使OPTIMIZER_MODE＝rule，這些features仍然會使用CBO

Components of the CBO
Query Transformer
    資料來自於parse後的SQL,是一系列的query block。其目標是測定SQL的形式是否有利於產生好的query plan。有以下4種：
      1. View Merging
          SQL 中的view 被擴充套件到單獨的query block中。Optimizer會單獨分析view query block，這樣通常會導致在整體最佳化上得不到最優的結果。因此query transformer會將大部分view和其他的query block 合併，這樣可以在整體上統一最佳化。
      2. Predicate Pushing
          針對沒有被merge的view, push the relevant predicates from the containing query block into the view query block, which can be used either to access indexes or to act as filters
      3. Subquery Unnesting
          子查詢是nested在主查詢中的，這樣很難得到好的最佳化結果。所以將他們unnested，變成join
      4. Query Rewrite with Materialized Views
          如果查詢與某個物化檢視符合的化，則會按照物化檢視重寫這個查詢，因為物化檢視的結果都是事先計算好的。

Estimator
產生 3 種度量標準：
    1. Selectivity
        表示有多少 rows 可以透過謂詞被選擇出來，大小介於 0.0~1.0，0 表示沒有 row 被選擇出來。
        如果沒有 statistics，estimator 會使用一個預設的 selectivity 值，這個值根據謂詞的不同而異。比如 '=' 的 selectivity 小於 '        如果有 statistics，比如對於 last_name = 'Smith'，estimator 使用 last_name 列的 distinct 值的倒數（注：是指表中所有 last_name 的 distinct 值），作為 selectivity。
如果 last_name 列上有 histogram，則使用 histogram 根據 last_name 值的分佈情況產生的 selectivity 作為 selectivity。Histogram 在當列有資料傾斜時可以大大幫助 CBO 產生好的 selectivity。

    2. Cardinality
        表示一個 row set 的行數。
        Base cardinality：base table 的行數。如果表分析過了，則直接使用分析的統計資訊。如果沒有，則使用表 extents 的數量來估計。
        Effective cardinality：有效行集，指從基表中選擇出來的行數。是 Base cardinality 和表上所有謂詞的組合 Selectivity 的乘積。如果表上沒有謂詞，那麼 Effective cardinality = Base cardinality。
        Join cardinality：兩表 join 後產生的行數。是兩表 cardinality 的乘積(Cartesian)乘以 Join 謂詞的 selectivity。
        Distinct cardinality：列上 distinct 值的行數。
        Group cardinality：GROUP BY 操作之後 row set 的行數。由 grouping columns 的 distinct cardinality 和整個 row set 的行數決定。
          group cardinality lies between max ( dist. card. colx , dist. card. coly )
                                               and min ( (dist. card. colx * dist. card. coly) ,
                                                              num rows in row set )

    3. Cost
        Cost 表現了 Disk I/O, CPU usage, Memory usage 資源單位的使用數量（units of work or resource used）。
        Access path 決定從 base table 獲得資料所需的 units of work 的數量。也就是說Access path 決定 Cost 的值。Access path 可以是 table scan, fast full index scan, index scan。

        Clustering Factor：
          Index 的一種屬性，表示被索引的行在資料塊中的分佈情況，表徵表中資料的儲存順序和某索引欄位順序的符合程度。直接影響使用 rowid 找到 row 的cost。大小介於 block 數和 rownum 之間。
         （以下來自biti_rainy http://blog.itpub.net/post/330/2970 ）
          Oracle 按照索引塊中儲存的 rowid 來識別相臨的索引中記錄在表 block 中是否為相同塊，如果索引中存在記錄 rowid a,b,c,d......,若b 和 a 是同一個block，則比較 c 和 b，若這時不是同一個block，則 clustering_factor + 1 ,然後比較 d 和 c，若還不是同一個 block，則clustering_factor + 1......
若 clustering_factor 接近表 block 數量，則說明表中資料具有比較好的跟索引欄位一樣排序順序的儲存，透過索引進行 range scan 的代價比較小（需要讀取的表塊可能比較少）,若 clustering_factor 接近 row 數量，則說明表中資料和索引欄位排序順序差異很大，雜亂無張。則透過索引進行 range scan 的代價比較大（需要讀取的表塊可能更多）。
當然，在 oracle 920 開始，對於cluster_factor 比較接近表塊數量的根據索引的大範圍查詢做了特別的處理，不再是讀一個索引記錄去搜尋一個表記錄了，而是成批處理（透過索引塊一批 rowid 一次去表塊中獲得一批記錄），這樣就大大節約了讀的成本（consistent gets）。

        Join Cost：
        表徵了做 join 的兩個 row sets 分別 cost 的組合。
          Nested loop join：
            outer row set 的每行遍歷 inner row set 的所有行，尋找匹配的行。
            cost = outer access cost + (inner access cost * outer cardinality)
          Sort merge join：
            做 join 的兩個 row sets 根據 join keys 進行排序，如果他們不是按照 join keys 的順序的話。
            cost = outer access cost + inner access cost + sort costs (outer and inner, if sort is used)
          Hash join：
            cost = (outer access cost * # of hash partitions) + inner access cost
            首先理解 hash table 的資料結構：
              可以把 hash table 看做一個 2 維陣列 a[200][100]，現有 1000 個無序數字用來被查詢。我們考慮把這 1000 個數字除以 200，根據其餘數放在 a[200][100] 中，餘數就是陣列的第一維下標。這樣平均一個 a[i] 只放5個數字。當查詢的時候，對數字除以 200（這就是一個簡單的 hash 演算法），根據餘數 i 去 a[i] 中查詢，大約遍歷 5 次就能找到。
              Inner row（小表）被 hash 在記憶體中，並且透過 join key 建立 hash table（作為第一個下標），然後 scan outer table，到 hash table 中查詢 joined rows（透過 hash 演算法）。
              hash table 會按照 multiblock_IO 決定分成幾個 partitions。如果 hash table 太大超出了 hash_area_size，則將超出部分的 partitions 放到 temporary segments 中。
可以透過 10104 events 檢視 hash join 的 statistics：
          ALTER SESSION SET EVENTS '10104 trace name context forever, level 10'; 比如：
          Total number of partitions:
          Number of partitions which could fit in memory:
          如果後者大於前者，則說明一些 partitions 因為超出了 hash_area_size，要被放置到臨時表空間中。

Plan Generator
    作用是嘗試各種可能的執行計劃，選擇 cost 最低的一種。
    Plan Generator 會先為 nested subqueries and nonmerged views 產生 subplans，並且從 innermost query block 開始往外最佳化。
    Plan Generator 會使用 internal cutoff 來減少 plan 的嘗試數量。internal cutoff 基於現有的最優計劃的 cost。如果很大，那麼會嘗試較多的計劃；如果很小，那麼會很快結束嘗試。
    如果 plan generator 從一個接近最優的 initial join order 開始，那麼 internal cutoff 可以很好的工作。Plan Generator 根據 join items 的 effective cardinality 來確定 initial join order，小的在前面，大的在後面。

Understanding Execution Plans
Execution Plan：
為了執行 SQL 語句，Oracle 會執行很多步驟，這些步驟的綜合叫做 execution plan，包括 access path 和 join order。

Understanding Access Paths for the CBO
Access paths：
    從資料庫獲得資料的方式。

Full Table Scans
    表中所有在 HWM 以下的 blocks 都被掃描一遍，確定符合 where 條件的行。
    塊的讀取範圍從 1 到 DB_FILE_MULTIBLOCK_READ_COUNT。Multiblock reads 可以提高執行效率，當訪問表中大量塊時 full table scans 比 index range scans 效率高。
    最佳化器選擇 Full Table Scans 的情況：
      Lack of Index
      Large Amount of Data
      Small Table               --大小表的區分由 _small_table_threshold 隱含引數決定，預設為 db_cache_size 的 2%。
      High Degree of Parallelism
    Full Table Scan Hints：/*+ FULL(table alias) */

Rowid Scans
    獲得一行資料的最快方法。
    一般要先透過 index scan 獲得 Rowid，如果需要的列不在 index 中，再進行 Rowid Scans 獲得相應的行，如果在 index 中，則不需要 Rowid Scans。

Index Scans
    Index Unique Scans
      最多返回一個 rowid，用於 Unique Index 且 index cols 在條件中使用"等於"。
    Index Range Scans
      返回的資料按照 index columns 升序排列，index column 為同一個值的多行按照行 rowid 的升序排列。如果 order by/group by 的順序和 Index Range Scans 返回的 row set 的順序相同就不需要再 sort 了，否則還需要再對 row set 進行 sort。
      Unique index 中的 < > 條件，以及 nonunique indexe 的 < = > 條件，都會引起 Index Range Scans。如果進行 wild-card searches，% 不能放最前面，否則不會進行 Index Range Scans。
      如果某列上有索引，該列有 skewed distribution，且具有 histograms，但當時用 bind variable 時，Oracle 不知道該變數具體是什麼值，而無法使用 histograms，導致選擇 full table scan，這種情況下可以透過使用 hints 進行調整。
    Index Range Scans Descending
      和 Index Range Scans 相同，只是用於降序返回結果，或者返回小於某特定值的結果。
      HINT：INDEX_DESC(table_alias index_name)
    Index Skip Scans
      用於前導列沒有出現在查詢中（skiped）時使用索引。它將 composite index 拆分成若干個小的邏輯子索引。子索引的個數由前導列的 distinct 值決定。適用於前導列 distinct 值很少（子索引就少了），非前導列 distinct 值很多的情況。
    Full Scans
      適用於：
        1.A predicate references one of the columns in the index. The predicate does not need to be an index driver.
        2.No predicate，並且：
            A.查詢中引用的列都在 index 中
            B.只少有一個索引列不為空。
      它是先定位到索引的 root block，然後到 branch block（如果有的話），再定位到第一個 leaf block，然後根據 leaf block 的雙向連結串列順序讀取。它所讀取的塊都是有順序的，也是經過排序的。
    Fast Full Index Scans
      和 Full Scans 適用於：查詢的所有列都在索引中出現，且至少有一個索引列具有 NOT NULL 約束。區別在於它是從段頭開始，讀取包含點陣圖塊，root block，所有的branch block，leaf block，讀取的順序完全有物理儲存位置決定，並採取多塊讀，沒次讀取db_file_multiblock_read_count個塊。（更詳細的說明參見汪海的《Index Full Scan vs Index Fast Full Scan 》）
      Fast Full Index Scans 可以利用多塊讀和並行讀，只能用於 CBO，不能在 bitmap indexes 上使用。
      當需要排序時，Oracle 會使用 Full Index Scans，因為他的結果已經排好序；當不排序時，會使用 Fast Full Index Scans，因為能使用多塊讀，速度更快。
      在 rebuild index 時使用的就是 Fast Full Index Scans，所以 rebuild index 需要排序。（參見汪海的《Rebuild Index與 Sort》）
      HINT：INDEX_FFS(table_alias index_name)
      Fast Full Index Scan 限制：
        索引列中至少有一列有 NOT NULL 約束
        如果要用並行 fast full index scan 必須在建立索引時單獨指定 parallel clause，不能從索引所在的表上繼承
        必須分析索引，否則最佳化器可能不會使用它
    Index Joins
      他是一個有若干個 indexes 組成的 hash join，包含查詢中需要的所有列。
      他無法 eliminate a sort operation，必須在 CBO 中使用。
      HINT：INDEX_JOIN
    Bitmap Joins
      A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
      只能使用於 CBO，且需要企業版。

Cluster Scans
    用於從存放於 indexed cluster 中的表中獲得相同 cluster key 值的資料。具有相同 cluster key 值的所有資料存放於同一個 BLOCK。透過掃描 cluster index 獲得相應的 rowid，再透過 rowid 定位到所需的行。

Hash Scans
    用於從存放於 hash cluster 中的表中獲得相同 hash value 值的資料。具有相同 hash value 值的所有資料存放於同一個 BLOCK。透過將 hash function 應用於 cluster key 值上，獲得 hash value，再透過 hash value 定位到所需的行上。

Sample Table Scans
    從表中獲得 a random sample of data。
    SAMPLE clause：從表中隨機獲得指定百分比的行資料。
    SAMPLE BLOCK clause：從表中隨機獲得指定百分比的塊資料。
    限制：
      查詢不能包含 a join or a remote table
      需要使用 CBO

How the CBO Chooses an Access Path
    1.語句所有可用的 access paths
    2.使用每種 access paths 或者 combination of paths 時，估計的 cost 值
    影響 CBO 的因素：
      1.Optimizer Hints
        hints 會覆蓋可用的 access paths，除非包含 SAMPLE or SAMPLE BLOCK
      2.Old Statistics
        影響 cost 的估計


Understanding Joins
How the CBO Executes Join Statements
    決定一下幾個引數：
      Access Paths
      Join Method（nested loop, sort merge, cartesian, and hash joins）
      Join Order

How the CBO Chooses the Join Method
    估計每個 join method 的 cost，選擇 cost 最少的那種 join method。
    如果 join 返回大量行（一般來說，大於 10000 行），考慮以下因素：
      1.nested loop join 是低效的，最佳化器不會使用它
        nested loop join cost= access cost of A + (access cost of B * number of rows from A)
      2.CBO 中，hash join 是最高效的
        hash join cost= (access cost of A * number of hash partitions of B) + access cost of B
      3.RBO 中，merge join 是最高效的
        merge join cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)
        （如果資料是預先排序好的，sort cost 為 0）

How the CBO Chooses Execution Plans for Join Types
    CBO，RBO 都適用的：
      1.最佳化器透過 UNIQUE and PRIMARY KEY 約束找到最多返回一行的表，如果這樣的表存在，就把它放在連線順序的第一位，再處理連線中的其他表。
      2.For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule.
    CBO：
      估計各種 join orders, join methods, and available access paths 的 cost，選擇 cost 最低的
    其他因素：
      1.較小的 sort area size 會增加 sort merge join 的 cost，因為需要更多的 CPU time and I/O
      2.較大的多塊讀相對於 nested loop join 來說，會減少 sort merge join 的 cost。
    CBO 中 join orders 的選擇會被 ORDERED hint 覆蓋，但如果 HINT 和 outer join 的規則衝突，那麼 HINT 會被忽略。

Nested Loop Joins
    當 small subsets of data 被連線，並且 join condition 對於 inner table 來說是一種高效的訪問方式時，nested loop joins 是非常有用的。
    必須保證 inner table is driven from (dependent on) the outer table，否則效能很差，這種情況適用 hash joins 更好。
    HINT：USE_NL(table1_alias table2_alias)

Hash Joins
    Hash joins 適用於連線 large data sets。最佳化器選擇較小的 data source 透過 join key 建立一個 hash table 存放在記憶體中。透過掃描大表，查詢 hash table 找到所需的資料。
    當 hash table 太大無法放到記憶體中，他會被拆分，一部分放到 temporary segments 中，這時 temporary extent sizes 影響 I/O 效能。這種情況下，效能很差。
    當連線使用 equijoin，並且以下任何一種情況下，會使用 hash join：
      1. A large amount of data needs to be joined.
      2. A large fraction of the table needs to be joined.
    Execution Expain 中在上面的是先被掃描的小表，用作建立 hash table，在下的是大表。
    HINT：USE_HASH(table1_alias table2_alias)
    當使用 Hash Joins 時碰到問題時，應該注意 HASH_AREA_SIZE and HASH_JOIN_ENABLED 引數。

Sort Merge Joins
    Sort Merge Join 沒有驅動表的概念，他的執行步驟：
      1.Sort join operation: 兩個表分別按照 join key 排序（如果已經按照 join key 排序，這步省略）
      2.Merge join operation: 將排好序的結果 merge together.
    效能比較：
      Sort Merge Join 適用於兩個 independent sources。一般來說 Hash join 比 Sort Merge Join 效能好，除非以下兩個條件同時滿足
        1.row sources 已經被排序
        2.排序操作不需要再進行
      如果 Sort Merge Join 選擇了 slower access method，比如全表掃描，他的優勢就失去了。
      當兩個表透過 , or >= 連線時，Sort merge join 是很有用的，在進行兩個大 data sets 連線時，他比 nested loop join 效能好，hash join 適合用 = 連線。
    當兩個大資料量的 data sets 連線時，最佳化器何時選擇 sort merge join 而不選擇 hash join：
      1.連線條件不是 equi-join，而是 , or >= (but not a nonequality)
      2.OPTIMIZER_MODE is set to RULE，hash join 需要 CBO
      3.HASH_JOIN_ENABLED is false.
      4.連線的表已經排好序
      5.評估 HASH_AREA_SIZE and SORT_AREA_SIZE 大小之後，最佳化器認為 hash join 的 cost 較高
    HINT：USE_MERGE(table1_alias table2_alias) 可能還需要指定相應的 access path

Cartesian Joins
    當兩個表連線時，沒有指定連線條件，就會導致 Cartesian Join，一般是由於 poor SQL 造成的。
    HINT：ORDERED，By specifying a table before its join table is specified, the optimizer does a Cartesian join.

Outer Joins
    返回一個表滿足連線條件的所有行以及另一個表的全部或者部分行，不管這些行符不符合連線條件。

    Nested Loop Outer Joins
      返回 outer (preserved) table 的所有行，即使 inner (optional) table 沒有符合條件的行。
      Nested Loop Outer Join 時，外部表永遠是驅動表，不根據 cost 來決定哪個是驅動表。
      在以下條件下，最佳化器選擇 Nested Loop Outer Join
        1.外部表驅動內部表是可能的
        2.資料量小到使得 nested loop 效率較高

    Hash Join Outer Joins
      當連線的表的資料量大到使用 hash join 效率更高，或者外部表無法驅動內部表時，最佳化器選擇 Hash Join Outer Joins。
      表的連線順序也不按照 cost 來決定，外部表先進行處理，用它構建 hash table。

    Sort Merge Outer Joins
      當外表不能驅動內表，無法使用 hash join or nested loop joins 時，那麼使用 Sort Merge Outer Joins。
      由於資料量或者表已經經過排序操作，使得 Sort Merge Outer Joins 效率更高時，最佳化器選擇 Sort Merge Outer Joins。

    Full Outer Joins
      Left and right outer joins 的聯合。


Setting Cost-Based Optimizer Parameters
Enabling CBO Features
    OPTIMIZER_FEATURES_ENABLE
      後面跟版本號，設定 Oracle 允許哪些 CBO 相關的特徵被使用，只允許被設定了的版本的 CBO 特徵，其他的不允許，在升級版本之後，為了執行計劃的穩定性和向後相容可以使用，否則不需要設定。
    Peeking of User-Defined Bind Variables
      在第一次 invocation of a cursor 的時候，Oracle peeks at 使用者定義繫結變數的值，來決定所有 where 條件的 selectivity，即使沒有使用繫結變數。在這之後的 invocations of the cursor 就不用再 peek 了，cursor 被共享，即使繫結變數的值不同。
      使用繫結變數假設 cursor sharing 是故意的，並且假設不同的 invocations 使用相同的執行計劃，如果沒有使用相同的執行計劃，那麼繫結變數的使用可能不正確。

Controlling the Behavior. of the CBO
    CURSOR_SHARING
      將語句中的 literal values 轉為繫結變數，提高 cursor sharing 並且影響語句的執行計劃，生成的執行計劃將基於繫結變數而不是 actual literal values。
    DB_FILE_MULTIBLOCK_READ_COUNT
      Full table scan or index fast full scan 時，一次 I/O 讀取的塊數。用於估計 full table scans and index fast full scans 的 cost。大的 DB_FILE_MULTIBLOCK_READ_COUNT 值使得 full table scans 的 cost 較低，從而選擇 full table scans 而不是 index scan。
    HASH_AREA_SIZE
      用於 hash joins 的記憶體大小（bytes），越大 hash join 的 cost 越低。
    HASH_JOIN_ENABLED
      是否使用 hash joins
    OPTIMIZER_INDEX_CACHING
      控制 an index probe in conjunction with a nested loop 的 cost。範圍 0~100，表明索引塊在 buffer cache 中的百分比。他影響最佳化器對 index caching for nested loops and IN-list iterators 的假設。100 表示 100% 的索引塊在 buffer cache 中能找到，這將影響最佳化器對 an index probe or nested loop cost 的調整。
    OPTIMIZER_INDEX_COST_ADJ
      用於調整 index probe 的 cost。範圍 0~10000，預設值 100，表示 indexe 是基於 normal costing model 的 access path；如果設為 10，表示 index 的 cost 是一般 index access path 的 cost 的 1/10。
    OPTIMIZER_MAX_PERMUTATIONS
      用於控制 CBO 對帶有連線的 SQL 語句產生的執行計劃數。範圍 4 to 80000，80000 相當於無限。當將他設定成小於 1000 可以保證 parse times 降到幾秒甚者更少。這個引數可以用來減少多表連線語句的 parse times，但是可能會丟失最優的執行計劃。
    OPTIMIZER_MODE
      設定最佳化器的模式：RULE, CHOOSE, ALL_ROWS, FIRST_ROWS_n, and FIRST_ROWS
    PARTITION_VIEW_ENABLED
      是否使用 partition view pruning。如果設定成 true，CBO 只掃描需要的 partitions，根據 view predicates or filters。
    QUERY_REWRITE_ENABLED
      是否使用 query rewrite 的特性。Query rewrite 是和 materialized views 一起工作的。如果設定為 true，Oracle 會考慮使用 query rewrite 來查詢 materialized views 而不是原始的大表（參見 D.C.B.A 對 query rewrite 的解釋）。另外該引數還用來控制是否使用 function-based indexes。
    SORT_AREA_SIZE
      執行 sort 所使用的記憶體大小（bytes）。如果 sort 的資料超過該值，那麼超過的部分會放到 temporary tablespace 中。CBO 用該值估量 sort 的 cost，包括 sort merge joins。
    STAR_TRANSFORMATION_ENABLED
      This parameter, if set to true, enables the CBO to cost a star transformation for star queries. The star transformation combines the bitmap indexes on the various fact table columns rather than using a Cartesian approach.（D.C.B.A 對該功能的測試：）

轉載大師的讀書筆記 1

相關文章