Menu
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 ); 
464
19.1. In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement: After the statement has executed, you can display the plan by querying the V$SQL_PLAN view.
Running EXPLAIN PLANEXPLAIN PLAN FOR SELECT last_name FROM employees; This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE . This is useful if you do not have any other plans in PLAN_TABLE , or if you only want to look at the last statement.
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> See How to create and display Explain Plan
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
