...
- 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 03:00 - 04: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)
Activity Log
Sunday 10-14-2007
Satruday 10-13-2007
- 19:30 (Drew) I remember I mentioned the parameter OPTIMIZER_INDEX_COST_ADJ and you answered that you have tried to lower the parameter to as
low as 20 and it did not work. I did some my own tests today with tables that have 168K rows and a query that has similar structure as the CM
query we discussed, i.e, all rows in the select statement comes from one table and none from the other table. The purpose of my tests was to
see if adjust the OPTIMIZER_INDEX_COST_ADJ can change the execution plan. I adjust that parameter using "alter session"|| so that other session
will not be affected. The parameter can also be adjusted on system level, which will affect all the sessions in the database. The index on
PROVIDER_ID is not a unique index. The following are my test results.
The first two tests were when the parameter set to 100. Those two tests show when rows are selected from one table, that table will be full
table scanned. Then I start to adjust the OPTIMIZER_INDEX_COST_ADJ. Just as what you have said, as low as 20 will not change the execution
plan. In my case, even as low as 5 still does not use the index. So, I adjust that parameter to 1, the lowest possible value. The execution
plan finally pick up the index and no more full table scan. The detail tests steps are shown below.
I am not suggesting anythings here to you and I just want to share my test results, in case you are interested in doing similar tests on your
query. Since I don't know if the modification of the parameter will change other queries' behavior, I think more tests have to be done to
determine if it's good or bad. In my case, it only needs about 10 minutes to do all the tests.
– OPTIMIZER_INDEX_COST_ADJ
Friday 10-12-2007
- 15:57 (Patrick) The way things are currently configured on caje, the 5 tables will be switched at 6am and 6pm each
day. The autostats window is now set for 5am Monday-Friday. That means the tables will have stats on them for at
most one hour.
...