Category: Tuning

Categories
PeopleTools Performance SQL Server Tuning

PeopleTools Indexing on SQL Server

PeopleTools was designed to be database agnostic. In the past, the database support included databases such as Gupta SQLBase, Allbase, Informix and Sybase. The ability to support multiple platforms and add others relatively easily was a consequence of some excellent initial design decisions. Sadly, database platform support has dwindled to Oracle, SQL Server and DB2 in more recent releases of PeopleTools.

However, supporting multiple databases in this way meant that many database specific features were not taken advantage of. Since the Oracle takeover of Peopleoft, there have been some moves to add some Oracle database specific feature support e.g. GTT, but almost nothing on the other databases.

Continue reading “PeopleTools Indexing on SQL Server”

Categories
PeopleTools SQL Server Tuning Upgrade

PSTREENODE Clustered Index Change

I noticed that in PT 8.55 (well at least in .08 and later) that the PeopleTools meta-data for the Clustered Index on PSTREENODE lost the “clustered” attribute. I’m not sure why this was changed but you should be aware that this change could impact your execution plans – especially if you use row level security.

PT 8.55 loses Clustered Flag on "key"
PT 8.55 loses Clustered Flag on “key”

 

"Key" is now Nonclustered
“Key” is now Nonclustered

 

And, more importantly what we now have is a “heap” and so data is no longer ordered as we might expect. This also forces any sequential access to refer to index allocation map pages. Overall, changing this is not something to be done lightly, and I do wonder at the thought process behind this change – or was it just a mistake?

Categories
Hints and Tips Peoplesoft PeopleTools SQL Server Tuning

PeopleSoft – INCLUDE and WHERE (filtering) on SQL Server Indexes

It is trivial to add support for INCLUDE() and WHERE() clauses on SQL Server indexes in PeopleSoft – just change the model DDL for Index creation to have the two optional clauses defaulted to blank and then override on the specific index as needed:

model_ddl_index_sql_server

Obviously, at the individual index level you will need the full syntax including the keywords e.g.

INCLUDE (col1,col2,col3 …)

WHERE (criteria)

Be aware that the criteria you can include is limited – refer to the Microsoft documentation for more details.

Categories
Performance SQL Server Trace Flags Tuning

SQL 2014 Cardinality Estimator Trace Flags

For reference:

  • 9481  – Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 7.0 through SQL Server 2012) of the cardinality estimator when creating the query plan.
  • 2312 – Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 and later) of the cardinality estimator when creating the query plan.
Categories
App Engine Hints and Tips Peoplecode Peoplesoft PeopleTools Performance Tuning

Jackson Structured Programming (JSP) – Read Ahead and PeopleCode

Jackson Structured Programming – now that brings back memories of my COBOL training at British Telecom in the late 1980’s.

What prompted this short post was a dreadful piece of hand-crafted PeopleCode to load a CSV file using a file layout. The usual “Operand of . is null” occurred unless the input file contained a “blank line” at the end.

The underlying reason for this was a failure to apply one of the fundamental techniques in JSP – the single read-ahead rule:

Single Read-ahead rule: Place the initial read immediately after opening the file, prior to any code that uses the data; place subsequent reads in the code that processes the data, immediately after the data has been processed.

In fact, this approach is exactly what you get when you drag a file layout into a PeopleCode step in Application Engine – sample code that uses the JSP single read-ahead rule.

Categories
Fedora Linux Oracle Linux Peoplesoft Tuning VirtualBox

Reducing PeopleSoft DPK VM Size using zerofree

One of the slightly irritating parts of the build of a VirtualBox VM using the PUM downloads is the fact that the build script copies the DPK files into the VM prior to unpacking them. The data is stored under /opt/oracle/psft/dpk which is a mount point for “disk2”. Typically this disk expands to 23GB+ during the build process as a result of this approach.

To reduce the size of the VMs, what I like to do after the VM has been built is to:

  1. Attach the disk2, disk3 and disk 4 .vmdk’s to a simple Linux server VM – I use a minimal Fedora 24 install but it doesn’t really matter just so long as the zerofree utility is installed. Note: You could install zerofree into the PeopleSoft VM and do this step using Oracle Linux, but I use a smaller Linux install as it boots quicker.
  2. Boot the VM and mount the three disks read/write
  3. Delete any large files I no longer need e.g. the DPK tgz/zip files, PeopleTools 8.53/8.54 client folders, ptengs.db
  4. Re-mount the disks read only (mount -o remount,ro /dev/sd[bcd]1 {mount-point} )
  5. Run zerofree -v /dev/sd[bcd]1 on each disk to zero the empty space created by the file deletions.

I then close down the VM, detach the .vmdk files and clone the .vmdk disk files to .vdi files using virtual media manager. This has the effect of shrinking the resulting files – essentially doing a “VBoxManage modifyhd {vdi_file} –compact“.

Once I have the .vdi versions of the files, I remove the .vmdk files from the PeopleSoft VM, add back the .vdi files, boot the PeopleSoft VM, test it and delete the original .vmdk files if everything works.

In general, this approach releases approximately 27 Gb per VM – making the resulting VMs around 30-36 Gb. Still absurdly big of course 🙂

Categories
Administration Humour Oracle Peoplesoft PeopleTools Performance Tuning

A Conversation with a PeopleSoft “Developer” – Part 2

Another amusing conversation and somewhat shocking “discovery”.

More poor performing SQL – obviously missing any sort of suitable index (on Oracle).

Me (to developer): Could you add a suitable composite (multi-column) index to table PS_xxxx please for columns C,B and A – there seems to be frequent access using these three columns that would be vastly improved by adding an index.

Developer: OK.

After a few days I notice new “alternate search key” indexes in the DEV environment. One for each of the columns A, B and C.

Me: Did you add that index?

Developer: Yes, but in the testing I did it wasn’t very much faster.

Continue reading “A Conversation with a PeopleSoft “Developer” – Part 2″
Categories
Tuning Windows

TrustedInstaller.exe – Memory and CPU Hog!

What an obscene memory and CPU hog this can be. I’ve seen it grow to 3.2 Gb of RAM on one of my servers.

As it is used for Windows Updates, I stop the related Windows Services and mark them as manual to avoid this issue.

The services to stop/set to manual are:

“Windows Installer Modules Installer”
“Windows Update”

Sure, I have to manually update but I want that on my servers.

Categories
Java Languages Peoplesoft PeopleTools Tuning Weblogic

Weblogic Java VM Memory Parameters

When increasing the Java VM memory parameters for a PeopleSoft system under Weblogic (either in setEnv.cmd or manually by editing the cmdline registry entry if that’s your “thing”), be very careful not to increase the -XX:MaxPermSize memory allocation too much.

In fact I would leave it at 128MB or perhaps 256MB. Whatever you do, don’t update all three parameters in step, especially on a 32-bit Java, as you will hit Out of Memory errors on Weblogic startup way earlier than you expect. The MaxPermSize memory is allocated outside of the heap so the total memory you potentially need is more than you expect.

Categories
CRM Peoplesoft Performance Tuning

RC_BACKLOG_VW in PeopleSoft CRM 9.1

Seriously? Who thought it would be a good idea to create a view to return the DISTINCT list of BUSINESS_UNIT from RC_CASE? Talk about an O (n) performance disaster.

What’s wrong with a SELECT BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_RC WHERE EXISTS (… case sub-query….).

Oh look … Two index seeks … shocker! It scales too …. wow … well I never.

Duh.