Oracle Database: Oracle Performance Secret

When working with a new Oracle Database on a Windows 2012R2 server running Oracle 11.2.0.4.  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?

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

Smart Thinking Newsletter

Choosing the Right ERP Software for Your Business

Investing in ERP software is a huge decision, one that has long-lasting consequences for your business and it’s bottom line. Learn about what you should look for when evaluating your ERP software options, including versatility, customization, and reporting

Smart Thinking Newsletter

Is your ERP clock running out?

Older ERP Systems may feel like they can go forever, but as they age a plan for replacement needs to be considered – to improve reliability, increase usability, and get your systems ready for the next generation of doing business. Check out some tips, courtesy of…

I was putting a Campus Solutions Bundle on a system for a client and as per usual PeopleSoft delivered an emergency ISIR patch to follow up.  However, this time when I went to apply the ISIR patch after the fact Change Assistant was complaining that the pre-requisite bundle was not applied yet.  Now I know my memory is bad, but it was just the other day, I know I put the patch on this environment.

First, I thought cache, so I stopped all the agents and all the environments cleared the cache and reset the PSEM Agents & Hub.  Fired it back up and still the same error.  I even reset the GUID on PSOPTIONS.

I checked the PS_MAINTENANCE_LOG and clearly the update I put on is there, however, it looks a little different on one of the fields for other bundles.  The FIXOPRID for the bundle was PS and not PPLSOFT like the other bundles.  Sure enough I did a

UPDATE PS_MAINTENANCE_LOG SET FIXOPRID = ‘PPLSOFT’ WHERE UPDATE_ID = ‘{CSBundle##}’

Re-ran the check and sure enough all is right in the world again.  All pre-requisites need to have the FIXOPRID be PPLSOFT otherwise things don’t go as planned.

Here is an oldie that loves to surface every once in awhile.  I was fixing some Windows Process Schedulers today so that Crystal would work properly.  I was able to get DEMO working but when I went to one of the test environments I got a connection error, login was denied.  I always love how there are some crazy oddities to 3rd Party programs with PeopleSoft.  Firstly, Crystal is still a 32 bit application even though as of 8.53 PeopleTools all the PeopleSoft connections are 64 bit.  If you are wondering yes SQR is still 32 bit as well. 🙂

However, the login was being denied because there was multiple entries in the PSACCESSPRFL table, and several of them were invalid, and Crystal likes to “randomly”  pick an entry from the table as it does not have a where clause associated with its connection.  So in this case I simply changed all the symbolic ids to use the same accessid and password, however, if you really do need to have multiple accessids, make sure they have the right access.