如何在 Oracle 中查看 SQL 执行计划？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11799344/
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 can I see the SQL execution plan in Oracle?
提问by user1573640
I'm learning about database indexes right now, and I'm trying to understand the efficiency of using them.
我现在正在学习数据库索引，并试图了解使用它们的效率。
- I'd like to see whether a specific query uses an index.
- I want to actually see the difference between executing the query using an index and without using the index (so I want to see the execution plan for my query).
- 我想看看特定查询是否使用索引。
- 我想真正看到使用索引和不使用索引执行查询之间的区别（所以我想查看我的查询的执行计划）。
I am using sql+.
我正在使用sql+。
How do I see the execution plan and where can I found in it the information telling me whether my index was used or not?
我如何查看执行计划以及在哪里可以找到告诉我是否使用了我的索引的信息？
回答by Birupakhya Dash
Try using this code to first explain and then see the plan:
尝试用这段代码先说明再看方案：
Explain the plan:
解释计划：
explain plan
for
select * from table_name where ...;
See the plan:
看计划：
select * from table(dbms_xplan.display);
Edit: Removed the brackets
编辑：删除括号
回答by Glenn
Take a look at Explain Plan. EXPLAIN works across many db types.
看看解释计划。EXPLAIN 适用于多种数据库类型。
For sqlPlus specifically, see sqlplus's AUTO TRACEfacility.
对于 sqlPlus，请参阅 sqlplus 的AUTO TRACE工具。
回答by Amaethon
Try this:
尝试这个：
http://www.dba-oracle.com/t_explain_plan.htm
http://www.dba-oracle.com/t_explain_plan.htm
The execution plan will mention the index whenever it is used. Just read through the execution plan.
每当使用索引时，执行计划都会提及它。只需通读执行计划即可。
回答by Faruk ?EV?K
SQL> explain plan for 2
select * from hr.employees;
Explained.
解释了。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7704 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7704 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.
OR
或者
SQL> set autotrace traceonly explain
SQL> select * from hr.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7704 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7704 | 3 (0)| 00:00:01 |
回答by Vlad Mihalcea
This is a very common question, so I decided to turn this answer into an article.
这是一个很常见的问题，所以我决定把这个答案变成一篇文章。
The estimated SQL execution plan
估计的 SQL 执行计划
The estimated execution plan is generated by the Optimizer without executing the SQL query. You can generate the estimated execution plan from any SQL client using EXPLAIN PLAN FORor you can use Oracle SQL Developerfor this task.
估计的执行计划由优化器生成，不执行 SQL 查询。您可以使用EXPLAIN PLAN FOR从任何 SQL 客户端生成估计的执行计划，也可以使用Oracle SQL Developer执行此任务。
EXPLAIN PLAN FOR
解释计划
When using Oracle, if you prepend the EXPLAIN PLAN FORcommand to a given SQL query, the database will store the estimated execution plan in the associated PLAN_TABLE:
使用 Oracle 时，如果您将EXPLAIN PLAN FOR命令添加到给定的 SQL 查询中，数据库会将估计的执行计划存储在关联的PLAN_TABLE:
EXPLAIN PLAN FOR
SELECT p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
To view the estimated execution plan, you need to use DBMS_XPLAN.DISPLAY, as illustrated in the following example:
要查看估计的执行计划，您需要使用DBMS_XPLAN.DISPLAY，如下例所示：
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'))
The ALL +OUTLINE formatting option allows you to get more details about the estimated execution plan than using the default formatting option.
与使用默认格式选项相比，ALL +OUTLINE 格式选项允许您获得有关估计执行计划的更多详细信息。
Oracle SQL Developer
Oracle SQL 开发人员
If you have installed SQL Developer, you can easily get the estimated execution plan for any SQL query without having to prepend the EXPLAIN PLAN FOR command:
如果您安装了 SQL Developer，您可以轻松获得任何 SQL 查询的估计执行计划，而无需预先添加 EXPLAIN PLAN FOR 命令：
The actual SQL execution plan
实际的 SQL 执行计划
The actual SQL execution plan is generated by the Optimizer when running the SQL query. So, unlike the estimated Execution Plan, you need to execute the SQL query in order to get its actual execution plan.
实际的 SQL 执行计划是在运行 SQL 查询时由优化器生成的。因此，与估计的执行计划不同，您需要执行 SQL 查询才能获得其实际执行计划。
The actual plan should not differ significantly from the estimated one, as long as the table statistics have been properly collected by the underlying relational database.
只要底层关系数据库正确收集了表统计信息，实际计划不应与估计的计划有太大差异。
GATHER_PLAN_STATISTICS query hint
GATHER_PLAN_STATISTICS 查询提示
To instruct Oracle to store the actual execution plan for a given SQL query, you can use the GATHER_PLAN_STATISTICSquery hint:
要指示 Oracle 存储给定 SQL 查询的实际执行计划，您可以使用GATHER_PLAN_STATISTICS查询提示：
SELECT /*+ GATHER_PLAN_STATISTICS */
p.id
FROM post p
WHERE EXISTS (
SELECT 1
FROM post_comment pc
WHERE
pc.post_id = p.id AND
pc.review = 'Bingo'
)
ORDER BY p.title
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
To visualize the actual execution plan, you can use DBMS_XPLAN.DISPLAY_CURSOR:
要可视化实际执行计划，您可以使用DBMS_XPLAN.DISPLAY_CURSOR：
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'))
Enable STATISTICS for all queries
为所有查询启用 STATISTICS
If you want to get the execution plans for all queries generated within a given session, you can set the STATISTICS_LEVELsession configuration to ALL:
如果要获取给定会话中生成的所有查询的执行计划，可以将会话STATISTICS_LEVEL配置设置为 ALL：
ALTER SESSION SET STATISTICS_LEVEL='ALL'
This will have the same effect as setting the GATHER_PLAN_STATISTICSquery hint on every execution query. So, just like with the GATHER_PLAN_STATISTICSquery hint, you can use DBMS_XPLAN.DISPLAY_CURSORto view the actual execution plan.
这与GATHER_PLAN_STATISTICS在每个执行查询上设置查询提示具有相同的效果。因此，就像GATHER_PLAN_STATISTICS查询提示一样，您可以使用DBMS_XPLAN.DISPLAY_CURSOR来查看实际的执行计划。
You should reset the
STATISTICS_LEVELsetting to the default mode once you are done collecting the execution plans you were interested in. This is very important, especially if you are using connection pooling, and database connections get reused.ALTER SESSION SET STATISTICS_LEVEL='TYPICAL'
STATISTICS_LEVEL收集完您感兴趣的执行计划后，您应该将设置重置为默认模式。这非常重要，尤其是当您使用连接池并且数据库连接被重用时。ALTER SESSION SET STATISTICS_LEVEL='TYPICAL'
