I am working on Oracle 8.0.6 and the Init file has the Optimizer_mode set to = CHOOSE .
I did an estimate of statistics and the registration of the webiste became very slow .
It became fine when I gave the command " Analyze table tablename delete statistics"
I want to know under what conditions the analisation of tables can be disadvantageous .
Although Oracle recommends to use cost optimizer,
but as I know, in the industry, there are a lot shops
are still using rule base optimzer.
We are using rule base optimzer and running
Oracle 8.0.6 on Sun Solaris.
I heard there are a lot of Oracle applications are still
using rule. :-(
I noticed that having the optimizer_index_cost_adj parameter a bit high sometimes makes performance suffer. This
gives the optimizer a high cost associated with using an index.
A good number to set it at would be 20.
This can be done at the database level by modifying the init.ora, or just for a sql session. To do it for a sql session run the following at an sql prompt:
alter session set optimizer_index_cost_adj=20;
This will set the parameter for your entire sql session.
After doing this you should be able to run the code without the hints. I suggest making the parameter change in the init.ora file for the whole
Anyway, normally (like in our case it was a default value which was high) it is not listed in the init.ora file.
This helps sometimes and it did in our case. I normally compute statistics because the difference is worth the extra wait.
I am curious about:
What is it used for?
Even though there are statistics, sometimes our index is faster. The higher the value, the more it would disregard our index.
Anyway, we were running queries and could see that it was not going faster. We used hints to force usage of our index and wham it was great. We wanted the system to consider our index even without hints, so I tried changing per session, and it was great.
Anyway I am not sure if it would address your problem, but there is so much new stuff (and fewer people that know about it), I just thought it was worth a check out.
Good luck, and let me know if it helps.
Yeah, since I can't find the definition from my doc,
I will appreciate if you post the explaination of it.
You know from Oracle doc, I can find
OPTIMIZER_MODE specifies the behavior of the optimizer. When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)
You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time.
For more information about tuning SQL statements, see Oracle8 Tuning. For more information about the optimizer, see Oracle8 Concepts and Oracle8 Tuning.
It could also be that in this case, the estimate wasn't large enough. I'd try doing a compute statistics and see if that helps. If that still doesn't force the use of the index, I'd look and see if you need to create histograms on the column(s) that the index references.
When you deleted the statistics it forced the CBO to default back to rule_based optimization.
As was said earlier, you can use the parameter
optimizer_index_cost_adj = ## which will influence how much weight is given to indexes when computing the cost for using indexes. Oracle typically responds to users complaining that the CBO isn't using their indexes correctly will tell users to set the value to 10 and if it works then start adjusting it until it seems to be selecting the best access path.
You could always use the CBO and use a hint to influence the particular query as well.
Senior Database Administrator
An additional comment on RBO vs. CBO
Oracle itself actually continues to user RBO. Oracle
does not support (at least this used to be the case)
analyzing the system tables.
Typically in an OLTP environment you want to use
first_rows because it optimizes to return the first
row as quickly as possible, while it may take longer
to retrieve the rest of the rows. It is typical in these
environments for a user to want to see only a single
In a DSS environment you typically are returning
many rows during a query and very rarely are only
returning a single row. Most of the queries involve
full table scans vs. indexes (one of the reasons Oracle
put in the CBO was to deal with the fact that with
queries that are returning a large percentage of table
rows it is better to do a full table scan than using an
index - in RBO, a full table scan is the absolute last
resort). In the case of these types of queries, you
don't care about how quickly the first row is returned
(best response time) but rather how much resource
is used to return the entire data set (best throughput).
Just as a little extra....
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key w/ unique or primary key
4. Single row by unique or primary key
5. Cluster join
6. Hash cluster key
7. Index cluster key
8. Composite key
9. Single-column indexes
10. Bounded range search on indexed columns
11. Unbounded range search on indexed columns
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table scan
The RBO will choose the lowest numbered access path possible.
There are also several unranked access paths (can't be used with RBO)
Fast full index scan
Bitmap index scan
Senior Database Administrator
This is just because I came across an explanation in my manual (oracle 8i DBA Handbook) pg 919- Appendix B:
Default is 100; sets a percentage multiplier (0 to 10, 000) by which the optimizer should adjust the cost of index_based accesses. Setting a value below 100 will cause index accesses to have a lower estimated cost, and therefore be more likely to be chosen"
Hope this helps.
do not generate statistic if data is 'wrong'
CBO works by its own rules which makes decisions using statistics. If statistics are wrong, then it builds wrong plans. Recently I had this problem:
table had a lot of test records with the same column f.k. value. CBO desided not to use the index just because it found out this index has only 7 distinct values for 500.000 records.
So, because w/o statistics CBO uses rule, then it is better to clean statistcsif one is not sure about them: CBO features still are available, like partition pruning, but no messing with 'wrong' data.
Click Here to Expand Forum to Full Width