Re: index skip scan
Date: Tue, 6 Jan 2009 14:12:11 -0800 (PST)
Message-ID: <dc662126-1bdb-4a31-92f0-2901dc2521fc_at_s9g2000prm.googlegroups.com>
On Jan 6, 3:03 pm, "jerni..._at_nospam.kochind.com" <jerni..._at_kochind.com> wrote:
> I noticed this also
>
> prefetching is on for INV_TRANS_QTY_IND3
On Jan 6, 3:03 pm, "jerni..._at_nospam.kochind.com"
<jerni..._at_kochind.com> 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:
http://www.freelists.org/post/oracle-l/RE-Calculating-LIOs,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
*
FROM
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