I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting shared pool errors in the PDB that lead to ORA-4031 errors in the PeopleSoft application.   I have written a longer version of this article on my Oracle blog, but here are the main points. SGA Management with a Parse Intensive System (PeopleSoft). PeopleSoft systems dynamically generate lots of non-shareable SQL code.  This leads to lots of parse and consumes mor ..read more

In the cloud (or any virtualised environment), performance is instrumented as cost.  This is also true in any other on-premises environment, but it takes a lot longer to feedback! If you never run out of CPU, then you have probably bought/rented/allocated/licensed too many CPUs. If you do run out of CPU, then you should use the database resource manager to prioritise the processes that are most important to the business. If you don't enable the resource manager, you will have less visibility of when you do run out of CPU. At the very least, you can use one of the sample resource manag ..read more

Sometimes, performance analysis will turn up a problem SQL query that is probably a PS/Query. However, I need to know which PS/Query it is should I wish to alter it or talk to the user who wrote it.  Is it a PS/Query? It is quite easy to spot SQL queries that are generated from queries defined in the PS/Query tool. These are typical characteristics: Single character row source aliases (eg. A, B, D)  The same row source with a suffix 1 (eg. D1) for query security records. Effective date/sequence subqueries are always correlated back to the same table. Order by column position number ..read more

I wrote about controlling the operating system priority of processes in PeopleSoft Tuxedo domains in Chapters 13 of 14 of PeopleSoft for the Oracle DBA, but I think it is worth a note here. On Linux and Unix systems, the nice command can be used to lower the operating system scheduling priority of a process (or a privileged can increase the priority). When a server has no free CPU, processes with a lower priority get less time on the CPU. However, when there is free CPU available, the scheduling priority does not affect the amount of CPU that the process can utilise.  On Unix, the ..read more

Batch processing is like opera (and baseball) - "It ain't over till the fat lady sings".  Users care about when it starts and when it finishes.  If the last process finishes earlier, then that is an improvement in performance.  This note describes a method of additionally prioritising processes queued to run on the process scheduler in PeopleSoft by their requesting operator ID and run control.  Where processing consists of more instances of the same process than can run concurrently, it can be used to make the process scheduler run longer-running processes before shor ..read more

It is easy to access the PeopleSoft message log in Process Monitor component, but it can be a little difficult to work with in SQL because it is stored in multiple tables. This started when I wanted to generate a PeopleSoft log message as a single string of text, so I could investigate shared pool memory errors by searching for ORA-04031 errors.  Ultimately, the string 'ORA-04031' is stored in PS_MESSAGE_LOGPARM, but I wanted to see the whole error message. Each process, has a request record on PSPRCSRQST, it can have many messages. Each message is stored in the message log table PS_MESS ..read more

I found this question on a message forum, wrote a note, and forgot about it: Anyone have any tricks on suspending the process schedulers programmatically?  I just tried using the following but the weird thing was I saw at least one of my process schedulers unsuspend itself, so I must be missing an update to a table. UPDATE PSSERVERSTAT set SERVERSTATUS = '2' where SERVERNAME like '%PSUNX%' The process scheduler writes its status to SERVERSTATUS so that it can be seen in the Process Monitor.  Instructions to the process scheduler are read from SERVERACTION, so this is the column th ..read more

Sometimes, ASH and AWR are not enough.  SQL may not be sampled by ASH if it is short-lived, and even if it is sampled, the SQL may not be captured by AWR.  Sometimes, in order to investigate a problem effectively, it is necessary to use database session SQL trace.   It is easy to trace a process initiated by the process scheduler with a trigger (see Enabling Oracle Database Trace on PeopleSoft processes with a Trigger). Another tactic is to use an AFTER LOGON trigger with logic to look at the program name.  The program name can be read using SYS_CONTEXT().  I ..read more

Come to the Optimizer Panel Group session at the UKOUG Breakthrough'22 conference in Birmingham - and PeopleSoft poses special challenges for the optimizer!  Reply to this tweet to submit a question in advance. @UKOUG #UKOUGBreakthrough22 is this week! The Optimizer Panel Group with @JLOracle @VLDBB @dani_schnider @ChandlerDBA @davidmkurtz is on Thursday @ 16:00. If you have a question for the panel reply here. If you want to hear a question answered, please like the reply. pic.twitter.com/eUNN8ey7ON — David Kurtz - /*+Go-Faster*/ Consultancy ?? (@davidmkurtz) November 29, 2022 ©David ..read more

It is a feature of PeopleSoft that it uses lots of views to present data within the application, and frequently views reference other views.  There are examples of this design going 5 levels deep in HR and deeper in Financials.  When faced with a SQL execution plan for such a view you often wonder which view referenced which table. However, the Oracle database describes "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links" in the view ALL_DEPENDENCIES. Thi ..read more