查看Oracle執(zhí)行計(jì)劃的方法

4815

查看Oracle執(zhí)行計(jì)劃的方法

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

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

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

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

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

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

統(tǒng)計(jì)信息：

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

動(dòng)態(tài)采樣：

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

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

六種執(zhí)行計(jì)劃

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

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

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

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

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

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

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

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

查看Oracle執(zhí)行計(jì)劃的方法

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

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

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

3、收集方式靈活。可針對(duì)分區(qū)表的某個(gè)分區(qū)進(jìn)行，可采用并行機(jī)制來收集表和索引的信息；

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

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

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

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

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

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

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)點(diǎn)：無需真正執(zhí)行，快捷方便；

缺點(diǎn)：

1、沒有輸出相關(guān)統(tǒng)計(jì)信息，例如產(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í)行計(jì)劃 ---------------------------------------------------------- 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)計(jì)信息 ---------------------------------------------------------- 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)點(diǎn)：

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

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

缺點(diǎn)：

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í)行計(jì)劃預(yù)計(jì)返回的行數(shù)；

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

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

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

6、Reads：物理讀；

優(yōu)點(diǎn)：

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

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

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

缺點(diǎn)：

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

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個(gè)sql語句的sql_id，可直接查看v$sql

優(yōu)點(diǎn)：

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

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

缺點(diǎn)：

1、沒有輸出運(yùn)行的統(tǒng)計(jì)相關(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é)束后再看哪個(gè)是多出來的文件即可）

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

優(yōu)點(diǎn)：

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

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

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

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

5、可以跟蹤整個(gè)程序包

缺點(diǎn)：

1、步驟繁瑣；

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

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

推薦：Oracle數(shù)據(jù)庫學(xué)習(xí)教程

? 版權(quán)聲明
THE END
數(shù)據(jù)庫
# 數(shù)據(jù)庫# sql# 事件# oracle# select# for# table# NULL# 遞歸# Session# this# sort
喜歡就支持一下吧
點(diǎn)贊15 分享