RE: Parallel DELETE on Partitioned Table with Domain Index

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 13 Feb 2009 11:43:17 -0500
Message-ID: <EAB8DD26381E46FCAB1EBF7E471D4C82_at_rsiz.com>



Two things to measure are:  
  1. Simply without the parallel
  2. Keep instead of delete
    1. Create a "keepers" table
    2. Union keepers to your table (not union all in this case) for the view used by applications
    3. Copy the rows you do not want to delete from the partition to
      "keepers"
    4. Swap partitions
    5. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Andrew Kerber
Sent: Friday, February 13, 2009 10:52 AM To: hkchital_at_singnet.com.sg
Cc: oracle-l_at_freelists.org
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_at_singnet.com.sg> 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

--
http://www.freelists.org/webpage/oracle-l






-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'




--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 13 2009 - 10:43:17 CST

Original text of this message