8 May 2013
There is nothing about query optimization that is ever black or white; just many, many shades of grey. This is the case with the two index-related database parameters: optimizer_index_cost_adj and optimizer_index_caching. The one thing I can say with confidence is that the default values are not ideal for Primavera P6 OLTP. If these parameters are set to the default values, chances are you will see some high Physical I/O (PIO) in the database.
The P6 transactional database (PMDB) is not used like any typical OTLP database. The P6 application uses the database more like a hybrid between an OLTP and DSS (Decision Support System) database, with queries that often join many tables and return thousands, or tens of thousands, of rows.
Read the full article here...