[sql] 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 );

This question is related to sql oracle oracle-sqldeveloper sql-execution-plan

The answer is


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


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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle-sqldeveloper

how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Extract number from string with Oracle function How to run .sql file in Oracle SQL developer tool to import database? SQL Developer with JDK (64 bit) cannot find JVM How do I view the Explain Plan in Oracle Sql developer? NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle SqlDeveloper JDK path Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection?

Examples related to sql-execution-plan

How do I view the Explain Plan in Oracle Sql developer? How do I obtain a Query Execution Plan in SQL Server? Understanding the results of Execute Explain Plan in Oracle SQL Developer Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"