動的SQLの実行計画確認

(8.0.26)

drop table tab1;

create table tab1
( col1   int primary key
, col2   int
, col3   int
, col4   int
)
 partition by range (col1)
 ( partition p1 values less than (0)
 , partition p2 values less than (maxvalue)
 );


set @sSQL := concat(" explain "," select * from tab1 t1,tab1 t2 ");
prepare stmt from @sSQL;
execute stmt;
deallocate prepare stmt;

 

(19c)

drop table tab1 purge;

create table tab1
( col1   int primary key
, col2   int
, col3   int
, col4   int
)
 partition by range (col2)
 ( partition p1 values less than (0)
 , partition p2 values less than (maxvalue)
 );


declare
sSQL varchar2(4000);
begin

  sSQL := ' EXPLAIN PLAN ' || ' SET STATEMENT_ID = ''1'' FOR SELECT * from tab1 partition(p1) ';
  execute immediate sSQL;

  sSQL := ' EXPLAIN PLAN ' || ' SET STATEMENT_ID = ''2'' FOR SELECT * from tab1 partition(p2) ';
  execute immediate sSQL;

end;
/

 

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '1','TYPICAL'));


SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', '2','TYPICAL'));


※PLAN_TABLEはグローバル一時表のため、同一セッションで確認する必要がある

(14)

drop table tab1 cascade;

create table tab1
( col1   int primary key
, col2   int
)
;


do
$$
declare
rec record;
cur1 refcursor;

begin
  open cur1 for execute ' explain analyze ' || ' select * from tab1 t1,tab1 t2 ';
  loop
    fetch cur1 into rec;
    if not found then
      exit;
    end if;
    raise info 'QUERY PLAN = %' , rec."QUERY PLAN";
  end loop;
  close cur1;
end;
$$
language plpgsql
;

(2019)

drop table tab1;

create table tab1
( col1   int not null primary key
, col2   int
)
;

set statistics profile on

execute (N' select ' + N' * from tab1 t1,tab1 t2 ')

set statistics profile off