Identifying Appropriate Indexes

Following is the system analysis you need to do before you implement special indexes.

Now that you know the indexes that General Ledger creates, determine whether the delivered indexes are suitable or you need additional indexes. Changes to the ChartFields, changes in configuration, and differences in data content all affect the indexes and their effectiveness.

Capturing SQL Statements

First, determine the indexes currently used by your system. Do this by capturing the SQL statements executed by the system, then running them in isolation to determine the database access path for retrieving the data. For either realtime online access or batch processes, you can identify the SQL statements that access the ledger and journal line tables and whose performance might be a concern. Refer to PeopleTools documentation for information about turning on the SQL trace for online and batch processes.

See PeopleTools documentation: PeopleSoft Process Scheduler

See PeopleTools documentation: System and Server Administration

Establishing a Baseline

Next, determine the efficiency of your current indexes; you need to establish a method for measuring progress as you make changes. A baseline timing is generally used for comparison when trying out different indexes. Time either the individual SQL statements or the entire process, so long as you have some way of determining progress as you proceed with the tuning.

Determining Indexes Used

You have a list of processes that access the primary records. You now need to determine which indexes each process currently uses. In other words, you need to determine the database access path that the system takes when the statement is actually executed. Because the database access path might change according to differing volumes of data, it is important to execute the plan on approximately the same amount of data that the table contains in a production environment. It might be appropriate to take a copy of your production database specifically for the purpose of tuning the indexes. Generally, when obtaining plan information, you are not actually executing the statements; check your database administrator documentation to be sure this is the case before executing any statements in your production environment.

Each platform has a process for determining the database access path that the engine uses to access data for the SQL statement. Illustrated below is a brief outline of the DB2 approach.

Note:

Refer to your database administration documentation for your platform and consult with your database administrator.

If your system is on DB2, create a PLAN_TABLE if your database does not already have one. A sample CREATE statement is in your DB2 Performance Tuning documentation.

Include the SQL statement in the following and execute it:

DELETE FROM PLAN_TABLE WHERE QUERYNO=nnn;
EXPLAIN PLAN SET QUERYNO=nnn FOR
statement;

In this statement, nnn is a number you assign to this statement.

Retrieve the plan from PLAN_TABLE with the following SELECT:

SELECT QBLOCKNO, PLANNO, TNAME, ACCESSNAME, METHOD, 
ACCESSTYPE, MATCHCOLS, INDEXONLY, PREFETCH, SORTC_GROUPBY
FROM PLAN_TABLE
WHERE QUERYNO=nnn
ORDER BY QBLOCKNO, PLANNO;

The table contains other plan information, but these are the most pertinent columns for your purposes.

Note:

If your system is running Oracle, consult with your database administrator to create a plan_table if your database does not already have one.