tag:blogger.com,1999:blog-25740336.comments2023-08-24T22:26:48.675+01:00The PeopleSoft DBA BlogDavid Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger261125tag:blogger.com,1999:blog-25740336.post-11163568301181159282023-07-26T16:07:59.071+01:002023-07-26T16:07:59.071+01:00Thanks David - I got it to work with the where cla...Thanks David - I got it to work with the where clause after select * from X, or am I missing somethingBart22https://www.blogger.com/profile/10313948550190954306noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-55048815046161300892022-10-31T16:23:47.119+00:002022-10-31T16:23:47.119+00:00Re: "Can we do away with NT if we move strict...Re: "Can we do away with NT if we move strictly to OpenXML?" - I believe the answer is yes. It appears to be fixed in at least PeopleSoft 8.58. I have found that, generally, OpenXML performs better.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-90201293493260379042022-10-31T15:23:56.057+00:002022-10-31T15:23:56.057+00:00Can we do away with nt if we move strictly to open...Can we do away with nt if we move strictly to openxml? I have read that nplosion was fixed as well. I wonder if you have an update on your preferences to run one way or the other.mrd05dhttps://www.blogger.com/profile/03508377990957752276noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-3280142875154960602021-09-13T21:03:36.070+01:002021-09-13T21:03:36.070+01:00Bart - Yes. That is an important omission that I ...Bart - Yes. That is an important omission that I have corrected in the blog. Not sure how it crept in, but thank you.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-70845753827279127442021-09-13T19:47:00.937+01:002021-09-13T19:47:00.937+01:00Hi David - shouldn't the UPDATE psprcsrqst sta...Hi David - shouldn't the UPDATE psprcsrqst statement also contain the WHERE prcsinstance = ${PRCSINSTANCE} clause? I got strange results without it, where all my previous process instance statuses got updated while running a new one, and also I couldn't delete old PIs. This no longer happened after adding the WHERE prcsinstance = ${PRCSINSTANCE} clause to the UPDATE psprcsrqst statement.<br />Thank you for this - it has been very useful.Bart22https://www.blogger.com/profile/10313948550190954306noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-18839549437999220252021-06-17T20:29:40.837+01:002021-06-17T20:29:40.837+01:00Locks are used to deliberately serialise processin...Locks are used to deliberately serialise processing to prevent things from happening concurrently. In an Oracle database, a lock on a table never blocks a simple query. However, a SELECT ... FOR UPDATE will wait to acquire the lock before it returns a row. The database will report wait on <i>enq: TX - row lock contention</i>.<br />Increasing INITRANS reserves more space in the data block, to register interested transactions. It can grow to this on-demand if space is available, ie. if the data block is not already full. This can allow more concurrent transactions in a data block. If you run out of space in the interested transaction list, you can end up waiting on <i>enq: TX - allocate ITL entry</i>.<br />PSLOCK and PSVERSION are small tables and typically fit in a single block. Locks are used to deliberately serialise processing, usually to prevent one process from loading and caching a PeopleTools object while another process is updating it, which might involve updates to several PeopleTools tables. In your case, it sounds like increasing INITRANS simply allowed more transactions to be created until you ran out. The question is what is holding the locks and why.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-20845789913587129492021-06-07T13:40:12.626+01:002021-06-07T13:40:12.626+01:00I'm having a problem right now with an Oracle ...I'm having a problem right now with an Oracle database. A delivered process is locking pslock and psversion as it updates the cache numbers. When it does this, it locks users from accessing the application. I have tried to increase inittrans, it worked for a very short period of time, then stopped working after so many minutes. How do you get an Oracle database to allow you to read pslock and psversion while the table is being updated by another process?truffinghttps://www.blogger.com/profile/05467673553424596972noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-29343652720980905742021-03-30T19:05:48.847+01:002021-03-30T19:05:48.847+01:00Hi again - update: we were experiencing performanc...Hi again - update: we were experiencing performance issues with Pivot grids based on PeopleSoft queries after implementing shared cache. It turns out that the cache files for queries are indeed read-only (other cache files are not). We are now still preloading cache, but not using shared cache: we preload the cache files using the PLCACHE job and then copy the files to the individual PSAPPSRV_# cache folders in the application server, using a script. The shared cache option in psappsrv.cfg is switched off. Now we have no performance issues after maintenance, and no issues with read-only cache files.Bart22https://www.blogger.com/profile/10313948550190954306noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-14512295941783210982021-03-02T11:22:28.887+00:002021-03-02T11:22:28.887+00:00Thank you for sharing your blog, seems to be usefu...Thank you for sharing your blog, seems to be useful information can’t wait to dig deep! Appsianhttps://www.blogger.com/profile/01063540998194332779noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-40328593517730943712021-01-18T09:26:21.931+00:002021-01-18T09:26:21.931+00:00 Thank you for sharing your blog, seems to be usef... Thank you for sharing your blog, seems to be useful information can’t wait to dig deep!Anonymoushttps://www.blogger.com/profile/13575563188287335851noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-43343095788720432502020-10-21T15:36:40.284+01:002020-10-21T15:36:40.284+01:00Nice Blog! Thanks for sharing such post.Nice Blog! Thanks for sharing such post.Appsianhttps://www.blogger.com/profile/01063540998194332779noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-56239934133815086722020-02-21T18:39:29.177+00:002020-02-21T18:39:29.177+00:00I asked Oracle this question:
Preloaded cache fil...I asked Oracle this question:<br /><br />Preloaded cache files do not behave differently from 'regular' cache files. Oracle says that preloaded cache files when using shared cache are read-only, but tracing shows no difference in behaviour between preloaded cache files and 'regular' cache: in both cases objects with new versions are being loaded from the database into memory cache, until the moment that an APPSRV process recycles. Then the cache is updated, and next time an object is accessed, it will be from the cache file (not database). This principle is valid for both preloaded and non-preloaded cache. This would also mean that there is no need to recreate the preloaded cache files (by rerunning the preload AE process).<br />Is this correct?<br /><br />Oracle's answer:<br />I have discussed your latest statement internally and it is a correct statement.Bart22https://www.blogger.com/profile/12497947543723802408noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-57786320755468398642020-01-21T20:16:39.446+00:002020-01-21T20:16:39.446+00:00The file times on the physical cache files are bei...The file times on the physical cache files are being updated when changing an object in app designer, and reloading the page (with the object) in PIA. Even when changing cache files permissions to read-only (400), the file times are updated - no error occurs. But when I change permissions on the directory (SHARE) to 400, application server uses the 'old' PSAPPSRV_1, PSAPPSRV_2 cache directories etc to get its object - no error, just different behaviour.<br />I didn't turn on all possible tracing, so an error message may have occurred somewhere in the process. <br /><br />Bart22https://www.blogger.com/profile/08224700879216784574noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-16440259550110814572020-01-21T18:58:06.154+00:002020-01-21T18:58:06.154+00:00I haven't investigated this area for a very lo...I haven't investigated this area for a very long time. My understanding is that the shared physical is not updated by the application server processes. In your example, I can see a <i>File-Cache Read</i> operation and <i>Get File Cache</i> operation. I cannot see anything that suggests the physical cache is being written to.<br />i) Are the file times on the physical cache files are being updated as the application server runs?<br />ii) What happens if you change the permissions on the physical cache file to make them (and the directory) read-only? Does the application server raise an error?David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-2702277877234886282020-01-16T16:51:48.933+00:002020-01-16T16:51:48.933+00:00Thanks David, for your quick reply.I also read tha...Thanks David, for your quick reply.I also read that SHARE cache is read-only, but I'll paste my (partial) trace result here - to me it looks like after a reboot the file cache is actually updated when using shared cache (unless I'm reading it wrong), the object is no longer read from memory:<br /><br />make change to object (app designer) - don't reboot, access object (PIA):<br />PSAPPSRV.96138 (2) 1-4215 11.36.38 0.000022 Read Memory: PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1<br /><br />refresh page (no reboot), access object:<br />PSAPPSRV.96138 (2) 1-4215 11.36.38 0.000022 Read Memory: PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1<br /><br />reboot application server, access object:<br />PSAPPSRV.97543 (1) 1-860 11.40.57 0.000339 File-Cache Read: [PGM(UV_PBTEST/GBL/ENG)], duration: 0.000sec<br />PSAPPSRV.97543 (1) 1-861 11.40.57 0.000006 Get : File cache PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1 in /u01/app/psoft/cfg/FSXXX/appserv/FSXXX/CACHE/SHARE/<br /><br />The cache files in /u01/app/psoft/cfg/FSXXX/appserv/FSXXX/CACHE/SHARE/ have permissions 600, owner psoft - so on that level they are not read-only.<br /><br />Our PeopleTools version is 8.56.20.<br /><br />Thanks<br /><br />Bart22https://www.blogger.com/profile/08224700879216784574noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-59606163574473112022020-01-16T16:16:16.799+00:002020-01-16T16:16:16.799+00:00You are absolutely right. It is perfectly normal t...You are absolutely right. It is perfectly normal that every time the PSAPPSRV process recycles, or crashes and is restarted, the memory cache is lost and has to be reloaded from the physical cache, and if the cache entry for that object is out of date (determined by the version numbers) the object has to be loaded from the PeopleTools tables.<br />The difference (as I understand it) is that in a non-shared cache, the physical cache is updated at this point (for that directory of cache files, used by that instance of PSAPPSRV) so the object does not need to be loaded again from the database. Whereas, the shared cache is not updated and the object will have to be reloaded from PeopleTools tables every the PSAPPSRV process is restarted and it is accessed for the first time by the new process.<br />The lower the recycle count on the PSAPPSRV the greater the reload overhead. The catch is that (at least historically) the higher the recycle count the higher the risk of the PSAPPSRV crashing and the user getting an error.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-30092498317822400372020-01-16T13:46:02.429+00:002020-01-16T13:46:02.429+00:00Hi David, you say: "It is possible to run wit...Hi David, you say: "It is possible to run with a shared physical cache, but then it is read-only and must be pre-generated."<br />We would like to use the "Load Application Server Cache" application engine, which creates the application server cache files on the process scheduler server, which then need to be copied to a SHARE directory on the application server. I've been testing and tracing its behaviour, and what I found is that it doesn't behave differently from 'classic' cache: if you update an object, initially the (new version) object will be loaded into memory cache - until a PSAPPSRV process is restarted, at which point the application server process will get the object from its application server cache. So it may be read-only initially, but a recycle of PSAPPSRV will update automatically - just like when using non-shared and non-preloaded cache.<br />Can you confirm this, or am I missing something here?<br /><br /><br /><br />Bart22https://www.blogger.com/profile/08224700879216784574noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-18348735862745780882019-12-10T19:07:48.570+00:002019-12-10T19:07:48.570+00:00It never ceases to surprise me the range of troubl...It never ceases to surprise me the range of trouble people have with nVision. The problems really do vary from customer to customer. Without specific information, I can only generalise, but please feel free to contact me directly.<br />I expect you have already read the <a href="http://blog.psftdba.com/2017/10/nvision-performance-tuning.html" rel="nofollow">series of posts on nVision performance tuning</a>. The 12.2 defaults optimizer_adaptive_plans=TRUE and optimizer_adaptive_statistics=FALSE are generally best for PeopleSoft. If your budget data is now in PS_LEDGER rather than PS_LEDGER_BUDG then you have a much stronger case for partitioning. First range partition on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD and then list sub-partition on LEDGER. Then how you tackle statistics on the tree selector tables will depend on the size and complexity of your trees, how you use them, and whether this is an engineered system or not.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-55290818533681562042019-12-10T17:56:27.080+00:002019-12-10T17:56:27.080+00:00David, thank you for great information about nVisi...David, thank you for great information about nVision on your blog. We moved from Oracle 11G db to 12C this year when we moved to tools 8.56.15. To be honest we have had nothing but issues with nVision performance. It seems like we generate stats to fix an issue with Actuals reports, and the Budget reports go in the tank and vice versa. I wish there was an easier solution to solve the performance aspects of nVision. Especially when Oracle knows how many people are using this functionality. Any suggestions? <br /><br />SurinderRaistlin Majerehttps://www.blogger.com/profile/03046110510103990132noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-138180257949243582019-10-08T20:38:51.058+01:002019-10-08T20:38:51.058+01:00Hi David
Based on Doc ID 1537491.1, it seems that...Hi David<br /><br />Based on Doc ID <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?id=1537491.1" rel="nofollow">1537491.1</a>, it seems that OpenXML mode can be used with nVision layouts that need macro processing. The report is processed with OpenXML, but runs the macro processing in Excel Automation mode, then returns back into OpenXML. So, this may eliminate or reduce the need for an additional scheduler for some nVision reports, if you are running Windows. This wouldn't help if you have PSUNX schedulers and want to run nVision reports with macros.<br /><br />danDan Iversonhttps://www.blogger.com/profile/07401803061428401659noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-91021875410868234092019-09-01T23:54:58.140+01:002019-09-01T23:54:58.140+01:00You could look through the execution plans capture...You could look through the execution plans captured by AWR (DBA_HIST_SQL_PLAN) to find any execution plans and hence SQL statements that use these indexes. However, if you don’t find any plans, it does not mean that the indexes are not used, just that the plans were not captured by AWR. <br />It may be that some of the remaining function-based indexes are useful and you decide to retain them. However, there is a cost of ownership. You won’t be able to bring them into Application Designer, they will always have to be managed manually. They will always appear in DDDAUDIT. You will also have to be careful that the indexes are not dropped by Application Designer. This is something that might happen when altering the tables involved because they are not defined in the PeopleTools meta-data. The only way I can think of protecting them is with a DDL trigger – see <a href="http://blog.psftdba.com/2006/10/ddl-triggers-to-prevent-loss-of.html" rel="nofollow">DDL Triggers to prevent loss of database objects not managed by PeopleTools</a>David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-37716775225292273572019-07-10T22:08:24.820+01:002019-07-10T22:08:24.820+01:00Hi David,
We are upgrading from PeopleTools 8.54 ...Hi David,<br /><br />We are upgrading from PeopleTools 8.54 to 8.57 and have discovered that we have over 10K Descending Indexes still defined in our database instances (HCM and FSCM). We have removed those. Now we find that there are a few remaining function-based indexes (~20). I haven't been able to get a clear answer from Oracle as to if these are useful or not. <br /><br />The only relevant information I've found on your blog, etc. is that they are useful for when the UPPER() function is used on search pages.<br /><br />I believe the these indexes were added by our hosting provider DBA's after analyzing certain SQL statements. I am going to ask for the definitions of those.<br /><br />Thank you for your valuable insight.wydot_dlrosshttps://www.blogger.com/profile/10624164414565221031noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-12258934578320495872019-07-01T14:06:07.690+01:002019-07-01T14:06:07.690+01:00Hi David, I had one additional configuration step ...Hi David, I had one additional configuration step to get the shell scripts to run. In the server definition (PSUNX), I had to add "Shell Script" as one of the allowed process types to run on the scheduler. After that everything worked great.<br />Dan Iversonhttps://www.blogger.com/profile/03784022855435903693noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-53648689570542462072019-06-29T18:03:13.965+01:002019-06-29T18:03:13.965+01:00Good information
Thanks for sharingGood information <br /><a href="http://www.kscsmartguide.com/" rel="nofollow">Thanks for sharing</a>K.S.Chowdaryhttps://www.blogger.com/profile/07139717007931829179noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-76700610526254254352019-03-27T06:47:30.098+00:002019-03-27T06:47:30.098+00:00If the cache has been purged then it is gone and i...If the cache has been purged then it is gone and it can't be brought back. Simply let the application run and it will build up again over time.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.com