Avoid full table scans in Oracle 10g
Oracle 10g uses the cost based optimizer (CBO) and this uses table statistics or dynamic sampling to calculate the cost of a query path. The CBO however may get wrong if the tables get large. If the CBO assumes that a large portion of the table gets returned by a query, it uses a full table scan (FTS). This may be better by small tables but with large tables this can be a really bad idea. It is really bad in these cases:
- The data are not equally distributed and the queries usually return a small portion of the table. CBO thinks the query returns a large portion.
- The table is very large compared to the index and the buffer cache. If you habe 16GB buffer cache and the Index is 1GB but the table has 32GB, a index scan and the data returned may be as large as the buffer cache, but a full table scan is consuming much more resources.
- The data are inserted "in row" compared to the query. The CBO may choose a FTS because it assumes that a Oracle block contains for example 5 rows. So if you get 20% of the table rows, then you would read almost all blocks of the table - so a FTS is no more expensive. But if you select the data in the same order as the rows were inserted, the CBO assumes wrong.
There is a number of other causes why a FTS is used - these are easy to track down and circumvent. Example: use function based indexes if your query contains functions in the where clause.
But the problems above can be avoided by tuning 2 parameters:
optimizer_index_cost_adj
optimizer_index_caching
optimizer_index_cost_adj
The default value is 100. If you lower the value, then the CBO will prefer to use indexes. The cost for reading a index will be lower. Try to lower the value until unwanted FTS disappear. A good starting pint is 50.
optimizer_index_caching
The default value is 0. The CBO assumes that no index is cached. This parameter is used when joins are used (as Oracle documentations says). You should try to find out which percentage of your index is caced. Use the queries in Chapter "7.2.7 Determining Which Segments Have Many Buffers in the Pool" in the "Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)" to query that. Tune the parameter and after some time requery and check if the percentage grows.
Precaution
Always check if you have enough buffer cache to hold index blocks. If this is not the case you may get performance degradation with this hints!
;){kind=link}