Tuesday, May 24, 2011

PeopleSoft, Oracle and an unlucky DBA

If you're an Oracle DBA, chances are you have to deal with PeopleSoft databases. They're huge, complex databases that suddenly run out of temp space or increase many times their usual processing time, driving the Oracle DBA crazy in the process.

In short, in order to support other database engines PeopleSoft uses normal tables as temporary tables and tags them in the PSRECDEFN table as rectype=7, therefore you cannot just gather object statistics as usual because temporary tables grow a lot and get empty very fast. First of all, you should use the Appendix A script of PeopleSoft Enterprise Performance on Oracle 10g Database Red Paper, and if you read the whole document it will be worth the time.

This is what I know first-hand. The problem is that sometimes this special procedure of gathering statistics is not enough and you get the problems mentioned above anyway, but some fellow Oracle DBA after analyzing a lot of AWR reports, concluded that it helps gathering statistics of these objects:

DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'SYSADM',
TABNAME => 'PS_GP_ITER_TRGR',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.AUTO_DEGREE,
CASCADE => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'SYSADM',
TABNAME => 'PS_GP_RUNCTL',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.AUTO_DEGREE,
CASCADE => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'SYSADM',
TABNAME => 'PS_GP_CAL_RUN',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.AUTO_DEGREE,
CASCADE => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'SYSADM',
TABNAME => 'PS_GP_PYE_PRC_STAT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.AUTO_DEGREE,
CASCADE => TRUE);

DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'SYSADM',
TABNAME => 'PS_GP_HST_WRK',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
DEGREE => DBMS_STATS.AUTO_DEGREE,
CASCADE => TRUE);

analyze index SYSADM.PS_GP_PYE_SEG_STAT compute statistics;
analyze index SYSADM.PSAGP_PYE_PRC_STAT compute statistics;
analyze index SYSADM.PS_GP_PYE_PRC_STAT compute statistics;

I don't know for sure why this procedure solves some performance and temp space issues, but it works for me and the Oracle DBA that wrote this procedure is very good so it might work for you as well.

We put this procedure as a daily job in one PeopleSoft database and in general works fine, but even so we get problems from time to time with that database; this leads me to think this procedure should be run before scheduling a big or complex PeopleSoft job because upon running this procedure there is no problem at all. I mean, if you get two or more PeopleSoft complex tasks in one day it would be better to run this procedure before running each PeopleSoft process.

No comments:

Post a Comment