source

Ways of generating EXECUTION PLAN in Oracle Database

Generating EXECUTION PLAN from explain plan is the best method of reading the plan that optimizer defined for executing a SQL statement in Oracle Database. There are multiple ways of generating such execution plans, one is by running EXPLAIN PLAN and other from V$SQL_PLAN. In this blog, I am going to cover in detail about ways of generating EXECUTION PLAN that might help Oracle DBA in his day to day performance tuning activities.

Running EXPLAIN PLAN is detailed in my earlier blog EXPLAIN PLAN for multiple SQL statements.

Activities:

1. Generate EXECUTION PLAN from PLAN_TABLE.
2. Generate EXECUTION PLAN using DBMS_XPLAN.

2.1 Using function DISPLAY().

2.2 Using function DISPLAY_CURSOR().

1. Generate EXECUTION PLAN from PLAN_TABLE.

Explain plan records the execution plan of optimizer into the default table PLAN_TABLE. We can fetch the execution plan from PLAN_TABLE by running a conventional SELECT statement with proper formatting which can look alike the output of DBMS_XPLAN package.

Every user session will have PLAN_TABLE accessible to allow user generated execution plan since Oracle 10g.

Let us now run explain plan on one SQL statement and then write SELECT statement to generate reasonable execution plan from PLAN_TABLE.

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’);

Explained.

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;

EXECUTION_PLAN                                           COST CARDINALITY

————————————————–      ———- ———–

  SELECT STATEMENT                                         298       31225

    TABLE ACCESS FULL T1                                  298       31225

Consider modifying this SELECT statement to include/exclude the columns you wish to be in the execution plan.

2. Generate EXECUTION PLAN using DBMS_XPLAN.

DBMS_XPLAN is the most widely used package to generate execution plan of SQL statement. There are two functions DISPLAY() and DISPLAY_CURSOR() mostly used with this package.

DISPLAY() function will extract the execution plan of a specific SQL statement(s) from PLAN_TABLE, this means running EXPLAIN PLAN of that statement is pre-requisite.

DISPLAY_CURSOR() function is used to extract execution plan of SQL statement from V$SQL_PLAN  if query is available in shared pool.

Let us generate execution plan with these two functions.

2.1 Using function DISPLAY().

SQL> explain plan for

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

Explained.

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

PLAN_TABLE_OUTPUT

—————————————————————————————————-

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      | 31225 |  2988K|   298   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T1   | 31225 |  2988K|   298   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

—————————————————————————————————-

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

13 rows selected.

Compare this execution plan with the output of SELECT statement we ran on PLAN_TABLE in the above discussion.

2.2 Using function DISPLAY_CURSOR().

To generate execution plan of a query using DISPLAY_CURSOR, you will require SQL ID and cursor child number of that query.

I have run the below SQL from SCOTT schema and captured SQL_ID and latest cursor child number from V$SQL, you can consider using V$SQL_SHARED_CURSOR for much details on cursor.

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

————- ————

54skb3zk6qux7 0

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

PLAN_TABLE_OUTPUT

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

SQL_ID  54skb3zk6qux7, child number 0

————————————-

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

Plan hash value: 3617692013

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

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

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

|   0 | SELECT STATEMENT  |      |       |       |   298 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   | 31225 |  2988K|   298   (1)| 00:00:04 |

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

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

18 rows selected.

Conclusion:

• We can use explain plan if the statement is not found in the shared_pool.
• If the SQL statement is already in shared_pool use DISPLAY_CURSOR function from DBMS_XPLAN package.
• It is always advisable to generate EXECUTION PLAN using DBMS_XPLAN.DISPLAY_CURSOR as it gives us the accurate plan of execution.
• EXPLAIN PLAN will generate execution plan without considering the actual values of bind variables. Optimizer is intelligent enough to change the execution plan before executing the SQL statement by considering bind variable values which can be only found using DISPLAY_CURSOR function.

                       youtube : https://youtu.be/s0Lyh-hQo-g