RE: Cost of Index Skip Scan
Date: Mon, 7 May 2018 13:24:05 -0400
Message-ID: <022c01d3e628$4bcddfa0$e3699ee0$_at_rsiz.com>
From a completely different perspective:
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE
SEGMENT1 = :B1
MAY, emphasis, MAY, often (depending on your processes, but often as of last
time I worked on Oracle EBIZ Manufacturing) be handled by
pausing the new system_items external loading job (just to be certain, this
probably already runs on a schedule and resubmits itself at a re-run gap you
can configure)
be either handled by copy everything out not marked "complete" or whatever the status is, or just everything where segment1 is NOT your :B1, truncate it, and copy anything back you really need.
You may observe that you don't need anything once the job is complete.
Also, if you MUST stay with the delete, getting rid of un-used, non-referential integrity indexes is likely the best policy.
This can be done for lots of the ..._interface... tables in ebiz. Often the first time you arrange to truncate it you will discover that it is the first time it has been space managed since the system was installed and there are huge tracts of empty space far from the next opportunity to re-use space for conventional inserts, so Oracle looks at how many blocks it would cost for an FTS and it is silly stupid to pick that.
IF you are able to arrange a copy-out, minimal copy back in even only in maintenance mode, I'd be curious of the resulting plan afterwards even if you stay safely with the original DELETE.
My answer has little to do with your actual question but may be the correct action for your situation. On your initial question Wolfgang and JL possibly only left out that more indexes will make this DELETE more expensive, not less expensive, which is just a little askew from your question as a comment on you adding an index.
Finally, if you convert this to delete where rowid in an ordered by blockid from select rowid where segment1 = :B1, then it will also probably run faster. I think JL may even have a blog about that as well. I'm not certain whether Oracle does that under the covers yet (and if so at what release). In principle it should be faster that way if you can't do a truncate.
mwf
mwf
mwf
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Sunday, May 06, 2018 4:25 AM
To: oracle-l_at_freelists.org
Subject: Re: Cost of Index Skip Scan
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> Sent: 06 May 2018 09:15
To: oracle-l_at_freelists.org
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Wolfgang Breitling <breitliw_at_centrexcc.com> Sent: 06 May 2018 03:11:31
To: Amir.Hameed_at_xerox.com
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_at_xerox.com<mailto:Amir.Hameed_at_xerox.com>> 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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 07 2018 - 19:24:05 CEST