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
» 