Degraded performance of a query after adding Index

Question

Hi, I have a query which part of a SP which is executed fairly regularly, and the query took a while to execute, so I decided to have a look at it. I did an autotrace on the query, and this was the execution plan returned [ pasted in pastebin due to excessive size ]

I added indexes on the tables which was undergoing full table access, and ran the query. The query performance was worse then before, despite the cost being significantly lower.

Why is this so, can anyone shed a light on the same ?

The database is an Oracle 10gR2 ( Release 10.2.0.1.0 ).

This is the query being run

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;

Answer 1

We had a similar problem and it turned out to be fragmentation of the index. Have your DBA check all the stats on the indices you're using and see if any need to be rebuilt.

Remember, an online rebuild will keep you going for a while but an offline rebuild will probably need to be done at some point.

Answer 2

No query "should" ever be adversely affected by adding a new index. Only write operations(that need to modify the index) should ever be slowed down by the presence of an index) Even an index that has fragmentation should be faster than no index at all. And if you just added the index, it's brand neww (more or less) and should not be fragmented to any appreciable extent. Adding to this that you say that the cost numbers are lower, I suspect that you have something else going on here unrelated to the index. Is it possible that some other transactions might have temporarily placed read locks on some of the data rows needed by the query and blocked it for some substantial period ?

Answer 3

Similar to what Charles Brentana stated, adding an index shouldn't degrade performance of a query.

This would be true if all the statistics are up-to-date so that the cost-based optimizer chooses a good execution plan. Again, please double-check the statistics, both on the tables and the indexes.

If everything is kosher there, I can only assume that there is some other factor affecting the performance of the query. Is there any other load (long running batch job, backup, etc.) on the DB server so the query runs longer? Is there major update activity on one of the tables you are using in the query? Has the data been in the db cache the first time but not the second time? I have no idea how you can test the two statements side-by-side, but there's got to be some reason for this strange behaviour ...

Answer 4

I've seen queries get slower in this way where the indexed table was small. The query plan changed from constructing a temporary hash table to using the (tree based) index, which was slower (but would scale better). Cost based optimisers doesn't always get it right with the stats available, indeed it cannot if you think about it. For a sufficiently complex query plan, it's not going to be possible to perfectly predict the performance without doing the query.

Answer 5

You need to be aware that the cost based optimizer, while being quite sophisticated and most of the time correct, can be wrong sometimes.

There is plenty of Oracle specific literature available (see for example Tom Kyte's blog) that clearly proves that adding an index can in fact decrease performance of SELECT statements. Among other reasons, accessing a data set by index can be more expensive than doing it by a full scan if the density of the data is high enough.

Sometimes you can make the cost based optimizer aware of the data distribution by generating histograms for the columns involved, but even this sometimes can fail. I'd try generating the histograms for the indexed columns first, if that fails then we'll have to look at your statement and access plan again.

Answer 6

A few things:

First, if you are accessing over half of the data blocks, full scan will be faster because reading the index block is another IO call, so the read of an indexed row is generally twice as expensive time wise as reading a sequential row.

Second, you need to look at your plans with and without the index. There will be information here that will let you know what changed. If you see a "Merge Join Cartesian" the planner has made an error. That plan is NEVER good. Inner loops of full scans have the same IO cost, but take less memory and temp space.

Third, you built stats with ANALYZE TABLE. Don't. Even Oracle says it is bad and broken. Use the dbms_stats package to build your stats, and you will get more accurate stats. If it is still odd, change your sample size, or do full stats instead of estimated.

Answer 7

Just for interest, is table PGIM_ZIP_CODES really required in the query ? It looks to me like changing "AND ZC_STATE_CODE =" [line 16 of the sql] to "and A.PCPI_STATE =" allows you to remove PGIM_ZIP_CODES from the body of the query.

Secondly, it looks like the index created on PGIM_PROD_TARIFF_DATA failed to do the job properly - in my limited experience, a table with only 78k rows is normally faster to tablescan unless the added index is unique or reduces the plan for that table to an index-only lookup (the second plan looks like 2 indexes were created on the table, and they were not unique).

Thirdly, now that I've looked a little harder. it looks like your query could resolve to :

select distinct CAC_FLEX_03, CAC_FLEX_04

from PCOM_CODES_APPL_CODES

where CAC_FLEX_03 = 0

and CAC_TYPE = 'TERR-CODE'

where exists (blah blah blah)

• always assuming that the where exists is required - because the existence of one result row from table A, B and C will indeed return all of the rows from table D with CAC_FLEX_03 = 0 and TYPE = 'TERR-CODE'

PS I have I haven't misunderstood the question !