Rebuilding the P6 EPPM Index Table

Indexes can become skewed if you frequently access parts of the index and not others. As a result, disk contention may occur and create a bottleneck in SQL performance. To prevent this performance degradation, you should monitor your P6 EPPM indexes and rebuild if necessary.

You can use the analyze_P6EPPM_indexes.sql script to compute statistics on the P6 EPPM index, validate the index structure, and return a report that includes the following information:

The number of values in the index that exceed 100.
Deleted entries in the index that represents 20% or more of the current entries or the index depth is more than four levels based on index_stats result

The indexes that are returned from this report should be considered for a rebuild as they could represent a skewed tree structure and can lead to unnecessary database block reads of the index.

To run analyze_P6EPPM_indexes.sql:

Download analyze_P6EPPM_indexes.sql from https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=1327603.1&attachid=1327603.1:ANALYZE_INDEXES&clickstream=yes.
Save the script to your local database server.
Open a command prompt and then change the directory to the location of the script.
From the command line, run the following scripts and provide the password when prompted:
sqlplus <admuser>@<db_tns_names_entry>

@analyze_P6EPPM_indexes.sql

If you need to rebuild an index, you can run the following script:

ALTER INDEX <Index_Name> REBUILD ONLINE;

Where: <Index_Name> is the name of the index returned in the report.

Related Topics