How do I view the Explain Plan in Oracle Sql developer?

Question

I have few SQL queries which has very low query running performance and I want to check the query execution plan for this query. I am trying to execute the below query but its not showing any query execution plan. Its only display message plan FOR succeeded. I dont know is there any settings that we have to do in oracle sql developer to vies explain plan for query :

EXPLAIN PLAN FOR 
Select SO.P_OPTION_ID FROM
SIMSIM 
   JOIN P_TYPE PT on PT.KEY=SIM.P_TYPE_KEY JOIN P_CONFIG PC ON PC.ID=PT.PRODUCT_CONFIG_ID
JOIN P_OPTION PO ON PO.OPTION_KEY=PC.DEFAULT_PRODUCT_OPTIONS JOIN S_OPTION SO ON SO.SERVICE_ID=SIM.ASSIGNED_TO_SERVICE_ID
JOIN AVV_NO AN ON SIM.ASSIGNED_ANUMBER_ID = AN.ID
 where SO.STATUS_ID IN (20,40) 
 and SO.ID < to_char(SYSDATE - numtodsinterval (  1,'MINUTE' ), 'YYYYMMDDHH24MISS')||'0000'
 and SO.ID > to_char(SYSDATE - numtodsinterval (  1, 'HOUR' ), 'YYYYMMDDHH24MISS')||'0000'
and NOT EXISTS(SELECT ID from TEMP_BPL T WHERE T.ID = SO.ID );

Oxpecker

You also can do it using SQL Profiler tool in dbForge Studio for Oracle. Have a look at feature review page - SQL Profiler. – Navvy

Answer 1

EXPLAIN PLAN FOR

In SQL Developer, you don't have to use EXPLAIN PLAN FOR statement. Press F10 or click the Explain Plan icon.

It will be then displayed in the Explain Plan window.

If you are using SQL*Plus then use DBMS_XPLAN.

For example,

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM DUAL;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

See How to create and display Explain Plan

Misconstrue

For the record, I find SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); to produce vastly more readable output than SQL Developer's Explain Plan window. – Eclectic

@Eclectic It depends from individual to individual. I personally use more command-line interface, however, my manager is fond of GUI for similar things. – Misconstrue

I like GUIs when they present info well. I'm saying the GUI in this case doesn't present info very well. ;) – Eclectic

@Eclectic Yes, it is pretty basic GUI compared to other tools in the market. – Misconstrue

Answer 2

Explain only shows how the optimizer thinks the query will execute.

To show the real plan, you will need to run the sql once. Then use the same session run the following:

@yoursql;
select * from table(dbms_xplan.display_cursor()) 

This way can show the real plan used during execution. There are several other ways in showing plan using dbms_xplan. You can Google with term "dbms_xplan".

Ancestry

Is this valid for PL/SQL? – Familiarize

typo.. select * from table(dbms_xplan.display_cursor()) – Calibrate

@RickHenderson search for the execution plan, it's valid and you SHOULD use it before running your query:) – Riendeau

Answer 3

We use Oracle PL/SQL Developer(Version 12.0.7). And we use F5 button to view the explain plan.

Speak