The Do

1
The Dos and Donts of CBO

• by Jim Gillespie
• ORAMAIN Consulting Services, LLC
• emailid jimgillespie_at_oramain.com
• website www.oramain.com
• Phone [phone]
• Cell 608-217-4351

2
The Dos and Donts of CBO
Agenda What is the Optimizer? Why Optimize?
Available Optimizers Why is RBO being removed?
Why move to CBO? Initialization parameters
that affect CBO Internal Oracle parameters that
affect CBO Setup changes for migrating to CBO
Generating Statistics DML Monitoring
Hints Statistics for SYS schema How to
analyze execution plans in CBO?
3
The Dos and Donts of CBO
WHAT IS THE OPTIMIZER? An engine running in
the database. It is dedicated to derive a list
of execution plans. Cost Based Optimization
(CBO) Rule Based Optimization (RBO) In CBO the
plan with the lowest cost is used. In RBO the
plan with the best ranking is used.
4
The Dos and Donts of CBO
WHY OPTIMIZE? To execute a SQL statement in
the shortest time. To utilized the fewest
resources. CPU I/O Memory Network
operations One bad SQL statement can impact all
process on the server.
5
The Dos and Donts of CBO
AVAILABLE OPTIMIZERS. Oracle has two modes
(pre 10g) Rule based optimization (RBO) Cost
based optimization (CBO) RBO Follows a ranking
methodology. Fifteen ranking points in RBO. Was
the preferred mode. The fifteen ranking points
are
6
The Dos and Donts of CBO
Single row by ROWID Single row by cluster join
Single row by hash cluster with unique or
primary key Single row by unique or primary key
Cluster join Hash cluster key Indexed cluster
key Composite key Single column indexes
Bounded range on index columns Unbounded range
on indexed columns Sort merge join MAX or MIN
on indexed column ORDER BY on indexed columns
Full table scan
7
The Dos and Donts of CBO
CBO Follows expense calculation
methodology. All execution plans get a
cost. The lower the cost the less resources
used. Uses statistics and histograms that are
in the dictionary. Also uses user supplied hints
and initora parameters. Can build up to 80,000
permutations of execution plans.
8
The Dos and Donts of CBO
STATISTICS Critical input for the
Optimizer. Generated on data storing
objects. May be exact or estimated. The more
accurate, the better CBO works. Estimate uses a
provided sample size, either number of rows or a
percent. Block sampling is an option usually
for huge tables.
9
The Dos and Donts of CBO
STATISTICS (cont.) Good execution plans depend
on how close the estimate is to the exact
values. Test using different sample sizes. More
on this later. Statistics are stored in tables
owned by the SYS user. The DBA_ views show the
statistics used by the optimizer.
10
The Dos and Donts of CBO
DBA_TABLES NUM_ROWS - Number of rows. BLOCKS
- Number of used blocks. EMPTY_BLOCKS - Number
of empty blocks that have never been used.
AVG_SPACE - Average free space (in bytes) in
blocks allocated to the table. All empty and free
blocks are considered for this. CHAIN_CNT -
Number of chained or migrated rows.
11
The Dos and Donts of CBO
DBA_TABLES (cont.) AVG_ROW_LEN - Average row
length in bytes. LAST_ANALYZED - Date when the
table was last analyzed. SAMPLE_SIZE - Sample
size provided for ESTIMATE statistics. Equal to
NUM_ROWS if COMPUTE. Statistics for individual
partitions of a table can be seen from
DBA_TAB_PARTITIONS. Cluster statistics are
available from DBA_CLUSTERS.
12
The Dos and Donts of CBO
DBA_TAB_COLUMNS NUM_DISTINCT - Number of
distinct values. LOW_VALUE - Lowest value
HIGH_VALUE - Highest value DENSITY - Density
of the column NUM_NULLS - Number of records
with null value for the concerned column.
LAST_ANALYZED - Date when the table was last
analyzed.
13
The Dos and Donts of CBO
DBA_INDEXES BLEVEL - Depth of the index, from
root to leaf. LEAF_BLOCKS - Number of leaf
blocks. DISTINCT KEYS - Number of distinct
keys. AVG_LEAF_BLOCKS_PER_KEY - Average number
of leaf blocks in which each distinct key
appears, should be 1 for unique indexes.
AVG_DATA_BLOCKS_PER_KEY - Average number of
blocks in the table that are pointed to by a
distinct key.
14
The Dos and Donts of CBO
DBA_INDEXES (cont.) NUM_ROWS - Number of rows
indexed. SAMPLE_SIZE - Sample size provided for
ESTIMATE statistics. Equal to NUM_ROWS if
COMPUTE. LAST_ANALYZED - Date when the table was
last analyzed. GLOBAL_STATS - For partitioned
indexes, YES - statistics collected as a whole,
NO - statistics are estimated from statistics.
15
The Dos and Donts of CBO
AVAILABLE CBO MODES CBO has two available modes
in which to run ALL_ROWS FIRST_ROWS
FIRST_ROWS aims at returning the first row(s)
of the statement as soon as possible. Prefers
nested-loops. Best for OLTP. As of 9i,
FIRST_ROWS_n can be used.
16
The Dos and Donts of CBO
AVAILABLE CBO MODES (cont.) ALL_ROWS processes
all rows for a given query before returning the
output. It forces the optimizer to consider
minimal use of resources and best throughput.
ALL_ROWS prefers sort-merge joins. Good for
batch type processing.
17
The Dos and Donts of CBO
CBO is dynamic and tunes its execution plans as
the database grows in size. Do not be taken
aback if the same query that works perfectly in
one database setup is behaving badly in some
other database of the same application. This
would happen if the setup and statistics differ
between the two databases. To prevent such
behavior, you may consider using optimizer plan
stability, which is covered later.
18
The Dos and Donts of CBO
BASIC CBO TERMS The following terms will be used
quite often when analyzing statements in
CBO. CostThe COST computed in CBO is a unit of
expense involved with each operation. The logic
as to how the cost is actually derived is not
documented or made external. Moreover, this may
change across releases. CardinalityThe number
of rows in the table or number of distinct row
links in the index. The cardinality of a query is
the number of rows that is expected to be
returned by it.
19
The Dos and Donts of CBO
BASIC CBO TERMS (cont.) StatisticsMuch required
information gathered for various data holding
objects. This information is vital for the CBO to
decide on execution plans. Join MethodsOracle
uses joins like Hash, sort-merge and nested
loops. A query may run faster using one type of
join as compared to other methods. This should be
evaluated for individual queries.
20
The Dos and Donts of CBO
BASIC CBO TERMS (cont.) FTSFTS or Full Table
Scan relates to a query sequentially scanning a
table from the first block to the last allocated
block. This could be very expensive for big
tables and should be avoided. Index scanRelates
to random access of a table by use of one or more
indexes on the table..
21
The Dos and Donts of CBO
WHY IS RBO BEING REMOVED? The existence of RBO
prevents Oracle from making key enhancements to
its query-processing engine. Its removal will
permit Oracle to improve performance and
reliability of the query-processing components of
the database engine. Oracle 9i release 2 is the
last version that supports RBO. Switch to CBO
before this version is no longer supported. RBO
will be available in Oracle 10g, but not
supported. Presently, Oracle support for RBO is
limited to bug fixes only and no new
functionality will be added to RBO.
22
The Dos and Donts of CBO
WHY MOVE TO CBO? Oracle stopped developing for
RBO environment. RBO will be removed from the
Oracle database. RBO has a limited number of
access methods compared to CBO. All new features
require CBO. CBO is enabled to identify these
features, and how to evaluate their cost. These
features will be of importance for any setup
e.g. IOTs, bitmap indexes, Function-based
indexes, reverse-key indexes, Partitioning, Hash
joins, Materialized views, parallel query, star
joins, etc.
23
The Dos and Donts of CBO
WHY MOVE TO CBO? (cont.) Once RBO is no longer
supported, Oracle support will not be
available. CBO has matured. Distributed and
remote queries are more reliable. RBO performed
poorly joining tables across links. CBO is aware
of statistics on the remote tables.
24
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER
_MODE Valid values are RULE, CHOOSE, ALL_ROWS,
FIRST_ROWS (_n). Default choose.
Dynamic If set to CHOOSE. The optimizer tries
to run the query in either CBO or RBO depending
on the availability or unavailability of
statistics. Therefore, if the tables present in
the query have statistics generated on them, CBO
(ALL_ROWS only) is preferred else RBO is used.
25
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER
_FEATURES_ENABLE Set to a version number such
as- 8.1.5, 8.1.7, 9.0.0. Setting it to a lower
version will prevent the use of new features that
have come in later versions. Default is current
release. Static. e.g. optimizer_features_enable
8.1.7
26
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER
_MAX_PERMUTATIONS Specifies the maximum number of
permutations that should be considered for
queries with joins, to choose an execution
plan. Influences the parse time of queries.
Set to a lower value. Default is 80000 in
Oracle 8, in Oracle 9i it is defaulted to 2000.
Dynamic. e.g. optimizer_max_permutations 3000
27
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER COMPATIBL
E Used to provide backward compatibility with
earlier releases. May restrict the use of some
new features. Only three digits are required to
be specified, however, you can specify more for
documentation purposes. Default is current
release. Static. e.g. compatible 8.1.7
28
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER HASH_JOIN
_ENABLED Values, true or false. If set to
false, hash joins will not be considered by the
Optimizer. Default is true. Dynamic e.g.
hash_join_enabled false
29
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER HASH_AREA
_SIZE This specifies the maximum amount of memory
in bytes to be used for a hash join per process.
Oracle recommends the use of PGA_AGGREGATE_TARGET
instead of this parameter from Oracle 9i.
Default is 2 times SORT_AREA_SIZE.
Dynamic. e.g. hash_area_size 2097152 Setting
this to a very low number may sometimes result in
the following error. ORA-6580 Hash Join ran out
of memory while keeping large rows in memory.
30
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER OPTIMIZER
_DYNAMIC_SAMPLING Is used in situations where
tables are not analyzed. As CBO depends heavily
on statistics, the parameter tells the optimizer
to sample the unanalyzed tables that are being
used in a query. A level of 0 to 10 can be
specified, the higher the value the more time
optimizer spends in sampling. Default is 1 in
9.2 and above, 0 if below. Dynamic. e.g.
optimizer_dynamic_sampling 4
31
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER CURSOR_SH
ARING Determines what kind of SQL statements can
share the same cursor. It can be set to FORCE,
SIMILAR or EXACT. FORCE will try to squeeze
statements that may differ in some literals to
share the same cursor. SIMILAR is somewhat the
same but will try to maintain the plan
optimization for identical statements. EXACT
allows statements with exact identical text to
share a cursor. Using FORCE may sometimes result
in unexpected results. Default is exact.
Dynamic e.g. cursor_sharing force
32
The Dos and Donts of CBO
INITORA PARMS THAT AFFECT THE OPTIMIZER PGA_AGGRE
GATE_TARGET Introduced in Oracle 9i, this
parameter specifies the aggregate PGA memory
available to all server processes attached to an
instance. This parameter can be set for automatic
sizing of SQL working areas. It replaces other
existing parameters like SORT_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE and HASH_AREA_SIZE. Default
is 0, automatic memory management is off.
Dynamic. It can be set to a value between 10 MB
to 4096 GB-1, depending on the setup
requirement. Determine how much memory you have,
subtract the SGA and give the rest to PGA, if
possible.
33
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Undocumented, set by Oracle. Should
not change unless recommended by Oracle
Support. Begin with underscore (_). e.g.
ALTER SESSION SET _COMPLEX_VIEW_MERGING
TRUE The following parameters changed the
default from false to true when upgrading from 8i
to 9i. They may have impact on response time.
Negative or positive.
34
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _COMPLEX_VIEW_MERGING T
his parameter is related to improving the SQL
performance on complex views (including inline
views). Oracle tries to merge the query criteria
with the existing view criteria that would result
in a faster single query. For example, if a view
is created with a GROUP BY clause in it and a
query is executed on the view having a where
clause, Oracle tries to merge the two and create
a single query that would run the where clause
prior to grouping it, thus giving better
performance.
35
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _PUSH_JOIN_PREDICATE Thi
s enables the push join predicate feature that
allows the optimizer to push join predicates
inside a non-mergeable view(s). This would
achieve something similar to a complex view
merging feature, but in this case the join
conditions provided in the query are pushed into
the view. The view in this case cannot be merged
with the query.
36
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _PUSH_JOIN_UNION_VIEW S
ame as above, but this parameter allows optimizer
to push join predicates inside non-merge able
views that contain UNION ALL set operators.
_TABLE_SCAN_COST_PLUS_ONE This parameter
increases the cost of a full table scan by one,
in order to eliminate ties between a full table
scan on a small lookup table and unique or range
scan on the lookup table.
37
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _OR_EXPAND_NVL_PREDICATE
This feature expands the NVL function predicates
to evaluate the use of an index that may be
present on the column used in the function. For
example, if the expression is of the type
"column1 nvl(b1, column1)" and column1 has an
index on it, then optimizer may transform it to a
new expression that uses the OR operator. This
new expression will again be further transformed
to make use of the UNION operator.
38
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _USE_COLUMN_STATS_FOR_FU
NCTION Allows the use of column statistics for
columns that are involved in non-operative
expressions in query, such as numcol 0
charcol '' Such expressions were mainly
used in RBO to prevent the use of indexes.
39
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _ORDERED_NESTED_LOOP Th
is reduces the cost of a nested loop join when
the left side of the join is using an index or
sort row source. _NEW_INITIAL_JOIN_ORDERS This
parameter enables join permutation optimization.
New ordering directives have been added to CBO
for better processing of joins, setting this
parameter will allow use of these directives.
40
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _B_TREE_BITMAP_PLANS En
ables creation of interim bitmap representation
for tables in a query with only binary
index(es). _UNNEST_SUBQUERY This enables
un-nesting of correlated sub-queries. Such
queries may undergo MERGE join operations.
41
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Changed default (cont.). _INDEX_JOIN_ENABLED Ena
bles the use of index joins wherever feasible,
rather than at table level.
42
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Response time implications. _SQLEXEC_PROGRESSION
_COST This controls the population of
VSESSION_LONGOPS view by long running queries.
This view is used to monitor the progress of
queries that are running for long duration.
Queries that cost more than the value that has
been set are identified for monitoring.
Progression monitoring involves overhead and may
affect the performance. Default is 1000, which
may prevent SQL statements from being shared!
Setting it to 0 will turn off the monitoring.
43
The Dos and Donts of CBO
INTERNAL INITORA PARMS THAT AFFECT THE OPTIMIZER.
Response time implications (cont.).
_OPTIMIZER_MODE_FORCE This parameter decides
the optimizer mode for users recursive SQL, for
example, queries running from the PL/SQL block.
In CBO, recursive SQL is executed in CHOOSE mode
if this parameter is set to FALSE. If this
parameter is set to TRUE, then recursive SQL
inherits the session's optimizer mode. Hence, if
the session is running in FIRST_ROWS, then all
SQL processing carried out will be done in the
same optimizer mode. Default is false.
44
The Dos and Donts of CBO
Setup changes for migrating to CBO Here we
highlight a number of key points to consider when
moving to CBO. In addition, we highlight 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 to the documentation and
check with Oracle support for any kind of
anomalies.
45
The Dos and Donts of CBO
Setup changes for migrating to CBO
(cont.) OPTIMIZER_MODE FIRST_ROWS(_n) for
OLTP. ALL_ROWS for DSS, batch,
wharehous. CHOOSE for mixed modes, i.e., Some
applications or modules are in RBO
mode and some in CBO mode. In CHOOSE mode CBO
defaults to ALL_ROWS. Use alter session to force
FIRST_ROWS(_n).
46
The Dos and Donts of CBO
Generate adequate statistics at proper
intervals. Use DBMS_STATS to generate periodic
statistics. ESTIMATE using 5 to 10 is usually
adequate. Use COMPUTE for indexes and
indexed-organized tables. Statistics are not
incremental. Frequency of change determines
frequency of analyzing. Global Temporary Tables
can not be analyzed. Use DBMS_STATS.SET_TABLE_STA
TS.
47
The Dos and Donts of CBO
Generate adequate statistics at proper intervals
(cont.). SQLgt exec dbms_stats.set_table_stats(own
name gt 'SYS', tabname gt EMP', numrows gt
3000, numblks gt 300, avgrlen gt 50) PL/SQL
procedure successfully completed. SQLgt select
num_rows, blocks, avg_row_len, temporary,
user_stats from dba_tables where table_name
'EMP' NUM_ROWS BLOCKS AVG_ROW_LEN T
USE ---------- ----------
----------- - --- 3000
300 50
Y YES
48
The Dos and Donts of CBO
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.
49
The Dos and Donts of CBO
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.
50
The Dos and Donts of CBO
Coding habits and Technical Guidelines Moving to
CBO opens many new features for developing and
designing. 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, the last
table in the where clause is the driver. The
first table is the driver in CBO.
51
The Dos and Donts of CBO
Coding habits and Technical Guidelines
(cont.). The ORDERED hint used in CBO picks up
tables left-to-right for processing. 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.
52
The Dos and Donts of CBO
Plan stability using stored outlines. Stored
outlines store existing execution plans. Using
stored outlines tells optimizer to consider the
execution path specified explicitly.
53
The Dos and Donts of CBO
Use Hints Hints will become a favorite practice
for developers. Use hints in queries to direct
optimizer to consider an alternative path than
the one being chosen. Hints will help queries
that behave differently on different databases.
CBO will give more preference to hints than to
the statistics present. But this doesnt
gaurantee the hints will be used.
54
The Dos and Donts of CBO
Provide sufficient sort space Gathering
statistics on tables requires sorting to be
done. 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.
55
The Dos and Donts of CBO
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 needed.
56
The Dos and Donts of CBO
Generating Statistics COMPUTE 100
accurate. Takes time. Use on indexes and
IOTs. ESTIMATE needs input, number of rows or
percent NUM_ROWS, AVG_SPACE and AVG_ROW_LEN
will have their values derived. Thus
NUM_ROWS may not reflect actual row count.
57
The Dos and Donts of CBO
HOW MUCH TO ESTIMATE? No perfect number of rows
or percent. Varies by table. Derive statistics
using different percents and report on results.
58
The Dos and Donts of CBO
HOW MUCH TO ESTIMATE? (cont.) Example The table
abc.emp has around 4.5 million records. Below are
statistics at various sample sizes. Commands
used dbms_stats.gather_table_stats('abc',
emp') --compute dbms_stats.gather_table_stats('a
bc', 'emp ', estimate_percent gt
5) dbms_stats.gather_table_stats('abc', ' emp ',
estimate_percent gt 10) dbms_stats.gather_table_s
tats('abc', ' emp ', estimate_percent gt 51)
59
The Dos and Donts of CBO
HOW MUCH TO ESTIMATE? (cont.) Final statistics
MODE NUM_ROWSSAMPLE_SIZETime
taken --------------------------------------------
------------------ compute 4591474
4591474 2 hr at 5 4582460
229123 8 mts at 10
4587520 458752 17 mts at 20
4591635 918327 32 mts at
51 4590890.2 2341354 1 hr
56 mts The NUM_ROWS difference between full
statistics and 5 is only 9014 records.
Statistics at a 5 sample size serves my purpose
as the variance is less than 1 that of actual
value.
60
The Dos and Donts of CBO
WHAT SHOULD THE TIME INTERVAL BE? Start with a
weekly generation. DML Monitoring can be used on
tables with heavy activity. Generate statistics
after bulk loading. Generate statistics after
building a work table. Group objects by type
and generate at different intervals. e.g
transaction tables versus master tables.
61
The Dos and Donts of CBO
STATISTICS LOCKS? Tables analyzed cannot undergo
DDL changes. DML activities can be carried on.
Analyzing an index puts a shared lock on the
related table hence, neither DML nor DDL can be
performed. Preferably avoid all activities
during the statistics generation phase.
62
The Dos and Donts of CBO
UTILITIES TO GENERATE STATISTICS DBMS_UTILITY
ANALYZE command DBMS_DDL DBMS_STATS
63
The Dos and Donts of CBO
UTILITIES TO GENERATE STATISTICS
(cont.) DBMS_STATS The recommend package
provided for gathering and maintaining statistics
in a database. The following can be done with
this package Gathering statistics Deleting
statistics Providing user statistics
Retrieving statistics Exporting and importing
statistics
64
The Dos and Donts of CBO
UTILITIES TO GENERATE STATISTICS
(cont.) GATHERING STATISTICS WITH
DBMS_STATS DBMS_STATS.GATHER_TABLE_STATS gathers
statistics for a table and its columns, and
optionally the associated indexes. Call
syntax dbms_stats.gather_table_stats(ownname,
tabname, partname, estimate_percent,
block_sample, method_opt, degree,
granularity, cascade, stattab, statid,
statown) The first two parms are mandatory, the
rest defaulted.
65
The Dos and Donts of CBO
DBMS_STATS.GATHER_TABLE_STATS(cont.) PARAMETERS o
wnname - owner tabname - table name partname -
partition name estimate_percent - sample percent
ratio block_sample - consider random blocks
sampling rather than rows sampling. TRUE/FALSE
method_opt - method options. FOR ALL COLUMNS/FOR
ALL INDEXED COLUMNS. Append the phase SIZE 1 if
it is required to generate statistics in
parallel.
66
The Dos and Donts of CBO
DBMS_STATS.GATHER_TABLE_STATS(cont.) PARAMETERS
(cont.) degree - degree of parallelism.
granularity - for partitioned tables.
DEFAULT/SUBPARTITION /PARTITION/GLOBAL/ALL.
cascade - gather statistics for indexes also.
TRUE/FALSE stattab, statid, statown - required
for user statistics, covered below in this
section.
67
The Dos and Donts of CBO
DBMS_STATS.GATHER_TABLE_STATS examples. e.g.
Estimate statistics for a table and its
columns SQLgt exec dbms_stats.gather_table_stats (
ownname gt SCOTT', tabname gt EMP',
estimate_percent gt 5) e.g. Estimate
statistics for a table, its columns and
indexes. SQLgt exec dbms_stats.gather_table_stats
(ownname gt SCOTT', tabname gt 'EMP',
estimate_percent gt 5, cascade gt true)
68
The Dos and Donts of CBO
DBMS_STATS.GATHER_TABLE_STATS examples(cont.). e.
g. Estimate statistics in parallel, the
following uses 8 threads to complete the
task. SQLgt exec dbms_stats.gather_table_stats
(ownname gt 'SCOTT', tabname gt 'EMP_TRX',
estimate_percent gt 5, degree gt 8) When the
above process is running select from
vpx_process produces -
69
The Dos and Donts of CBO
DBMS_STATS.GATHER_TABLE_STATS examples(cont.). SE
RVSTATUS PIDSPID SID
SERIAL P000 IN USE 509684
7 50586 P001 IN USE
659686 60 51561 P002 IN USE
669688 17 2694 P003 IN USE
679690 30 39243 P004
IN USE 689692 74
11017 P005 IN USE 699694
48 4253 P006 IN USE
709696 76 17 P007 IN USE
719698 68 1285 8 rows
selected.
70
The Dos and Donts of CBO
DBMS_STATS.GATHER_INDEX_STATS examples. Call
syntax dbms_stats.gather_index_stats(ownname,
indname, partname, estimate_percent, stattab,
statid, statown) e.g. SQLgt exec
dbms_stats.gather_index_stats (ownname gt
'SCOTT', indname gt EMP_IDX')
71
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples. Call
Syntax dbms_stats.gather_schema_stats(ownname,
estimate_percent, block_sample, method_opt,
degree, granularity, cascade, stattab, statid,
options, objlist, statown)
72
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.). Parameters options - object
information can be further specified here.
GATHER - gather statistics for all objects
(default). GATHER STALE - update statistics for
stale objects, identified with the monitoring
option. GATHER EMPTY - gather statistics for
objects without any statistics.
73
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.). Parameters LIST STALE - return a list
of stale objects, this depends on the SMON
processing. LIST EMPTY - return a list of
objects with no statistics. GATHER AUTO - same
as STALE but will include objects without any
statistics. objlist - table of type
DBMS_STATS.OBJECTTAB, returns an empty or stale
list.
74
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.). e.g. Gather schema statistics, for
tables and indexes at 5 estimate. SQLgt exec
dbms_stats.gather_schema_stats(ownname gt
'SCOTT', estimate_percent gt 5, cascade gt true,
options gt 'GATHER')
75
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.). e.g. Gather statistics for objects
with no statistics. The GATHER EMPTY options
generates statistics for all objects without any
statistics. SQLgt exec dbms_stats.gather_schema_st
ats (ownname gt 'SCOTT',
estimate_percent gt 5, options gt
'GATHER EMPTY')
76
The Dos and Donts of CBO
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.). e.g. To identify a list of objects
without any statistics. SQLgtdeclare
l_owner varchar2(30) 'SCOTT'
l_emptylst dbms_stats.objecttab
begin dbms_stats.gather_schema_stats(ownname gt
l_owner options gt 'LIST EMPTY', objlist gt
l_emptylst) for i in
nvl(l_emptylst.first, 0) .. nvl(l_emptylst.last,
0) loop dbms_output.put_line(l_emptylst(i).objty
pe '/' l_emptylst(i).objname) end loop
end / INDEX/EMP_N1 TABLE/EMP
77
The Dos and Donts of CBO
DBMS_STATS.GATHER_DATABASE_STATS examples Call
Syntax dbms_stats.gather_database_stats(estimate
_percent, block_sample, method_opt, degree,
granularity, cascade, stattab, statid, options,
objlist, statown) In 8i this will generate
statistics for the SYS schema. In 9i it will not.
78
The Dos and Donts of CBO
DBMS_STATS.DELETE_TABLE_STATS Call
Syntax dbms_stats.delete_table_stats (ownname,
tabname, partname, stattab, statid,
cascade_parts, cascade_columns,
cascade_indexes, statown)
79
The Dos and Donts of CBO
DBMS_STATS.DELETE_TABLE_STATS examples
(cont.) Parameters cascade_parts - delete
statistics for all partitions (partname should be
null). cascade_columns - delete column
statistics. Default is true. cascade_indexes -
delete index statistics. Default is true.
80
The Dos and Donts of CBO
DBMS_STATS.DELETE_TABLE_STATS examples
(cont.) e.g. Delete statistics for a table and
its columns and indexes. SQLgt exec
dbms_stats.delete_table_stats (ownname
gt 'SCOTT', tabname gt 'EMP') e.g. Delete
statistics for table only. Column and index
statistics will be preserved. SQLgt exec
dbms_stats.delete_table_stats (ownname
gt 'SCOTT', tabname gt 'EMP',
cascade_columns gt false, cascade_indexes gt
false)
81
The Dos and Donts of CBO
DBMS_STATS.DELETE_COLUMN_STATS examples Call
Syntax dbms_stats.delete_column_stats(ownname,
tabname, colname, partname, stattab, statid,
cascade_parts, statown) e.g. Deleting
statistics for one column. SQLgt exec
dbms_stats.delete_column_stats (ownname
gt SCOTT', tabname gt 'EMP', colname
gt ENAME')
82
The Dos and Donts of CBO
DBMS_STATS.DELETE_INDEX_STATS examples Call
Syntax dbms_stats.delete_index_stats(ownname,
indname, partname, stattab, statid,
cascade_parts, statown) e.g. Deleting index
statistics. SQLgt exec dbms_stats.delete_index_sta
ts (ownname gt 'SCOTT', indname gt
'EMP_N1')
83
The Dos and Donts of CBO
DBMS_STATS.DELETE_SCHEMA_STATS examples Call
Syntax dbms_stats.delete_schema_stats(ownname,
stattab, statid, statown) e.g. Deleting
statistics for schema SCOTT. SQLgt exec
dbms_stats.delete_schema_stats(SCOTT')
84
The Dos and Donts of CBO
DBMS_STATS.DELETE_SCHEMA_STATS examples Call
Syntax dbms_stats.delete_schema_stats(ownname,
stattab, statid, statown) e.g. Deleting
statistics for schema SCOTT. SQLgt exec
dbms_stats.delete_schema_stats(SCOTT') DBMS_STA
TS.DELETE_DATABASE_STATS Call Syntax dbms_stats.d
elete_database_stats(stattab, statid, statown)
85
The Dos and Donts of CBO
DBMS_STATS.SET_TABLE_STATS examples Call
Syntax dbms_stats.set_table_stats(ownname,
tabname, partname, stattab, statid, numrows,
numblks, avgrlen, flags, statown) Parameters nu
mrows - number of rows. numblks - blocks in the
table. avgrlen - average row length. flags -
currently for internal use only.
86
The Dos and Donts of CBO
DBMS_STATS.SET_TABLE_STATS examples e.g. SQLgt
exec dbms_stats.set_table_stats
(ownname gt 'SCOTT', tabname gt EMP,
numrows gt 12422, numblks gt 100, avgrlen gt
124) SQLgt select owner, num_rows, blocks,
avg_row_len from dba_tables
where table_name EMP OWNER
NUM_ROWS BLOCKSAVG_ROW_LEN SCOTT
12422 100
124
87
The Dos and Donts of CBO
DBMS_STATS examples (cont.) ALSO dbms_stats.se
t_column_stats dbms_stats.set_index_stats
88
The Dos and Donts of CBO
DBMS_STATS.GET_TABLE_STATS examples Call
syntax dbms_stats.get_table_stats(ownname,
tabname, partname, stattab, statid,
numrows, numblks, avgrlen, statown)
89
The Dos and Donts of CBO
DBMS_STATS.GET_TABLE_STATS examples
(cont.) e.g. getting table statistics data. SQLgt
declare l_numrows number l_numblks number
l_avgrlen number begin
dbms_stats.get_table_stats(ownname gt 'SCOTT',
tabname gt 'EMP', numrows gt l_numrows,
numblks gt l_numblks, avgrlen gt l_avgrlen)
dbms_output.put_line('No. of rows '
l_numrows) dbms_output.put_line('No. of
blks ' l_numblks) dbms_output.put_line
('Avg row length ' l_avgrlen) end
/ No. of rows 4106860 No. of blks 6219 Avg row
length 3
90
The Dos and Donts of CBO
DBMS_STATS examples (cont.) ALSO dbms_stats.ge
t_column_stats dbms_stats.get_index_stats
91
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS has routines for
gathering statistics and storing them outside the
dictionary. This does not influence the
optimizer. Most of the procedures in this package
have three common parameters - STATID, STATTAB
and STATOWN that are related to user processing
of statistics. Advantages of this feature 1.
Estimated statistics at different percentages
could be stored and used for testing. 2.
Statistics generated on one database could be
transferred to another database.
92
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.CREATE_STAT_TABLE Call
syntax dbms_stats.create_stat_table(ownname,
stattab, tblspace) Parameters stattab -
statistics table name. tblspace - tablespace to
be used.
93
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.CREATE_STAT_TABLE
(cont.) e.g. creating a user statistics
table. SQLgt exec dbms_stats.create_stat_table
(ownname gt 'SYS', stattab gt
'STAT_AT_5PC', tblspace gt
'SYSTEM') The table looks like this. Desc
stat_at_5pc
94
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.CREATE_STAT_TABLE
(cont.) Name
Null? Type ----------------------
-------- ----------------------- STAT
ID
VARCHAR2(30) TYPE
CHAR(1) VERSION
NUMBER
FLAGS
NUMBER C1
VARCHAR2(30) C2

VARCHAR2(30) C3
VARCHAR2(30) C4

VARCHAR2(30) C5
VARCHAR2(30)
95
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.CREATE_STAT_TABLE
(cont.) Name
Null? Type ----------------------
-------- ----------------------- N1

NUMBER N2
NUMBER N3

NUMBER N4
NUMBER N5

NUMBER N6
NUMBER N7
NUMBER
N7
NUMBER N8
NUMBER
96
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.CREATE_STAT_TABLE
(cont.) Name
Null? Type ----------------------
-------- -----------------------
N9
NUMBER N10
NUMBER N11

NUMBER N12
NUMBER D1

DATE R1
RAW(32) R2

RAW(32) CH1
VARCHAR2(1000)
97
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.EXPORT_TABLE_STATS Retrie
ves table statistics for a particular table and
puts it in the user statistics table. Call
syntax dbms_stats.export_table_stats(ownname,
tabname, partname, stattab, statid, cascade,
statown)
98
The Dos and Donts of CBO

• Exporting and importing statistics with
  DBMS_STATS
• DBMS_STATS.EXPORT_TABLE_STATS (cont.)
• e.g. exporting EMP stats for testing purpose,
  including table and indexes.
• SQLgt exec dbms_stats.export_table_stats
• (ownname gt SCOTT', tabname gt EMP',
• stattab gt 'STAT_AT_5PC', cascade gt
  true,
• statown gt SYS')

99
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.EXPORT_COLUMN_STATS Call
syntax dbms_stats.export_table_stats(ownname,
tabname, colname, partname, stattab, statid,
statown) DBMS_STATS.EXPORT_INDEX_STATS Call
syntax dbms_stats.export_index_stats(ownname,
indname, partname, stattab, statid, statown)
100
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.EXPORT_SCHEMA_STATS Call
syntax dbms_stats.export_schema_stats(ownname,
stattab, statid, statown) DBMS_STATS.EXPORT_DATA
BASE_STATS Call syntax dbms_stats.export_databas
e_stats(stattab, statid, statown)
101
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.IMPORT_TABLE_STATS Retrie
ves statistics for a table from a user statistics
table and stores it in dictionary. Call
syntax dbms_stats.import_table_stats(ownname,
tabname, partname, stattab, statid, cascade,
statown)
102
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.IMPORT_TABLE_STATS
(cont.) e.g. importing statistics for table
emp, including column and indexes. SQLgt exec
dbms_stats.import_table_stats (ownname
gt 'SCOTT', tabname gt EMP',
stattab gt 'STAT_AT_5PC', cascade gt true,
statown gt 'SYS')
103
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.IMPORT_COLUMN_STATS Call
syntax dbms_stats.import_column_stats(ownname,
tabname, colname, partname, stattab, statid,
statown) DBMS_STATS.IMPORT_INDEX_STATS Call
syntax dbms_stats.import_index_stats(ownname,
indname, partname, stattab, statid, statown)
104
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.IMPORT_SCHEMA_STATS Call
syntax dbms_stats.import_schema_stats(ownname,
stattab, statid, statown) DBMS_STATS.IMPORT_DATA
BASE_STATS Call syntax dbms_stats.import_schema_
stats(stattab, statid, statown)
105
The Dos and Donts of CBO
Exporting and importing statistics with
DBMS_STATS DBMS_STATS.DROP_STAT_TABLE Drops a
user statistics table. Call syntax dbms_stats.dr
op_stat_table(ownname, stattab) e.g. dropping
my stat table. SQLgt exec dbms_stats.drop_stat_tab
le(ownname gt 'SCOTT', stattab gt 'STAT_AT_5PC')
106
The Dos and Donts of CBO
DML MONITORING Used to automate the updating of
statistics as tables are updated. When enabled
for a table, Oracle monitors the DML changes
(including truncates) being done on the table and
maintains the details in the SGA. After time
SMON wakes up and post the information in the
dictionary. In Oracle 9i, this time is 15
minutes. In 8i it is 3 hours. Enable with
Alter table, Create table or DBMS_STATS.
107
The Dos and Donts of CBO
DML MONITORING (cont.) SQLgt alter table EMP
monitoring SQLgt select monitoring from
dba_tables where table_name EMP' MON --- YES
e.g. Monitoring option for table EMP. SQLgt exec
dbms_stats.gather_table_stats(SCOTT', EMP')
108
The Dos and Donts of CBO
DML MONITORING (cont.) Statistics on tables that
had gt 10 modifications can be seen in the
DBA_TAB_MODIFICATIONS view. SQLgt select from
dba_tab_modifications no rows selected --DML
activities were carried on the table. SQLgt
select table_owner, table_name, inserts, updates,
deletes, timestamp, truncated
from dba_tab_modifications
109
The Dos and Donts of CBO

• DML MONITORING (cont.)
• TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
  TIMESTAMP TRU
• ----------- ---------- ------- ------- -------
  --------- ---
• SCOTT EMP
  1028577 0
• 19-SEP-03 NO
• SQLgt exec dbms_stats.gather_schema_stats
• (ownname gt SCOTT',
• options gt 'GATHER STALE')
• SQLgt select from dba_tab_modifications
• no rows selected

110
The Dos and Donts of CBO
DML MONITORING (cont.) No known SGA issues when
using montoring. Does not result in performance
issues. Use on tables that are being changed
significantly and optimizer results fluctuate
significantly.
111
The Dos and Donts of CBO
HINTS Clues or directives that will assist the
optimizer in choosing an execution plan. Guide
the Optimizer to do things in a certain way--the
way we would like a statement to run. Not
orders but directives to the optimizer.
Provided in comment form / lthintgt / --
lthintgt
112
The Dos and Donts of CBO
HINTS (cont.) Multiple hints can be provided in
a single comment for a statement, each separated
with spaces. Meant for DML statements. Are not
case sensitive. If a wrong or invalid hint is
provided, the optimizer ignores it and continues
with the execution of the statement. The
optimizer will not notify the user about such
hints.
113
The Dos and Donts of CBO
HINTS (cont.) Hints are CBO features. Using them
in RBO setup will force the queries to run in
cost mode. The exception to this is the RULE hint
that invokes the RBO for executing a statement.
Hints can be used to influence the mode of the
optimizer, the access path, the join order, the
join method used etc..
114
The Dos and Donts of CBO
HINTS (cont.) SQL statements that are executed
directly may behave differently when executed
from within PL/SQL. Make use of hints in such
cases to achieve the required results. Table
hints can be provided with the table name. . If
an alias name is provided, use it instead of the
table name. Avoid the use of schema name along
with the table name in hints, even if they appear
in the FROM clause. Using aliases is a safe bet.
115
The Dos and Donts of CBO
HINTS (cont.) As stated by Oracle Documentation,
the use of hints involves extra code that must be
managed, checked and controlled. Use hints to
tame queries that execute with sub-optimal
execution plans, but take care to provide the
right access paths. Here is a list of the most
widely used hints ALL_ROWS for good throughput
and resource utilization. FIRST_ROWS for good
response time. RULE Use rule-based optimization
rather than cost. CHOOSE Decide on rule or cost
optimization based on the existence of
statistics.
116
The Dos and Donts of CBO
HINTS (cont.) FULL for doing a full table scan
on the table. HASH hash scan, applies only for
clustered tables (do not confuse with HASH
join). ROWID table scan by rowid. CLUSTER
cluster scan, applies only for clustered
tables. INDEX index scan, specify the table and
the index name. INDEX_ASC for range scan, scan
index in ascending order of values. INDEX_DESC
for range scan, scan index in descending order of
values. INDEX_JOIN use index join as an access
path. Two indexes could be joined to return the
required values.
117
The Dos and Donts of CBO
HINTS (cont.) INDEX_FFS perform a fast full
scan on the index rather than on the
table. NO_INDEX avoid the use of the specified
index or all indexes. INDEX_COMBINE explicitly
choose a bitmap access path, make use of bitmap
indexes. ORDERED access and join tables in the
order mentioned in the FROM clause, left to right.
118
The Dos and Donts of CBO
HINTS (cont.) USE_NL use Nested Loop for
joining tables. USE_HASH use Hash
joins. USE_MERGE use Sort-Merge joins. The
optimizer hints ALL_ROWS, FIRST_ROWS, RULE and
CHOOSE affect the Optimizer mode for executing
the query, irrespective of what is set at session
level. The RULE Hint causes the Optimizer to use
rule based optimization to choose the execution
path. This is an instant solution for queries
that ran perfectly in RBO but have slowed down in
CBO.
119
The Dos and Donts of CBO
HINTS examples e.g. improving a queries
response time. select / first_rows /
trx_value from jnc_rtl_sales_iface_hdr where
trx_no 1211 e.g. Full table scan
directive. select / full(a) / a.shop_no,
a.subinventory_code, b.item_code from
jnc_shop_mapping a, jnc_rtl_sales_iface_hdr
b where b.shop_no a.shop_no
120
The Dos and Donts of CBO
Statistics for SYS schema. One issue that has
always been in doubts is whether to generate
statistics for SYS schema. Generating statistics
for dictionary tables owned by SYS is not
recommended in Oracle 8i. The dictionary views
that reference the SYS tables execute efficiently
with the Rule Based Optimizer. You may generate
statistics in Oracle 9i but you will have to
evaluate this option for your setup. As per a
note I came across, Oracle does not perform any
regression testing with dictionaries analyzed and
there may be a possibility of performance issues.
Oracle 10 and above would require statistic
generation for SYS schema as RBO will be
desupported.
121
The Dos and Donts of CBO

• Statistics for SYS schema (cont.)
• Ways to deal with SYS related queries
• Run your setup in CHOOSE mode. Generate
  statistics for application specific schemas.
  Avoid doing so for SYS schema.
• This way, RBO will be used when accessing the
  dictionary and CBO when your application runs.
  The only catch is that CBO will resort to
  ALL_ROWS and that may cause issues in OLTP
  systems. Setting the initialization parameters
  appropriately and extensive use of hints for
  application queries will stabilize the system in
  due course.

122
The Dos and Donts of CBO
Statistics for SYS schema (cont.) Ways to deal
with SYS related queries (cont.) Run your setup
in ALL_ROWS or FIRST_ROWS mode. Generate
statistics for application specific schemas.
Avoid doing so for SYS schema. Make extensive use
of RULE hints for dictionary queries that are
slow. This way, Dictionary related queries will
still be on RBO and the application can run on
CBO.
123
The Dos and Donts of CBO
How to get execution plans in CBO? Same as
RBO. Use sqltrace with TKPROF. Use Explain
Plan. Two scripts, UTLXPLS.SQL (serial) and
UTLXPLP.SQL (parallel executions), are provided
by oracle to show the formatted execution plans.
Use autotrace. Set timing on.
124

The Dos and Donts of CBO
PLAN STABILITY - most useful when you cannot
risk performance changes - preserves
execution plans in stored outlines -
the optimizer generates equivalent
execution plans from the stored outlines. -
facilitates migration from rule to cost
based optimization when you upgrade.
125

The Dos and Donts of CBO
PLAN STABILITY - will use hints - needs
exact text matching - degradation can occur
if datatypes change - contrary to
CBO. - uses system table OL and OLHINTS
- outlines are retained indefinetly
126

The Dos and Donts of CBO
CREATING STORED OUTLINES - INITORA
parameter CREATE_STORED_OUTLINE TRUE -
schema needs CREATE ANY OUTLINE privilege.
- see the CREATE OUTLINE statement in
the ORACLE9i SQL Reference.
127

The Dos and Donts of CBO
USING STORED OUTLINES - set system parameter
USED_STORED_OUTLINES to TRUE or to a
category. If set to TRUE then Oracle uses
the DEFAULT category. If a category is listed
then Oracle uses that category until you set
the category to another name or set
USED_STORED_OUTLINES to FALSE. - there is an
OUTLN_PKG for managing stored outlines and
their categories, see Oracle9i Supplied
PL/SQL Packages Ref.
128

The Dos and Donts of CBO
VIEWING STORED OUTLINES - select
outline_category from vsql where sql_text like
your sql text - select name, sql_text
from user_outlines where category
yourcategory
129

The Dos and Donts of CBO
WHEW!!! As you can see CBO will bring many
changes. Start now so your ready for 10g. Use
the phase approach. Set optimizer_mode to CHOOSE
and convert a few modules at a time. Lastly,
if you need help, contact me. Questions?
THANK YOU!