61

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 );
Andrew
  • 3,632
  • 24
  • 64
  • 113
  • 1
    You also can do it using SQL Profiler tool in dbForge Studio for Oracle. Have a look at feature review page - [SQL Profiler](https://www.devart.com/dbforge/oracle/studio/oracle-sql-profiler.html#header). – Devart Dec 22 '16 at 09:01

3 Answers3

104

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

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 7
    For the record, I find `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` to produce *vastly* more readable output than SQL Developer's [Explain Plan window](https://i.stack.imgur.com/dOMhm.png). – jpmc26 Oct 31 '17 at 02:26
  • @jpmc26 It depends from individual to individual. I personally use more command-line interface, however, my manager is fond of GUI for similar things. – Lalit Kumar B Oct 31 '17 at 07:27
  • 3
    I like GUIs when they present info well. I'm saying the GUI in this case doesn't present info very well. ;) – jpmc26 Oct 31 '17 at 18:33
  • @jpmc26 Yes, it is pretty basic GUI compared to other tools in the market. – Lalit Kumar B Nov 03 '17 at 12:20
10

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".

jlemley
  • 533
  • 1
  • 4
  • 15
Jin
  • 121
  • 1
  • 4
1

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

amdg
  • 2,211
  • 1
  • 14
  • 25