PeopleSoft – Kill Application Engine (Gently)

Smart Panda - PeopleSoft in the CloudKill Application Engine (AE)

It amazes me that in the world of these high end databases that simple data handling can go for a crap simply because data was bulk loaded into the system.  Usually during big changes to an environment: data conversions, upgrades,  archiving or other aggressive changes, data will become out of sync with the statistics that are stored on the tables.

What are Database Statistics? In a nut shell it is data about data or  simply metadata. Oracle statistics is metadata about your data. There are several kinds of statistics in Oracle mainly: Object statistics, System statistics and fixed table statistics.

So if you have bad statistics your Application Engine may not be able to execute code correctly.  It will simply appear to be hung.  I have seen processes run for days and it will never return a result because of bad statistics.  After updating the statistics those exact same processes that ran for days will run in seconds, sometimes faster.  Okay, so now the catch is you have to “kill” the process in order for the process to be re-ran and pick up the correct statistics. This isn’t the easiest process in the world to do, because if you try to just cancel the job through the process scheduler it may or may not cancel nicely. However, if you “kill” the SQL at the database level that is hung the application engine for go to ERROR, and you can restart the AE from the last committed point that it executed to.  This is much cleaner than any other method I have found, especially if you have a huge amount of stuff already done by the application engine prior to running into this issue.  Upgrades are notorious for this, which is why they have “update statististics” steps all throughout the upgrade process now.

So in Oracle SQL we need to find the SQL that is causing the issue:

select * from v$session where program like ‘%PSAESRV%’;  –See SQL running by Application Engine

select * from v$sql where sql_id = ‘SQL_ID from v$Session’;  — Confirm you have the write SQL

alter system kill session ‘{sid},{serial#}’;

It may take a few minutes to find the SQL causing the problems, but don’t worry, it will still be running after you find it.  Once it is killed, the AE will go to error, and you can restart it.  It will start up at the last commit point, which you can usually see if you query the AE Control table:

select * from ps_aeruncontrol;

Rerun your statistics for the schema or for the tables in question.

exec dbms_stats.gather_schema_stats(‘SYSADM’, cascade=>true);

Good luck out there!

Oracle Performance Secret

Oracle Database: Oracle Performance Secret

Smart Panda - DatabaseWhen working with a new Oracle Database on a Windows 2012R2 server running Oracle  The PeopleTools Upgrade process was on the last step which is to do a Final Alter Audit (FNLALTAUD).  This Upgrade Step looks at all the tables within the system and determines if the metadata is in sync with what is actually in the database.

14 hours into the process and has to be done, but as per usual it is next to impossible to tell with PeopleSoft.  Since this really should not take anymore than an hour or two, the process is killed and the following Oracle Performance Secret is executed in SQLPlus:

analyze table sys.CDEF$ delete statistics;
analyze table sys.CON$ delete statistics;
analyze table sys.USER$ delete statistics;

Rerunning the Upgrade Task after apply the Oracle Performance Secret and it is now running in:  29 minutes.  That is much more acceptable.

What is the SYS Schema?

The SYS schema stores all of the base tables and views for the Oracle Database’s Data Dictionary.  The Data Dictionary is critical to the operation of Oracle Database.  It is imperative to maintain the integrity of the data dictionary, so manipulation of the Data Dictionary should only be done by Oracle itself.

Where did this fix come from?

Smart Panda - IdeaDuring upgrades of environments there has been some reported cases of very poor performance using Oracle PeopleSoft Data Mover to import data into the new environment.  We have experienced this problem while upgrading environments specifically the Final Table Audit (or Initial Audit) Upgrade Tasks.  PeopleSoft has a very high number of constraints (Finance v9.2, I just queried had 2.7 million rows), which are stored on the CDEF$ & CON$ Data Dictionary Tables. If the Cost Based Optimizer (COB) has bad statistics to work from it is often seen that the optimization plan will come up with Full Table Scans which are extremely poor performing.  Patch #4618615 & #5103126 from Oracle apparently address this issue, and so the problem should be limited to Oracle Database 10.2 to 11.0.  However, often times when poor performance is effecting an environment we apply this Oracle Performance Secret and performance typically is greatly improved.