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.sqlfrom 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.