Oracle执行计划显示
生成和顯示SQL語句的執行計劃，是大多數DBA、SQL開發人員以及性能優化專家經常做的工作，因為執行計劃能夠提供SQL語句性能相關的信息。執行計劃解釋了SQL語句執行的詳細過程，這個過程記錄了一系列的數據庫操作以及每個操作涉及到的數據行數和生成的數據行數。優化器使用查詢轉換和物理優化技術決定數據庫操作順序和過程實現。
執行計劃通常以扁平的表格形式呈現，是一個事實樹形結構。下面是一個基于SH模式中SALES、PRODUCTS表生成的查詢：
SQL> select prod_category, avg(amount_sold)
?2?? from sales s, products p
?3?? where p.prod_id = s.prod_id
?4?? group by prod_category;
執行計劃以表格的形式呈現：
——————————————
?Id?? Operation????????????? Name
——————————————
?? 0? SELECT STATEMENT
?? 1?? HASH GROUP BY
?? 2??? HASH JOIN
?? 3???? TABLE ACCESS FULL?? PRODUCTS
?? 4???? PARTITION RANGE ALL
?? 5????? TABLE ACCESS FULL? SALES
——————————————
通過事實樹來解讀執行計劃：
?? GROUP BY
????? |
???? JOIN
?_____|_______
?|??????????? |
ACCESS???? ACCESS
(PRODUCTS) (SALES)
計劃樹的執行順序是從下到上，上述的例子中，首先執行的是表的訪問操作，也就是樹的葉子部分。通過執行計劃，我們可以看出訪問操作是全表掃描，表掃描返回的數據行用來做連接操作，這里連接操作的類型是hash連接。最后對連接操作返回的數據行進行group-by，這里分組同樣使用的是hash。
值得一提的是，查詢優化器最終選擇的執行計劃是從眾多的可選擇執行計劃中選取的代價最低的一個。這里的代價可以理解為性能的衡量指標，代價越低性能越好。查詢優化器使用的代價模型是通過評估IO、CPU及網絡等方面的數據計算出來的。
Oracle數據庫中可以通過兩種方式查看SQL語句的執行計劃：
EXPLIAN PLAN 命令 — 這種方式沒有實際執行SQL語句而僅僅把執行計劃顯示出來；
V$SQL_PALN — 從Oracle 9i開始引入了這個V$視圖，通過該視圖可以查詢游標緩存中存在的SQL語句的執行計劃。
在某些特定的場景下，使用EXPLAIN PLAN得到的執行計劃可能和V$SQL_PLAN不同。比如，當SQL語句包含綁定變量時，EXPLAIN PLAN得到的執行計劃會忽略綁定變量值，而V$SQL_PLAN中記錄的執行計劃則考慮了綁定變量值。
Oracle 9i引入了dbms_xplan包，隨后的版本中該包的功能不斷的增強，使得查看執行計劃變得更加便利。這個包里提供了幾個PL/SQL函數，用于從不同的數據源獲取執行計劃：
1. EXPLAIN PLAN command
2. V$SQL_PLAN
3. Automatic Workload Repository (AWR)
4. SQL Tuning Set (STS)
5. SQL Plan Baseline (SPM)
下面通過一些具體的例子展示如果使用dbms_xplan包提供的函數，產生和顯示SQL語句的執行計劃。
例1：使用EXPLAIN PLAN command和dbms_xplan.display函數
SQL> EXPLAIN PLAN FOR
?2?? select prod_category, avg(amount_sold)
?3?? from sales s, products p
?4?? where p.prod_id = s.prod_id
?5?? group by prod_category;
Explained.
SQL> select plan_table_output
?2?? from table(dbms_xplan.display('plan_table', null, 'basic'));
——————————————
?Id?? Operation????????????? Name
——————————————
?? 0? SELECT STATEMENT
?? 1?? HASH GROUP BY
?? 2??? HASH JOIN
?? 3???? TABLE ACCESS FULL?? PRODUCTS
?? 4???? PARTITION RANGE ALL
?? 5????? TABLE ACCESS FULL? SALES
——————————————
dbms_xplan.display使用的參數有：
plan table name (默認 'PLAN_TABLE')
statement_id (默認 null),
format (默認 'TYPICAL')
更詳細的信息可以查看$ORACLE_HOME/rdbms/admin/dbmsxpln.sql。
例2：生成和顯示會話中最后執行的SQL語句的執行計劃
SQL> select prod_category, avg(amount_sold)
?2?? from sales s, products p
?3?? where p.prod_id = s.prod_id
?4?? group by prod_category;
no rows selected
SQL> select plan_table_output
?2?? from table(dbms_xplan.display_cursor(null, null, 'basic'));
——————————————
?Id?? Operation????????????? Name
——————————————
?? 0? SELECT STATEMENT
?? 1?? HASH GROUP BY
?? 2??? HASH JOIN
?? 3???? TABLE ACCESS FULL?? PRODUCTS
?? 4???? PARTITION RANGE ALL
?? 5????? TABLE ACCESS FULL? SALES
——————————————
dbms_xplan.display_cursor使用的參數有：
SQL ID (默認 null, null意味著當前會話中最后執行的SQL語句)
child number (默認 0),
format (默認 'TYPICAL')
例3：顯示任何其他語句的執行計劃
直接提供SQL_ID：
SQL> select plan_table_output from
?2?? table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));
間接查詢獲取SQL_ID：
SQL> select plan_table_output
?2?? from v$sql s,
?3?? table(dbms_xplan.display_cursor(s.sql_id,
?4????????????????????????????????? s.child_number, 'basic')) t
?5?? where s.sql_text like 'select PROD_CATEGORY%';
例4：根據SQL Plan Baseline顯示執行計劃。SQL Plan Baseline是Oracle 11g中引入的新概念，用于支持SQL Plan Management (SPM)特性。
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SQL> select prod_category, avg(amount_sold)
?2?? from sales s, products p
?3?? where p.prod_id = s.prod_id
?4?? group by prod_category;
no rows selected
假如上述語句執行超過一次，將會產生一個該語句的SQL Plan Baseline，可以通過下面的查詢進行確認：
SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
?2?? from dba_sql_plan_baselines
?3?? where sql_text like 'select prod_category%';
SQL_HANDLE???????????????????? PLAN_NAME????????????????????? ACC
—————————— —————————— —
SYS_SQL_1899bb9331ed7772?????? SYS_SQL_PLAN_31ed7772f2c7a4c2? YES
上面創建的SQL Plan Baseline可以通過下面的方式顯示：
直接提供SQL_HANDLE：
SQL> select t.* from
?2?? table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
?3????????????????????????????????????????????? format => 'basic')) t;
間接查詢獲取SQL_HANDLE：
SQL> select t.*
?2?????? from (select distinct sql_handle
?3???????????? from dba_sql_plan_baselines
?4???????????? where sql_text like 'select prod_category%') pb,
?5?????? table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
?6????????????????????????????????????????????????? null,'basic')) t;
這兩條語句的輸出結果如下：
—————————————————————————-
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
????????? where p.prod_id = s.prod_id group by prod_category
—————————————————————————-
Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
Enabled: YES???? Fixed: NO????? Accepted: YES???? Origin: AUTO-CAPTURE
—————————————————————————-
Plan hash value: 4073170114
———————————————————
?Id?? Operation???????????????? Name???????????????
———————————————————
?? 0? SELECT STATEMENT?????????????????????????????
?? 1?? HASH GROUP BY???????????????????????????????
?? 2??? HASH JOIN??????????????????????????????????
?? 3???? VIEW?????????????????? index$_join$_002???
?? 4????? HASH JOIN????????????????????????????????
?? 5?????? INDEX FAST FULL SCAN PRODUCTS_PK????????
?? 6?????? INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
?? 7???? PARTITION RANGE ALL???????????????????????
?? 8????? TABLE ACCESS FULL???? SALES??????????????
———————————————————
格式化
格式化參數可以進行高度定制，可以根據實際需要輸出適當的信息。高級別的選項有：
Basic 輸出的執行計劃包括操作、選項和對象名（表、索引、物化視圖等）
Typical 輸出的執行計劃包括Basic內容，加上和優化器相關的內部信息，如代價、大小、基數等，具體包括執行計劃中的每個操作，優化器計算出來的每個操作的代價，每個操作返回的數據行數等，同時還會包括操作中所使用的評估謂詞。Oracle CBO中存在兩種謂詞：ACCESS和FILTER。ACCESS表示根據查詢條件，使用索引提取相關的數據塊。FILTER表示數據塊提取之后的評估。
All 輸出的執行計劃包括Typical內容，加上每個操作生成的表達式列表、提示別名、查詢塊的名稱。
低級選項允許包括或者排除諸如謂詞、代價之類的細節，舉例如下：
SQL> select plan_table_output
?2?? from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));
——————————————————-
?Id?? Operation????????????? Name????? Cost (%CPU)
——————————————————-
?? 0? SELECT STATEMENT??????????????????? 17? (18)
?? 1?? HASH GROUP BY????????????????????? 17? (18)
*? 2??? HASH JOIN???????????????????????? 15?? (7)
?? 3???? TABLE ACCESS FULL?? PRODUCTS????? 9?? (0)
?? 4???? PARTITION RANGE ALL?????????????? 5?? (0)
?? 5????? TABLE ACCESS FULL? SALES???????? 5?? (0)
——————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access("P"."PROD_ID"="S"."PROD_ID")
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));
—————————————————————————-
?Id?? Operation????????????? Name????? Rows? Time????? Pstart Pstop
—————————————————————————-
?? 0? SELECT STATEMENT??????????????????? 4? 00:00:01?????????????
?? 1?? HASH GROUP BY????????????????????? 4? 00:00:01?????????????
*? 2??? HASH JOIN?????????????????????? 960? 00:00:01?????????????
?? 3???? TABLE ACCESS FULL?? PRODUCTS?? 766? 00:00:01?????????????
?? 4???? PARTITION RANGE ALL??????????? 960? 00:00:01????? 1???? 16
?? 5????? TABLE ACCESS FULL? SALES????? 960? 00:00:01????? 1???? 16
—————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access("P"."PROD_ID"="S"."PROD_ID")
注解部分
dbms_xplan包生成執行計劃時會將注釋顯示在NOTE部分，比如查詢優化過程中使用了動態抽樣或者星形轉換應用在查詢中。比如SALES表沒有統計信息，優化器在分析代價時將會使用動態抽樣，執行計劃將會把這一過程記錄在NOTE部分。
SQL> select plan_table_output
?2?? from table(dbms_xplan.display('plan_table',null,'basic +note'));
——————————————
?Id?? Operation????????????? Name???
——————————————
?? 0? SELECT STATEMENT??????????????
?? 1?? HASH GROUP BY????????????????
?? 2??? HASH JOIN???????????????????
?? 3???? TABLE ACCESS FULL?? PRODUCTS
?? 4???? PARTITION RANGE ALL????????
?? 5????? TABLE ACCESS FULL? SALES??
——————————————
Note
—–
- dynamic sampling used for this statement
綁定窺視
在生成執行計劃時，優化器會考慮綁定變量的實際值，這一過程就是所謂的綁定變量窺視。就像我們前面提到的那樣，V$SQL_PLAN中記錄的執行計劃考慮了綁定變量值而EXPLAIN PLAN命令生成的執行計劃并沒有考慮這一點。從Oracle10gR2開始，dbms_xplan包可以顯示用于生成特定計劃或者游標的綁定變量值，只需要在display_cursor()函數時加上'+peeked_binds'參數即可。
下面的例子展示了一點：
SQL> variable pcat varchar2(50)
SQL> exec :pcat := 'Women'
SQL> select PROD_CATEGORY, avg(amount_sold)
?2?? from sales s, products p
?3?? where p.PROD_ID = s.PROD_ID
?4?? and prod_category != :pcat
?5?? group by PROD_CATEGORY;
SQL> select plan_table_output
?2?? from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));
——————————————
?Id?? Operation????????????? Name???
——————————————
?? 0? SELECT STATEMENT??????????????
?? 1?? HASH GROUP BY????????????????
?? 2??? HASH JOIN???????????????????
?? 3???? TABLE ACCESS FULL?? PRODUCTS
?? 4???? PARTITION RANGE ALL????????
?? 5????? TABLE ACCESS FULL? SALES??
——————————————
Peeked Binds (identified by position):
————————————–
1 –??CAT (VARCHAR2(30), CSID=2): 'Women'
總結
以上是生活随笔為你收集整理的Oracle执行计划显示的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux 平台安装 Oracle Ti
- 下一篇： 议rman的crosscheck和obs