sqloracle-databaseoracle-sqldevelopersql-execution-plan

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


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 );

Solution

  • EXPLAIN PLAN FOR

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

    enter image description here

    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