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.
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
- Resolve error in invoking target agent nmhs of makefile
- Purge RECYCLEBIN in Oracle
- How to get tablespace DDL in oracle
- How to create tablespace in Oracle
- Switchover steps in Oracle 11G, 12c, 19c
- Run Expdp Jobs in Background