什麼是執行計劃?
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執行計劃的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章!