Oracle SQL優化器簡介

目錄

• 一、Oracle的優化器
  • 1.1 優化器簡介
  • 1.2 SQL執行過程
• 二、優化器優化方式
  • 2.1 優化器的優化方式
  • 2.2 基于規則的優化器
  • 2.3 基于成本的優化器
• 三、優化器優化模式
  • 3.1 優化器優化模式分類
  • 3.2 優化模式使用方法

在看《基于Oracle的SQL優化一書》知道了很多專業名稱，做了記錄，CBO、優化器、查詢轉換、執行計劃、Hint、并行、遊标、綁定變量、統計資訊、直方圖、索引等等。這篇部落格可以說是讀書筆記

一、Oracle的優化器

1.1 優化器簡介

優化器(Optimizer):優化器是Oracle資料庫内置的一個核心子系統，負責解析SQL，Oracle優化器是Oracle系統的一個核心元件，其目的是按照一定的原則來擷取目标SQL在目前情形下執行的最高效執行路徑，也可以說是執行計劃。

1.2 SQL執行過程

Oracle SQL的執行過程：

二、優化器優化方式

2.1 優化器的優化方式

Oracle優化器按照優化方式分為兩種

• 基于規則的優化器(Rule-Based Optimizer)，簡稱RBO
• 基于成本的優化器(Cost-Based Optimizer)，簡稱CBO

2.2 基于規則的優化器

2.2.1 RBO簡介

基于規則的優化器(Rule-Based Optimizer)：所謂基于規則的優化器是指Oracle按照寫死在資料庫的一系列規則來決定SQL的執行計劃，簡稱是RBO

2.2.2 RBO缺陷

RBO在oracle10後官方就不建議用，因為RBO并不支援oracle一些性能比較好的功能特性,也不會根據表的資料量等等擷取執行計劃，而且RBO定的那些規則也不是很容易修改。不過代碼在oracle10後版本還是儲存的，是以要學習測試也可以用SQL開啟RBO模式

alter session set optimizer_mode='RULE';           

2.2.3 RBO執行過程

RBO的執行過程：對于一條sql，oracle會事先給sql各種情況的執行計劃定一個等級，一共有15個等級，從等級1到等級15，規則是等級越低執行效率越高，也就是等級1的執行計劃執行效率是最高的。然後oracle自然就選出等級1的執行路徑作為執行計劃。

2.2.4 RBO特殊情況

• 對于執行路徑一樣的情況:假如出現執行路徑一樣的情況，這時候就要根據資料字典緩存來确定最低的等級了，意思就是擷取緩存中的先後順序确定哪條作為執行計劃

2.2.5 強制CBO的情況

上面說了可以通過SQL開啟CBO模式，這是針對普通情況的，假如出現下面情況，那就是強制使用CBO

• SQL涉及對象有IOT(Index Organized Table)
• SQL涉及的對象分區表
• 使用了并行查詢或者并行DML
• 使用了星型連接配接
• 使用了哈希連接配接
• 使用了索引快速全掃描
• 使用了函數索引
• ....

這些情況總結來自《基于Oracle的SQL優化一書》

雖然Oracle針對上述情況都開啟了強制CBO，但是我們還是可以手動解決的，方法也是來自《基于Oracle的SQL優化一書》，作者提供了改寫等價sql的方法，比如在sql的where條件中對number或者date類型的列加0，

select * from 表格 where a+0>參數           

如果是varchar2類型的，加可以加個空字元串

select * from 表格 where a || '' = 參數           

2.3 基于成本的優化器

2.3.1 CBO簡介

介紹一下基于成本的優化器(Cost-Based Optimization)：基于成本的優化器簡稱是CBO，在SQL執行過程，會緩存執行的一些資訊到Oracle的資料字典裡，這裡的資訊就有sql執行路徑的I/O、網絡資源、CPU的使用情況，其實這個就是SQL的執行成本，也是按照這個成本來确定執行計劃。是以CBO概念就是根據I/O、網絡資源、CPU的使用情況來确定SQL執行路徑也可以說是執行計劃的優化器。

2.3.2 集的勢

集的勢(Cardinality)是CBO特有的概念，集的勢指結果集的行數。引入這個概念是為了表示SQL執行成本值，Cardinality越大，也就是說sql執行傳回的結果集所包含的行數就越多，也說明成本越大。

2.3.3 可選擇率

可選擇率(Selectivity)：指施加指定謂語條件後傳回結果集的記錄數占未施加任何謂語條件的原始結果集的記錄數的比率。可選擇率的範圍是0~1,它的值越小，說明可選擇性越好，值越大說明可選擇性越差，也就是成本值越大。可選擇率為1時性能是最差的。

可選擇率 = 施加指定謂語條件後傳回結果集的記錄數/未施加任何謂語條件的原始結果集的記錄數。

三、優化器優化模式

3.1 優化器優化模式分類

優化器優化模式分為Rule、Choose、First rows、All rows

• Rule：就是基于規則Rule的方式
• Choose：當一個表或索引有統計資訊,則走CBO的方式,如果表或索引沒統計資訊,表又不是特别的小,而且相應的列有索引時,那麼就走索引,走RBO的方式。這是Oracle的預設方式
• First rows：與Choose方式是類似的,所不同的是當一個表有統計資訊時,它将是以最快的方式傳回查詢的最先的幾行,從總體上減少了響應時間。
• All rows：其實就是基于Cost方式

3.2 優化模式使用方法

要修改優化模式可以使用類似SQL

alter session set optimizer_mode='RULE';