aplan.sql
実行統計付実行計画を表示する
code:SQL
-- Display the execution plan. Specify SQLID and ChildNo in the argument.
-- Example: @aplan a5ks9fhw2v9s1 0
define SQLID=&1
define CHDNO=&2
set autot off
set trim on
set feed off
set timing off
set pages 10000
set lines 1000
set long 1000000
set longchunksize 1000000
set heading off
col fname new_value spool_file format a100
--select 'aplan_'||to_char(sysdate,'mmddhh24miss')||'.txt' fname from dual;
--spool &spool_file
--select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',null,'ALLSTATS LAST'));
--select * from table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&SQLID',FORMAT=>'ALL -PROJECTION -ALIAS +OUTLINE ALLSTATS LAST'));
--select * from table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&SQLID',FORMAT=>'ALL -PROJECTION +OUTLINE ALLSTATS LAST'));
--select * from table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&SQLID',FORMAT=>'ALL -PROJECTION -ALIAS ALLSTATS LAST'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&SQLID',CURSOR_CHILD_NO=>'&CHDNO',FORMAT=>'ALL -PROJECTION -ALIAS ALLSTATS LAST'));
set heading on
@aplans &SQLID
--spool off
undefine SQLID
undefine CHDNO
@default_settings