A m Z o n e

Oracle Optimizer: Moving to and working with CBO
Part 3

This part will cover the setup changes that should be considered for migrating to CBO.

8. Setup changes for migrating to CBO

I am highlighting some key points that may be considered while moving to CBO and there after maintaining it in good shape. Tuning in CBO is an ongoing process and proper analysis should be done. You may encounter scenario specific to your environment that is 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 the above section 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 what kind of environment you want. These parameters have been covered in details in the 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 datawarhousing systems set it to ALL_ROWS.

Using ALL_ROWS in OLTP system would 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 all the rows before retrieving them. Sometimes it may be even 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. Infact it 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 could be migrated in phases. Problem 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 in increasing the memory allocation parameter sizes by 3-10% to accommodate the additional changes/features and avoid response time issues. You can later on 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
Ok some internal information. The Optimizer mode parameter is meant only for the statements that are directly fired and not for the statements fired from PL/SQL. So if you have tested out your query from SQL*Plus or any other tool, and then incorporating the same in 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 do not influence them.

If you have enabled CBO in your setup, then PL/SQL statements will be defaulted to ALL_ROWS. This is logical as stored procedures will return results only after everything is processed. But it so happens that I often find queries that run best 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 also. So 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 test environment by setting it in the initialization file or at individual session level. As this parameter begins with underscore use double quotes to set it.

SQL> alter session set "_optimizer_mode_force" = true;

Session altered.

Plan name: RESOURCE_SHARING
GROUP             USERS               DIRECTIVE
HIGH_PRIORITY     Online users        Allow 87% x 4 CPU usage. 
                                      This means 87% resource on all the 4 CPUs.

MEDIUM_PRIORITY   Online Report       Allow 10% x 4 CPU usage.
                  generation          This means 10% resource on all the 4 CPUs.
                  programs, batch
                  jobs, Business
                  Objects tool, 
                  Datastage tool, 
                  data upload and 
                  download interface.

LOW_PRIORITY      Adhoc online and    Allow 3% x 4 CPU usage.
                  batch reports,      This means 3% resource on all the 4 CPUs. 
                  Support/Development
                  Team sessions.
                  Maintenance activity.

e.g.: Setting optimizer at session level.
SQL> select value from v$parameter where name = 'optimizer_mode';

VALUE
__________
CHOOSE

SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select value from v$parameter where name = 'optimizer_mode';

VALUE
___________
FIRST_ROWS

e.g.:
SQL> select num_rows, blocks, avg_row_len, temporary, user_stats
  2  from dba_tables
  3  where table_name = 'AM21';

  NUM_ROWS     BLOCKS AVG_ROW_LEN T USE
---------- ---------- ----------- - ---
                                  Y NO

jaDA>exec dbms_stats.gather_table_stats('DATASTAGE', 'TMP_GL_VALUE_DIFF');
BEGIN dbms_stats.gather_table_stats('DATASTAGE', 'TMP_GL_VALUE_DIFF'); END; 
*
ERROR at line 1:
ORA-20000: Gathering statistics for a temporary table is not supported
ORA-06512: at "SYS.DBMS_STATS", line 4481
ORA-06512: at line 1

SQL> exec dbms_stats.set_table_stats(ownname => 'SYS', tabname => 'AM21', -
> numrows => 3000, numblks => 300, avgrlen => 50);

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks, avg_row_len, temporary, user_stats
  2  from dba_tables
  3  where table_name = 'AM21';

  NUM_ROWS     BLOCKS AVG_ROW_LEN T USE
---------- ---------- ----------- - ---
      3000        300          50 Y YES

SQL> select * from tmp_cntx
  2  where  seg4 || '' = '1700019'
  3  and    catseg1 = 'WSCNTX';

SQL> select * from tmp_bad_attribute1
  2  where inventory_item_id = 3666
  3  and   organization_id + 0 = 54;

SQL> select * from tmp_bad_attribute1
  2  where inventory_item_id = 3666
  3  and   nvl(organization_id, 0) = 54;

begin
  ...
      select sum(qty) qty
      into   rec.qty
      from   jncdm_ra_transactions a, ra_customer_trx_all b, ra_cust_trx_types_all c
      where  calendar_month_id = pi_calendar_month_id
      and    b.customer_trx_id = a.customer_trx_id
      and    b.cust_trx_type_id != 1666
      and    a.inventory_item_id = rec.inventory_item_id
      and    a.warehouse_id = rec.warehouse_id
      and    c.cust_trx_type_id = b.cust_trx_type_id
      and    c.type = 'INV';
  ...
end;

declare
  ...
  l_exclude_trx_type_id  number := '1666';
  l_mod_type varchar2(3) := 'INV';
begin
  ...
      select sum(qty) qty
      into   rec.qty
      from   jncdm_ra_transactions a, ra_customer_trx_all b, ra_cust_trx_types_all c
      where  calendar_month_id = pi_calendar_month_id
      and    b.customer_trx_id = a.customer_trx_id
      and    b.cust_trx_type_id != l_exclude_trx_type_id
      and    a.inventory_item_id = rec.inventory_item_id
      and    a.warehouse_id = rec.warehouse_id
      and    c.cust_trx_type_id = b.cust_trx_type_id
      and    c.type = l_mod_type;
  ...
end;