代码之家 › 专栏 › 技术社区 › Sathyajith Bhat ron tornambe

添加索引后查询性能降低

oracle10g performance sql

Sathyajith Bhat ron tornambe · 技术社区 · 15 年前

我有一个查询,一个SP的哪一部分执行得相当正常,查询需要一段时间才能执行,所以我决定看看它。我在查询中进行了自动跟踪,并且 this was the execution 平面图返回[因尺寸过大而粘贴在Pastebin中]

我在正在进行完全表访问的表上添加了索引,并运行了查询。尽管 cost being significantly lower .

为什么会这样呢,有人能在这上面开灯吗?

数据库是Oracle10gr2(10.2.0.1.0版)。

这是正在运行的查询

SELECT DISTINCT CAC_FLEX_03, CAC_FLEX_04
        FROM PCOM_CUST_PRACTICE_INFO A,
             PGIM_ZIP_CODES          C,
             PGIM_PROD_TARIFF_DATA   B,
             PCOM_CODES_APPL_CODES   D
       WHERE A.PCPI_CUST_CODE IN ('002023', '002025')
         AND C.ZC_ZIP_CODE = A.PCPI_PIN_CODE
         AND C.ZC_CITY_CODE = A.PCPI_CITY
         AND C.ZC_COUNTY_CODE = A.PCPI_COUNTY
         AND C.ZC_STATE_CODE = A.PCPI_STATE
         AND B.PTD_CVR_CODE = 'TF-001'
         AND B.PTD_VALUE_SET2 = A.PCPI_STATE
         AND B.PTD_VALUE_SET4 = A.PCPI_COUNTY
         AND B.PTD_VALUE_SET5 = D.CAC_FLEX_03
         AND D.CAC_FLEX_04 IS NOT NULL
         AND ZC_STATE_CODE =
             (SELECT POL_FLEX_04
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND B.PTD_VALUE_SET3 =
             (SELECT POL_FLEX_01
                FROM PGIT_POLICY
               WHERE POL_SYS_ID = 541332)
         AND CAC_TYPE = 'TERR-CODE'
         AND CAC_FLEX_03 = 0;

7 回复 | 直到 15 年前

Grant Johnson 15 年前

几件事:

首先,如果要访问超过一半的数据块,则完全扫描将更快,因为读取索引块是另一个IO调用,因此读取索引行的时间开销通常是读取连续行的两倍。

第二,你需要看看你的计划有没有索引。这里有一些信息可以让你知道发生了什么变化。如果你看到一个“合并连接笛卡尔”,计划者就犯了一个错误。那个计划从来都不好。完整扫描的内部循环具有相同的IO成本,但占用的内存和临时空间更少。

第三,使用分析表构建统计信息。别这样,连甲骨文都说它坏了而且坏了。使用dbms_stats包构建您的统计信息,您将获得更准确的统计信息。如果仍然很奇怪,请更改样本大小,或者执行完整的统计而不是估计。

phlip 15 年前

我看到在索引表很小的地方查询速度会变慢。查询计划从构建临时哈希表改为使用(基于树的)索引,该索引速度较慢(但扩展性更好)。基于成本的乐观主义者并不总是用可用的统计数据来正确的,事实上,如果你考虑它的话,就不可能。对于一个足够复杂的查询计划,不进行查询就无法完美地预测性能。

Steve De Caux 15 年前

只是出于兴趣,查询中是否真的需要表PGIM-zip-codes?在我看来,它像是将“和zc_state_code=”[SQL的第16行]改为“和a.pcpi_state=”允许您从查询体中删除pgim_zip_codes。

第二,看起来在pgim_prod_tarifty_数据上创建的索引未能正确完成工作-在我有限的经验中,只有78k行的表通常比表扫描更快,除非添加的索引是唯一的,或者将该表的计划减少为仅索引查找(第二个计划看起来像是在表上创建了两个索引,它们不是唯一的)。

第三,现在我看起来更努力了。您的查询似乎可以解析为:

选择不同的cac_flex_03,cac_flex_04

来自PCOM代码应用代码

其中cac_flex_03=0

和cac_type='terr-code'

存在的地方(废话)

始终假设存在位置是必需的-因为表A、B和C中的一个结果行的存在确实会返回表D中c a c_flex_03=0和type='terr-code'的所有行

我没有误解这个问题!

Sonny Boy 15 年前

我们也遇到了类似的问题,结果是索引的碎片化。让您的DBA检查您正在使用的索引的所有统计信息,看看是否需要重新构建任何索引。

记住,在线重建会让您继续一段时间,但离线重建可能需要在某个时刻完成。

Charles Bretana 15 年前

“无查询” 应该 “任何时候都会受到新指数的不利影响。只有写操作(需要修改索引)会因索引的存在而减慢速度,即使是具有碎片的索引也应该比没有索引快。如果你刚刚添加了索引,它是全新的(或多或少),不应该被分割到任何明显的程度。再加上你说的成本更低,我怀疑你这里还有一些与指数无关的事情。是否有可能其他一些事务临时对查询所需的某些数据行设置了读锁,并在相当长的一段时间内将其阻止?

Thorsten 15 年前

与CharlesBrentana所说的类似,添加索引不应降低查询的性能。

如果所有的统计数据都是最新的,这样基于成本的优化器就可以选择一个好的执行计划,那么这是正确的。请再次检查表和索引上的统计数据。

如果这里的一切都是干净的,那么我只能假设还有其他一些因素影响查询的性能。数据库服务器上是否有其他负载(长时间运行的批处理作业、备份等),以便查询运行更长时间?在查询中使用的某个表上是否有主要的更新活动?数据是否第一次出现在数据库缓存中,而不是第二次?我不知道你怎么能同时测试这两个语句,但肯定有一些原因导致这种奇怪的行为…

Consultuning 15 年前

您需要知道,基于成本的优化器虽然非常复杂,而且大部分时间都是正确的,但有时可能是错误的。

有大量针对Oracle的文献(例如TomKyte的博客)可以清楚地证明添加索引实际上可以降低select语句的性能。除其他原因外,如果数据密度足够高,通过索引访问数据集比通过完全扫描访问数据集要昂贵。

有时,您可以通过为所涉及的列生成柱状图,让基于成本的优化器了解数据分布,但即使这样有时也会失败。我将首先尝试为索引列生成柱状图,如果失败,那么我们将不得不再次查看您的语句和访问计划。