OraFAQ Forum: Performance Tuning » Tuning of delete stmt

Home » RDBMS Server » Performance Tuning » Tuning of delete stmt (Oracle, 9.2.0.8, Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning of delete stmt [message #344695]

Mon, 01 September 2008 01:10

srihari.gurram
Messages: 13
Registered: May 2008

Junior Member

Hi,
I have the below delete statement which is taking more time. Can any one help me in tuning this?

    DELETE FROM T_PRC_MASTER a
    WHERE a.ROLLED_FLG = 'Y'
    AND a.END_ROLL_FLG != 'Y'
    AND a.PRC_DT < del_dt
    and (exists (select 1 from t_prc_master b
                where a.asset_id = b.asset_id
                and a.pricing_pt_id = b.pricing_pt_id
                and b.prc_dt > = del_dt
                and a.price <> b.price)
                or exists (select 1 from t_asset b
                           where b.asset_typ_cd = 16
                           and a.asset_id = b.asset_id
                           and a.price in (0.00000001,0.00000002)));

Attached is the explain plan output for the same...

Attachment: test2.csv
(Size: 1.09KB, Downloaded 1188 times)

Report message to a moderator

Re: Tuning of delete stmt [message #344712 is a reply to message #344695]

Mon, 01 September 2008 01:44

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

Report message to a moderator

Re: Tuning of delete stmt [message #345021 is a reply to message #344695]

Tue, 02 September 2008 02:45

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1. What indexes do you have (and what columns are used in theses indexes)?

2. How many rows are in T_PRC_MASTER table?

3. How many rows are deleted?

4. How many rows have PRC_DT < del_dt?

Report message to a moderator

Re: Tuning of delete stmt [message #345073 is a reply to message #345021]

Tue, 02 September 2008 04:30

srihari.gurram
Messages: 13
Registered: May 2008

Junior Member

Hi,

1.There are 4 indexes on t_prc_master table and these columns use these indexes ASSET_ID,PRC_DT,PRICING_PT_ID,ACCEPT_FLG.

2.There are 4 crore rows in t_prc_master table.

3. Around 1 crore rows gets deleted.

4.Around 6lakh records.

Report message to a moderator

Re: Tuning of delete stmt [message #345160 is a reply to message #344695]

Tue, 02 September 2008 07:43

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1. Please use regualr notation instead of Indian one (what is crore?).

2. Supply what index uses what columns and column's order inside the index.

Report message to a moderator

Re: Tuning of delete stmt [message #345298 is a reply to message #344695]

Tue, 02 September 2008 20:29

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Before you tune a DELETE statment, it pays to know where the time is being spent. I like to use this method of testing timings for DELETE in cases like yours:

Your Original Query:

    DELETE FROM T_PRC_MASTER a
    WHERE a.ROLLED_FLG = 'Y'
    AND a.END_ROLL_FLG != 'Y'
    AND a.PRC_DT < del_dt
    and (exists (select 1 from t_prc_master b
                where a.asset_id = b.asset_id
                and a.pricing_pt_id = b.pricing_pt_id
                and b.prc_dt > = del_dt
                and a.price <> b.price)
                or exists (select 1 from t_asset b
                           where b.asset_typ_cd = 16
                           and a.asset_id = b.asset_id
                           and a.price in (0.00000001,0.00000002)));

Can be split into two parts: query cost, and delete cost.

Query cost can be approximated quite closely using this:

create table temp1 nologging
as
    select rowid deleted_row_rowid
    from t_prc_master
    WHERE a.ROLLED_FLG = 'Y'
    AND a.END_ROLL_FLG != 'Y'
    AND a.PRC_DT < del_dt
    and (exists (select 1 from t_prc_master b
                where a.asset_id = b.asset_id
                and a.pricing_pt_id = b.pricing_pt_id
                and b.prc_dt > = del_dt
                and a.price <> b.price)
                or exists (select 1 from t_asset b
                           where b.asset_typ_cd = 16
                           and a.asset_id = b.asset_id
                           and a.price in (0.00000001,0.00000002)));

And delete cost can be approximated well using this:

delete
from t_prc_master
where rowid = (select deleted_row_rowid from temp1);

If most of your time is spent in figuring out what to delete, then you can try tuning the query under the delete statement.

If most of your time is spent actually removing rows from the table then you have a much more difficult tuning task ahead of you.

Try this and post results for us so we can all see where your time is being spent.

Good luck, Kevin

Report message to a moderator

Re: Tuning of delete stmt [message #346034 is a reply to message #344695]

Fri, 05 September 2008 12:27

si_buh
Messages: 1
Registered: May 2008
Location: Chicago

Junior Member

Thanks Kevin for your prompt reply...

This is Buhari here from the same team.

What is the best way to tune the Query Cost

select rowid deleted_row_rowid
from t_prc_master
WHERE a.ROLLED_FLG = 'Y'
AND a.END_ROLL_FLG != 'Y'
AND a.PRC_DT < del_dt
and (exists (select 1 from t_prc_master b
where a.asset_id = b.asset_id
and a.pricing_pt_id = b.pricing_pt_id
and b.prc_dt > = del_dt
and a.price <> b.price)
or exists (select 1 from t_asset b
where b.asset_typ_cd = 16
and a.asset_id = b.asset_id
and a.price in
(0.00000001,0.00000002)));

I am sure that the above one is taking more time..

I am attaching the explain plan for the below query here with

DELETE FROM T_PRC_MASTER a
WHERE a.ROLLED_FLG = 'Y'
AND a.END_ROLL_FLG != 'Y'
AND a.PRC_DT < '27-May-08'
and (exists (select 1 from t_prc_master b
where a.asset_id = b.asset_id
and a.pricing_pt_id = b.pricing_pt_id
and b.prc_dt > = '27-May-08'
and a.price <> b.price)
or exists (select 1 from t_asset b
where b.asset_typ_cd = 16
and a.asset_id = b.asset_id
and a.price in
(0.00000001,0.00000002)));

It's using Full Table Scan..

Report message to a moderator

Re: Tuning of delete stmt [message #346035 is a reply to message #344695]

Fri, 05 September 2008 12:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Is not below improved over what you posted?

DELETE FROM T_PRC_MASTER A
WHERE       A.ROLLED_FLG = 'Y'
            AND A.END_ROLL_FLG != 'Y'
            AND A.PRC_DT < '27-May-08'
            AND (EXISTS (SELECT 1
                         FROM   T_PRC_MASTER B
                         WHERE  A.ASSET_ID = B.ASSET_ID
                                AND A.PRICING_PT_ID = B.PRICING_PT_ID
                                AND B.PRC_DT > = '27-May-08'
                                AND A.PRICE <> B.PRICE)
                  OR EXISTS (SELECT 1
                             FROM   T_ASSET B
                             WHERE  B.ASSET_TYP_CD = 16
                                    AND A.ASSET_ID = B.ASSET_ID
                                    AND A.PRICE IN (0.00000001,
                                                    0.00000002)));

You should have indexes on all fields in WHERE clause
& use TO_DATE() when comparing against PRC_DT

[Updated on: Fri, 05 September 2008 12:34] by Moderator

Report message to a moderator

Re: Tuning of delete stmt [message #346556 is a reply to message #346035]

Mon, 08 September 2008 19:34

harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts

Senior Member

First, - Kevin, good approach in separating the costs which
I have made a mental tatoo for my own future reference.

I too would like to know as Michael asked, how many rows are expected to be deleted, and furthermore, which condition
contributes to the most hits.

For now, let me take Kevin's separation approach and go a step further and see if we can get figures on which operation
in the query is the most troublesome.

Ahha! - The full table scan? Let me play public defendant
for the table scan until proven guilty. So far we just
know a table scan exists.

And until Michel's advice is taken and we do comparisson
of proper indexing and incoporate his suggestion for using
to_date, the table scan can not be called the villian.

I offer to the jury some alternatives weight:

First, a full hash join:

(I apologize that once again I am not at an Oracle terminal
so I may have fat fingered something here. This will change tomorrow when I get my remote access lap top issued!)

Like to see first what the SELECT portion for the self-join
criteria performs as with:


SELECT  /*+ USE_HASH(A,B) */
	A.ROWID RID FROM T_PRC_MASTER A,
		         T_PRC_MASTER B     
WHERE (  A.ROLLED_FLG = 'Y' 
AND      A.END_ROLL_FLAG != 'Y'  
AND	 A.PRC_DT <  TO_DATE('05/27/08','MM/DD/YY') 
AND      B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY')
AND      A.PRICING_PT_ID = B.PRICING_PT_ID  
AND      A.PRICE        <> B.PRICE)

In first passes to decipher SQL I like breaking the criteria into individual select components. The below DELETE will
use a materialized view of such components


WITH TA AS 
(SELECT /*+ MATERIALIZE */  C.ASSET_ID FROM T_ASSET C 
            WHERE  A.PRICE IN (0.00000001,0.00000002)
            AND    C.ASSET_TYP_CD = 16 ),
M1 AS 
(SELECT  /*+ MATERIALIZE */ A.ROWID RID, 
A.PRICING_PT_ID, 
A.PRICE,
A.ASSET_ID 
 FROM T_PRC_MASTER A 
   WHERE A.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
   AND A.ROLLED_FLG = 'Y' 
   AND A.END_ROLL_FLAG != 'Y'),
M2 AS
(SELECT /*+ MATERIALIZE */  B.PRICING_PT_ID, B.PRICE  
 FROM T_PRC_MASTER B
   WHERE B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY'))
DELETE T_PRC_MASTER A 
WHERE ROWID IN (
 SELECT M1.RID FROM M1,M2 
 WHERE   M1.PRICING_PT_ID = 
         M2.PRICING_PT_ID 
 AND     M1.PRICE <> M2.PRICE
 )
OR 
A.ASSET_ID IN 
(SELECT ASSET_ID FROM TA);

Final candidate I would weight:



DELETE T_PRC_MASTER TPM 
WHERE    TPM.ROLLED_FLG = 'Y' 
AND      TPM.END_ROLL_FLAG != 'Y'  
AND	 TPM.PRC_DT <  TO_DATE('05/27/08','MM/DD/YY') 
AND      (
   TPM.ROWID IN
    (SELECT /*+ USE_HASH(M1 M2) */ M1.RID FROM 
      (SELECT /*+ PUSH_SUBQ */
               A.ROWID RID, 
               A.PRICING_PT_ID, 
               A.PRICE 
             FROM T_PRC_MASTER A 
             WHERE A.PRC_DT < TO_DATE('05/27/08','MM/DD/YY')
            ) M1,
	(SELECT /*+ PUSH_SUBQ */ 
               B.PRICING_PT_ID, 
               B.PRICE  
	       FROM T_PRC_MASTER B
               WHERE B.PRC_DT >= TO_DATE('05/27/08','MM/DD/YY')) M2
       WHERE M1.PRICING_PT_ID = M2.PRICING_PT_ID AND 
             M1.PRICE !=        M2.PRICE
     ) 
OR TPM.ASSET_ID IN      --OR THE WHERE EXIST VERSION
 (SELECT C.ASSET_ID FROM T_ASSET C
  WHERE  C.PRICE IN (0.00000001,0.00000002)
  AND    C.ASSET_TYP_CD = 16) 
  );

Just throwing out things to just compare performance until
further information as requested.

Again, sorry if I missed syntax; from here out I will
be connected to Oracle at the same time I post but
I just could not resist my duty to defend my
good friend the full table scan.!

Best Regards,
Harry

Report message to a moderator

Re: Tuning of delete stmt [message #346557 is a reply to message #346556]

Mon, 08 September 2008 19:36

harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts

Senior Member

oops - blatant typo i caught post-click!

Quote:

WITH TA AS
(SELECT /*+ MATERIALIZE */ C.ASSET_ID FROM T_ASSET C
WHERE A.PRICE IN (0.00000001,0.00000002)
AND C.ASSET_TYP_CD = 16 ),

All fields should be prefaced with C. A.PRICE was my bad!
sorry
-H

Report message to a moderator

Previous Topic:	Use of Index
Next Topic:	to find out time taken

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:38:25 CDT 2024