Proposed Solutions
- As we learned from today's Oracle Tuning class, disabling Automatic Storage Management (ASM) could potentially fix our index problems
- Change Oracle's statistics gathering function to run between 0403:00 - 0504:00 instead at 22:00
- Oracle assumes to be the only active user during stats gathering
- All the tables, indexes, views need to be created in the same order via scripts
- Switch to RBO (Rule Based Optimizer) for certain views
- Eventual deterministic/stable performance after queries have been tuned
- Need to be careful that we're not joining these non-analyzed tables to analyzed ones
- We may need to switch our OPTIMIZER_MODE setting over to CHOOSE?
- Keep in mind after rebuilding the CM tables there are only 2 tables and 2 queries that run into trouble regularly. We should be careful not to implement DB wide strategies to attack these 2 problems. We need to understand why they differ and see if they can be tuned. (KA)
...