Queries vs Indexes and the 10053

From: Don Seiler <don_at_seiler.us>
Date: Fri, 1 Feb 2008 11:29:17 -0600
Message-ID: <716f7a630802010929u12d12c7bgbe2bf8875596dbd4@mail.gmail.com>

Running Oracle 10.2.0.3 64-bit on RHEL4. I have a query similar to this:

SELECT *
FROM FOO

WHERE col1='004'
    AND col2 = 'N'
    AND (col3 ='Home' or norm_type='Inc')

    AND col4>TO_DATE('20070820235959', 'YYYYMMDDHH24MISS')     AND col4<=TO_DATE('20080120235959', 'YYYYMMDDHH24MISS');

Table FOO is partitioned by col4 monthly, and then subpartitioned by hash on another varchar2 field not used in this query. I also have an index on all fields in the predicate, but the order is (col1, col3, col2, col4). col1, col2, and col3 are varchar2, col4 is a date (obviously).

This particular query is now performing a full tablescan rather than use the index.

A few weeks ago, I unset the following parameters that had been set in the database for at least the last 4 years:

optimizer_index_caching              integer     90
optimizer_index_cost_adj             integer     10

One development instance that still has these set does choose the index. Another development instance that doesn't have them set (as in production) chooses the full table scan. I was really hoping to not have to have these values set. I'm pretty sure they were set when we first upgraded to 9i many moons ago.

Here's some excerpts from the 10053 trace that will hopefully be helpful:

  Access Path: TableScan
    Cost: 2703046.14 Resp: 187711.54 Degree: 0

      Cost_io: 2656412.00  Cost_cpu: 457567691358
      Resp_io: 184473.06  Resp_cpu: 31775534122

  Access Path: index (RangeScan)
    Index: FOO_BAR_IDX
    resc_io: 1497961.00 resc_cpu: 13337242671     ix_sel: 0.0054295 ix_sel_with_filters: 0.0054295     Cost: 1499320.30 Resp: 277651.91 Degree: 6

Hopefully I'm reading my Jonathan Lewis correctly [0] and the CBO seems to think that the index rangescan will cost Resp * Degree = 277651.91 * 6 = 1665911.46. I don't quite understand how this compares with the cost of the tablescan going from 2703046 to 187711.

Statistics are up-to-date (using the default 10g GATHER_STATS_JOB with histograms). It does seem fairly clear that the two optimizer parameters play a factor. The 10053 trace on the development server makes note of it before listing the index access path:

OPTIMIZER PERCENT INDEX CACHING = 90
  Access Path: index (RangeScan)
    Index: FOO_BAR_IDX
    resc_io: 1078682.00 resc_cpu: 9605449851     ix_sel: 0.0039125 ix_sel_with_filters: 0.0039125     Cost: 107966.10 Resp: 19993.72 Degree: 6

The production instance does have some more data, since the development instance is from a 21 Jan rman duplication. Basically the last partition in the table has 40 million rows compared to 30 million in development.

To quote King Arthur, "So, uh, anything that you could do to, uh... to help... would be... very... helpful."

[0] http://jonathanlewis.wordpress.com/2007/01/11/rescresp/

-- 
Don Seiler
http://seilerwerks.wordpress.com
ultimate: http://www.mufc.us
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 01 2008 - 11:29:17 CST