We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in Data Analyst | Batch Starting from 10 March 2025 | Register for Free Demo

Know More

explain Plan

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 12 Dec, 2021
  • 0 Comments
  • 1 Min Read

explain Plan

Oracle Explain Plan - Performance Tuning



1. Creating a Plan Table

The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema. The following script will create it for you if you don’t already have it:

export ORACLE_SID=PrimeDG

sqlplus / as sysdba

@?/rdbms/admin/utlxplan.sql




2. Explain Plan Syntax:


EXPLAIN PLAN FOR your-sql-statement;


For example:

explain plan for

Select empno, ename, sal from emp where empno=9999;


OR if you have the statement_id, then you can use that as well.


EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR your-sql-statement;




3. Formatting the output

After running EXPLAIN PLAN, Oracle populates the PLAN_TABLE table with data that needs to be formatted to presented to the user in a more readable format. Several scripts exist for this, however, one of the easiest methods available is to cast dbms_xplan.display to a table and select from it (see examples below).


Some Examples

SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;

Explained.

SQL> set linesize 132

SQL> SELECT * FROM TABLE(dbms_xplan.display);

or

SQL> @?/rdbms/admin/utlxpls.sql



PLAN_TABLE_OUTPUT

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

Plan hash value: 2852011669

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

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

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

|   0 | SELECT STATEMENT        |     | 1 | 20 | 1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN     | PK_DEPT | 1 |   | 0   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):

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


   2 – access(“DEPTNO”=40)


14 rows selected.




4. Using SQL*Plus Autotrace

SQL*Plus also offers an AUTOTACE facility that will display the query plan and execution statistics as each query executes. 

For Example: 

SQL> SET AUTOTRACE ON



5. FOR only explain information from autotrace, use:

SQL> set linesize 200

SQL> set  autotrace traceonly explain;



6. For query output and explain information from autotrace, use:

For example:

SQL> set autotrace on explain

SQL> select count(*) from At_Common.oms_order_item where trim(size_name)=’0.5′ and site_id = 611;


  COUNT(*)

———-

      0


Elapsed: 00:00:16.98


Execution Plan

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

Plan hash value: 1101236086


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

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

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

|   0 | SELECT STATEMENT   |            | 1 | 9 | 50243   (4) | 00:10:03 |

|   1 |  SORT AGGREGATE |            | 1 | 9 |        |      |

|*  2 |   TABLE ACCESS FULL| OMS_ORDER_ITEM |47138 | 414K| 50243   (4) | 00:10:03 |

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


Predicate Information (identified by operation id):

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


   2 – filter(“SITE_ID”=611 AND TRIM(“SIZE_NAME”)=’0.5′)







ADDM Report
SQL TUNING ADVISOR