showplan.sql
Explain Planの結果を表示する
code:SQL
-- Display the explain plan result from PLAN_TABLE.
set autot off
set lines 300
set pages 0
set feed off
set timing off
col Operation for a80
col Name for a30
col Pstart for a20
col Pstop for a20
--col fname new_value spool_file format a100
--select 'plan_'||to_char(sysdate,'mmddhh24miss')||'.txt' fname from dual;
--spool &spool_file
--== ALL -PROJECTION +ALIAS +OUTLINE ALLSTATS LAST
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'PLAN_TABLE',statement_id=>NULL,FORMAT=>'ALL -PROJECTION +ALIAS +OUTLINE ALLSTATS LAST'));
--== ALL -PROJECTION +ALIAS -OUTLINE ALLSTATS LAS
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'PLAN_TABLE',statement_id=>NULL,FORMAT=>'ALL -PROJECTION +ALIAS -OUTLINE ALLSTATS LAST'));
--== ALL -PROJECTION -ALIAS +HINT_REPORT
--select * from table(dbms_xplan.display(table_name=>'PLAN_TABLE',statement_id=>NULL,format=>'ALL -PROJECTION -ALIAS +HINT_REPORT')) EXEC_PLAN;
--== ALL -PROJECTION -ALIAS -PREDICATE
--select * from table(dbms_xplan.display(table_name=>'PLAN_TABLE',statement_id=>NULL,format=>'ALL -PROJECTION -ALIAS -PREDICATE')) EXEC_PLAN;
--== ALL -PROJECTION -ALIAS -PREDICAT
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'PLAN_TABLE',statement_id=>NULL,FORMAT=>'ALL -PROJECTION -ALIAS +OUTLINE ALLSTATS LAST ADVANCED'));
@plan_order
--spool off
@default_settings