如何在 Oracle Sql developer 中查看 Explain Plan？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30070910/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I view the Explain Plan in Oracle Sql developer?
提问by Andrew
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 :
我有几个查询运行性能非常低的 SQL 查询，我想检查此查询的查询执行计划。我正在尝试执行以下查询，但未显示任何查询执行计划。它唯一的显示消息计划 FOR 成功。我不知道我们必须在 oracle sql developer 中进行任何设置来解释查询计划：
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 );
回答by Lalit Kumar B
EXPLAIN PLAN FOR
解释计划
In SQL Developer, you don't have to use EXPLAIN PLAN FORstatement. Press F10or click the Explain Plan icon.
在SQL Developer 中，您不必使用EXPLAIN PLAN FOR语句。按F10或单击解释计划图标。
It will be then displayed in the Explain Plan window.
然后它将显示在“解释计划”窗口中。
If you are using SQL*Plusthen use DBMS_XPLAN.
如果您使用SQL*Plus，则使用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
请参阅如何创建和显示说明计划
回答by Jin
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:
要显示真正的计划，您需要运行 sql 一次。然后使用相同的会话运行以下命令：
@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".
这种方式可以显示执行过程中使用的真实计划。还有其他几种使用 dbms_xplan 显示计划的方法。您可以使用术语“dbms_xplan”进行 Google。
回答by amdg
We use Oracle PL/SQL Developer(Version 12.0.7). And we use F5button to view the explain plan.
我们使用 Oracle PL/SQL Developer（版本 12.0.7）。我们使用F5按钮查看解释计划。
