查看Oracle執行計劃的方法

什麼是執行計劃？

SQL是一種傻瓜語言，每個條件就是一個需求，存取的順序不同就形成了不同的執行計劃。 Oracle必須做出選擇，一次只能有一種存取路徑。 執行計劃是一條查詢語句在Oracle中的執行過程或存取路徑的描述。

執行計劃的選擇：

通常一則SQL有多個執行計劃，那我們要如何選擇？那種執行開銷更低，就代表效能更好，速度更快，我們就選哪一種，這個過程叫做Oracle的解析過程，然後Oracle會把更好的執行計畫放到SGA的Shared Pool裡，後續再執行同樣的SQL只要在Shared Pool裡取得就行了，不需要再去分析。

執行計畫選定依據：

根據統計資料來選擇執行計畫。

統計資料：

什麼是統計資料：記錄數、區塊數等，請看dba_tables / dba_indexes

動態取樣：

Oracle正常情況下會在每天的某段時間收集統計信息，對於新建的表，Oracl如何收集統計信息？採用動態取樣。

set autotrace on
set linesize 1000
--執行SQL語句
--會出現dynamic sampling used for this statement(level=2)關鍵

六種執行計劃

Oracle提供了6種執行計劃獲取方法，各種方法重點不同：

選擇時一般遵循以下規則：

1.如果sql執行很久才出結果或回傳不了結果，用方法1：explain plan for

2.追蹤某條sql最簡單的方法是方法1：explain plan for，其次是方法2：set autotrace on

3.如果相關察某個sql多個執行計劃的情況，只能用方法4：dbms_xplan.display_cursor或方法6：awrsqrpt.sql

4.如果sql含有函數，函數中有含有sql，即存在多層調用，想準確分析只能用方法5：10046追蹤

5.想法看到真實的執行計劃，不能用方法1：explain plan for和方法2：set autotrace on

6.想要取得表格被存取的次數，只能用方法3：statistics_level = all

#Oracle如何收集統計資料：

1、Oracle會選擇在一個特定的時間段收集表和索引的統計資料（預設週一至週五： 22：00，週六週日：06：00），使用者可自行調整，主要為了避開高峰期；

2、表與索引的分析有閾值限制，超過閾值才會自動進行分析。如果資料變化量不大，Oracle是不會去分析的；

3、收集方式靈活。可針對分區表的某個分區進行，可採用並行機制來收集表和索引的資訊；

如何收集統計資料：

--收集表統計資料

exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns');

--收集索引統計資料

exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>
10,degree => '4');

--收集表與索引的統計資料

exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns',cascade => true);

（1）explain plan for

SQL> show user
     USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 and employees.department_id=50;
已解释。
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择19行。

優點：無需真正執行，快速方便；

缺點：

1、沒有輸出相關統計訊息，例如產生了多少邏輯讀，多少次物理讀，多少次遞歸呼叫的情況；

2.無法判斷處理了多少行；

3、無法判斷表格執行了多少次

（2）set autotrace on

##用法：

指令作用：

SET AUTOT[RACE] OFF 停止AutoTrace

SET AUTOT[RACE] ON 開啟AutoTrace，顯示AUTOTRACE資訊與SQL執行結果
SET AUTOT[RACE] TRACEONLY 開啟AutoTrace ，僅顯示AUTOTRACE資訊
SET AUTOT[RACE] ON EXPLAIN 開啟AutoTrace，僅顯示AUTOTRACE的EXPLAIN資訊
SET AUTOT[RACE] ON STATISTICS 開啟AutoTrace，僅顯示AUTOTRACE的STATISTICS資訊

## 從

優點：

1、可以輸出運行時的相關統計資料（產生多少邏輯讀、多少次遞歸呼叫、多少次物理讀等）；

2、雖然要等語句執行完才能輸出執行計劃，但可以有traceonly開關來控制回傳結果不打螢幕輸出；

#缺點：

1、必須要等SQL語句執行完，才出結果；

2、無法看到表格被存取了多少次；

（3）statistics_level=all

步驟一：ALTER SESSION SET STATISTICS_LEVEL=ALL;

步驟二：執行待分析的SQL

步驟三：select * from table(dbms_xplan.display_cursor('sql_id/hash_value',null,'allstats last'));

SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果（略）
-- ...
已选择45行。
 
执行计划
----------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
5040 bytes sent via SQL*Net to client
433 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
45 rows processed

關鍵字解讀：

1、starts：SQL執行的次數；

2、E-Rows：執行計畫預計回傳的行數；

3、R-Rows ：執行計畫實際傳回的行數；

4、A-Time：每一步執行的時間(HH:MM:SS.FF)，根據這一行可知SQL耗時在哪些地方；

5、Buffers：每一步實際執行的邏輯讀或一致性讀；

6、Reads：物理讀；

#優點：

####1、可以清晰的從starts得出表被訪問多少次；###

2、可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；

3、虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；

缺点：

1、必须要等执行完后才能输出结果；

2、无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；

3、看不出递归调用，看不出物理读的数值

（4）dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到

注释：

1、还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取

2、如果有多个执行计划，可用以下方法查出：

select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&sql_id',1));
*/
SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = 'LOG$'
Plan hash value: 2408911181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='LOG$')
19 rows selected

注释：如何查看1个sql语句的sql_id，可直接查看v$sql

优点：

1、知道sql_id即可得到执行计划，与explain plan for一样无需执行；

2、可得到真实的执行计划

缺点：

1、没有输出运行的统计相关信息；

2、无法判断处理了多少行；

3、无法判断表被访问了多少次；

（5）事件10046 trace跟踪

步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪

步骤2：执行sql语句；

步骤3：alter session set events '10046 trace name context off'; --关闭追踪

步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）

步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

优点：

1、可以看出sql语句对应的等待事件；

2、如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；

3、可以方便的看处理的行数，产生的逻辑物理读；

4、可以方便的看解析时间和执行时间；

5、可以跟踪整个程序包

缺点：

1、步骤繁琐；

2、无法判断表被访问了多少次；

3、执行计划中的谓词部分不能清晰的展现出来

推荐：Oracle数据库学习教程

以上是查看Oracle執行計劃的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！