Performance of Archive Jobs Since Switching Partitions

[Former Member]

Hi,

Since MIMIX switching from one partition to another , the run time for archive write jobs on EC_PCA_ITM archiving object has increased from 9 minutes to several hours and for delete jobs from less than an hour to up to 8 hours. This is for GLPCA file which has 7 indexes which I believe are maintained across the mirror by the journal delete entry to the target.

The partitions have the same amount of resource memory/CPU/disk following a switch. We have stopped SAP and deleted SQL packs as part of switch. The only action I performed was to switch off journaling on what was the target system and is now the primary system on file GLPCA to try to speed up the MIMIX apply backlog but I don't see this caused the problem ; 200million records were deleted during this time. I have not performed any re-orgs on this file as yet ( it will take 2 weeks and I was leaving it until all data deleted) but this should not be the difference ?

Any advice which may correct my understanding or action to identify the problem would be appreciated.

Regards

David

[Former Member]

The root of the problem related to some housekeeping of large files which must have resided on the newer 4328 disk. When SAP was stopped and restarted all of the temporary storage & disk I/O was occuring on the 15 4328s instead of the other 435 smaller disks so IO was the cause of the bottleneck !! Sorry I didn't spot this sooner - the disks and I/O are now re-balanced.

[Former Member]

Thanks for the replies.

Ajay - we also mirror the archive files to the IFS , because the compression was so good, so the data access is the same. I don't see this difference on any of the other archiving objects CO_ITEM,FI_DOCUMNT etc.

Volker - we delete the SQL packs after a switch so when we switched back the SQL libraries were empty. Also we delete SQL packs each Sunday evening and nothing improved since each Sunday.

Regards

David

[volker_gldenpfennig]

Hi David,

ok, that looks like an optimizer issue ...

=> you need to start an SQL trace and investigate the issue. I'm sure, you will have VERY long running SQLs there, where you will detect after invevstigation, that there "would be a nearly useful index". So, the optimizer used that one earlier, but not any more. Then after investigation the question goes most likely to a new index or to open a PMR.

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.net - http://www.4soi.de - http://www.easymarketplace.de

[blaw]

Check and see if the table your are mirroring is a Temporary table.

We had a simular problem with DART extracts with a table TXW_INDEX - A Temporary table for data segment index -

This table gets created and deleted so we just omitted from being journaled to the HA system. Remember youwill still have to journal locally or you will get an sql error.

Regards,

Brian

[Former Member]

Hi Ajay,

Thanks for your response however I already built this index early on when we discovered it was slow and it is mirrored o.k so this is not the problem - it is really weird

Regards

David

[Former Member]

Hi David,

I am thinking from Archiving functionality that is causing this performance when MIMIX switching from one partition to another.

During writing of archive file by write program, it will call for functional module ARCHIVE_PUT_RECORD & ARCHIVE_SAVE_OBJECT, in this area I think system is taking long time because of switiching partition. you can debug.

Where are Delete program will first read archive file sequentially and then delete data from database... here also i feel that due to switching it might be taking more time.

Performance issue is only with EC_PCA_ITM or other archiving objects also?

-Thanks,

Ajay

Edited by: Ajay Kumar on Feb 23, 2010 2:51 PM

Edited by: Ajay Kumar on Feb 23, 2010 3:11 PM

[volker_gldenpfennig]

Hi David,

that sounds like an optimizer bug ;-(

Did you delete the SQL Packs at the point of Mimix Switch ? If not, this would be very likely to be the reason ...

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.net - http://www.4soi.de - http://www.easymarketplace.de

[Former Member]

Hi David,

The write program uses an index for table GLPCA in which the period field (POPER) does not occur.

In order to improve performance of EC_PCA_ITM, you have to create Index for table GLPCA in customer Namespace.

This index should contains all the field of Index No1 - Profit center based index in the same sequence and add the field POPER at the end.

This will resolve your problem, as i have experience same and resolved by above solution.

-Thanks,

Ajay

[Former Member]

Hi Effan,

Yes we have an HA environment. It is only after switching that the increase in run times has been observed for the archive jobs - but number of disk arms,memory and CPUs are the same when in the role of primary system.

Regards

David

[Former Member]

OK, David, seems this one is not the root cause since it doesn't change on both sides, but value *FLDBDY can significantly minimizing data journaling time than *NONE which replicates everything of data row changed.

Back to your concern, what do you mean by switching MIMIX to another LPAR? Are you running HA between 2 LPAR, and the problem occurs after a failover? Please elaborate your environment to help me understand further.

Thanks,

Effan

[Former Member]

Hi

This is set to *NONE on both systems.

Regards

David

[Former Member]

Hi my friend

What value is set for journal option --> Minimize entry data?

Regards,

Effan