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