And I should always check my blog before I answer any question.
I've just found this interesting item that ends with a comment on the
remarkable inconsistency between what Oracle does (did in 2013) calculate and
what it could calculate.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
Sent: 06 May 2018 09:15
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Cost of Index Skip Scan
Wolfgang,
How can I not respond to an email that starts like that ?
I agree with your comment about NDV of the leading column being used in the
calculation - except it's such a long time and so many versions (any skip scan
anomalies) since I wrote the book that I hesitate to make any comment about how
the aritmetic works now.
Historically, for a skip scan to the second column of a 2 column index the
arithmetic was largely:
ndv(column1) * cost of (column1 = unknown and column2 = bind variable)
with (a) a limiting value that was the cost of an index full scan, and (b) an
adjustment that effectively allowed that the root block would be pinned (so
perhaps "cost - 1" in the second expression). But even when I wrote the book
there seemed to be some anomalies in how this was calculated; and I've seen
several variants (in SRs) over the years for skipping multiple columns (e.g.
using ndv(first column skipped) only, using ndv(last column skipped) only,
using product of ndvs of all skipped columns) - and I haven't started to look
at options for having histograms on columns or column groups to see if they
have any effect.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
Sent: 06 May 2018 03:11:31
To: Amir.Hameed@xxxxxxxxx
Cc: Oracle-L Freelists
Subject: Re: Cost of Index Skip Scan
I should probably just wait and defer to Jonathan but I’ll take a stab. The
cost - and performance (!) - of an index skip scan depends on the cardinality
if the leading column ( columns? - not sure if there safe skip scans using
indexes with more than one “missing” leading column, I certainly have never
seen one ). In any case, I was/am always suspicious when I see a skip scan in a
plan. So far it’s never been good.
Sent from my iPhone. Typing errors may have occurred.
On May 5, 2018, at 19:52, Hameed, Amir
<Amir.Hameed@xxxxxxxxx<mailto:Amir.Hameed@xxxxxxxxx>> wrote:
I was looking at a very simple statement as shown below to see if it can be
further optimized. This is a standard Oracle E-Business statement. The database
version is 11.2.0.4:
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE
SEGMENT1 = :B1
;
Column SEGMENT1 is the second column of a two-column index,
MTL_SYSTEM_ITEMS_INTERFACE_N4. The original plan shows INDEX SKIP SCAN of the
index:
The original plan is shown below:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0 DELETE MTL_SYSTEM_ITEMS_INTERFACE (cr=676
pr=457 pw=0 time=761695 us)
806 806 806 INDEX SKIP SCAN
MTL_SYSTEM_ITEMS_INTERFACE_N4 (cr=640 pr=0 pw=0 time=6248 us cost=277 size=6930
card=33)(object id 1989058)
I decided to create a separate index just on the SEGMENT1 column,
MTL_SYSTEM_ITEMS_INTERFACE_T, to see if that would improve the response time.
The explain plan is shown below
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ----------
---------------------------------------------------
0 0 0 DELETE MTL_SYSTEM_ITEMS_INTERFACE (cr=3 pr=0
pw=0 time=37 us)
806 806 0 INDEX RANGE SCAN
MTL_SYSTEM_ITEMS_INTERFACE_T (cr=3 pr=0 pw=0 time=12 us cost=1 size=2743
card=13)(object id 7755594)
There is a marked improvement in the cost of index scan when doing regular
index scan. We normally do not create custom indexes on standard Oracle tables
but since this statement is part of a one-time conversion run that we are
trying to optimize to fit in the allotted outage window, we can create this
index temporarily and then drop it after the conversion run.
I am trying to understand why the cost associated with the SKIP SCAN would be
that much different than a regular scan.
Thanks,
Amir
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
