Re: index skip scan
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 06 Jan 2009 22:18:59 +0100
Message-ID: <4963cac3$0$187$e4fe514c_at_news.xs4all.nl>
Jonathan Lewis schreef:
> "jernigam_at_nospam.kochind.com" <jernigam_at_kochind.com> wrote in message
> news:557f004c-1e91-40ea-99df-06057ea90093_at_i24g2000prf.googlegroups.com...
>
>
> There are various "anomalies" in the costing of skip scans.
>
> In principal, you should get a component of cost which
> indicates the number of times that Oracle thinks it will
> have to probe on the first column (and this is likely to be
> vaguely similar to the number of distinct values of the first
> column), plus the cost of the table visit (which shouldn't
> be very different from the cost of the table vist from the
> range scan.
>
> Your results suggest that Oracle thinks you have only
> a very few distinct values for the addr_id, and an index
> on effective_date that is less well-packed than the index
> on the (addr_id, effective_date) pair.
>
> The figures you posted for Charles Hooper, however,
> do not seem to indicate this.
>
> Could you post the entire "Single Table Access" section
> of the 10053 trace - I am curious to see how the optimizer
> managed to get the results it did.
>
> If you are very confident that you don't have any code where
> a skip scan will be a really good idea, then you could (after the
> usual consultation with Oracle support) set the hidden parameter
> _optimizer_skip_scan_enabled to false.
>
>
Date: Tue, 06 Jan 2009 22:18:59 +0100
Message-ID: <4963cac3$0$187$e4fe514c_at_news.xs4all.nl>
Jonathan Lewis schreef:
> "jernigam_at_nospam.kochind.com" <jernigam_at_kochind.com> wrote in message
> news:557f004c-1e91-40ea-99df-06057ea90093_at_i24g2000prf.googlegroups.com...
>> First of all I am using 10.2.0.3 running on solaris 64-bit. >> >> I have the following query. >> >> select * from inventory_trans_qty itq >> where itq.effective_date > '01-Dec-2008' >> and itq.effective_date <= '05-Dec-2008' >> >> I have the following two indexes on that table. >> >> inv_trans_qty_ind2 (effective_date) >> inv_trans_qty_ind3 (addr_id, effective_date) >> >> Instead of doing a range scan on inv_trans_qty_ind2, the query does a >> skip scan on inv_trans_qty_ind3. >> >> If I hint the query to use the ind2 index, the logical reads drops and >> it runs faster. Unfortunately I can't hint the real queries, because >> they are in a packaged application. >> >> The skip scan shows a lower cost. How is the cost of a skip scan >> calculated? I would think it would have a higher cost given the two >> fields. >> >> >> >>
>
>
> There are various "anomalies" in the costing of skip scans.
>
> In principal, you should get a component of cost which
> indicates the number of times that Oracle thinks it will
> have to probe on the first column (and this is likely to be
> vaguely similar to the number of distinct values of the first
> column), plus the cost of the table visit (which shouldn't
> be very different from the cost of the table vist from the
> range scan.
>
> Your results suggest that Oracle thinks you have only
> a very few distinct values for the addr_id, and an index
> on effective_date that is less well-packed than the index
> on the (addr_id, effective_date) pair.
>
> The figures you posted for Charles Hooper, however,
> do not seem to indicate this.
>
> Could you post the entire "Single Table Access" section
> of the 10053 trace - I am curious to see how the optimizer
> managed to get the results it did.
>
> If you are very confident that you don't have any code where
> a skip scan will be a really good idea, then you could (after the
> usual consultation with Oracle support) set the hidden parameter
> _optimizer_skip_scan_enabled to false.
>
>
Jonathan,
what's the meaning of resc_io here? I googled but can not find any explanation for it. It might be a cause for Oracle to use the skip scan. It seems to go for plans with low resc_io... while googling I saw more examples of this behavior.
Shakespeare Received on Tue Jan 06 2009 - 15:18:59 CST