Queries vs Indexes and the 10053

  • From: "Don Seiler" <don@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Feb 2008 11:29:17 -0600

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: