This article highlights a number of key points to consider when moving to CBO. In addition, it highlights a number of good maintenance practices. Tuning in CBO is an ongoing process and proper analysis should be done. You may encounter scenarios specific to your environment that are not mentioned here. Make it a point to refer the documentation and check with Oracle support for any kind of anomalies.

8.1) Set the Initialization parameters properly

The parameters specified in Part 2 are very critical for your setup. Parameters like OPTIMIZER_MODE, OPTIMIZER_MAX_PERMUTATIONS, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING, OPTIMIZER_FEATURES_ENABLE, COMPATIBLE etc. directly affect the optimizer. Please provide appropriate values based on the type of environment you require. Again, these parameters have been covered in details in Part 2.

8.2) Set the optimizer mode properly

Set the OPTIMIZER_MODE parameter to FIRST_ROWS(_nnn) for OLTP systems. For batch processing or data warhousing systems set it to ALL_ROWS.

Using ALL_ROWS in an OLTP system will result in a slight (sometimes negligible) stand still before the data is shown in online screens as Oracle concentrates on completing the query and processing of the rows before retrieving them. On the other hand, the delay may be worse as there are more occurrences of full table scans in ALL_ROWS than in FIRST_ROWS.

You may also consider CHOOSE mode as an intermediate option between RBO and CBO. In fact, CHOOSE tries to run the query in either CBO or RBO, depending on the availability or unavailability of statistics. This is the preferred mode if the system can be migrated in phases. Problems may arise if tables with statistics are being used along with tables without statistics in queries; the Optimizer may sometimes choose bad execution plans in such cases.

If features like partitioning or materialized views are being used, related queries will always resort to CBO mode.

8.3) Provide additional memory

I have found it beneficial to increase the memory allocation parameter sizes by 3-10% to accommodate the additional changes/features and avoid response time issues. Later, you can evaluate the increase or decrease in memory utilization and adjust the parameters accordingly. Parameters DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, JAVA_POOL_SIZE and LARGE_POOL_SIZE should be considered.

8.4) SQL and PL/SQL

The Optimizer mode parameter is meant only for statements that are directly fired and not for statements fired from PL/SQL. Therefore, if you have tested your query from SQL*Plus or any other tool, and then incorporated the same in a PL/SQL block (anonymous or stored routines), the query may or may not run the same! DML statements from PL/SQL are run in CHOOSE mode (ALL_ROWS if statistics is present) by default and OPTIMIZER_MODE set at session level does not influence them.

If you have enabled CBO in your setup, then PL/SQL statements default to ALL_ROWS. This is logical, as stored procedures return results only after everything is processed. However, I often find queries run better in FIRST_ROWS than in ALL_ROWS, the reason being that indexes look more appealing in FIRST_ROWS.

For OLTP systems, this may result in response time issues.

We can prevent PL/SQL DML statements from running in ALL_ROWS by doing the following.

1. Talk to Oracle support and set the parameter _OPTIMIZER_MODE_FORCE to true. This parameter was introduced to force the optimizer mode set at session level to be used in PL/SQL as well. Therefore, if your session is running in FIRST_ROWS, then the recursive SQLs (or PL/SQL statements) will also be executed in FIRST_ROWS.

You may evaluate the importance of this parameter in a test environment by setting it in the initialization file or at individual session level. As this parameter begins with an underscore, use double quotes to set it.

SQL> alter session set "_optimizer_mode_force" = true;
Session altered.

2. Use Hints and direct Optimizer to use a particular mode for individual queries. This is a very powerful option and will become a key inclusion in coding for the CBO environment.

I have come across, and read about cases where setting the _OPTIMIZER_MODE_FORCE to true did not force some specific queries to use the session level mode. Since this is a hidden parameter not much is documented about various scenarios. If you come across such cases, please make use of hints to direct the optimizer.

Previous   Next

Back to DBAsupport.com