tag:blogger.com,1999:blog-25740336.post7405043168070076477..comments2023-08-24T22:26:48.675+01:00Comments on The PeopleSoft DBA Blog: Managing Changes to the Number of Instances of Temporary Tables used in Application Engine ProgramsDavid Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-25740336.post-70408999257601603672018-11-29T20:21:14.282+00:002018-11-29T20:21:14.282+00:00Daryn, it’s a Catch-22 situation.
You cannot bui...Daryn, it’s a Catch-22 situation. <br />You cannot build the extra tables with Application Designer until you have increased the number of temporary table instances, and as soon as you increase the number of temporary table instances, an Application Engine initiated by the Process Scheduler will assume the tables have been created and will attempt to use them if necessary. <br />Remember that the number of on-line temporary table instances is applied to <b>every</b> temporary record in the database. In Financials, you will have thousands of tables to build across all modules, whether you use the module or not. So you can really only manage this with Application Designer.<br />If you are using stand-alone PSAE processes the change is recognised immediately, but if you are using PSAESRV server processes it will be picked up as the server processes recycle, or when the scheduler is restarted. Similarly the new on-line temporary table instances will be used as application server processes recycle, or when the application server is restarted.<br />A similar problem exists with the number of non-shared temporary tables used by a scheduled Application Engine program, but that setting is an attribute of the Application Engine program and is migrated with Application Designer.<br />The problem is how avoid a production outage rather than in a development or test system. <br />You could <br />1. Increase the number on-line temporary table instances on a test system<br />2. Use Application Designer on that test system to create the DDL script to create the additional tables <br />3. Apply that script in production before <br />4. Increase the number of on-line temporary table instances. <br />5. As a final test, try to build the create table script again with Application Designer. If this doesn’t create any tables then the process worked successfully.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-89006251318631343902018-11-29T09:32:36.975+00:002018-11-29T09:32:36.975+00:00Hi David, great blog post as usual however I wonde...Hi David, great blog post as usual however I wonder if you could clarify something for me.<br /><br />When Application waits are seen due to the contention updating ps_aeonlineinst oracle recommends increasing the online temp instance count in psoptions. Doc ID 651316.1, but it states that ALL Temp Tables need to be rebuilt and I couldn't understand why as this would essentially require an outage, however looking at your blog post I see you mention the following:<br /><br /> 3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.<br /><br />On seeing this I thought perhaps the first x number of instances for online AEs were created without the PROCESS_INSTANCE column and therefore full rebuild of TAOs was required due to the different table structure used for AEs run via CallApEngine, but I see the TAO structures are all identical. My query therefore is why can the operation not be done via the following procedure rather than rebuilding all the TAOs as oracle state?<br /><br />a) Determine quantity of Temp tables required for online+batch usage and alter accordingly in App designer for each AE utilizing Temp tables<br />b) Create project with all affected TAOs and run build using create table. This will create & run the SQL just for the missing instances from current count to new count, leaving currently existing and possibly in use tables untouched. (No outage)<br />c) Increase the online temp ae count accordingly<br /><br />Is there something I'm missing in the whole process? I thought we could just create the additional n TAO instances via above procedure and then increase online temp instances by n.<br /><br />Many thanks in advance.<br />Anonymoushttps://www.blogger.com/profile/06748546339047952007noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-70619450103280564502009-01-30T23:30:00.000+00:002009-01-30T23:30:00.000+00:00That is recorded in PS_AETEMPTBLMGR.That is recorded in PS_AETEMPTBLMGR.David Kurtzhttps://www.blogger.com/profile/00924323960047469300noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-25229294706848933912009-01-30T20:41:00.000+00:002009-01-30T20:41:00.000+00:00Good one.I want to find the locked and free tempro...Good one.<BR/><BR/>I want to find the locked and free temprory table instances by query. <BR/><BR/>how can i do that ?<BR/><BR/>After an AE goes to Success is their any way to find which temp table insatance that process used.Ganeshhttps://www.blogger.com/profile/05671330781617372189noreply@blogger.com