For those who don't have GUI tools like SQL developer or Enterprise Manager, you can still get an explain plan for a statement in SQL*Plus.
How to Explain Plan in SQL Developer ?
We saw a SQL statement which didn't perform well as we thought, so we need to analyze its execution path. The best way to see it is to explain plan for the statement.
In this post, we'll talked about how to display the explain plan for a specific SQL statement in SQL developer, and how to deal with "TABLE ACCESS FULL". Let's see the steps
SQL worksheet
First of all, open a SQL worksheet and put your statement there.
Explain Plain
Next, click on the third icon above the statement or press F10 to start to explain the plan. Then, you can see the analyzed execution path in a breakdown structure below.
TABLE ACCESS FULL
As we can see, the path of data extraction is determined by the result of "TABLE ACCESS FULL" which means the query does not use any index to access.
It may be no index can be used, or the optimizer thinks the full table scanning is efficient enough to perform the query, especially the table is small.
If the table is pretty small, then you don't have to worry about it. Otherwise, you should create some proper indexes for your query to improve its performance. Let's see what SQL tuning can do for you.
SQL Tuning
In a complex query, you may not know what proper indexes should be. In such situation, you can run a SQL tuning report for the statement and check what findings it has.
In the SQL tuning report, some findings indicate that adding suggested indexes or applying a better SQL profile will improve your SQL statement and some degree of benefit ratio is expected.
PLAN_TABLE
As I said in PLAN_TABLE, How and Why, PLAN_TABLE has become a PUBLIC synonym from 11g, which points to a global temporary table SYS.PLAN_TABLE$ to hold explain plans for all users in their sessions.
Theoretically, you don't have to create it. In some certain scenarios, however, you need to create it for displaying explain plans. You may have a look.