Performance Tuning the Performance Monitor

I am a big fan of the PeopleSoft Performance Monitor, but the more often I use it I discover it has a few rough edges. Some of the analytics components do not perform well when their is either a large amount of data in the PSPMTRANSHIST table, or when there are a lot of agents in a system. So I have used the Performance Monitor to trace the Performance Monitor
components.

You have to configure it to self-monitor, ignoring all the warning messages.

But, you are not interested in the sampled statistics on this database, so disable monitoring by setting the filter level on all agents to standby.

Instead enable Performance Trace with either Verbose or Debug
The component trace indicates which analytic queries take the most time.

I have changed and added the following indexes to the performance tables.

CREATE INDEX PSDPSPMEVENTHIST ON PSPMEVENTHIST
(PM_EVENT_DEFN_SET, PM_EVENT_DEFN_ID, PM_AGENTID, PM_AGENT_DTTM)
TABLESPACE PSINDEX ... PCTFREE 1 COMPRESS 3
/
CREATE INDEX PSCPSPMTRANSHIST ON PSPMTRANSHIST
(
PM_TRANS_DEFN_SET,PM_TRANS_DEFN_ID, PM_PERF_TRACE,PM_METRIC_VALUE7
/*,PM_MON_STRT_DTTM */
) TABLESPACE PSINDEX ... PCTFREE 1 COMPRESS 4
/
CREATE INDEX PSDPSPMTRANSHIST ON PSPMTRANSHIST
(PM_TOP_INST_ID, PM_TRANS_DEFN_SET, PM_TRANS_DEFN_ID)
TABLESPACE PSINDEX ... PCTFREE 1
COMPRESS 3
/

The Performance Monitor tables (PSPMTRANSHIST, PSPMTRANSARCH, PSPMEVENTHIST, PSPMEVENTARCH) are never updated. The performance collator application server process (PSPPMSRV) inserts data into the performance monitor tables, and later the performance monitor archive Appliction Engine process (PSPM_ARCHIVE) will insert them into the %ARCH tables, and delete them from the %HIST tables. Therefore, it is sensible to pack the data by minimising free space in these tables, and so reduce both logical and phyiscal I/O. So
I have reduced the free space in the data blocks (PCTFREE) to 0% on tables and indexes, and increased PCTUSED to 99%.

I have also found it beneficial to collect histograms on certain columns

BEGIN
sys.dbms_stats.gather_table_Stats
(ownname => 'SYSADM'
,tabname => 'PSPMTRANSHIST'
,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt => 'FOR COLUMNS PM_PERF_TRACE, PM_TRANS_DEFN_ID,
pm_metric_value7'
,cascade => TRUE
);
END;
/

If you have performance problems with Performance Monitor, remember that you can also use Performance Monitor to analyse its own analytics.