Cost based optimizer - common problems

Most common problems of a cost based optimizer are the following :

1. Skewness problem (30 % of the time)

Sometimes the optimizer is aware of how many distinct values are there for a particular column but is often unaware of how many rows exist for each of those columns. It then assumes that the values are evenly spread in the table (it may do a full table scan a result). To make Oracle aware of the skewness, analyze all indexed coumns like the following :

ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS

2. Analyzing with wrong data (25% of the time)
Sometimes a table is heavily modified after collecting statistics, this can make the previously collected statistics meaningless since those stats were based on different type (or volume) of data.

After heavy modifications or table rebuilt, fresh statistics should be collected

To check when a table was last analyzed :

select table_name, num_rows, last_analyzed from user_tables

select table_name from all_tab_columns where column_name = 'LAST_ANALYZED'

3. Mixing optimizers in joins (20% of the time).
Sometimes in queries in which tables are being joined, and one table join is analyzed and other tables are not. This can make cost based optimizer to perform at its worst. Make sure all the objects that you are joining have been analyzed.

4. Choosing an inferior index (20% of the time).
When referencing many columns of a table in a WHERE clause, using a composite index involving all the referenced columns can provide much better performance than using seperate indexes for each columns.

If the composite index is unique, then it can provide even better performance.

If serveral of the columns in an index have lower number of distinct values, the optimizer will bypass the use of index for an SQL referencing most of the columns.

5. Joining too many tables (less than 5%of the time)
When joining too many tables, try to make sure (if possible) that these tables are joined with each other using primary key of unique key columns. This will ensure that the joing dont return too many rows cost lots of performance.

If you are using a third-party package, your best option may be to create a view with a hint, if that is allowable and possible with the package you are using.

6. Incorrect setting in parameter file (less 5% of the time)
Certain parameters can influence the database performance. The interesting ones are the following :

SORT_AREA_SIZE
The number of bytes allocated on a per-user session basis to sort data in memory. If the parameter is set at its default of 64K, NESTED LOOPS will be favored instead of SORT MERGES and HASH JOINS.

HASH_AREA_SIZE
The number of bytes to use on a per-user basis to perform hash joins in memory. The default is twice the SORT_AREA_SIZE. Hash joins often will not work unless this parameter is set to at least 1 megabyte.

HASH_JOIN_ENABLED
Enables or disables the usage of hash joins. It has a default of TRUE, and usually doesn't need to be set.

OPTIMIZER_MODE
May be CHOOSE, FIRST_ROWS, or ALL_ROWS. CHOOSE causes the cost-based optimizer to be used if statistics exist. FIRST_ROWS will operate the same way, but will tend to favor NESTED LOOPS instead of SORT MERGE or HASH JOINS. ALL_ROWS will favor SORT MERGEs and HASH JOINS in preference to NESTED LOOP joins.

DB_FILE_MULTIBLOCK_READ_COUNT
The number of blocks that Oracle will retrieve with each read of the table. If you specify a large value, such as 16 or 32, Oracle will, in many cases, bias towards FULL TABLE SCANS instead of NESTED LOOPS.

OPTIMIZER_MODE_ENABLE
Enables new optimizer features to be enabled. For example, setting the parameter to 8.1.7 will enable all of the optimizer features up to and including Oracle 8.1.7. This parameter can also automatically enable other parameters such as FAST_FULL_SCAN_ENABLED. Some of the major improvements that have occurred with the various Oracle versions include: 8.0.4 (ordered nested loops, fast full scans), 8.0.5 (many, many optimizer bug fixes), 8.1.6 (improved histograms, partitions, and nested loop processing), 8.1.7 (improved partition handling and subexpression optimization), and 9.0.1 (much improved index joins, complex view merging, bitmap improvements, subquery improvements, and push join predicate improvements).

OPTIMIZER_INDEX_CACHING
Tells Oracle the percentage of index data that is expected to be found in memory. This parameter defaults to 0, with a range of 0 to 100. The higher the value, the more likely that NESTED LOOPS will be used in preference to SORT MERGE and HASH JOINs. Some sites have reported performance improvements when this parameter is set to 90.

OPTIMIZER_INDEX_COST_ADJ
This parameter can be set to encourage the use of indexes. It has a default of 100. If you lower the parameter to 10, you are telling the cost-based optimizer to lower the cost of index usage to 10% of its usual value. You can also set the value to something way beyond 100 to force a SORT MERGE or a HASH JOIN. Sites report performance improvements when the parameter is set to between 10 and 50 for OLTP and 50 for decision support systems. Adjusting it downwards may speed up some OLTP enquiries, but make overnight jobs run forever. If you increase its value, the reverse may occur.

STAR_TRANSFORMATION_ENABLED
Causes a star transformation to be used to combine bitmap indexes on fact table columns. This is different from the Cartesian join that usually occurs for star queries.

QUERY_REWRITE_ENABLED
Allows the use of function-based indexes as well as allowing query rewrites for materialized views. The default is FALSE, which may explain why your function indexes are not being used. Set it to TRUE.

PARTITION_VIEW_ENABLED
Enables the use of partition views. If you are utilizing partitioned views, you will have to set this parameter to TRUE because the default is FALSE. A partition view is basically a view that has a UNION ALL join of tables. Partition views were the predecessor to Oracle partitions, and are used very successfully by many sites for archiving and to speed performance.

PARALLEL_BROADCAST_ENABLED
This parameter is used by parallel query when small lookup tables are involved. It has a default of FALSE. If set to TRUE, the rows of small tables are sent to each slave process to speed MERGE JOIN and HASH JOIN times when joining a small table to a larger table.

OPTIMIZER_MAX_PERMUTATIONS
Can be used to reduce parse times. However, reducing the permutations can cause an inefficient execution plan, so this parameter should not be modified from its default setting.

CURSOR_SHARING
If set to FORCE or SIMILAR, can result in faster parsing, reduced memory usage in the shared pool, and reduced latch contention. This is achieved by translating similar statements that contain literals in the WHERE clause into statements that have bind variables.The default is EXACT. We suggest that you consider setting this parameter to SIMILAR with Oracle9i only if you are certain that there are lots of similar statements with the only differences between them being the values in the literals. It is far better to write your application to use bind variables if you can.Setting the parameter to FORCE causes the similar statements to use the same SQL area, which can degrade performance. FORCE should not be used.Note that STAR TRANSFORMATION will not work if this parameter is set to SIMILAR or FORCE.

ALWAYS_SEMI_JOINT
his parameter can make a dramatic improvement to applications that make heavy use of WHERE EXISTS. Setting this parameter to MERGE or HASH has caused queries to run in only minutes whereas before they had used up hours of runtime. The default isSTANDARD, which means that the main query (not the subquery) drives the execution plan. If you specifically set this parameter, the subquery becomes the driving query.

ALWAYS_ANTI_JOINT
his parameter will change the behavior of NOT IN statements, and can speed processing considerably if set to HASH or MERGE. Setting the parameter causes a merge or hash join rather than the ugly and time-consuming Cartesian join that will occur with standard NOT IN execution.Remember that if any of these parameters are different in your pre-production database than in your production database, it is possible that the execution plans for your SQL statements will be different. Make the parameters identical to ensure consistent behavior.