PeopleTools 8.54: Oracle Resource Manager

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Oracle Resource manager is about prioritising one database session over another, or about restricting the overhead of one session for the good of the other database users.  A resource plan is a set of rules that are applied to some or all database sessions for some or all (more...)

PeopleTools 8.54: Multiple Query Security Records

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

This post is not about a database feature newly supported in PeopleTools, but PeopleTools is capable of doing something new that could negatively impact the database.  When I saw the following warning in the PeopleTools 8.54 release notes, I thought I should look into it.
"PeopleTools has (more...)

PeopleTools 8.54: %SelectDummyTable Meta-SQL

This is the sixth in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
PeopleTools simply evaluates this meta-SQL as 'DUAL' on Oracle.

In Oracle, DUAL is just a convenience table.  You don't need to use it, you can use anything you want. PeopleSoft applications often use PS_INSTALLATION when they needs a single row source in a query.  (more...)

PeopleTools 8.54: %SQLHint Meta-SQL

This is the fifth in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.
This new PeopleCode meta-SQL macro performs a search of SQL statement for the nth instance of SQL command keyword and inserts a string after it.
%SqlHint(SQL_cmd, index, hint_text, DB_platform [, {ENABLE | DISABLE}])
It is particularly effective with the %InsertSelect meta-SQL.  Previously the only way (more...)

PeopleTools 8.54: Table/Index Partitioning

Partitioning in Oracle

Partitioning of table (and index) segments involves breaking them into several smaller segments where certain data values only occur in certain segments.  Thus if a query is looking for a certain data value it may be able to eliminate some partitions without having to scan them because by definition those values cannot occur in those partitions.  Thus saving logical and physical read, and improving performance.  This is called partition (more...)

PeopleTools 8.54: Global Temporary Tables

This is the third in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Database Feature Overview

Global Temporary tables were introduced in Oracle 8i.  They can be used where an application temporarily needs a working storage tables.  They are named
  • Global because the content is private
  • Temporary because the definition is permanent
Or if you prefer

PeopleTools 8.54: Materialized Views

This is the second in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Materialized Views in the Database

Snapshots were introduced in Oracle 7.  They were a way of building and maintaining a physical table to hold the results of a SQL query.  We are well used to a view being the results of a query, but here the (more...)

PeopleTools 8.54: Descending Indexes are not supported

This is the first in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

"With PeopleTools 8.54, PeopleTools will no longer support descending indexes on the Oracle database platform" - PeopleTools 8.54 Release Notes

They have gone again!  Forgive the trip down memory lane, but I think it is worth reviewing their history.

PeopleTools 8.54 for the Oracle DBA

The UKOUG PeopleSoft Roadshow 2015 comes to London on 31st March 2015.  In a moment of enthusiasm, I offered to talk about PeopleTools 8.54 from the perspective of an Oracle DBA.

Since the presentation has been chosen, I am now doing some research.  I have picked out 7 topics that I want to talk about.  I will discuss how the feature has been implemented, and what I think are the (more...)

Filtering PeopleTools SQL from Performance Monitor Traces

I have been doing some on-line performance tuning on a PeopleSoft Financials system using PeopleSoft Performance Monitor (PPM).  End-users have collect verbose PPM traces. Usually, when I use PPM in a production system, all the components are fully cached by the normal activity of the user (except when the application server caches have recently been cleared).  However, when working in a user test environment it is common to find that the components are (more...)

Minimising Parse Time in Application Engine with ReUseStatement

This article explains how to determine the overhead of using literal values rather than bind variables in SQL submitted by PeopleSoft Application Engine programs. Using a combination of PeopleSoft Application Engine Batch Timings and Oracle Active Session History (ASH), it is possible to determine where it is most effective to change to alter this behaviour by setting the ReUse attribute.

ReUse Statement Flag

I originally wrote about the Performance Benefits of ReUseStatement in Application Engine (more...)

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.


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