$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: false, vro: false, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
0

Performance: Mass delete in the COEP table

Nov 08, 2016 at 04:16 PM

130

avatar image

Hi,

we're performing a client copy, but the delete of the old client takes a lot of time (more than 1 day). It seems to be executing the following statement:

DELETE FROM "SAPSR3"."COEP" WHERE "MANDT" = '100' AND ROWNUM <= 7919;

When running the explain in Oracle, we get this output:

So, the CPU cost is 398000.

The Z02 index it seems to use, does contain MANDT, PERNR and OBJNR.

Is there a way we can speed up this delete? (archiving is already disabled)

capture.png (10.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Harish Karra Nov 08, 2016 at 07:29 PM
0

Hi Gregory,

How many processes that you selected for the client copy? Are other processes active and deleting data from different tables?

If yes, let it go and wait for the completion.

You can delete the data from bigger tables by executing delete statement from sqlplus but you need to be more careful while giving MANDT.

Regards,

Harish Karra

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Harish,

the copy itself is running with 8 processes, but in SM50 we can see that only 1 background process is taking care of the deletion. For the rest there are no heavy loads on the system. The processing speed is about 2 million records in 14 hours.

Regards,

Gregory

0

Hi Gregory,

I think deletion is done for all other tables other than COEP that is the reason SM50 is showing only one table.

If you can wait, let the copy process go as it is. If you have any time constraint then you may go ahead and delete entries from sqlplus (Make sure you are deleting data only of 100, where MANDT='100').

Regards,

Harish Karra

0
Gregory Petit Nov 10, 2016 at 12:33 PM
0

I've created an index with only the MANDT field, restarted the delete and now in 4,5H around 32.000.000 records got deleted. The cost via this index got reduced to 77183.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Good to hear Gregory, So deletion is faster and you will see copy soon.

Regards,

Harish Karra

0
Jim Spath
Nov 15, 2016 at 07:47 PM
0

Ignoring the business risk of "archiving disabled", what is your I/O subsystem doing?

Log switch much?

Log write sync time?

Share
10 |10000 characters needed characters left characters exceeded