Peoplesoft PeopleTools Performance SQL Server Tools Tuning Utilities

_WA_Sys Stats or Hand Crafted?

SQL Server generates histogram statistics automatically whenever a column is referenced in a predicate and it is not already the leading column in an existing histogram statistic. By design, indexes create histogram statistics automatically so any column you have that is the leading column in an index will already have histogram statistics.

The autogenerated statistics are prefixed _WA_Sys_ followed by:

  • the column name (in older SQL Server versions) or the column number in hex (for SQL Server 2008 onwards) e.g. EMPLID or 00000001
  • the object_id of the table in hex e.g. 57490C28

separated by an underscore. So auto-generated statistics look like this:

  • _WA_Sys_DESCR_57490C28 or _WA_Sys_00000013_57490C28

The issue for me as a PeopleSoft Administrator/DBA is whether I should keep these statistics or recreate them as “user” statistics with more sensible names. Oddly enough the “old style” naming convention which included the column name was actually better than the “hex” version we have now. After all what is column 00000014 (decimal 20) in table X?

My personal preference is to process the system generated statistics and recreate them as statistic “column name” on table X. I have a perl script that generates the relevant DROP/CREATE STATISTICS commands for an entire PeopleSoft database. The output looks like this:

--- Remove statistic _WA_Sys_AUDITRECNAME_5921A398 for column AUDITRECNAME on table PSRECDEFN
--- Create User Statistic for column AUDITRECNAME (+ 1 keys) on table PSRECDEFN

Why do I like to do this? Well there are a number of reasons:

  1. The naming convention is clearer to me – open the statistics tree within a table in object explorer and you get a list of column names plus the statistics created for any indexes.
  2. Once I have changed to “user” statistics I can query for any new _WA_Sys_ statistics (yes I leave auto-generate switched on). This shows me any “new” query activity that accesses via previously unused predicate columns. This can help in deciding if perhaps new indexes are required.
  3. I can ensure the existing statistic are recreated using a “WITH FULLSCAN” not just sampled data. For many PeopleSoft systems this is essential – data can often be very skewed and sampling can miss this on occasion.
  4. I like to add in the “key” columns of the table to the histogram to provide better density information for the optimizer.  This can be essential information when intermediate key fields have a single value e.g. EMPL_RCD in HR is typically 0 in most cases.

If you would like to discuss this in more detail, leave a comment.

App Engine Hints and Tips Oracle PeopleTools PSVERSION Tools VERSION

VERSION 3.0 Application Engine!

If you are running a pre-8.53 version of PeopleTools you will (hopefully) be aware of the issues and requirements of using the VERSION Application Engine to correct problems with object versioning in PeopleSoft.

To summarise, the older VERSION App Engine just resets all the counter to 1! This is known as “Classic Mode” in the later release of the VERSION App Engine and is to be avoided at all costs really. You need everyone out of PeopleSoft, have to take all Application and Process Schedulers down, clear cache and run the App Engine from the command line to be 100% safe using it. Ugh.

Oracle recommends downloading and installing the most current VERSION Application Engine on all systems running PeopleTools >= 8.44 up to the latest release. Why? Because the later release is safer and better in all respects.

It has three execution modes:

  • Report Only Mode – Prefix your Run Control Id with the string “REPORTONLY” and you will get a report of the problems you have (if any) with versioning.
  • Classic Mode – Prefix your Run Control Id with the string “RESETVERSIONS” and the App Engine will run the “old way” – with all of it’s limitations (servers down, everybody out, cache cleared and run from command line). DO NOT USE THIS!
  • Enhanced Mode – Use and other Run Control prefix and the App Engine will just fix the versions that are wrong. It won’t reset back to 1 – just make everything line up correctly. You can even run this via Process Scheduler and with the servers up. What’s not to like about that?

Oracle Support Document 611565.1 (E-AS: Instructions Regarding the Use of the VERSION Application Engine Program) can be found at: Oracle Support Document 611565.1

BI Publisher Configuration Peoplesoft Tools

BI Publisher fix for XLSX Cell Merging Problem in PeopleSoft

Under PeopleTools 8.53 the default Excel output format for BI Publisher reports is now native XLSX rather than “MHTML” as in previous releases. This has a number of advantages such as a much smaller output file size due to it being a real Excel format and not HTML :-).

The change to this format by default also has some interesting issues with cells and cell merging that are fixed in a BI Publisher patch. The description of the bug is:

Peoplesoft Tools Utilities

Downloading from Oracle with wget

Downloads from My Oracle Support are facilitated through a wget shell ( script that can be used as an alternative to interactively downloading the individual files via a web browser. This feature is particularly useful for headless *nix servers or where X is not installed at all, but also for those large image downloads you would like to download outside core office hours.

However, more recent versions of wget can have issues with the generated script provided by Oracle. This post offers some .wgetrc settings to make the supplied scripts work correctly without modification (apart from adding your password).

Here is a .wgetrc that works on the Linux systems I have tried i.e. Debian/Ubuntu based ones like LinuxMint:

# Suitable wget settings for downloads from 

Note: The entries continue=on and progress=dot:giga are more for useablity. I like to use resumed downloads where possible in case a script fails and I need to restart it. Oracle’s web server supports ranged downloads so this does work. The progress report changes just reduce the size of the log files by making each line represent 32M of downloaded data.

Development Tools Utilities

Beyond Compare 4

I just invested in the latest version of Scooter Software’s incredible file/folder/data comparison tool Beyond Compare. Lots of new features but most significantly for me is that there is now a native Mac OSX version. Full list of changes here

The jury is out on the UI changes but it is still as slick as ever at what it does. Buy it!