I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database. Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g. I believe a slightly different approach is required.
In 2009, I wrote a series of blog postings on the subject of collecting statistics
. However these were all based on Oracle 10g. I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, (more...)
Recently, I have been looking into how to gather cost-based optimizer
statistics on composite partitioned objects.
Database partitioning is not used by default in PeopleSoft because it is database specific. Not all databases support partitioning, and where they do it is done in a platform specific manner. Application Designer has no capability to create partitioned objects (although from PeopleTools 8.51 it will preserve partitioning in tables and index that are already partitioned in an Oracle database). It is therefore no surprise that it is not used widely by PeopleSoft customers. However, partitioning is essential in (more...)
This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation
by Chistian Antognini
They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home
Deferred segment creation is available from (more...)
In a previous blog entry
, I discussed how to detect whether an Application Engine process had run out of non-shared Temporary Table instances by querying the message log table for the entries written when this happens.
This can happen because processes that have locked all available non-shared table instances are currently processing. However, if a restartable Application Engine program terminates with an error, the tables remain locked to that process instance so that it can be restarted. If that process instance is neither cancelled nor restarted so that it subsequently runs to success then the tables will remain locked, (more...)
In PeopleSoft for the Oracle DBA
(Ch11, p309) I commented on how PS/Query will automatically add effective date criteria to and EFFDT column, even if it is not a key column.
PS/Query does warn you that it has added the criteria, but it won't warn that the column is not a key column
If EFFDT is not part of the key, then it is unlikely that you will need this processing. It is unlikely to change the result of the query, and it incurs additional work.
Below is an example of the code added to a query (more...)
My apologies in advance, but this entry is a bit of a rant. It is one of those things in PeopleSoft about which you do nothing, but it still is interesting to know because it reveals something of how it works under the covers.
In the PeopleSoft for the DBA
, I commented on how the columns updated in the UPDATE statements issued by the Component Processor at save time contain only the fields that have been updated in the component. You get different update statements depending upon what you update in the component, but the alternative is (more...)
I recently worked on a PeopleTools 8.50 system in production for the first time and was able to make use of the new Oracle specific instrumentation in PeopleTools.
PeopleTools now uses the DBMS_APPLICATION_INFO
package to set module
session attributes. This data is then copied into the Active Session History (ASH).
- Within the PIA, the application server sets module and action to the name of the current component and page within the current components
- For Integration Broker messages they are set to service and queue name.
- For Application Engine processes the module is set to PSAE and (more...)
I was asked to look at a fairly simple SQR program that reported on payroll data. It pivoted data for each employee on which it reported. It makes 21 calls to two procedures that each obtain a value by summing data across different sets of data in one of two payroll result tables.
The ASH data shows that most of the database time, 184 out of 192 seconds is spent in the two statements that aggregate that data. These statements are in the procedures that are called within the driving query.
SQL_ID SQL_PLAN_HASH_VALUE EXEC_SECS ASH_SECS
------------- ------------------- ---------- ----------
Run Control records are used to pass parameters into processes scheduled processes. These tables tend to grow, and are rarely purged. Generally, once created a run control is not deleted. When operator accounts are deleted, the Run Controls remain, but are no longer accessible to anyone else.
I have worked on systems where new Run Controls, whose IDs contain either a date or sequence number, are generated for each process. The result is that the Run Control tables, especially child tables, grow quickly and if not regularly managed will become very large. On one system, I found 18 million (more...)
I have created a reference to the PeopleTools tables and views
on my website.
In the course of my work on PeopleSoft, I spend a lot of time looking at the PeopleTools tables. They contain meta-data about the PeopleSoft application. Much of the application is stored in various tables that are maintained by Application Designer. Some tables provide information about the Data Model. Others contain configuration data that is maintained via PeopleTools components in the PIA.
Many of my utility scripts query information from PeopleTools tables, and some also update them. Of course, that is strictly not supported, but (more...)
Occasionally, I see very large PeopleSoft systems running on large proprietary Unix servers with many CPUs. In an extreme case, I needed to configure application server domains with up to 14 PSAPPSRV processes per domain (each domain was on a virtual server with 8 CPU cores, co-resident with the Process Scheduler).
The first and most important point to make is don't have too many server processes. If you run out of CPU or if you fully utilise all the physical memory and start to page memory from disk, then you have too many server processes. It is (more...)
Oracle's Flashback Query facility lets you query a past version of a row by using the information in the undo segment. The VERSIONS option lets you seen all the versions that are available. Thus, it is possible to write a simple query to retrieve the all values that changed on a process request record through its life cycle.
The Oracle parameter undo_retention
determines how long that data remains in the undo segment. In my example, it is set to 900 seconds, so I can only query versions in the last 15 minutes. If I attempt to go back further (more...)