Working with Performance Monitor Tables
As with any PeopleTool or PeopleSoft application, the underlying application definitions and application data reside in a collection of database tables that are designed using Application Designer. Although most PeopleSoft applications provide data models that show the relationships between the database entities, typically, for PeopleTools, knowledge of the underlying database tables is not required.
However, with the Performance Monitor, knowledge of the underlying database tables may be required. For example, the Performance Monitor interface provides numerous options to use when you are viewing performance data, such as viewing by time range, viewing by user, viewing by component, and so on. In some cases, you may want a more customized view of your performance data than what the interface offers.
You can use PeopleSoft Query or your SQL tool of choice to build queries that run against the Performance Monitor tables and return the specific information that you require.
To assist you in creating custom queries, the Performance Monitor data model appears in the form of an entity relationship diagram (ERD) that is posted on My Oracle Support. Refer to the PeopleTools Release Notes for this release for the current location of the Performance Monitor ERD.
See Performance Monitor Database Schema and Use Cases on My Oracle Support.
Note: The Performance Monitor database schema may change in future releases.
To view the results of sample queries running against the Performance Monitor tables, select To view the definitions and SQL of these sample queries, use PeopleSoft Query Manager. The sample queries attempt to show a realistic query while using all of the tables that you may want to include in similar queries.
The sample query definitions are:
|
Query |
Description |
|---|---|
|
PPM_COMP_BUILD_CACHE |
This query returns all application server requests for a specific system that had to retrieve metadata from the database as opposed to the cache. It also shows the file cache and memory cache for comparison. This query returns information from the PMU history table. |
|
PPM_COMP_BUILD_CACHE_ARCH |
This query is similar to PPM_COMP_BUILD_CACHE, except that it returns information from the PMU archive table. |
|
PPM_TIMEOUT_SQL_REQ |
This query returns information from the PMU history table while joining information that is stored in the event table. This query retrieves all PMU 400s (Tuxedo Service PCode and SQL) that were running SQL statements when an Event 500 (Jolt Service Exception) was received. It is assumed that this exception occurred because of a timeout, but it could also have been due to an application server outage or a Jolt error. |
|
PPM_TIMEOUT_SQL_REQ_ARCH |
This query is similar to PPM_TIMEOUT_SQL_REQ, except that it returns information from the PMU and Event archive tables. |
|
PPM_APPSRV_START_COUNTS |
This query returns starting counts for different server processes over a period of time for a specific domain. |
|
PPM_APPSRV_START_COUNTS_ARCH |
This query is similar to PPM_APPSRV_START_COUNTS, except that it fetches information from the event archive table. |
Note: When you are running a sample query, the system prompts you to enter a date. The format for the date is MM/DD/YYYY HH:MM:SS AM/PM. For example, 09/03/2003 12:00:01AM.