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  lt  to char SYSDATE - numtodsinterval    1  MINUTE      YYYYMMDDHH24MISS     0000   and SO ID  gt  to char SYSDATE - numtodsinterval    1   HOUR      YYYYMMDDHH24MISS     0000  and NOT EXISTS SELECT ID from TEMP BPL T WHERE T ID   SO ID

User · Answer

We use Oracle PL SQL Developer Version 12 0 7   And we use F5 button to view the explain plan

User · Answer

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

User · Answer

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 gt  EXPLAIN PLAN FOR   2  SELECT   FROM DUAL   Explained   SQL gt  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 gt    See How to create and display Explain Plan

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

Examples related to sql

Examples related to oracle

Examples related to oracle-sqldeveloper

Examples related to sql-execution-plan