RE: Parallel DELETE on Partitioned Table with Domain Index
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <andrew.kerber@xxxxxxxxx>, <hkchital@xxxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2009 11:43:17 -0500
Two things to measure are:
1) Simply without the parallel
2) Keep instead of delete
a. Create a "keepers" table
b. Union keepers to your table (not union all in this case) for the
view used by applications
c. Copy the rows you do not want to delete from the partition to
"keepers"
d. Swap partitions
e. Remove keepers from the view (or revert a synonym)
Whether #2 will work out to your advantage depends on a lot of specifics of
your situation, including but not limited to:
1) Percentage and size of the rows you're keeping compared to deleting
(a delete being very roughly twice as expensive as an insert)
2) How often this application runs and whether the name changes force
"too many" reparses and recompiles and global index reconstructions for the
time when you do them.
3) Whether doing it this way introduces contention more expensive than
the extra cost of a delete over an insert (if the relative cost of total
deletes versus total inserts is even more expensive in your case).
Some implementations inherently prohibit this method. But when it is
workable I find that it is often quite cheap. Experimental cases can be set
up to give results either way, so what is important is how the measurements
come out for you. Whether you engage in the experiment should be governed by
the amount of cost that might be saved, inconvenience of the "solution", and
your perception of risk.
#1 is a low risk measurement. The relative rate of performance of
non-parallel operations will help you determine where to look for contention
that you might be able to remove.
Quite possibly running multiple copies of the application with different
range parameters on some column(s) in parallel against disjoint portions of
the index tree instead of running the delete itself parallel 8 can remove
the contention. Since you have evidence (albeit hearsay) that NOT having the
Domain Index runs "fast" perhaps trying disjoint sets in parallel on that
index would be a good test.
Good luck!
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Andrew Kerber
Sent: Friday, February 13, 2009 10:52 AM
To: hkchital@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Parallel DELETE on Partitioned Table with Domain Index
I have seen that. Take a look at blockers/waiters and v$locks to see if
contention is the problem.
On Fri, Feb 13, 2009 at 9:48 AM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
wrote:
I have an application that runs a DELETE statement on single Partition, but
with PARALLEL 8. The table also has Domain Index -- I haven't been told if
it is Partitioned or not.
The DELETE runs slowly. I'm trying to convince the DBA / Developer (I have
no access to the database) to run the DELETE without the PARALLEL.
(The PARALLEL 8 all operate within the *same* partition).
On a separate Test database without the Domain Index, the same DELETE, with
PARALLEL, I have been told, runs very fast. So, the team suspects the
Domain Index. I suspect contention on the Domain Index.
Any notes / stories that I can use ?
Hemant K Chitale
http://hemantoracledba.blogspot.com
"A 'No' uttered from the deepest conviction is better than a 'Yes' merely
uttered to please, or worse, to avoid trouble."
Mohandas Gandhi Quotes :
http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
--
//www.freelists.org/webpage/oracle-l
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
