Client deletion Problem - Urjent..

[Former Member]

Hi Experts,

There is an existing DEV-QA-PRD environment. There is a need to have a complete Configuration copy of PRD in DEV2 system, which will be used to build another Production Stream (DEV2, QA2, PRD2) for another project.

When we are deleting client ,below Problems we have facing :

1) Production equivalent client (4 TB of data) is taking too much time while deleting tables (step 4 in the diagram below).

2) Of the total 34,882 tables, 34,855 tables took almost 7-8 days to delete.

3) Last 30-35 tables are taking a lot of time to delete, these are probably the largest tables in the system

4) Tables EDIDS and CDCLS are currently being deleted for the last 3-4 days

5) Adding more processes to the Dev2 system is not helping either.

6) At the current rate, these tables will take another 1.5-2 weeks, which seems to be a long time

7) Some more tables which took more time were SWWLOGHIST, IDOCREL, SRRELROLES

Other information:

1) Operating System used for the ECC 5.0 is: Tru64 / HP, Oracle 9i DB (List of Service packs and Patches are attached)

2) Production system has data worth approx. 4TB

3) No Data Archiving has been done since the inception of the system

I need your Suggestions and Comments on this ---its urjent

Points will rewarded for suitable answers

Regards

Prabhu

[former_member204746 · Accepted solution]

tables EDIDS and CDCLS are of type LONG RAW. This table type is very unefficient when doing mass deleting or reorgs. not much can be done now.

I am unsure about this, but converting it to LOB might help in the long run... but converting will also take 3 to 4 days....

[former_member376769]

Jhony,

What I understand is that you are building a second landscape by building a new DEV from the production server of your first landscape.

The only reason I would do that would be a build of production support landscape if my first landscape has different service pack between DEV and PRD.

Otherwise, I will copy the first DEV to build DEV2.

Your first DEV should have to customizing already promoted on your PRD system.

Also, copying your PRD system will bring also Master and transactional data. You do not want to have that kind of data on your DEV system.

And then what is the plan to build your QA2 and PRD2?

Is it supposed to be distributed development? In this case why don't you have a central DEV system to develop global templates feeding 2 regional development systems?

Best regards.

Frank Markarian

SAP America

[Former Member]

Hi Guys

Thank you !

[markus_doehr2]

Do I understand you correctly that you want to copy your production system, delete the clients in there to just have a "clean" system without any clients? If yes, then, well, there´s not much you can do about it beside adding more memory to the target database server and check if you don´t use too much processes. I just did something likewise on Linux/MaxDB - a 2 TB client deletion took about one week.

Markus

[Former Member]

Hi,

I want actual solution why its taking this much time for deletion process.

Is this relate to hardware sizing , any index we have to creat for this,

Please replay..

Regards

Prabhu

[markus_doehr2]

I think the problem you´re seeing is cache expulsion. The big tables (EDIDC, CDCLS) contain many millions of entries. The deletion process needs to read them all (table scan) to distinguish, whether they containt data for to to-be-deleted client or not. EDIDC/EDI40 has > 120.000.000 records in our system, I think you system it´s even more. Since you run more than one deletion process, the data already read gets extinguished out of the database cache so it needs to be read again etc.

There´s nothing more you can do about it but wait and/or increase the SGA size so more data can be cached.

Markus

[Former Member]

Hi

Any other comments Plz...

Regards

Prabhu

[Former Member]

Hi Prabhakar

Any other comments Plz...

Ok, you wanted it )) here is mine

so far:

- you copied your huge database (where you don't delete / archive old data) to the new server

- you are now deleting row by row all that data

- you will end up having a still huge but empty database, because the tables / tablespaces won't shrink after the client deletion

This does not make any sense to me, am i missing something :(((

Are there other clients in the prod system, which you keep?

@ Markus: i don't think they have 100+ gb memory to cache their huge tables in the sga

I guess the client deletion has something like a MAX ROWS COMMIT limit to prevent undo overflow. If this number is for example 100'000 rows, then you have to execute 100 delete statements (-> 100 full scans) on a 10mio row table. This of course is taking ages. Even worse it gets slower and slower, because the first delete up to 100'000 rows gets the first blocks, the second scans the same blocks, but they are empty now, so it has to scan further, and so on...

Best regards

Michael

Update: i recall there was a sap note somewhere, i just checked, here it is:

365304 - CC-ADMIN: Reports for deleting tables

Edited by: mho on Jan 8, 2008 2:40 PM

[markus_doehr2]

> @ Markus: i don't think they have 100+ gb memory to cache their huge tables in the sga

yes - I'm sure so

He asked how to speed up - so I gave an idea.

Doing those kind of system rebuilds makes sense - if you want to have a client-less system but with your actual dictionary. If a system is pretty old (ours is > 12 years) then you can't just install a new system from scratch and do all the DDIC adaptions that have been made in that decade. We do those kind of copies from time to time since we use TDMS (Test Data Migration Server) to set up test/training systems with just some months of data.

I think he wants to have just a system with a current DDIC doing a client copy from 000 to start new customization. And yes, you're right - reorganization is imminent after the deletion.

Markus

[Former Member]

Hi Markus,

Thanks for your valuable suggestions...!

Thanks for your valuable suggestions...!

We are doing the same thing that you have suggested.

Like this, We cancelled the deletion process at R/3 level it to increase the SGA, and we are trying to change the optimizer settings in Oracle to speed up the delete.

We also restarted deletion process using R3trans command.

Is there any thing we need to do...?

Is there any problems we will get while doing this ?

Thank you

Regards

Jhony

[markus_doehr2]

You shouldn´t face any issues beside the fact, that the machine will do a lot of I/O and will be pretty busy Also switch to NOARCHIVELOG mode and in case you don´t use automatic undo management take care, that your rollback segments are big enough.

Markus