Some of the processes that depend on shared table instances , can experience a dramatic differences in performance, in case all of the instances available are being used, or remaining locked by some orphan or failed process. One way to resolve this problem is to go online and check if tables should be “released”. Here is a set of SQLs that would allow you to do the same thing and depending on you requirements – allow to automate this process, which could prove critical during the times of high usage:
-- Shared Temp Table instance control tableSELECT * FROM PS_TEMP_TBL_ASGNMWHERE TEMP_TBL_NAME = 'table_name'WITH UR -- Get tables that were locked by processes that ran 200 instance numbers beforeSELECT * FROM PS_TEMP_TBL_ASGNMWHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')with ur -- RELEASE ALL TEMP TABLE LOCKS -- FROM PROCESS INSTANCES THAT ARE LESS THAN 200 THAN THE LAST SUCCESSFUL ONEUPDATE ps_temp_tbl_asgnm SET in_use_sw = 'N', PROCESS_INSTANCE = 0WHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200 FROM psfssys.psprcsrqst WHERE runstatus = '9')
Let me know if you have any questions
Tags: PeopleSoft · Performance · SQL · TipsNo Comments
Loading ...
Oracle PeopleTools Blog
0 responses so far ↓
There are no comments yet...Kick things off by filling out the form below.