RE: Parallel DELETE on Partitioned Table with Domain Index
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, "hkchital@xxxxxxxxxxxxxx" <hkchital@xxxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2009 11:56:14 -0500
Also, for Mark's #2, consider:
create table keepers nologging as select * from original_table where (rows you
want to keep);
exchange partition w/ keepers table;
drop table;
Note that w/ the create table and nologging, you should get pretty good
performance.
Then, the exchange partition and drop table are DDLs, so, no worries there.
-Mark
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Mark W. Farnham [mwf@xxxxxxxx]
Sent: Friday, February 13, 2009 11:43 AM
To: andrew.kerber@xxxxxxxxx; hkchital@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Parallel DELETE on Partitioned Table with Domain Index
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<mailto: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.'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
