什么是執行計劃？
sql是一種傻瓜式語言，每一個條件就是一個需求，訪問的順序不同就形成了不同的執行計劃。oracle必須做出選擇，一次只能有一種訪問路徑。執行計劃是一條查詢語句在Oracle中的執行過程或訪問路徑的描述。
執行計劃的選擇：
通常一條SQL有多個執行計劃，那我們如何選擇？那種執行開銷更低，就意味著性能更好，速度更快，我們就選哪一種，這個過程叫做Oracle的解析過程，然后Oracle會把更好的執行計劃放到SGA的Shared Pool里，后續再執行同樣的SQL只需在Shared Pool里獲取就行了，不需要再去分析。
執行計劃選定依據：
根據統計信息來選擇執行計劃。
統計信息：
什么是統計信息： 記錄數、塊數等，具體查看dba_tables / dba_indexes
動態采樣：
Oracle正常情況下會在每天的某段時間收集統計信息，對于新建的表，Oracl如何收集統計信息？采用動態采樣。
set autotrace onset 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信息
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、可以輸出運行時的相關統計信息（產生多少邏輯讀、多少次遞歸調用、多少次物理讀等）；
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>?alter?session?set?statistics_level=all; SQL>?select?*?from?employees,jobs?where?employees.job_id=jobs.job_id?and?employees.department_id=50; --輸出結果 --... 已選擇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行。
關鍵字解讀：
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、執行計劃中的謂詞部分不能清晰的展現出來
