Re: Oracle optimization

>>>

2. When switching from rule-based to cost-based optimization some queries involving subqueries performed MUCH slower. Is this typical of subqueries or do we need to take other factors into account? <<<

When you switch from rule to cost based I advise the following: 1) Look for tables that due to heavy insert and delete activity the number of rows can vary significiantly from one day to the next. Work and temporary type tables fall into this category quite often. The statistics for these tables need to be generated when the table is at a typical size.

2) Do not run mass statistics updates. It will mess up those tables that fall into category 1 above.

3) Make use of hints, in particular, the index and rule hints. The cost optimizer seems to underway indexes in 7.1.6 and lower. It may be better in 7.2 or 7.3.

4) Always compare the default cost plan to the rule plan and use hints to try to recreate the rule plan to be able to compare the cost values.

5) Do not use the default 'estimate' factor (You have no choice in 7.1.3 and below as the analyze syntax in the 7.0 and some 7.1 SQL manuals is wrong, and when the correct syntax is entered the database ignores it.) But with 7.1.6 and hopefullly higher the 'analyze table owner.table_name estimate statistics sample N rows;' works. I recommend you use at least 5000 rows. The default is around 1064. For a lot of tables it is OK, but I have seen it miss by a country mile on row counts and on distinct column value counts.

I hope this is useful.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sat Dec 14 1996 - 00:00:00 CST