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

Question

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; 

我运行了以上两个查询，发现第二个查询的性能比第一个要差。唯一的区别是在第二个查询中增加了过滤条件 AND PRODUCT_VIEW IN ('PView') ，但该列已经创建了索引。请告诉我性能降低的原因，并且如何检查索引是否被使用？我正在使用Oracle SQL开发人员，尝试检查执行计划但得不到太多细节。

- Rajesh Bhat

---

дҪ еҸҜд»ҘдҪҝз”ЁPRODUCT_VIEW = 'PView'д»ЈжӣҝPRODUCT_VIEW IN ('PView')еҗ—пјҹиҝҷж ·дјҡжҸҗй«ҳжҖ§иғҪгҖӮеҸҰеӨ–пјҢдҪ иғҪеҲҶдә«дёҖдёӢдҪ зҡ„жү§иЎҢи®ЎеҲ’еҗ—пјҹ - undefined

我必须使用IN作为搜索，因为可以有多个字符串值。我无法在这里上传解释计划的图像。对于第一个查询，我主要看到两个部分。 - undefined

Answer 1

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

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

有时候Oracle成本优化器会选择次优的执行计划。很多时候更新统计信息可以解决问题。其他选择是添加其他索引，换句话说是一个多列索引。你可以提示一个SQL语句，但那很少需要。另外，还可以重写查询。

- Robert Dupuy

---

两个查询在object_name字段下显示不同的索引名称。这就是响应时间慢的原因吗？第一个查询显示了product_code索引，而第二个查询显示了product_View索引。为什么第二个查询没有同时使用这两个索引呢？ - undefined

Oracle CBO计算成本，并尝试执行其认为最优的解决方案。所以，如果它不使用第二个索引，那是因为它相信这样做实际上会减慢查询速度。当它明显错误时，通常是因为它没有有效的统计信息。所以，这是要检查的一件事情，最近是否更新了统计信息？你能重新运行表和索引的统计信息吗？ - undefined

在具有多个列谓词的相对较长运行查询中，动态采样也可以提供帮助。 - undefined

Answer 2

EXPLAIN PLAN语句是检查执行计划的最佳方式。 图形化执行计划可能会产生误导。

EXPLAIN PLAN相比常见的图形化执行计划有很多优点：

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

以下是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)

如果只是快速检查，可以按F10、F5、ctrl+E或其他您特定IDE中的快捷键。但对于需要与他人共享的严肃分析，请始终使用EXPLAIN PLAN。

- Jon Heller

Answer 3

你可以运行这个查询来检查索引的使用情况。

select ius.* , 
(select  sum(trunc(bytes / 1024 / 1024/ 1024  ,2)) 
 from dba_segments sgm 
 where  sgm.owner = ius.OWNER
   and sgm.segment_name = ius.name ) Used_Space 
   ,ind.DISTINCT_KEYS, ind.Total_Ind_Rows , ind.Total_Leaf_Block,
   trunc((ius.bucket_0_access_count / nullif(ius.total_access_count,0)) *100 ,2)Access_0_PCT,
   trunc((ius.bucket_1_access_count / nullif(ius.total_access_count,0)) *100 ,2) Access_1_PCT,
   trunc((ius.bucket_2_10_access_count / nullif(ius.total_access_count,0)) *100,2) Access_2_10_PCT,
   trunc((ius.bucket_11_100_access_count / nullif(ius.total_access_count,0)) *100,2) Access_11_100_PCT,
   trunc((ius.bucket_101_1000_access_count / nullif(ius.total_access_count,0)) *100,2) Access_101_1000_PCT,
   trunc((ius.bucket_1000_plus_access_count / nullif(ius.total_access_count,0)) *100,2) Access_1000_plus_PCT
from dba_index_usage ius , 
 (select ind.OWNER,ind.INDEX_NAME,ind.DISTINCT_KEYS, sum(ind.num_rows)Total_Ind_Rows,sum(ind.LEAF_BLOCKS)Total_Leaf_Block,
         trunc(sum(ind.num_rows)/nullif(sum(ind.LEAF_BLOCKS),0),2) RowsInBlocks
  from all_indexes ind 
  where ind.owner = &SchemaName
  group by ind.OWNER,ind.INDEX_NAME,ind.DISTINCT_KEYS
  ) ind
where ius.owner = &SchemaName 
  and ius.name = ind.INDEX_NAME

此外，您还可以添加v$sql_plan和dba_hist_sql_plan来过滤未使用的索引。

and not exists 
      (select 'x'
       from v$sql_plan pln
       where  pln.OPERATION = 'INDEX'
        and pln.OBJECT_OWNER = ius.owner
        and pln.OBJECT_NAME = ius.name)

- ZASA82

Answer 4

了解性能不佳的原因最好的方法是使用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);

你需要启用跟踪功能，运行这些查询，获取数据到最后（因为通常Oracle在获取数据时执行大部分工作），关闭会话（必须关闭游标以使带有时间统计的执行计划出现在跟踪文件中）。 然后你应该转到数据库服务器上的转储文件夹，获取跟踪文件，使用tkprof工具将其转换为更易读的方式，找到其中的这些查询，享受吧 :). SQL跟踪的好处是它提供了在SQL执行过程中使用的实际执行计划，并且对于计划的每个步骤，它提供非常精确的时间、读取、获取、一致性读取和CPU统计信息（不幸的是我不知道任何其他可以做到这一点的工具）。 缺点是您需要有权限来启动跟踪，并且需要访问转储文件夹（或者您需要请求DBA执行带有跟踪的查询并请求服务器管理员获取文件）。 更粗糙的选项是启用此SQL的监视（例如通过/*+ monitor */提示），并使用DBMS_SQLTUNE.REPORT_SQL_MONITOR http://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视图以找到适当的执行计划。

- Anton Zaviriukhin