Execution Plan(or Explain Plan) of a SQL Query

It's very important for a developer and DBAs as well to know how to take out the execution plan of a sql statement to troubleshoot a problem.

The SQL execution plan contains detailed information on how a particular query or a set of queries will be executed by the Advantage Database Server.

It tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA's primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.  


For example 2 queries    "select * from cron" and "delete from cron where cronid =1;"

 You can get the execution plan from the SQL Devloper or SQLPLUS.

1. From Sql Developer

Just open the database and write the query and press the F6 button which will runs an AutoTrace, which actually runs the SQL and also displays timings and the explain plan like in below pix.






 From SQL Plus:

You can do it by logging into the sqlplus command prompt either via your id and viz sys user.

I will tell you by logging into my id 1st after that with sys user also.

Login into the sqlplus viz your id like I have did in the below image.

if you are connected via Command Prompt:

then Open your command prompt > sqlplus debashis/********(here give ur uname and pwd)

It will open the sqlplus prompt.








You are done.


Via SYS USER 

command prompt > sqlplus / as sysdba









Do for all the query you want to troubleshoot.


Have Fun.