oracle Oracle如何检查索引是否被使用

How to check whether index is being used or not in Oracle

提问by Rajesh Bhat

SELECT * 
FROM   (SELECT TEMP.*, 
               ROWNUM RNUM 
        FROM   (SELECT entry_guid 
                FROM   alertdevtest.ENTRY 
                WHERE  Upper(alert_name) = 'alertname' 
                       AND user_guid = 'AlertProductClientTest' 
                       AND product_code = '-101' 
                       AND status_code != 13) TEMP 
        WHERE  ROWNUM <= 2500) 
WHERE  rnum >= 0; 

SELECT * 
FROM   (SELECT TEMP.*, 
               ROWNUM RNUM 
        FROM   (SELECT entry_guid 
                FROM   alertdevtest.ENTRY 
                WHERE  Upper(alert_name) = 'alertname' 
                       AND user_guid = 'AlertProductClientTest' 
                       AND product_code = '-101' 
                       AND status_code != 13 
                       AND product_view IN ( 'PView' )) TEMP 
        WHERE  ROWNUM <= 2500) 
WHERE  rnum >= 0; 

Am running above queries and seeing performance degradation in the second query compare to the first one. The only difference is being the additional filter AND PRODUCT_VIEW IN ('PView') in second query. But it has index on that column. Please let me know what can be the reason for performance degradation and how can I check whether index being used or not? Am using Oracle SQL developer and tried checking explain plan but couldn't get much details.

我在查询上面运行，看到第二个查询与第一个查询相比性能下降。唯一的区别是第二个查询中的附加过滤器 AND PRODUCT_VIEW IN ('PView')。但它在该列上有索引。请让我知道性能下降的原因是什么，我如何检查索引是否被使用？正在使用 Oracle SQL 开发人员并尝试检查解释计划，但无法获得太多详细信息。

回答by Robert Dupuy

In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used. For example, in a query I just ran, in the left column (OPERATION) it shows "SELECT STATEMENT" first, then SORT (AGGREGATE) and then INDEX (RANGE SCAN) and then in the OBJECT_NAME column it shows TICKER_IDX1, which is the name of an index on my table.

在 Oracle SQL Developer 中，当工作表中有 SQL 时，有一个按钮“解释计划”，您也可以按 F10。执行解释计划后，它将显示在 SQL Developer 的底部视图中。有一列“OBJECT_NAME”，它会告诉你正在使用什么索引。例如，在我刚刚运行的查询中，在左列 (OPERATION) 中，它首先显示“SELECT STATEMENT”，然后是 SORT (AGGREGATE)，然后是 INDEX (RANGE SCAN)，然后在 OBJECT_NAME 列中显示 TICKER_IDX1，这是我的表上的索引名称。

So you can see via the OBJECT_NAME column what indexes are being used.

因此，您可以通过 OBJECT_NAME 列查看正在使用的索引。

It can happen that the Oracle Cost Based Optimizer chooses a sub-optimal execution plan. Many times updating statistics will solve the issue. Other choices are to add additional indexes, in other words a multi-column index. You can hint a SQL statement, but that is rarely needed. Also, it's possible to rewrite the query.

Oracle Cost Based Optimizer 可能会选择次优执行计划。很多时候更新统计数据可以解决这个问题。其他选择是添加额外的索引，即多列索引。您可以提示 SQL 语句，但这很少需要。此外，还可以重写查询。

回答by Jon Heller

The EXPLAIN PLANstatement is the best way to check the execution plan. Graphical execution plans considered harmful.

该EXPLAIN PLAN声明是检查执行计划的最好方式。图形执行计划被认为是有害的。

EXPLAIN PLANhas many benefits over common graphical representation of execution plans:

EXPLAIN PLAN与执行计划的常见图形表示相比有很多好处：

1. Simple, Standard FormatDBMS_XPLAN.DISPLAYworks in any environment and produces output that every Oracle professional is familiar with. Anyone with access to Oracle can reproduce the issue, and everyone can discuss the issue with the same standard names. SQL Developer may be free but most developers and DBAs do not use it.
2. Easy to Process OutputThe output is easy to save and share - store the output in a table, copy the text into Notepad, etc. It's also much easier to compare using a program like WinMerge. Large queries may produce hundreds of lines in the execution plan, using a diff utility can make tuning much easier. For programming tasks text is better than a picture.
3. Includes Important SectionsFor some bizarre reason IDEs never include the Notesection in execution plans. That section often includes important information. In your example, it's possible that a DBA fixed a SQL Plan Baseline for one of the queries but not the other. Without the Notessection we'll just have to guess if there's something weird going on.
4. More AccurateSome tools use a separate session to generate graphical execution plans and produce wrong results. For example, plans may be very different without alter session enable parallel dml;. This doesn't appear to be an issue with SQL Developer but I've seen it with other programs.
5. More PowerfulDBMS_XPLANcan be scripted and has many powerful features, like format => '+outline', dbms_xplan.display_awr, etc.

1. 简单的标准格式DBMS_XPLAN.DISPLAY适用于任何环境，并生成每位 Oracle 专业人员都熟悉的输出。任何有权访问 Oracle 的人都可以重现该问题，并且每个人都可以使用相同的标准名称讨论该问题。SQL Developer 可能是免费的，但大多数开发人员和 DBA 并不使用它。
2. 易于处理输出输出易于保存和共享 - 将输出存储在表格中，将文本复制到记事本等。使用 WinMerge 之类的程序进行比较也容易得多。大型查询可能会在执行计划中产生数百行，使用 diff 实用程序可以使调优更容易。对于编程任务，文字胜于图片。
3. 包含重要部分出于某些奇怪的原因，IDE 从未Note在执行计划中包含该部分。该部分通常包含重要信息。在您的示例中，DBA 可能为其中一个查询而不是另一个查询修复了 SQL 计划基线。如果没有该Notes部分，我们将只需要猜测是否发生了一些奇怪的事情。
4. 更准确一些工具使用单独的会话来生成图形执行计划并产生错误的结果。例如，没有alter session enable parallel dml;. 这似乎不是 SQL Developer 的问题，但我已经在其他程序中看到过。
5. 更强大的DBMS_XPLAN可以编写脚本，有许多强大的功能，如format => '+outline'，dbms_xplan.display_awr等。

Below is a simple example of EXPLAIN PLAN. This plan is fine but it does have a huge red flag that most graphical execution plans would not show. The last line, dynamic statistics used: dynamic sampling (level=2)implies that one of the tables is missing optimizer statistics.

下面是一个简单的例子EXPLAIN PLAN。这个计划很好，但它确实有一个巨大的危险信号，大多数图形执行计划不会显示。最后一行， dynamic statistics used: dynamic sampling (level=2)暗示其中一个表缺少优化器统计信息。

drop table test1;
create table test1(a number);
explain plan for insert into test1 select * from test1;
select * from table(dbms_xplan.display);

Plan hash value: 4122059633

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST1 |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | TEST1 |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

For a quick check it's easier to hit F10, F5, ctrl+E, or whatever the shortcut is in your specific IDE. But for serious analysis that will be shared with others, always use EXPLAIN PLAN.

要快速检查，可以更轻松地按 F10、F5、ctrl+E 或特定 IDE 中的任何快捷方式。但是对于将与他人共享的严肃分析，请始终使用EXPLAIN PLAN.

回答by Anton Zaviriukhin

Best thing to understand reason of poor performance is to get SQL*Trace. There are several ways to enable tracing http://docs.oracle.com/cd/B19306_01/server.102/b14211/sqltrace.htm#g33356For example

了解性能不佳原因的最佳方法是获取 SQL*Trace。有几种方法可以启用跟踪http://docs.oracle.com/cd/B19306_01/server.102/b14211/sqltrace.htm#g33356例如

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

You need to enable trace, run these queries, fetch data to the end (because usually most of work Oracle perform during fetch), close session (cursor need to be closed to make execution plan with time statistics appear in trace file).
Then you should go to dump folder on database server, get trace file, user tkprof utility to convert it to more readable way, find these queries inside, enjoy :).
Benefits of SQL Trace is that it gives real plan that was used during SQL execution, and it gives very precise time,reads,gets, consistent reads, cpu statistics for each step of the plan (unfortunately I don't know any other tool that can do this).
Disadvantage is that you need privilege to start tracing and you need access to dump folder (or you need to ask DBA to execute query with tracing and server admin to get file).
More rough option is to enable monitoring for this SQL (for example by /*+ monitor */ hint) and use DBMS_SQLTUNE.REPORT_SQL_MONITORhttp://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDBHIBG- it may generate pretty HTML with stats for each step of execution plan (like in SQL Trace)
Advantage is ease of use (you don't need to have access to server folder to use this).
Disadvantage is that grain of statistics in this case is usually 1 second, so it is useful only for long-running queries (10 seconds and more).
If you need just check if index was used during SQL execution, you may query v$sql_plan view and v$sql, v$sql_monitor views to find appropriate plan.

您需要启用跟踪、运行这些查询、获取数据到最后（因为通常 Oracle 在获取期间执行的大部分工作）、关闭会话（需要关闭游标以使带有时间统计信息的执行计划出现在跟踪文件中）。
然后你应该去数据库服务器上的转储文件夹，获取跟踪文件，用户 tkprof 实用程序将其转换为更易读的方式，在里面找到这些查询，享受 :)。
SQL Trace 的好处是它提供了在 SQL 执行期间使用的真实计划，并且它为计划的每个步骤提供了非常精确的时间、读取、获取、一致读取、cpu 统计信息（不幸的是我不知道任何其他工具可以这样做）。
缺点是您需要特权才能开始跟踪，并且需要访问转储文件夹（或者您需要要求 DBA 使用跟踪和服务器管理员执行查询以获取文件）。
更粗略的选项是启用对此 SQL 的监控（例如通过 /*+ monitor */ 提示）并使用DBMS_SQLTUNE.REPORT_SQL_MONITORhttp://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDBHIBG-它可能会为执行计划的每个步骤生成带有统计信息的漂亮 HTML（就像在 SQL 跟踪中一样）
优点是易于使用（您不需要访问服务器文件夹来使用它）。
缺点是这种情况下的统计粒度通常为 1 秒，因此它仅适用于长时间运行的查询（10 秒及以上）。
如果您只需要检查在 SQL 执行过程中是否使用了索引，您可以查询 v$sql_plan 视图和 v$sql, v$sql_monitor 视图以找到合适的计划。