EXPLAIN PLAN is often used to generate execution plan of SQL statement or set of SQL statements. But we would always prefer to run EXPLAIN PLAN for each SQL statement and generate its execution plan using DBMS_XPLAN package and then pick up next SQL statement. But we can also run EXPLAIN PLAN for multiple SQL statements say 3 of them one after the other and later we can generate execution plan of all these 3 SQL statements. How? Let’s see that.

Basics:

EXPLAIN PLAN inserts the execution plan of SQL statement(s) into a table that can be user defined table or the default table “PLAN_TABLE”. PLAN_TABLE is global and is available for all the users of the database since Oracle 10g.

Notes:

• Oracle database 11.2.0.3.
• SCOTT schema generating execution plan.
• Default table PLAN_TABLE is used in this blog.

EXPLAIN PLAN for multiple SQL statements:

Let me pick up three different SQL statements to run EXPLAIN PLAN against them.

1. Select * from t1 where owner not in (‘SYS’,’SYSTEM’);
2. Select * from t1 where owner=’SYS’;
3. Select * from t1 where owner in (‘SYS’,’SYSTEM’,’DBSNMP’,’SCOTT’,’SYSMAN’);

Saving multiple execution plans in PLAN_TABLE is possible with “SET STATEMENT_ID” clause in EXPLAIN PLAN. For each SQL statement we should give a unique STATEMENT_ID so that PLAN_TABLE records all the execution plans.

Number of rows in PLAN_TABLE before running EXPLAIN_PLAN:

SQL> select count(*) from plan_table;

 COUNT(*)

———-

0

Let us now run EXPLAIN PLAN for all three SQL statements with unique STATEMENT_ID.

SQL> explain plan

set STATEMENT_ID=’EXPLAIN1′

for

Select * from t1 where owner not in (‘SYS’,’SYSTEM’);

Explained.

 

SQL> select unique statement_id from plan_table;

STATEMENT_ID

——————————

EXPLAIN1

SQL> explain plan

set STATEMENT_ID=’EXPLAIN2′

for

Select * from t1 where owner=’SYS’;

Explained.

SQL> select unique statement_id from plan_table;

STATEMENT_ID

——————————

EXPLAIN2

EXPLAIN1

SQL> explain plan

set STATEMENT_ID=’EXPLAIN3′

for

Select * from t1 where owner in (‘SYS’,’SYSTEM’,’DBSNMP’,’SCOTT’,’SYSMAN’);

Explained.

SQL> select unique statement_id from plan_table;

STATEMENT_ID

——————————

EXPLAIN2

EXPLAIN3

EXPLAIN1

Generate EXECUTION PLAN for these 3 statements:

As we have given a unique STATEMENT_ID for each EXPLAIN PLAN we ran, it is now easy to use these STATEMENT_ID’s to generate EXECUTION PLAN of each query.

SQL> select * from table(dbms_xplan.display(‘PLAN_TABLE’,’EXPLAIN1′,’TYPICAL’,NULL));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3617692013

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      | 43545 |  4167K|   298   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T1   | 43545 |  4167K|   298   (1)| 00:00:04 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

——————————————————————————–

1 – filter(“OWNER”<>’SYS’ AND “OWNER”<>’SYSTEM’)

13 rows selected.

SQL> select * from table(dbms_xplan.display(‘PLAN_TABLE’,’EXPLAIN2′,’TYPICAL’,NULL));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3617692013

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      | 30607 |  2929K|   298   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T1   | 30607 |  2929K|   298   (1)| 00:00:04 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

——————————————————————————–

1 – filter(“OWNER”=’SYS’)

13 rows selected.

SQL> select * from table (dbms_xplan.display(‘PLAN_TABLE’,’EXPLAIN3′,’TYPICAL’,NULL));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 3617692013

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      | 34898 |  3339K|   299   (2)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T1   | 34898 |  3339K|   299   (2)| 00:00:04 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

——————————————————————————–

1 – filter(“OWNER”=’DBSNMP’ OR “OWNER”=’SCOTT’ OR “OWNER”=’SYS’ OR

“OWNER”=’SYSMAN’ OR “OWNER”=’SYSTEM’)

14 rows selected.

Conclusion:

If you had to generate execution plans for multiple queries and would like to view them later, the possible solution is to use STATEMENT_ID clause. Last but not the least, if you EXIT from the current session and re-login then all the data in PLAN_TABLE will be erased. Be cautious J

SQL> exit

sqlplus scott

Enter password:

Connected.

SQL> select count(*) from plan_table;

COUNT(*)

———-

0

https://youtu.be/ZXKcrh3MUfk