• Arun Kumar

Generate Execution Plan in Oracle

While working on SQL performance tuning, you must know what execution plan optimiser is generating. This execution plan defines how the SQL statement will be executed.


There are multiple methods to generate an SQL plan. You can choose the best method that suits you.


AUTOTRACE – easy option


The autotrace option is used when you want to display the sql execution plan on your screen. The only problem with this option is that the sql query will execute first and then display the execution plan.


This might typically be a problem if a SQL statement is taking longer to execute. You need wait until the poor performing sql completes and then will be able to look at the execution plan.

set autotrace on;

select * from emp;


EXPLAIN PLAN


The explain plan method does not require query to execute first. It will display the optimiser execution plan without even executing the sql statement

explain plan for
select * from emp;

Run below to show the explain plan on the screen

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


Recent Posts

See All

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose

Top resource intensive SQL

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is runnin

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback