Friday, February 14, 2020

Multiple xplain plan

Multiple xplain plan


explain plan for select * from mqm101;
select * from table(dbms_xplan.display());

Need to use SET STATEMENT_ID

statement.1
explain plan SET STATEMENT_ID='EXPLAIN1'
for select * from mqm101;

check the explain plan table status:
select plan_id, statement_id from plan_table;

statement.2
explain plan SET STATEMENT_ID='EXPLAIN2'
for select * from mqm102;

check the explain plan table status:
select plan_id, statement_id from plan_table;

statement.3
explain plan SET STATEMENT_ID='EXPLAIN3'
for select * from mqm103;

check the explain plan table status:
SQL> select plan_id, statement_id from plan_table;

   PLAN_ID STATEMENT_ID
---------- ------------------------------
         5 EXPLAIN1
         5 EXPLAIN1
         6 EXPLAIN2
         6 EXPLAIN2
         7 EXPLAIN3
         7 EXPLAIN3

Now generate the execution plan:

select * from table(dbms_xplan.display());
or
select * from table(dbms_xplan.display('PLAN_TABLE','EXPLAIN1','TYPICAL',NULL ));

No comments:

Post a Comment