How to Generate Execution Plan in Oracle

Knowing how to generate an execution plan optimizer of the SQL statement is an important part of optimizing SQL performance. There are many methods for generating an execution plan. The best option that suits you should be your tuning tool.

Execution plan with autotrace

When you want to display the SQL execution plan and don’t want to execute the SQL query first, then you can use the autotrace option. The problem with this option is that when executing on screen, it shows both the SQL execution plan and calculates the result.

If a SQL statement is taking longer to execute, you will not be able to look at the execution plan until it completes. You typically won't be able to see this if it's working fine and you're only seeing shortcomings at certain times.

SQL> set autotrace on;
SQL> select * from test;

Get execution plan without query execution

Another way to generate an execution plan, without running the SQL statement beforehand, is simply by looking at the Oracle explain plan. This method does not require a query to execute first – it will display the optimizer execution plan without even executing the SQL statement.

SQL> explain plan for
SQL> select * from test;

Run the below query to get the execution plan

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

Find SQL ID against query

Using the below query you can find the sql_id against the query.

Step 1:

SQL> select /* MY_TEST_QUERY */ max(a.PADVICE_ID)
from XS0A0_20212022_SFPLSJPV.PAYment_advice_EXEPENSE a
where SUP_LEDGER_NAME IS NOT NULL
and  nvl(a.PADVICE_ID,'?') <> 'SFPLGNAVPAH95467348'
and not exists (select 1 from XS0A0_20212022_SFPLSJPV.voucher_view b
               where nvl(a.PADVICE_ID,'?') = nvl(b.link_id,'?'))
AND  PADVICE_ID NOT IN ('SFPLBLHSPAH477840499359');

Step 2:

SQL> select sql_id, child_number, sql_text from   v$sql 
where  sql_text like '%MY_TEST_QUERY%'
and    sql_text not like '%v$sql%';