獲取SQL執行計劃
在做最佳化的過程中，在執行最佳化之前，我們必須先知道原來執行的SQL語句的執行計劃是怎樣。
這裡，就講述兩種常用的方法或工具來獲取執行計劃。
dbms_xplan.display_cursor用來格式化v$sql_plan和v$sql_plan_statistics_all（是v$sql_plan,v$sql_plan_statistics和v$sql_workarea的資訊合集）
顯示格式有5種：Basic僅顯示最少的資訊)、typical（顯示大部分相關資訊）、serial、all(顯示除了提綱外的所有資訊)，advanced(顯示所有資訊)。
一、使用explain plan for獲取執行計劃，透過select * from table (dbms_xplan.display(null,null,'basic'))以不同格式化顯示執行計劃：
1、在hr使用者下執行一條查詢：select * from employees;
2、basic格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------
8 rows selected.
3、typical格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
4、serial格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
5、all格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
"EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
"EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
, "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
25 rows selected.
6、advanced格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'advanced');
select * from table (dbms_xplan.display(null,null,'advanced')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> select * from table (dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
"EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
"EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],
"EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
, "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
39 rows selected.
dbms_xplan.display_awr,專門用來格式化dba_hist_sql_plan中的執行計劃，
綜上所述，dbms_xplan可以格式化的顯示plan table、庫快取區、awr中的資訊。
二、使用autotrace工具顯示執行計劃：
同樣登入hr使用者，開啟set autotrace tracenoly方式，則顯示執行計劃與解析：
直接執行隨意的查詢語句：
1、SQL> select * from employees
2 where employee_id = 171;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=171)
2、SQL> select * from employees;
2 where last_name = 'Sewall';
Execution Plan
----------------------------------------------------------
Plan hash value: 2077747057
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='Sewall')
3、SQL> select * from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
這裡，就講述兩種常用的方法或工具來獲取執行計劃。
dbms_xplan.display_cursor用來格式化v$sql_plan和v$sql_plan_statistics_all（是v$sql_plan,v$sql_plan_statistics和v$sql_workarea的資訊合集）
顯示格式有5種：Basic僅顯示最少的資訊)、typical（顯示大部分相關資訊）、serial、all(顯示除了提綱外的所有資訊)，advanced(顯示所有資訊)。
一、使用explain plan for獲取執行計劃，透過select * from table (dbms_xplan.display(null,null,'basic'))以不同格式化顯示執行計劃：
1、在hr使用者下執行一條查詢：select * from employees;
2、basic格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------
8 rows selected.
3、typical格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
4、serial格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
5、all格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
"EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
"EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
, "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
25 rows selected.
6、advanced格式顯示：
SQL> select * from table (dbms_xplan.display(null,null,'advanced');
select * from table (dbms_xplan.display(null,null,'advanced')
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> select * from table (dbms_xplan.display(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
"EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
"EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
"EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],
"EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
, "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
"EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
39 rows selected.
dbms_xplan.display_awr,專門用來格式化dba_hist_sql_plan中的執行計劃，
綜上所述，dbms_xplan可以格式化的顯示plan table、庫快取區、awr中的資訊。
二、使用autotrace工具顯示執行計劃：
同樣登入hr使用者，開啟set autotrace tracenoly方式，則顯示執行計劃與解析：
直接執行隨意的查詢語句：
1、SQL> select * from employees
2 where employee_id = 171;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=171)
2、SQL> select * from employees;
2 where last_name = 'Sewall';
Execution Plan
----------------------------------------------------------
Plan hash value: 2077747057
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='Sewall')
3、SQL> select * from employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
以上就是常用的以下獲取執行計劃的方法。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2126484/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】獲取執行計劃SQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- 獲取SQL執行計劃的方式：SQL
- 執行計劃-1：獲取執行計劃
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- 獲取執行計劃之Autotrace
- 獲取執行計劃的方法
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 獲取oracle sql語句詳細些執行計劃OracleSQL
- oracle 9i 獲取sql執行計劃(書寫長的sql)OracleSQL
- Oracle獲取執行計劃的方法Oracle
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- 介紹幾種獲取SQL執行計劃的方法（上）SQL
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- oracle dbms_xplan獲取執行計劃Oracle
- 如何獲取真實的執行計劃
- 獲取執行計劃的6種方法
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- 利用statspack來獲取生成環境中top SQL及其執行計劃SQL
- sql 執行計劃SQL
- Oracle 獲取執行計劃的幾種方法Oracle
- Oracle10g如何獲取執行計劃Oracle
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 物化檢視重新整理遞迴SQL獲取執行計劃報錯遞迴SQL
- 根據SQL Id獲得SQL語句的執行計劃SQL
- 使用sql monitor獲取更加詳細的執行計劃 - dbms_sqltune.report_sql_monitorSQL
- NOTE: cannot fetch plan for SQL_ID_在plsql developer無法獲取sql執行計劃SQLDeveloper
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- 會話的跟蹤以及執行計劃的獲取會話
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- 獲得目標SQL語句執行計劃的方法SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer