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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Sunday, May 06, 2018 4:25 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
--
//www.freelists.org/webpage/oracle-l
