Optimizer Cost Parameters
By Brian Diehl on May 03, 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. To make it even more difficult for the optimizer, the queries usually do not specify exact bind criteria. Look at this example of a partial query:
select t.* from project p, projwbs pw, task t where p.proj_id = t.proj_id and pw.proj_id = p.proj_id and pw.proj_node_flag = 'Y' and t.proj_id in (select proj_id from projshar where session_id = :b1)
The specific PROJ_IDs, for any given execution, are not known at parse time. Even bind variable peeking will not reveal the truth. The cardinality for the join to t.proj_id is based on the cardinality for TASK.PROJ_ID and is an average that skews low as a result. Despite this happening (low cardinality), we often see queries using HASH JOINS and FULL SCANS. That seems odds since the cardinality calculations are lower that the expected values on execution.
The reason for the bad optimization is the settings for the two optimizer parameters.
optimizer_index_caching (link) - This is the percentage of index blocks the optimizer expects to find in the cache. The default value is zero. Yes, zero. That means the default is that the optimizer assumes all the index blocks will need to be physically read from disk. This is almost certainly not the case given the size of P6 database and the amount of SGA we can allocate today. Recommendation: Set value to 90. This reduces the CBO calculation for cost of index operations. (To be clear, these settings do nothing to reduce the actual execution cost; just the cost calculated by the optimizer when parsing.)
optimizer_index_cost_adj (link) - This parameter is less specific than the previous. It is a blanket cost modifier for index access. It can be tuned lower or higher. The lower value will make index access path look less expensive. The default value is 100. Recommendation: Set value to 1.
The settings can be adjusted using the following:
alter system set optimizer_index_caching=90 scope=both; alter system set optimizer_index_cost_adj=1 scope=both; alter system flush shared_pool; --Clear previous plans from the cache.
A major warning! As with any changes that affect query optimization, it is paramount to TEST, TEST and TEST again. At least these settings are easily adjusted or change back to the original value. Either test in a copy of the production database, or just test in production with the parameters temporarily set (using scope=memory).
These recommendations are specific the the P6 OLTP (PMDB) database only. They do not apply to the data warehouse.