| delete statement is causing lot of performance hurt in DB [message #325612] |
Fri, 06 June 2008 13:25  |
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 #325615 is a reply to message #325613] |
Fri, 06 June 2008 14:02  |
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 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
