如何在Oracle Sql开发人员中查看解释计划? [英] How do I view the Explain Plan in Oracle Sql developer?

问题描述

我很少有SQL查询，这些查询的查询运行性能非常低，我想检查该查询的查询执行计划.我正在尝试执行以下查询，但未显示任何查询执行计划.其唯一的显示消息计划FOR已成功.我不知道在oracle sql开发人员中有什么设置可以用来解释查询计划:

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

推荐答案

推荐计划

在 SQL Developer 中，您不必使用EXPLAIN PLAN FOR语句.按 F10 或单击解释计划"图标.

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

它随后将显示在解释计划"窗口中.

如果您使用的是 SQL * Plus ，请使用 DBMS_XPLAN .

If you are using SQL*Plus then use DBMS_XPLAN.

例如，

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>

请参见 如何创建和显示解释计划

这篇关于如何在Oracle Sql开发人员中查看解释计划?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！