EXPLAIN PLAN 的運用

EXPLAIN PLAN 用於分析SQL執行之效能

在執行前，必須由SYS進行授權

以下以SYS身分執行

GRANT SELECT ON v$session TO hr;
grant select on v$sql_plan_statistics_all to hr;
grant select on v$sql_plan to hr;
grant select on v$sql to hr;

OR

GRANT SELECT ON v_$session TO hr;
grant select on v_$sql_plan_statistics_all to hr;
grant select on v_$sql_plan to hr;
grant select on v_$sql to hr;

以下使用HR身分執行

explain plan for
select d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

select plan_table_output
from table(dbms_xplan.display(NULL,NULL,'basic'));
OR
select plan_table_output
from table(dbms_xplan.display_cursor(NULL,NULL,'basic'));

產生結果如下圖

但是上述PLAN TABLE 通常難以閱讀，可以先將要分析的SQL加以編號，再依據編號，直接搜尋該編號，以SQL查詢。

explain plan 
set statement_id='foo01'
for
select d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

另外一種方式，是在SELECTION裡面加上HINT

explain plan 
set statement_id='foo02'
for
select /*+GATHER_PLAN_STATISTICS */ d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

然後使用SQL查詢PLAN_TABLE_OUTPUT (參考此文章 )

select plan_id,
       operation,
       options,
       cost,
       cpu_cost,
       io_cost,
       temp_space,
       access_predicates,
       bytes,
       object_name,
       object_alias,
       optimizer,
       object_type
from plan_table
start with parent_id is null and statement_id = 'foo01'
connect by prior id = parent_id;

產生的結果如下圖所示

額外的資訊表示方式如下

select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +predicate +cost'));  
OR
select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes')); 
OR
select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note'));