Oracle 执行计划
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9269042/
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
Oracle Execution Plan
提问by Umut Derbento?lu
I am using Oracle 11g and Toad for Oracle. How can I display execution plan for queries? In Sql server management studio execution plan can be displayed as graphical format. Is there any functionality/tool like that on Toad for oracle?
我正在使用 Oracle 11g 和 Toad for Oracle。如何显示查询的执行计划？在Sql server management studio中执行计划可以以图形格式显示。Toad for oracle 上是否有类似的功能/工具？
回答by cagcowboy
CTRL-E
CTRL-E
Make sure you've ended the query with a semi-colon (and the query above)
确保您已使用分号结束查询（以及上面的查询）
Edit:
编辑：
You need to set-up the TOAD plan table for use. If you think it's already setup on your DB then you may just need to be granted access. Alternatively in my slightly older version of TOAD it's under:
您需要设置 TOAD 计划表以供使用。如果您认为它已经在您的数据库上设置，那么您可能只需要被授予访问权限。或者，在我稍旧版本的 TOAD 中，它位于：
Database --> Administer --> Server Side Objects Wizard. From here you can create the plan table(s) in a schema that you choose.
数据库 --> 管理 --> 服务器端对象向导。从这里您可以在您选择的模式中创建计划表。
回答by Evan
You should create the PLAN_TABLE using a script provided by Oracle which is named UTLXPLAN.SQL and is located in one of the installation folders on the database server.
您应该使用 Oracle 提供的名为 UTLXPLAN.SQL 的脚本创建 PLAN_TABLE，该脚本位于数据库服务器上的安装文件夹之一。
Then, you should use the EXPLAIN PLAN statement for generating a plan for a SQL statement, like this: EXPLAIN PLAN SET STATEMENT_ID = 'your_identifier_for_this_plan' FOR ... your statement ... ;
然后，您应该使用 EXPLAIN PLAN 语句为 SQL 语句生成计划，如下所示： EXPLAIN PLAN SET STATEMENT_ID = 'your_identifier_for_this_plan' FOR ... your statement ... ;
Then, you can use either a select from PLAN_TABLE (usually using a hierarchical query) or the DBMS_XPLAN.DISPLAY_PLAN procedure to display the plan. In the same folder where the UTLXPLAN.SQL file is located, there usually exist examples of using this procedure.
然后，您可以使用 PLAN_TABLE 中的选择（通常使用分层查询）或 DBMS_XPLAN.DISPLAY_PLAN 过程来显示计划。在 UTLXPLAN.SQL 文件所在的同一文件夹中，通常存在使用此过程的示例。
Also, in SQL*PLUS you can use the SET AUTOTRACE feature.
此外，在 SQL*PLUS 中，您可以使用 SET AUTOTRACE 功能。
回答by Jorge T
For TOAD FOR ORACLE
甲骨文的蟾蜍
this helped me How do I view the Explain Plan in Oracle Sql developer?, I just write what they did in sql developer and wrote in the toad editor and then execute.
这对我有帮助如何在 Oracle Sql 开发人员中查看解释计划？，我只是写了他们在sql developer中所做的，然后在toad编辑器中编写然后执行。
Example
explain plan for select field1, field2 from TABLE_NAME;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
回答by Pino
Check that all queries end with a semicolon, put the cursor on the query you want to analyze and hit CTRL-E.
检查所有查询是否以分号结尾，将光标放在要分析的查询上，然后按CTRL-E。
The first time you could get a popup that asks for the name of the plan table, it suggests TOAD_PLAN_TABLEbut it's better to use the standard Oracle table PLAN_TABLEthat should be already available. So enter PLAN_TABLEin place of TOAD_PLAN_TABLE(do not specify a schema) and hit OK. You should get a message saying that the object already exists: hit OK again to acknowledge it. Now try CTRL-E again and you'll get the explain plan.
第一次你会得到一个要求计划表名称的弹出窗口，它建议TOAD_PLAN_TABLE但最好使用PLAN_TABLE应该已经可用的标准 Oracle 表。所以输入PLAN_TABLE代替TOAD_PLAN_TABLE（不指定模式）并点击确定。您应该收到一条消息，指出该对象已经存在：再次点击“确定”以确认它。现在再次尝试 CTRL-E，您将获得解释计划。
To view/change the currently configured plan table name go to menu "View / Toad Options / Oracle General".
要查看/更改当前配置的计划表名称，请转到菜单“查看/Toad 选项/Oracle 常规”。
