Re: index skip scan

From: Charles Hooper <>
Date: Tue, 6 Jan 2009 14:12:11 -0800 (PST)
Message-ID: <>

On Jan 6, 3:03 pm, "" <> wrote:
> I noticed this also
> prefetching is on for INV_TRANS_QTY_IND3

On Jan 6, 3:03 pm, "" <> wrote:
> I noticed this also
> prefetching is on for INV_TRANS_QTY_IND3

Jonathan is probably one of the few people participating in this forum would know for certain, but that prefetching reference may be critical.

Browsing through the hidden parameters, I found _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT, with a description of "number of noncontiguous db blocks to be prefetched". A Google search found this page, which describes that parameter as being related to index prefetching:,11

Possibly the value of that parameter is used in the cost calculation to reduce the calculated cost of the skip scan using multi-block reads? can you post the output of the following: SELECT
  SYS.AUX_STATS$; You might also be able to extract that information from the 10053 trace, it might look something like this in the 10053 trace: SYSTEM STATISTICS INFORMATION

  Using WORKLOAD Stats
  CPUSPEED: 1411 millions instructions/sec
  SREADTIM: 3 milliseconds
  MREADTIM: 7 millisecons

  MBRC: 8.000000 blocks
  MAXTHR: 15360 bytes/sec
  SLAVETHR: -1 bytes/sec

> (val1-val2)/(high value-low value)+ 2/num distinct
> Or is the IX_SEL calculated differently?

Just tossing some numbers out here, possibly writing in circles: The computed cardinality of the INVENTORY_TRANS_QTY table (after applying applying the restrictions against EFFECTIVE_DATE) divided by the number of rows in the INVENTORY_TRANS_QTY table: 11140.26 / 13991744 = 7.9620e-4
The above is the same value listed for ix_sel_with_filters of INV_TRANS_QTY_IND2. There are 4022 distinct values of EFFECTIVE_DATE. Selecting one of those dates, in the abscence of a histogram, would be expected to return 1/4022 of the rows in the table. EFFECTIVE_DATE>=2008-12-01 and EFFECTIVE_DATE<2008-12-05, given the number of distinct values and the difference between the high and low values of 7305, means that on average, slightly more than half (55%) of the dates in the date range will exist in the index. In rough numbers, that might mean that index entries exist on 3 of the 5 dates in the date range. 1/4022*3=7.4589e-4, which is somewhat close to the value shown for ix_sel_with_filters for the INV_TRANS_QTY_IND2 index. There is probably a more direct method

One of the possible work arounds that Jonathan suggested, if all else fails, is to disable the use of index skip scans. This may be done at the session level, if necessary, using a logon trigger (after checking with Oracle support of course) using the parameter specified by Jonathan. Since the SQL is using constants (literals) which may change on each submission, stored outlines as I previously suggested, likely will not work.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jan 06 2009 - 16:12:11 CST

Original text of this message