在資料庫優化中，除了針對業務架構、邏輯及sql調整之外，資料庫本身也有很多影響優化器的一些技術，主要有資料庫引數、sql計劃管理器、hint等。如下圖所示：

圖 19-1 說明如下

下面我們具體看下這幾個技術

1.初始化引數

控制優化器行為的主要引數為：

初始化引數	描述
`CURSOR_INVALIDATION`	為 DDL 語句提供預設的遊標失效級別。 `IMMEDIATE` 為 DDL 設定與 Oracle Database 12c 第 2 版 (12.2) 之前的版本相同的遊標失效行為。這是預設設定。 `DEFERRED` 允許應用程式利用減少的 DDL 遊標失效，而無需對應用程式進行任何更改。延遲失效減少了遊標失效的次數，並隨著時間的推移分散了重新編譯的工作量。出於這個原因，遊標在重新編譯之前可能會以次優計劃執行，並且可能會產生很小的執行時間開銷。您可以在 `SYSTEM` 或 `SESSION` 級別設定此引數。請參閱 “ 關於共享遊標的生命週期 ” 。
`CURSOR_SHARING`	將 SQL 語句中的文字值轉換為繫結變數。轉換這些值可以改善遊標共享，並且會影響 SQL 語句的執行計劃。優化器根據繫結變數的存在而不是實際的文字值生成執行計劃。設定為 `FORCE` 在共享現有遊標或遊標計劃不是最佳時啟用新遊標的建立。設定為 `EXACT` 僅允許具有相同文字的語句共享相同的游標。
`DB_FILE_MULTIBLOCK_READ_COUNT`	指定在全表掃描或索引快速全掃描期間在單個 I/O 中讀取的塊數。優化器使用這個引數的值來計算全表掃描和索引快速全掃描的開銷。較大的值會降低全表掃描的成本，這可能會導致優化器選擇全表掃描而不是索引掃描。此引數的預設值對應於資料庫可以有效執行的最大 I/O 大小。此值取決於平臺，對於大多數平臺為 1 MB。因為該引數以塊表示，所以它被設定為等於可以有效執行的最大 I/O 大小除以標準塊大小的值。如果會話數非常大，則多塊讀取計數值會降低，以避免緩衝區快取被過多的表掃描緩衝區淹沒。
`OPTIMIZER_ADAPTIVE_PLANS`	控制適應性計劃。適應性計劃有替代選擇。優化器根據在查詢執行時收集的統計資訊在執行時決定計劃。預設情況下，此引數為 `true` ，表示啟用自適應計劃。設定此引數可 `false` 禁用以下功能：巢狀迴圈和雜湊連線選擇星形變換點陣圖修剪自適應並行分佈方法請參閱 “ ” 。
`OPTIMIZER_ADAPTIVE_REPORTING_ONLY`	控制自動重新優化和自適應計劃的報告模式（請參閱 “ ” ）。預設情況下，報告模式為關閉 ( `false` )，這意味著啟用了自適應優化。如果設定為 `true` ，則自適應優化以僅報告模式執行。在這種情況下，資料庫收集自適應優化所需的資訊，但不採取任何行動來更改計劃。例如，自適應計劃始終選擇預設計劃，但資料庫會收集有關在引數設定為時資料庫將使用哪個計劃的資訊 `false` 。您可以使用來檢視報告 `DBMS_XPLAN.DISPLAY_CURSOR` 。
`OPTIMIZER_ADAPTIVE_STATISTICS`	控制自適應統計。當查詢謂詞過於複雜而無法單獨依賴基表統計資訊時，優化器可以使用自適應統計資訊。預設情況下， `OPTIMIZER_ADAPTIVE_STATISTICS` is `false` ，這意味著禁用以下功能： SQL 計劃指令統計反饋自適應動態取樣請參閱 “ ” 。
`OPTIMIZER_MODE`	在資料庫例項啟動時設定優化器模式。可能的值是 `ALL_ROWS` ， `FIRST_ROWS_` `n` 和 `FIRST_ROWS` 。
`OPTIMIZER_INDEX_CACHING`	使用巢狀迴圈控制索引探測的成本分析。值的範圍 `0` 以 `100` 表示在緩衝區快取索引塊，的百分比，其修飾優化有關索引快取巢狀迴圈和IN-列表迭代假設。值 `100` 推斷 100% 的索引塊可能會在緩衝區快取中找到，因此優化器會相應地調整索引探測或巢狀迴圈的成本。設定此引數時要小心，因為執行計劃可能會更改以支援索引快取。
`OPTIMIZER_INDEX_COST_ADJ`	調整索引探測的成本。該值的範圍是 `1` 到 `10000` 。預設值為 `100` ，這意味著優化器將索引評估為基於正常成本模型的訪問路徑。值 `10` 表示索引訪問路徑的成本是索引訪問路徑正常成本的十分之一。
`OPTIMIZER_INMEMORY_AWARE`	此引數啟用 ( `TRUE` ) 或禁用 ( `FALSE` ) 所有 Oracle Database In-Memory (Database In-Memory) 優化器特性，包括 IM 列儲存的成本模型、表擴充套件、布隆過濾器等。將引數設定為 `FALSE` 會使優化器 `INMEMORY` 在優化 SQL 語句時忽略表的屬性。
`OPTIMIZER_REAL_TIME_STATISTICS`	當 `OPTIMIZER_REAL_TIME_STATISTICS` 初始化引數設定為時 `true` ，Oracle 資料庫會在常規 DML 操作期間自動收集實時統計資訊。預設設定為 `false` ，即禁用實時統計。
`OPTIMIZER_SESSION_TYPE`	確定資料庫在自動索引驗證期間是否驗證語句。預設是 `NORMAL` ，這意味著語句被驗證。 `CRITICAL` 優先於 `NORMAL` . 通過在會話中設定 `OPTIMIZER_SESSION_TYPE` 初始化引數 `ADHOC` ，您可以暫停此會話中查詢的自動索引編制。自動索引過程不識別索引候選，也不建立和驗證索引。此控制元件對於即席查詢或測試新功能可能很有用。
`OPTIMIZER_CAPTURE_SQL_QUARANTINE`	啟用或禁用 SQL 隔離配置的自動建立。要啟用 SQL 隔離在資源管理器終止查詢後自動建立配置，請將 `OPTIMIZER_CAPTURE_SQL_QUARANTINE` 初始化引數設定為 `TRUE` （預設為 `FALSE` ）。
`OPTIMIZER_USE_INVISIBLE_INDEXES`	啟用或禁用不可見索引的使用。
`QUERY_REWRITE_ENABLED`	啟用或禁用優化器的查詢重寫功能。 `TRUE` ，這是預設設定，使優化器能夠利用物化檢視來提高效能。 `FALSE` 禁用優化器的查詢重寫功能，並指示優化器不要使用物化檢視重寫查詢，即使未優化查詢的估計查詢成本較低。 `FORCE` 啟用優化器的查詢重寫功能，並指示優化器使用物化檢視重寫查詢，即使未優化查詢的估計查詢成本較低。
`OPTIMIZER_USE_SQL_QUARANTINE`	確定優化器在為 SQL 語句選擇執行計劃時是否考慮 SQL 隔離配置。要禁用現有 SQL 隔離配置的使用，請設定 `OPTIMIZER_USE_SQL_QUARANTINE` 為 `FALSE` （預設為 `TRUE` ）。
`QUERY_REWRITE_INTEGRITY`	確定強制執行查詢重寫的程度。預設情況下，完整性級別設定為 `ENFORCED` 。在此模式下，必須驗證所有約束。資料庫不使用依賴於非強制約束的查詢重寫轉換。因此，如果您使用 `ENABLE NOVALIDATE RELY` ，某些型別的查詢重寫可能不起作用。要在約束處於 `NOVALIDATE` 模式時啟用查詢重寫，完整性級別必須為 `TRUSTED` or `STALE_TOLERATED` 。在 `TRUSTED` 模式中，優化器相信維度和 `RELY` 約束中宣告的關係是正確的。在 `STALE_TOLERATED` 模式下，優化器使用有效但包含陳舊資料的物化檢視以及包含新資料的物化檢視。此模式提供最大的重寫能力，但會產生生成不準確結果的風險。
`RESULT_CACHE_MODE`	控制資料庫是將 SQL 查詢結果快取用於所有查詢，還是僅用於使用結果快取提示註釋的查詢。當設定為 `MANUAL` （預設）時，您必須使用 `RESULT_CACHE` 提示來指定將特定結果儲存在快取中。設定為時 `FORCE` ，資料庫將所有結果儲存在快取中。相應的選項 `MANUAL TEMP` 並 `FORCE TEMP` 指定查詢結果可以駐留在臨時表空間中，除非被提示禁止。設定此引數時，請考慮結果快取如何處理 PL/SQL 函式。資料庫使用跟蹤 PL/SQL 函式的資料依賴性的相同機制使結果快取中的查詢結果無效，但允許快取包含 PL/SQL 函式的查詢。由於 PL/SQL 函式結果快取失效不會跟蹤所有型別的依賴關係（例如對序列、 `SYSDATE` 、 `SYS_CONTEXT` 和包變數的依賴），因此在呼叫此類函式的查詢上不加區分地使用查詢結果快取可能會導致結果發生變化，即，結果不正確。因此，在選擇啟用結果快取時，請考慮正確性和效能，尤其是在設定 `RESULT_CACHE_MODE` 為時 `FORCE` 。
`RESULT_CACHE_MAX_SIZE`	指定結果快取可以使用的最大 SGA 記憶體量（以位元組為單位）。預設是從的值匯出 `SHARED_POOL_SIZE` ， `SGA_TARGET` 和 `MEMORY_TARGET` 。此引數的值四捨五入為不大於指定值的 32 KB 的最大倍數。該值 `0` 禁用快取。
`RESULT_CACHE_MAX_RESULT`	指定 `RESULT_CACHE_MAX_SIZE` 任何單個結果可以使用的百分比。預設值為 `5` ，但您可以指定 `1` 和之間的任何百分比值 `100` 。
`RESULT_CACHE_MAX_TEMP_RESULT`	指定一個快取查詢可以消耗的臨時表空間記憶體的最大百分比。預設值為 `5` 。此引數只能在系統級別修改。
`RESULT_CACHE_MAX_TEMP_SIZE`	指定結果快取可以在 PDB 中消耗的最大臨時表空間記憶體量。此引數只能在系統級別修改。預設值為的預設值或初始化值的 10 倍 `RESULT_CACHE_MAX_SIZE` 。下面的任何正值 `5` 都四捨五入為 `5` 。指定的值不能超過 `SYS` 模式中當前估計的總可用臨時表空間的 10% 。該值 `0` 禁用該功能。
`RESULT_CACHE_REMOTE_EXPIRATION`	指定依賴於遠端資料庫物件的結果保持有效的分鐘數。預設值為 `0` ，這意味著資料庫不應使用遠端物件快取結果。將此引數設定為非零值可能會產生過時的答案，例如，如果遠端資料庫修改了結果中引用的表。
`STAR_TRANSFORMATION_ENABLED`	使優化器能夠為星型查詢（如果 `true` ）進行星型轉換。星形轉換結合了各種事實表列上的點陣圖索引。

2. 提示

提示主要是在語句上加入相關提示，以強制資料庫按照提示方式執行。部分示例如下：

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;
--單表
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
FROM   employees 
WHERE  department_id > 50;
--多表
SELECT /*+ LEADING(e j) */ *
FROM   employees e, departments d, job_history j
WHERE  e.department_id = d.department_id
AND    e.hire_date = j.start_date;

3.DBMS_STATS

主要定期收集統計資訊，以供Oracle優化器選擇最優路徑。詳細資訊，後續部落格錄入。

4.SQL配置檔案

SQL 配置檔案是查詢的輔助統計資訊的集合，包括查詢中引用的所有表和列。

配置檔案以內部格式儲存在資料字典中。使用者介面是DBA_SQL_PROFILES字典檢視。優化程式在優化期間使用此資訊來確定最佳計劃。實現sql配置檔案示例：

DECLARE
  my_sqlprofile_name VARCHAR2(30);
BEGIN
  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
    task_name    => 'STA_SPECIFIC_EMP_TASK'
,   name         => 'my_sql_profile'
,   profile_type => DBMS_SQLTUNE.PX_PROFILE
,   force_match  => true 
);
END;
/
--列出已有的配置檔案
COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME, SQL_TEXT, CATEGORY, STATUS
FROM   DBA_SQL_PROFILES;
--更改配置檔案
VARIABLE pname my_sql_profile
BEGIN 
  DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
     name            =>  :pname
,    attribute_name  =>  'CATEGORY'
,    value           =>  'DEFAULT'   
);
END;
--刪除配置檔案
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE ( 
    name => 'my_sql_profile' 
);
END;
/

5.sql計劃管理

SQL 計劃管理使用一種稱為 SQL 計劃基線的機制，這是一組允許優化器用於 SQL 語句的可接受計劃。

其主要目的是--SQL 計劃管理可防止由計劃更改引起的效能迴歸。

SQL 配置檔案和 SQL 計劃基線都通過確保優化器僅使用最佳計劃來幫助提高 SQL 語句的效能。

配置檔案和基線都是使用提示在內部實現的。但是，這些機制存在顯著差異，包括：

通常，SQL 計劃基線是主動的，而 SQL 配置檔案是被動的。
SQL 計劃基線再現特定計劃，而 SQL 配置檔案則更正優化器成本估算。

也可參考：

http://blog.itpub.net/29487349/viewspace-2765000/

詳細情況可參考Oracle官方文件：

【OPTIMIZATION】Oracle影響優化器選擇的相關技術

1.初始化引數

2. 提示

3.DBMS_STATS

4.SQL配置檔案

5.sql計劃管理

相關文章