tag:blogger.com,1999:blog-25740336.post7987170379477136396..comments2023-08-24T22:26:48.675+01:00Comments on The PeopleSoft DBA Blog: Factors Affecting Performance of Concurrent Truncate of Working Storage TablesDavid Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-25740336.post-22430601780005523012012-05-16T16:08:16.717+01:002012-05-16T16:08:16.717+01:00Hi David,
To optimize the performance of our batc...Hi David,<br /><br />To optimize the performance of our batch process i.e. T&L Load, Paysheet, Paycalc, Payconfirm, Benefit calc plus other custom process we tried placing PSINDEX tablespace to 32KB block and tested the process and we experienced performance improvement for the batch process. However for online navigation (OLTP) performance was degraded because of the contention. So we analyzed and selected the indexes used by online activity back to 8kb block size and performance improved. So we rolled out the changes in production. However instead of improving the performance in production it became worse. We did check init.ora, DB architecture, DB Patches, OS, Hardware layout everything is the same, except Production have more resources allocated and more memory available. <br /><br />Have you seen other clients implementing multiple Datablocks within a same instance for PeopleSoft application on 11gR2, Unicode Characterset, running on PeopleTools 8.51 and PeopleSoft HCM 9.1.<br /><br />Any insights or experience you can share will be appreciated.<br /><br />ThanksMikenoreply@blogger.comtag:blogger.com,1999:blog-25740336.post-63383422579107531172009-01-27T21:06:00.000+00:002009-01-27T21:06:00.000+00:00Partitioning the working storage tables for Applic...Partitioning the working storage tables for Application Engine isn't viable because the default behaviour is to truncate the whole of the temporary table. Deleting the rows for a single process (which happens when you run out of instances temporary records and have to use the shared table) is very likely to be slower than truncating.<BR/><BR/>If you could devise a partitioning strategy that would map processes to partitions, and if you could also change the code to truncate just the partition on which you are working, you will have the same contention problems with the RO enqueue.<BR/><BR/>However, partitioning permanent result tables is a very effective strategy for processes such as PeopleSoft Global Payroll where you define ranges of EMPLIDs, and processes different ranges in different process. GP uses Cobol processes, which are not restartable. The working storage tables can be recreated as Global Temporary tables, and there is no inter-process contention on delete or truncate. Nor is there any need for consistent read on the partitioned tables.David Kurtzhttps://www.blogger.com/profile/00924323960047469300noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-11049418610119182852009-01-27T10:09:00.000+00:002009-01-27T10:09:00.000+00:00Can you use a partitioned table, with a partition ...Can you use a partitioned table, with a partition for each process?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-25740336.post-91019513109446246602009-01-27T08:25:00.000+00:002009-01-27T08:25:00.000+00:00Use of the RO enqueue during truncate operations a...Use of the RO enqueue during truncate operations and the resulting serialisation is not a fault in Oracle. It is how the database works. My tests have been run on 10.2.0.3 and on various platforms.David Kurtzhttps://www.blogger.com/profile/00924323960047469300noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-2593255902173191932009-01-27T01:15:00.000+00:002009-01-27T01:15:00.000+00:00Hey David:This is only for 9.2.0.4, isn't it?I wen...Hey David:<BR/>This is only for 9.2.0.4, isn't it?<BR/>I went through all the related entries in Metalink and it seems to be specific to that version, with patches available in later versions and all fixed up in 10g.<BR/>Is that indeed the case?Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.com