Oracle Optimizer: Moving to and working with CBO: Part 3 - Page 3

8.9) Statistics for SYS schema

The general rule is to not generate statistics for SYS schema if you are on Oracle 8i. In Oracle 9i, you will have to test this out in your setup to arrive at a conclusion. The dictionary will need to be analyzed in Oracle 10i, as RBO will be unsupported then.

Please refer Section 13 (Statistics for SYS schema) to be covered in subsequent part of the series.

8.10) Using DBMS_APPLICATION_INFO

This package allows you to insert your application specific information into the dynamic dictionary tables. This is very handy for implementing certain logics and for analyzing and tuning. I mention this package here mainly for analyzing and tuning purposes. By using this feature, you can integrate your application into the database and find vital information at any point of time, such as what is presently running in the database and from which part of the application a particular query is being executed and what the user sessions are doing.

Any time a performance issue arises, look at the application specific information to identify the problematic area instantly! Application specific information is set in dictionary tables such as V$SESSION (MODULE, ACTION, CLIENT_INFO columns).

8.11) Provide sufficient time for each site to settle down

If you are supporting multiple client installations, my recommendation is to consider migrating each setup on different dates. Each site may have its own unique issues relating to individual setups, and this will give you more time to examine performance issues at each site. Make sure that testing is done on individual site test boxes before moving the production box to CBO.

8.12) Change your scripts!!!

Most DBAs rely on scripts. These may be outdated. For example, include columns such as LAST_ANALYZED, MONITORING, GLOBAL_STATS and USER_STATS in scripts that look at the object information. Modify your tuning scripts to find out in what mode the database, session or particular queries are running.

8.13) Coding habits and Technical Guidelines

Something you can co-relate to when moving from RBO to CBO. I had to change my coding habits when I moved from RBO to CBO. Oracle says that most of the RBO code will thrive in CBO and changes are not major. However, coding habits will have to be altered for better output on CBO and for easy maintenance of the code.

Moving to CBO opens many new features for developing and designing, something that may not be present in your Technical Documentation Guidelines (if you have one) that developers rely on for standards. Liaise with the complete team to update your conventions.

In RBO, we have a habit of ordering tables right-to-left in queries, right being the driving table for the query. In CBO, I had to adapt to ordering from left-to-right, left being the driving table. The ORDERED hint used in CBO picks up tables left-to-right for processing. Take a pick.

Avoid RBO style coding techniques. Techniques used to prevent the use of indexes in RBO should be avoided. CBO has advanced features such as function-based and bitmap indexes, control processing of queries with proper where clauses and hints.

For example do not do the following anymore:

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;

8.14) Plan stability using stored outlines

You may evaluate using stored outlines for queries that behave badly on CBO, or show different execution plans for different setups. Using this option tells optimizer to consider the execution path specified explicitly.

Please refer section 12 (Stored Outlines) to be covered in a subsequent part of this series..

8.15) Use Hints

The use of Hints will become a favorite practice for developers. Make use of hints in queries to direct optimizer to consider an alternative path than the one being chosen. Hints may be required to be given to queries that behave differently on different databases. If proper hints are given to make a query run as desired, CBO will give more preference to these than to the statistics present.

Refer section 11 (Hints) to be covered in a subsequent part of this series.

8.16) Use bind variables

That's right, no hard coding of values in production system statements what so ever! Most of us have followed this practice of not hard coding values on the production system, and should continue to follow it. This becomes all the more important as the existence or non-existence of bind variables affects the optimizer in making decisions. Preferably, put your values in tables even if it requires one additional statement to retrieve them.

For example, below is an existing legacy system code that always uses a predicate with a hard coded value 1666. Though this may not change in the lifetime of the code, best practice is to avoid such coding and define this as a variable.

Original code:

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;

Change it to use a variable:

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;

CBO works best when statements use literals in the predicates, this allows the optimizer to use histograms on columns to decide on a proper execution plan. When using a bind variable the optimizer is not able to compute what percentage of rows fall below the variable value. Though it is true that literals provide optimizer with more information to choose an execution plan, it is still recommended to use bind variables to allow Sharable SQLs in the system.

8.17) Trace facility from front-end

(This is my own personal experience on Oracle Applications.) Oracle Applications provide an entry in the drop-down menu to enable/disable trace for individual running sessions. It is a key feature in instantly getting details regarding a session that is running slow. Enabling trace generates a trace file on the server for all transactions done in the screen. This file can then be reviewed by the development team to identify all of the bad queries. This is better than trying to simulate the same condition in a test, which may not always be feasible in CBO. Therefore, the next time a user complains about response time, you can request that a trace to be enabled for that session and then analyze the generated file. Instant Targeting of the issue! Consider using password protection to enable trace at session, this will prevent users from experimenting with the option and generating files on the server.

If you are using Oracle Applications you already have this option, if not, you can design something similar for your setup.

8.18) Provide sufficient sort space

Gathering statistics on tables requires sorting to be done and this takes up sort-area space in memory as well as temporary tablespace. Make sure you have enough temporary space to generate statistics (depending on ESTIMATE or COMPUTE) for the largest table. You may consider increasing the value of SORT_AREA_SIZE to allow more operations to take place in memory and save on I/O.

Gathering statistics on indexes do not require sorting.

8.19) FGAC changes

Execution plan may change if you are using Fine Grained Access control (FGAC). FGAC adds additional predicates to an existing query that may sometimes result in a change of execution plan. Test your queries with these additional predicates. Make use of hints to direct optimizer to do what is needful.

We will discuss statistics generation and Monitoring in Part-4 of this series.

» See All Articles by Columnist Amar Kumar Padhi