Query tuning example

A coworker of mine cleaned up a bunch of old data to improve performance on an older system of ours and one of the queries started running slower.  It looks like the optimizer was choosing a full table scan when an index existed that was much faster.  So, I took at look at why it was choosing the full scan and what could be done to get the query to run with the index.

This is a 9.2.0.6 64 bit HP-UX PA-Risc system.  The query without any hints runs like this:

Elapsed: 00:04:54.65

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=23208 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' 
              (Cost=23208 Card=68262 Bytes=819144)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     242929  consistent gets
     238854  physical reads
          0  redo size
        203  bytes sent via SQL*Net to client
        242  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I renamed the production table to MYTABLE to hide its name.  Here is how the query runs with the index hint:

Elapsed: 00:00:00.21

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=34865 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' 
              (Cost=34865 Card=68262 Bytes=819144)
   3    2       INDEX (RANGE SCAN) OF 'MYINDEX' (NON-UNIQUE) 
                (Cost=194 Card=68262)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1180  consistent gets
          0  physical reads
          0  redo size
        203  bytes sent via SQL*Net to client
        242  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I got this output by putting these commands before the sql:

set autotrace on
set timing on

The query looks something like this:

SELECT  sum(COLUMN2)
  FROM MYTABLE
   WHERE MYTABLE.COLUMN1 = '01242014';

The index is on COLUMN1 only.

Here is what the query looks like with the index hint:

SELECT /*+ INDEX (MYTABLE MYINDEX) */ sum(COLUMN2)
  FROM MYTABLE
   WHERE MYTABLE.COLUMN1 = '01242014';

So, the question is why does the optimizer choose a full scan which runs for almost 5 minutes instead of the index scan which runs in a fifth of a second?  I think that the answer is that the optimizer assumes there is no caching.  Notice the number of consistent gets and physical reads in each case:

FULL SCAN

     242929  consistent gets
     238854  physical reads

INDEX SCAN

       1180  consistent gets           
          0  physical reads

If you look at the autotrace output and you see consistent gets but no physical reads it means that the blocks are being read from memory and not the disk.  So, just about every block read by the full scan is read from the disk and not from memory.  In the case of the index scan all the blocks are in memory.

Other than the caching the optimizer’s cost estimates aren’t that far off.  I think the optimizer’s cost units are the equivalent of single block reads.  I think I read this in Jonathan Lewis’ book.  These are the sections of the plan that show the cost of the two ways of reading from the table:

   2    1     TABLE ACCESS (FULL) OF 'MYTABLE' 
              (Cost=23208 Card=68262 Bytes=819144)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' 
              (Cost=34865 Card=68262 Bytes=819144)
   3    2       INDEX (RANGE SCAN) OF 'MYINDEX' (NON-UNIQUE) 
                (Cost=194 Card=68262)

So the full scan has a cost of 23208 which is the equivalent of that many 10 millisecond single block reads.  That’s about 232 seconds which is about 4 minutes.  So, that’s not far off for the cost of the full scan which ran in 5 minutes.  Also, full table scans do multi block reads so when autotrace says physical reads = 238854 what it really means is that many blocks were read in through physical reads.  Our system has db_file_multiblock_read_count=16 so probably 16 blocks are read per physical read.  The optimizer estimated a cost of 23208 which is about 10% of the physical blocks and this wasn’t a bad estimate because the blocks are read together.  So it assumed that the 238854 blocks would be read in the time it would take for 23208 single block reads and this was pretty close.

But, the index scan estimated 34865 sequential reads which is about 6 minutes.  Now, if there was no caching this wouldn’t be such a bad estimate.  Autotrace says there are 1180 blocks read from memory.  If these were read from disk each block would be a separate disk I/O so it would be about 12 seconds.  I checked and I did find that some values of the column had more rows than others and for the value 01242014 that I was testing with was one of the values with fewer rows.  So, for different values of COLUMN1 without caching the index would be a lot closer to the full scan:

SELECT COLUMN1,count(*) cnt
FROM MYTABLE
group by COLUMN1
order by cnt desc;

COLUMN1         CNT
-------- ----------
11262013     873867
11252013     576299
09222013     237098
08052013     179476
12082013     177359
11102013     175178
09152013     174220
10132013     174204
11172013     174102
08252013     173758
... skipped some ...
01242014      53801
10232013      53785
10072013      53335

So the value 01242014 only has 53801 rows but 11262013 has 863867 rows so it probably has blocks closer to the estimated 34865.  Here is a query to show how many blocks each value of the column has:

SELECT COLUMN1,
count(distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)) cnt
FROM MYTABLE
group by COLUMN1
order by cnt desc;

COLUMN1        CNT
-------- ----------
11262013      16338
11252013      10847
09222013       4409
08052013       3437
12082013       3337
11102013       3305
09152013       3290
10132013       3286
11172013       3285
08252013       3281
10272013       3255
... skipped some ...
01242014        926
01232014        924
09262013        922
01132014        915

So, for the values of the column with the most rows there are 16338 blocks, somewhere in the vicinity of the estimated 34865 single block reads.  Anyway, if the optimizer is off by less than a factor of 100 I’d say it is pretty close.  But, the run time is off by about 600 – 5 minutes versus .2 seconds so that is a real problem.

Oracle’s method of dealing with caching of index blocks and indexed table blocks is one of the optimizer parameters.  Here are the settings as we have them on our system:

NAME                                 VALUE
------------------------------------ -----
optimizer_index_caching              0
optimizer_index_cost_adj             100

I messed with different values of optimizer_index_cost_adj and found that 66 was the highest value that would cause the plan to switch to an index scan.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=23011 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLE' 
              (Cost=23011 Card=68262 Bytes=819144)
   3    2       INDEX (RANGE SCAN) OF 'MYINDEX' (NON-UNIQUE) 
                (Cost=194 Card=68262)

Note that the cost of the index access to the table has come down to 23011 which is less than the 23208 estimated for the full scan.  Also note that 23011/34865=.66=66/100.  So, the cost of the index access to the table is just multiplied by the value of optimizer_index_cost_adj divided by 100.

So, now we have come down to where the rubber meets the road.  What should we then do with all of this information?  The development team has told us that they can add a hint to their code, but they are concerned that there is some general problem with the database and they will have to keep adding hints in various other places.  Note that this is a very old system with vendor supplied code.  Presumably before the data was cleaned up the cost of the full scan exceeded that of the index scan and that it is only because our new table has so much less data that the index scan has become less costly.

So, we could set optimizer_index_cost_adj to 66 or some other value but then we would need to test the entire system.  How much effort are we going to put into a system that we need to retire anyway?  This is really where I come down with hints and other performance methods.  I’ve known about using optimizer_index_cost_adj to encourage index scans for a long time.  But, if I’m supporting a bunch of older systems I can’t just go in and start tweaking parameters without appropriate testing.  So, I’d love to adjust the parameter rather than using the hint to band aid this one query, but we have a lot of databases to support and some times you just have to apply the quick fix rather than taking the time to apply a more global solution.

– Bobby