Generating Execution Plan In Oracle Advance (Best Ways 2022)

Generating EXECUTION PLAN is discussed in detail with the help of clear and comprehensive screenshots, along with a comparison of times saved. These include getting EXPLAIN PLAN and using V$SQL_PLAN to find out execution plan in Oracle Database.

Read: How to Add New Disk in ASM DiskGroup

Table of Contents

Environment for this practice

Generate EXECUTION PLAN from OCP_TABLE.
Generate EXECUTION PLAN using DBMS_XPLAN.

Generating EXECUTION PLAN from PLAN_TABLE

Explain how you can find execution plans of existing SQL queries via a PLAN_TABLE. With the DBMS_XPLAN package, you can look at the following statements for formatting to ensure the correct fetch request on this table and understand the output.

The main benefits of incorporating the PLAN_TABLE into user sessions are better utilization and a more detailed record of what is being executed in the database.

Let’s write a query to generate the execution plan for a given statement.

sql>set lines 100
sql>col EXECUTION_PLAN for a50
sql>show user

USER is “SCOTT

sql> explain plan for select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

Get execution plan:

SQL> select rtrim(lpad(‘ ‘,2*LEVEL) || rtrim(operation) || ‘ ‘ || rtrim(options) || ‘ ‘ || object_name) EXECUTION_PLAN, cost, cardinality from plan_table connect by prior id=parent_id start with id=0;

You will see a plan below

You can modify this SELECT statement to generate the execution plan you need, restricting it based on what columns you wish.

How to Generating EXECUTION PLAN using DBMS_XPLAN

DBMS_XPLAN is Oracle’s most powerful and widely used package for extracting execution plans from SQL statements. Some of the functions in DBMS_XPLAN are DISPLAY() and DISPLAY_CURSOR().

Have you figured out how to generate the execution plan of your SQL statement? DISPLAY() function will extract the EXPLAIN PLAN which means running EXPLAIN PLAN is a prerequisite.

Though DISPLAY_CURSOR() is primarily used to extract a query’s execution plan, it can also be used to obtain information about other key components of the shared pool at a moment in time.

Generating execution plan with below functions

Generate execution plan Using DISPLAY() function

SQL> explain plan for select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

Show execution plan:

SQL> select * from table(dbms_xplan.display);

Compare the execution plan generated by DBMS_XPLAN with the results of the preceding SELECT statement.

Generating execution plan Using DISPLAY_CURSOR() function

Generating execution plan with SQL ID and cursor child number. The DISPLAY_CURSOR() function generates a plan for each statement it runs.

Creating an execution plan with DISPLAY_CURSOR() is simple. Using the SQL*Plus command line, the below SQL has been run from Scott’s schema, gathering SQL_ID and the most recent cursor child number from V$SQL. You can then use V$SQL_SHARED_CURSOR to see more details.

SQL>conn scott/password
Connected.

SQL>select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

SQL>conn / as sysdba

SQL> select sql_id,child_number from v$sql where sql_text like ‘select * from t1 where owner in(%’;

SQL_ID        	CHILD_NUMBER
-------------	-----------
32skc1zj8axd9	0

SQL> select * from table(dbms_xplan.display_cursor(’32skc1zj8axd9′,0,’TYPICAL’));

Plan as per the above details:

Read below important points:

If the statement is not found in the shared_pool then we can use the explain plan.
DISPLAY_CURSOR function from DBMS_XPLAN package used if the SQL statement is already in shared_pool.
Oracle recommends generating an execution plan using DBMS_XPLAN. The DISPLAY_CURSOR as it gives us an accurate plan of execution.
EXPLAIN PLAN will change depending on the value of bind variable values found via DISPLAY_CURSOR. It is important to generate an execution plan before executing SQL statements.

You Must Read