How do I view the Explain Plan in Oracle Sql developer?
SqlOracleOracle SqldeveloperSql Execution-PlanSql Problem Overview
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 );
Sql Solutions
Solution 1 - Sql
>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>
Solution 2 - Sql
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".
Solution 3 - Sql
We use Oracle PL/SQL Developer(Version 12.0.7). And we use F5 button to view the explain plan.