Who is using this index?


Or, to put it another way, I want to change or drop this index, who and what will I impact?

The Challenge 

The problem that I am going to outline is certainly not exclusive to PeopleSoft, but I am going to illustrate it with examples from PeopleSoft. I often find tables with far more indexes than are good for them.
  • The Application Designer tool makes it very easy for developers to add indexes to (more...)

To Hint or not to hint (Application Engine), that is the question

Over the years Oracle has provided a number of plan stability technologies to control how SQL statements are executed.  At the risk of over simplification, Outlines (deprecated in 11g), Profiles, Baselines and Patches work by injecting a set of hints into a SQL statement at parse time.  There is quite a lot of advice from Oracle to use these technologies to fix errant execution plans rather than hint the application.  I think (more...)

Introducing the Analytic Keep Clause for Effective-Dated/Sequence Queries in PeopleSoft

Those of us who work with PeopleSoft, and especially the HCM product, are all too familiar with the concept of effect-dated data, and the need to find data that was effective at a particular date.  PeopleSoft products have always made extensive use of correlated sub-queries to determine the required rows from an effective-dated record.

The JOB record is a the heart of HCM. It is both effective-dated and effective sequenced. I will use it (more...)

Implementing Deferred Segment Creation After an Upgrade

I have written previously about Deferred Segment Creation. Each empty table and its indexes use only 64Kb (assuming an 8Kb blocksize and locally managed tablespaces), but in a PeopleSoft there can be be tens of thousands of such tables and that adds up to a saving worth making.

If you are upgrading your database to 11gR2 , you might want to make sure that you are using it.  Deferred segment creation was introduced in (more...)

Date to Timestamp Conversion during PeopleTools Upgrade

This document describes a script to convert Oracle date columns to Timestamps as used from PeopleTools 8.50 but only rebilding those indexes that reference those columns, rather than drop and recreate every index in the system, thus producing a significant saving of time during the upgrade.
 
(A longer (more...)

Minimum Number of Recycling Server Processes

When I rebuilt my demo system (some while ago) with PeopleTools 8.52, and have noticed a new message generated by ubbgen in PeopleTools 8.52 when the minimum number of recycling servers is set to 1.

WARNING: PSAPPSRV, PSSAMSRV, PSQRYSRV, PSQCKSRV, PSPPMSRV and PSANALYTICSRV are configured with Min instance  (more...)

PeopleTools 8.52 Application Engine sets MODULE and ACTION

I have written and spoken often about the huge importance of instrumentation in an application. PeopleSoft introduced internal instrumentation to PeopleTools 8.44 for its own Performance Monitor that works on any platform. Since PeopleTools 7.53, when running on an Oracle database, PeopleTools also calls the Oracle supplied package dbms_application_info package to set the module and action for the session. The values set were not particularly useful, so I wrote a PL/SQL package (psftapi) and trigger to set the module and action attributes when a process started.

In PeopleTools 8.50 this instrumentation was enhanced to set module (more...)

Using Two Temporary Tablespaces in PeopleSoft

A longer version of this posting, with all necessary code and a demonstration test, is available as a technical note on the Go-Faster website.  I am working with two different PeopleSoft customers who have had challenges with the size of the temporary tablespaces.  Critical batch processes have failed because they have exhausted space in the temporary tablespace.
ORA-01652 unable to extend temporary segment...
  • In one case, the one and only temporary tablespace in a Payroll system has over time been extended to in excess of 360 GB.  This has happen in response to PeopleSoft processes that failed (more...)

Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g

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...)

Enabling Oracle Extended SQL Trace by Module and Action

I have written previously about the value of assigning meaningful values to the module and action attributes on a database session (see Using Oracle Enterprise Manager (Grid Control) with PeopleSoft). Oracle added instrumentation to PeopleTools 8.50 (see PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions) that sets module and action for on-line and batch sessions.  However, I still use my own trigger to set these attributes for processes initiated by the Process Scheduler.

I originally became interested in module and action because it made it possible to analyse performance problems in specific processes with Active (more...)

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

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...)

Deferred Segment Creation in PeopleSoft

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...)

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.
More Priority Levels
By default, PeopleSoft process can be given priority 1 (low), 5 (medium) and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference (more...)

Unlocking Temporary Table Instances from Deleted Process Requests

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...)

Finding Unnecessary Effective Date Processing in PS/Query

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...)

Row Level Locking in the PeopleTools Component Processor

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...)

PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions

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 and action 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...)

Performance Overhead of Multiple SQL calls in SQR

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
------------- ------------------- ---------- ----------
515d3buvaf8us           (more...)

PeopleSoft Run Control Purge Utility

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...)

Announcing the Co-Operative PeopleTools Table Reference

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...)