Home » RDBMS Server » Performance Tuning » delete statement is causing lot of performance hurt in DB (oracle 10.2.0.2 solaris 10)
delete statement is causing lot of performance hurt in DB [message #325612] Fri, 06 June 2008 13:25 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
experts,

i am facing big problem with this delete statement.it is consuming log of cpu resoruce in my DB and wiat ieveents
this statement is working fine in stage database and it is not doing any thing in prod database. i looked into execution plan. it is going index scan. i am not able to find out exact problem. can any one help me.

how do trace it, and any suggestion to fix this problem.
please find some of the statistics of this query.
Elapsed Time (sec) 27817.50
CPU Time (sec) 24834.53
Wait Time (sec) 2982.97

please find the query along with execution plan.

DELETE FROM DEVX_REL_EXT 
WHERE EXT2EVENT 
IN (SELECT OBJID FROM DEVX_EVENT A 
    WHERE A.BDS_DATE_CREATED = :B2 AND A.STAGE_TYPE =   'NON-INVOICE' 
     AND A.PC_NAME = :B1 
    AND EXISTS (SELECT 1 FROM TXN_DISPOSITION B 
                WHERE B.STATUS = 'ERROR' AND B.PRIMARY_CARRIER_NAME = :B1 
                AND B.BDS_DATE_CREATED BETWEEN :B2 AND :B3 
                AND B.PRIMARY_CARRIER_NAME = A.PC_NAME 
                AND to_char(B.EVENTID) = A.EVENT_ID
               )
    )

execution plan is:

-----------------------------------
call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.00          0          0          0           0

Execute      2     26.90      26.95        139    3249541          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3     26.91      26.96        139    3249541          0           0

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: CHOOSE

Parsing user id: 185  (BILLING)   (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  DELETE  DEVX_REL_EXT (cr=3249541 pr=139 pw=0 time=26928541 us)

      0   FILTER  (cr=3249541 pr=139 pw=0 time=26928408 us)

      0    NESTED LOOPS SEMI (cr=3249541 pr=139 pw=0 time=26928392 us)

      0     NESTED LOOPS  (cr=3249541 pr=139 pw=0 time=26928285 us)

 988805      PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=283123 pr=135 pw=0 time=15821326 us)

 988805       TABLE ACCESS BY LOCAL INDEX ROWID DEVX_EVENT PARTITION: KEY KEY (cr=283123 pr=135 pw=0 time=14832476 us)

1184079        INDEX RANGE SCAN DEVX_EVENT_N1 PARTITION: KEY KEY (cr=8673 pr=131 pw=0 time=1184438 us)(object id 78646)

      0      INDEX RANGE SCAN DEVX_EXT_NU1 (cr=2966418 pr=4 pw=0 time=15335962 us)(object id 78632)

      0     TABLE ACCESS BY GLOBAL INDEX ROWID TXN_DISPOSITION PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us)

      0      INDEX RANGE SCAN TXN_DISPOSITION_N6 (cr=0 pr=0 pw=0 time=0 us)(object id 77312)

 

 

Rows     Execution Plan

-------  ---------------------------------------------------

      0  DELETE STATEMENT   MODE: CHOOSE

      0   DELETE OF 'DEVX_REL_EXT'

      0    PX COORDINATOR

      0     PX SEND (QC (RANDOM)) OF ':TQ10002' [:Q1002]

      0      FILTER [:Q1002]

 988805       NESTED LOOPS [:Q1002]

 988805        HASH JOIN (RIGHT SEMI) [:Q1002]

1184079         PX RECEIVE [:Q1002]

      0          PX SEND (HASH) OF ':TQ10001' [:Q1001]

      0           PX PARTITION RANGE (ITERATOR) [:Q1001]

                      PARTITION:KEYKEY

      0            TABLE ACCESS   MODE: ANALYZED (BY LOCAL INDEX

                     ROWID) OF 'TXN_DISPOSITION' (TABLE) [:Q1001]

                       PARTITION:KEYKEY

      0             INDEX   MODE: ANALYZED (RANGE SCAN) OF

                      'TXN_DISPOSITION_N2' (INDEX) [:Q1001]

                        PARTITION:KEYKEY

      0         BUFFER (SORT) [:Q1002]

      0          PX RECEIVE [:Q1002]

      0           PX SEND (HASH) OF ':TQ10000'

      0            PARTITION RANGE (SINGLE) PARTITION:KEYKEY

      0             TABLE ACCESS   MODE: ANALYZED (BY LOCAL

                      INDEX ROWID) OF 'DEVX_EVENT' (TABLE)

                        PARTITION:KEYKEY

      0              INDEX   MODE: ANALYZED (RANGE SCAN) OF

                         'DEVX_EVENT_N3' (INDEX) PARTITION:KEYKEY

      0        INDEX   MODE: ANALYZED (RANGE SCAN) OF 'DEVX_EXT_NU1'

                   (INDEX) [:Q1002]







Re: delete statement is causing lot of performance hurt in DB [message #325613 is a reply to message #325612] Fri, 06 June 2008 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>please find some of the statistics of this query.
>Elapsed Time (sec) 27817.50
>CPU Time (sec) 24834.53
>Wait Time (sec) 2982.97

I can not reconcile the values above with the fact that the execution shows it ran in just under 27 seconds.

Which values are really real?
Re: delete statement is causing lot of performance hurt in DB [message #325615 is a reply to message #325613] Fri, 06 June 2008 14:02 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
hai,

i am watching through OEM. here i found this information for this sql statement.

now i what to dig more deeper level in this query. using sql id
can you help to find more information.



elapsed time :27,414  
cpu time     :27,215
execution    : 0
elap per exec: 
total db time: 67.52
sql id       : 8hjqyb6tumuxu

---
this is the execution plan
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                           |                 |     1 |   114 |   107K  (1)| 00:21:32 |       |       |        |      |            |
|   1 |  DELETE                                    | DEVX_REL_EXT    |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                           |                 |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                     | :TQ10002        |     1 |   114 |   107K  (1)| 00:21:32 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   4 |     NESTED LOOPS                           |                 |     1 |   114 |   107K  (1)| 00:21:32 |       |       |  Q1,02 | PCWP |            |
|*  5 |      HASH JOIN RIGHT SEMI                  |                 |     1 |    82 |   107K  (1)| 00:21:32 |       |       |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE                           |                 | 53513 |  1620K| 25414   (2)| 00:05:05 |       |       |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH                        | :TQ10001        | 53513 |  1620K| 25414   (2)| 00:05:05 |       |       |  Q1,01 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR                  |                 | 53513 |  1620K| 25414   (2)| 00:05:05 |    29 |    29 |  Q1,01 | PCWC |            |
|*  9 |          TABLE ACCESS FULL                 | TXN_DISPOSITION | 53513 |  1620K| 25414   (2)| 00:05:05 |    29 |    29 |  Q1,01 | PCWP |            |
|  10 |       BUFFER SORT                          |                 |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|  11 |        PX RECEIVE                          |                 |  1174K|    57M| 82195   (1)| 00:16:27 |       |       |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH                       | :TQ10000        |  1174K|    57M| 82195   (1)| 00:16:27 |       |       |        | S->P | HASH       |
|  13 |          PARTITION RANGE SINGLE            |                 |  1174K|    57M| 82195   (1)| 00:16:27 |     9 |     9 |        |      |            |
|  14 |           TABLE ACCESS BY LOCAL INDEX ROWID| DEVX_EVENT      |  1174K|    57M| 82195   (1)| 00:16:27 |     9 |     9 |        |      |            |
|* 15 |            INDEX RANGE SCAN                | DEVX_EVENT_N3   |  1191K|       | 10504   (1)| 00:02:07 |     9 |     9 |        |      |            |
|* 16 |      INDEX RANGE SCAN                      | DEVX_EXT_NU1    |     1 |    32 |     3   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------


[Updated on: Fri, 06 June 2008 14:47]

Report message to a moderator

Re: delete statement is causing lot of performance hurt in DB [message #327079 is a reply to message #325612] Fri, 13 June 2008 12:28 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Maybe some additional analysis will yeild helpful information. For example:

1) how many rows are you expecting to delete?

2) have you tried seeing if the issue is the query or the actual delete of rows?

Try finding out how long the different pieces of the sql are taking:


SELECT 1
 FROM txn_Disposition b
 WHERE b.Status = 'ERROR'
 AND b.Primary_Carrier_Name = :B1
 AND b.bds_Date_Created BETWEEN :B2
 AND :B3
 AND b.Primary_Carrier_Name = a.pc_Name
 AND To_char(b.EventId) = a.Event_Id)


vs.

(SELECT objId
 FROM   devx_Event a
 WHERE  a.bds_Date_Created = :B2
        AND a.Stage_Type = 'NON-INVOICE'
        AND a.pc_Name = :B1
        AND EXISTS (SELECT 1
                    FROM   txn_Disposition b
                    WHERE  b.Status = 'ERROR'
                           AND b.Primary_Carrier_Name = :B1
                           AND b.bds_Date_Created BETWEEN :B2
                                                          AND :B3
                           AND b.Primary_Carrier_Name = a.pc_Name
                           AND To_char(b.EventId) = a.Event_Id))


vs.

create table temp1
as
(SELECT objId
 FROM   devx_Event a
 WHERE  a.bds_Date_Created = :B2
        AND a.Stage_Type = 'NON-INVOICE'
        AND a.pc_Name = :B1
        AND EXISTS (SELECT 1
                    FROM   txn_Disposition b
                    WHERE  b.Status = 'ERROR'
                           AND b.Primary_Carrier_Name = :B1
                           AND b.bds_Date_Created BETWEEN :B2
                                                          AND :B3
                           AND b.Primary_Carrier_Name = a.pc_Name
                           AND To_char(b.EventId) = a.Event_Id))
/

DELETE FROM devx_rel_ext
WHERE       ext2Event IN (SELECT objId
                          FROM   temp1)


Assuming there is only one basic issue at hand, this process should show you if the issue is in querying your data, or in actually deleting your data. Once you know this, you can focus your thought in an appropriate direction.

Good luck, Kevin
Previous Topic: sessions were hung for long time
Next Topic: SQL Needs to be tuned
Goto Forum:
  


Current Time: Fri Dec 09 21:25:18 CST 2016

Total time taken to generate the page: 0.11293 seconds