執行計劃-2：檢視更多的資訊

在 Part 1中我們瞭解了兩種獲取執行計劃的方式，第一種是最佳化器預測它將會怎麼執行，透過以下方法查詢獲取：

explain plan for {sql statement}
select * from table(dbms_xplan.display);

第二種是最佳化器在我們執行語句後真正的執行路徑，透過以下方法查詢獲取：

set serveroutput off
{sql statement}
select * from table(dbms_xplan.display_cursor);

事實上，在 Part 1 中，我故意使用兩種獲取執行計劃的方法，來證明對同一條存在繫結變數的語句可以生成兩種截然不同的執行計劃。

Part 2中我們會評估幾種檢視真實執行計劃的方法，但是首先我們關注目前我們接觸到函式的其他呼叫選項，這會使我們對執行計劃在終端使用者的環境中的復現更加自信。

Format Options

一般來說，如果想要執行計劃與終端使用者環境一致的話，我們需要他們生產資料的備份，對應的統計資訊，系統的引數還有一致的輸入。 生產資料以及統計資訊一般都是一樣的，所以我們真正需要關注的就是客戶環境是否不同以及語句輸入； 我們可以透過資料庫來幫助我們獲取在某一刻的執行資訊。

這裡也有一個小提醒，需要注意資料、統計資訊還有謂詞條件的及時同步。 舉個例子，如果你的生產資料是好幾個星期前的，那麼你執行語句時要根據幾個星期前使用者當時執行的語句去執行； 因為如果條件中存在 SYSDATE，那麼就不能很好的模擬當時的環境。

在 Part 1中提到過的，呼叫 dbms_xplan.display_cursor()可以賦值三個引數，它們分別是 sql_id， child_number和 formatting option。 有兩個 formatting option引數能有效的幫助你解決最近一次出現的問題， "peeked_binds"和 "outline"。 前者會列出 (只需要一點運氣 )最佳化查詢時使用的真實的值，後者會提供一個 hint的列表，如果我們為了這個查詢建立了一個儲存大綱或者 SQL Plan Baseline，透過這些 hint我們可以發現最佳化器的環境是否不同。

這裡有個小例子，透過對檢視 dba_extents 的 copy 表的查詢來演示使用和輸出。

alter session setworkarea_size_policy = manual;
alter session setsort_area_size = 10485760;
alter session setoptimizer_mode = first_rows_10;
alter session set"_hash_join_enabled" = false;
set serveroutput off
variable m_owner varchar2(32)
variable m_object varchar2(32)
execute :m_owner :='TEST_USER'
execute :m_object := 'T1'
select /*+ tracking */ * from t1 
where owner = :m_owner 
and segment_name =:m_object 
order by extent_id;
select * from table(
dbms_xplan.display_cursor(null,null,'outlinepeeked_binds')
);

我修改了一些最佳化器相關的引數， set serveroutput off不會導致 dbms_xplan.display_cursor()輸出關於 dbms_output.get_lines()的呼叫，可以在查詢時透過定義的一組引數使用繫結變數。 之後我呼叫 display_cursor(null, null, 'outlinepeeked_binds')來展示我最近的查詢並且新增可用的繫結變數的值到 Outline/SQL Pan Management資訊中。 這是輸出結果：

SQL_ID  0wwbn4bhvrrxj, child number 0
-------------------------------------
select /*+ tracking */  * from t1 where owner = :m_owner and
segment_name = :m_object order by extent_id
Plan hash value: 3684778271
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT ORDER BY               |       |     1 |    65 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    65 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I2 |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."SEGMENT_NAME"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :M_OWNER (VARCHAR2(30), CSID=178): 'TEST_USER'
   2 - :M_OBJECT (VARCHAR2(30), CSID=178): 'T1'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:M_OWNER)
   3 - access("SEGMENT_NAME"=:M_OBJECT)

這個例子中，我們很幸運的獲得了最佳化器在執行計劃資訊中記錄的所有繫結變數的值，包括 CSID(charcater set id) 這種細節，當你開使用 NLS 字符集時，有時候會造成一些意外。

我們先看 ”Outline Data”，可以看到 hint “first_rows(10)”是唯一被記錄的我們會話的變更，剩下的變更都沒有被捕捉到； format option也給了我們一個提示，我們可以在這個查詢開始試驗時需要做一些額外的檢查，來確認我們能否獲得一個更好的計劃。 這次我們假設其他人已經執行過這個查詢了，在我們分析前，我們需要根據一些特殊的文字來追蹤 (這就是我為什麼在語句中新增 /*+ tracking */，這個實際上不是 hint)。

column sql_id new_valuem_sql_id
column child_number new_valuem_child_no
select  sql_id,child_number, sql_text 
from   v$sql 
where   sql_textlike '%tracking%' 
and    sql_text not like '%v$sql%'
;
select * from table(
dbms_xplan.display_cursor('&m_sql_id',&m_child_no,'outlinepeeked_binds')
);
select 
name, value 
from 
v$sql_optimizer_env 
where 
sql_id      = '&m_sql_id' 
and child_number =&m_child_no 
and isdefault   = 'NO'
;

這些程式碼都能在 SQL*PLUS裡執行，我已經設定了一些列定義用來捕捉這個查詢語句上次返回的值， SQL_ID和 CHILD_NUMBER的值是我們所需要的。 在這裡我假設這條語句僅僅只有一個子遊標。 就像你看到的，我使用 '&替換 '將獲取到的值帶入到 display_cursor()的呼叫與 v$sql_optimizer_env的查詢中。 這裡不顯示對 display_cursor()的呼叫結果 (輸出結果與前面的重複 )，只顯示對 v$sql_optimizer_env 的查詢結果。

NAME                                    VALUE
-----------------------------------------------------------------
hash_area_size                          20971520
sort_area_size                          10485760
optimizer_mode                          first_rows_10
_hash_join_enabled                      false
workarea_size_policy                    manual
_smm_auto_cost_enabled                  false

v$sql_optimizer_env檢視儲存了最佳化器環境的引數，這些引數在最佳化每一個子遊標時都是生效的。 我選擇了那些沒有預設值，可以看到 Oracle輸出了 6個引數，其中 4個是我們設定的，還有兩個 :hash_area_size， _smm_auto_cost_enabled。 hash_area_size如果沒有明確的設定過，它會等於兩倍的 sort_area_size，當我們將 workarea_size_policy設定為 manual時， _smm_auto_cost_enabled引數也會自動的設定為 false。 在 11.2.0.4中有 330個最佳化器引數 (12.1.0.1中增加至 415個，這也是為什麼很難從二手資訊中獲取到給定查詢到執行路徑原因之一 ),其中 50個引數是非隱藏引數，其餘的大部分都是隱藏引數，只有在改變他們的預設值時才會展示在查詢結果裡。

我在簡單的提下另一個 formatting 選項  "advanced'，奇怪的是他會給我們比 "all'選項更多的資訊。 如果你僅僅想看執行查詢時記憶體裡所有的資訊，那麼你可以呼叫 display_cursor({sql_id},{child_number},’all’)(如果有許多子游標，可以將 child_number值設為 NULL，則會輸出所有的語句 )。 之後如果需要解決問題時我們還會繼續學習更多 formatting選項，不過目前我們要暫時先放下，來看看一些可以 "事後 "從生產系統獲取執行計劃的選項。 如果想獲得更多可用的選項，可以檢視 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

AWR and Statspack

如果一個查詢屬於 "資源密集型 "的，那麼他會出現在 statspack或 AWR裡，並且它的執行計劃以及一些統計會儲存在資源庫裡。 在 OEM(Grid Control， Cloud Control)中有關於 AWR的圖形化介面， $ORACLE_HOME/rdbms/admin中有 statspack和 AWR的 SQL介面。 statspack的指令碼為 sprepsql.sql， AWR的為 awrsqrpt.sql。

如果你對於用命令列生成 AWR或者 statspack很熟悉，那麼除了需要 SQL_ID(AWR)或者 "old hashvalues"(statspack)以外，幾乎都是一樣的。 報告會給你語句的全文，一些執行統計資訊，在指定期間所有不同的執行計劃，還有不同計劃進入和離開 library cache的時間表。 這個計劃不會輸出繫結值， outline information，甚至謂詞資訊，就是很基礎的執行計劃還有一些效能的統計。 AWR在這方面比 statspack好，因為它分別聚合了不同執行計劃的統計資料，所以更容易比較不同的執行計劃。

另一個 AWR的優點是你可以透過呼叫 dbms_xplan.display_awr()獲取 AWR裡歷史的執行計劃。 這個呼叫需要 4個引數， SQL_ID， plan_hash_value， database id和 format optino； 最簡單的呼叫就是隻給定 SQL_ID的值，結果會輸出 AWR中儲存的所有這條 SQL的執行計劃，你也可以選擇輸出你想要的執行計劃。 就像呼叫 dbms_xplan.display_cursor() 一樣，你也可以得到 outline information和最佳化器使用的繫結值，下面是個例子：

select * from table(
dbms_xplan.display_awr('9yaqj3djhwxa2',null, null,'outline peeked_binds')
);

關於使用 dbms_xplan查詢和操作執行計劃的內容方面還有很多變化，也隨著 Oracle的新版本變得更多樣化，最近的版本甚至可以輸出計劃中的不同點，其中一個最有用的小特性與如何填充 AWR有關而不是從 AWR中獲取報告。 在 11g中，你可以將一個 SQL_ID標記為 "感興趣 "，當 AWR快照程式在建立快照時會捕獲任何關於該語句可用的資訊。 因為除非該語句是 "top N"的語句，否則它不會出現在 AWR報告中，但是你可以使用 awrsqrpt.sql或者 dbms_xplan.display_awr()來獲得計劃。

如果你想標記某條 SQL ，你可以呼叫 dbms_workload_repository 包：

executedbms_workload_repository.add_colored_sql({sql_id})
--
-- 輸出當前被標記的語句
--
select * fromwrm$_colored_sql;
--
-- 解除語句標記
--
executedbms_workload_repository.remove_colored_sql({sql_id})

這個過程的好處是 ( 甚至可以針對一些效率很高的語句 ) ，當效能較好的應用突然變慢了，如果你標記了所有程式執行的語句，那麼你可以很輕鬆的找到出現什麼問題以及執行計劃是什麼時候改變的。

Conclusion

儘管我們沒有列舉出所有生成或者獲取執行計劃的方法，但是我們學習了足夠的原理保證我們可以十分精確地獲得我們需要檢查的語句執行時的環境，也學習瞭如何獲得一些歷史的、不在記憶體裡的執行計劃； 還有一種保證我們所需要的語句總是會被 AWR快照捕捉的方法。

下一篇文章我們會來學習一些基本的解釋執行計劃的原理，從簡單的 select語句開始，使用檢視來區分連線順序，訪問方式和連線方式。  

原文連結 : https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-2-things-to-see/

原文作者 : Jonathan Lewis

| 譯者簡介

林錦森·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年從事Oracle資料庫，較豐富的故障處理、效能調優、資料遷移及備份恢復經驗