什么是執(zhí)行計劃？

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

執(zhí)行計劃的選擇：

通常一條SQL有多個執(zhí)行計劃，那我們?nèi)绾芜x擇？那種執(zhí)行開銷更低，就意味著性能更好，速度更快，我們就選哪一種，這個過程叫做Oracle的解析過程，然后Oracle會把更好的執(zhí)行計劃放到SGA的Shared Pool里，后續(xù)再執(zhí)行同樣的SQL只需在Shared Pool里獲取就行了，不需要再去分析。

執(zhí)行計劃選定依據(jù)：

根據(jù)統(tǒng)計信息來選擇執(zhí)行計劃。

統(tǒng)計信息：

什么是統(tǒng)計信息： 記錄數(shù)、塊數(shù)等，具體查看dba_tables / dba_indexes

動態(tài)采樣：

Oracle正常情況下會在每天的某段時間收集統(tǒng)計信息，對于新建的表，Oracl如何收集統(tǒng)計信息？采用動態(tài)采樣。

set autotrace on
set linesize 1000
–執(zhí)行SQL語句
–會出現(xiàn)dynamic sampling used for this statement(level=2)關(guān)鍵

六種執(zhí)行計劃

Oracle提供了6種執(zhí)行計劃獲取方法，各種方法側(cè)重點不同：

選擇時一般遵循以下規(guī)則：

1.如果sql執(zhí)行很長時間才出結(jié)果或返回不了結(jié)果，用方法1：explain plan for

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

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

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

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

6.想要獲取表被訪問的次數(shù)，只能用方法3：statistics_level = all

Oracle如何收集統(tǒng)計信息：

1、Oracle會選擇在一個特定的時間段收集表和索引的統(tǒng)計信息（默認周一至周五：22：00，周六周日：06：00），用戶可自行調(diào)整，主要為了避開高峰期；

2、表與索引的分析有閾值限制，超過閾值才會自動進行分析。如果數(shù)據(jù)變化量不大，Oracle是不會去分析的；

3、收集方式靈活?？舍槍Ψ謪^(qū)表的某個分區(qū)進行，可采用并行機制來收集表和索引的信息；

如何收集統(tǒng)計信息：

–收集表統(tǒng)計信息

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

–收集索引統(tǒng)計信息

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

–收集表與索引的統(tǒng)計信息

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行。

優(yōu)點：無需真正執(zhí)行，快捷方便；

缺點：

1、沒有輸出相關(guān)統(tǒng)計信息，例如產(chǎn)生了多少邏輯讀，多少次物理讀，多少次遞歸調(diào)用的情況；

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

3、無法判斷表執(zhí)行了多少次

（2）set autotrace on

用法：

命令作用：

SET AUTOT[RACE] OFF 停止AutoTrace
SET AUTOT[RACE] ON 開啟AutoTrace，顯示AUTOTRACE信息和SQL執(zhí)行結(jié)果
SET AUTOT[RACE] TRACEONLY 開啟AutoTrace，僅顯示AUTOTRACE信息
SET AUTOT[RACE] ON EXPLAIN 開啟AutoTrace，僅顯示AUTOTRACE的EXPLAIN信息
SET AUTOT[RACE] ON STATISTICS 開啟AutoTrace，僅顯示AUTOTRACE的STATISTICS信息

SQL> set autotrace on SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50; --輸出結(jié)果（略） -- ... 已選擇45行。   執(zhí)行計劃 ---------------------------------------------------------- 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) 統(tǒng)計信息 ---------------------------------------------------------- 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

優(yōu)點：

1、可以輸出運行時的相關(guān)統(tǒng)計信息（產(chǎn)生多少邏輯讀、多少次遞歸調(diào)用、多少次物理讀等）；

2、雖然要等語句執(zhí)行完才能輸出執(zhí)行計劃，但是可以有traceonly開關(guān)來控制返回結(jié)果不打屏輸出；

缺點：

1、必須要等SQL語句執(zhí)行完，才出結(jié)果；

2、無法看到表被訪問了多少次；

（3）statistics_level=all

步驟一：ALTER SESSION SET STATISTICS_LEVEL=ALL;

步驟二：執(zhí)行待分析的SQL

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

SQL> alter session set statistics_level=all; SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50; --輸出結(jié)果 --... 已選擇45行。   SQL> set linesize 1000 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----------- SQL_ID d8jzhcdwmd9ut, child number 0 ------------------------------------- select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50 Plan hash value: 303035560 ------------------------------------------------------------------------------------------------------------------------ ---------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ ---------------- | 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | | | | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ------------- | 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | | | | | 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | | | | | 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | | | | |* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 | 6144 | 6144 (0)| |* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | | | | ------------------------------------------------------------------------------------------------------------------------ ---------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID") PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ ----- filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID") 5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50) 已選擇25行。

關(guān)鍵字解讀：

1、starts：SQL執(zhí)行的次數(shù)；

2、E-Rows：執(zhí)行計劃預(yù)計返回的行數(shù)；

3、R-Rows：執(zhí)行計劃實際返回的行數(shù)；

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

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

6、Reads：物理讀；

優(yōu)點：

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

2、可以從E-Rows和A-Rows得到預(yù)測的行數(shù)和真實的行數(shù)，從而可以準確判斷Oracle評估是否準確；

3、雖然沒有準確的輸出運行時的相關(guān)統(tǒng)計信息，但是執(zhí)行計劃中的Buffers就是真實的邏輯讀的數(shù)值；

缺點：

1、必須要等執(zhí)行完后才能輸出結(jié)果；

2、無法控制結(jié)果打屏輸出，不像autotrace可以設(shè)置traceonly保證不輸出結(jié)果；

3、看不出遞歸調(diào)用，看不出物理讀的數(shù)值

（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、如果有多個執(zhí)行計劃，可用以下方法查出：

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

優(yōu)點：

1、知道sql_id即可得到執(zhí)行計劃，與explain plan for一樣無需執(zhí)行；

2、可得到真實的執(zhí)行計劃

缺點：

1、沒有輸出運行的統(tǒng)計相關(guān)信息；

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

3、無法判斷表被訪問了多少次；

（5）事件10046 trace跟蹤

步驟1：alter session set events '10046 trace name context forever,level 12'; –開啟追蹤

步驟2：執(zhí)行sql語句；

步驟3：alter session set events '10046 trace name context off'; –關(guān)閉追蹤

步驟4：找到跟蹤后產(chǎn)生的文件（開啟10046前先用‘ls -lrt’看一下文件，執(zhí)行結(jié)束后再看哪個是多出來的文件即可）

步驟5：tkprof trc文件 目標文件 sys=no sort=prsela,exeela,fchela –格式化命令

優(yōu)點：

1、可以看出sql語句對應(yīng)的等待事件；

2、如果函數(shù)中有sql調(diào)用，函數(shù)中有包含sql，將會被列出，無處遁形；

3、可以方便的看處理的行數(shù)，產(chǎn)生的邏輯物理讀；

4、可以方便的看解析時間和執(zhí)行時間；

5、可以跟蹤整個程序包

缺點：

1、步驟繁瑣；

2、無法判斷表被訪問了多少次；

3、執(zhí)行計劃中的謂詞部分不能清晰的展現(xiàn)出來