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
Oracle PeopleTools Blog
Why not just running a more general:
update PS_TEMP_TBL_ASGNM set
in_use_sw=’N',
process_instance=0
Or, if you are afraid of currently running processes, you could use something like this:
UPDATE PS_TEMP_TBL_ASGNM SET
in_use_sw=’N',
process_instance=0
WHERE process_instance IN
(
SELECT prcsinstance FROM psprcsrqst
WHERE runstatus NOT IN (5, 7)
);
— is there anything wrong with this approach?
as processes execute a they will populate this table with tables that they are currently using, in case you execute a generic SQL like you mentioned this may unlock some of those table for use and cause trouble in case some other contending process may try and use them
This actually may be a great idea – can’t see any downsides to this one – thank you