jim gillespie - cbo
Post on 06-Apr-2018
232 Views
Preview:
TRANSCRIPT
-
8/3/2019 Jim Gillespie - Cbo
1/129
ORAMAIN Consulting Services,
LLC 1
The Dos and Donts of CBO
by Jim Gillespie
ORAMAIN Consulting Services, LLC
emailid: jimgillespie@oramain.com
website: www.oramain.com
Phone: 608-848-8642
Cell : 608-217-4351
-
8/3/2019 Jim Gillespie - Cbo
2/129
ORAMAIN Consulting Services,
LLC 2
AgendaWhat 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 StatisticsDML Monitoring
Hints
Statistics for SYS schema
How to analyze execution plans in CBO?
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
3/129
ORAMAIN Consulting Services,
LLC 3
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
4/129
ORAMAIN Consulting Services,
LLC 4
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
5/129
ORAMAIN Consulting Services,
LLC 5
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;
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
6/129
ORAMAIN Consulting Services,
LLC 6
The Dos and Donts of CBO
Single row by ROWIDSingle 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 joinMAX or MIN on indexed column
ORDER BY on indexed columns
Full table scan
-
8/3/2019 Jim Gillespie - Cbo
7/129
ORAMAIN Consulting Services,LLC 7
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
8/129
ORAMAIN Consulting Services,LLC 8
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 optionusually for huge tables.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
9/129
ORAMAIN Consulting Services,LLC 9
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
10/129
ORAMAIN Consulting Services,LLC 10
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
11/129
ORAMAIN Consulting Services,LLC 11
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 fromDBA_TAB_PARTITIONS.
Cluster statistics are available from DBA_CLUSTERS.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
12/129
ORAMAIN Consulting Services,LLC 12
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
13/129
ORAMAIN Consulting Services,LLC 13
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 uniqu
indexes.
AVG_DATA_BLOCKS_PER_KEY - Average number of
blocks in the table that are pointed to by a distinct key.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
14/129
ORAMAIN Consulting Services,LLC 14
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
15/129
ORAMAIN Consulting Services,LLC 15
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
16/129
ORAMAIN Consulting Services,LLC 16
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
17/129
ORAMAIN Consulting Services,LLC 17
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 perfectl
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
18/129
ORAMAIN Consulting Services,LLC 18
BASIC CBO TERMS
The following terms will be used quite often when analyzing
statements in CBO.
Cost
The COST computed in CBO is a unit of expense involved witheach operation. The logic as to how the cost is actually derived is
not documented or made external. Moreover, this may change
across releases.
Cardinality
The 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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
19/129
ORAMAIN Consulting Services,LLC 19
BASIC CBO TERMS (cont.)
Statistics
Much required information gathered for various data holding
objects. This information is vital for the CBO to decide on
execution plans.
Join Methods
Oracle uses joins like Hash, sort-merge and nested loops. A
query may run faster using one type of join as compared to othermethods. This should be evaluated for individual queries.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
20/129
ORAMAIN Consulting Services,LLC 20
BASIC CBO TERMS (cont.)
FTS
FTS 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 scan
Relates to random access of a table by use of one or more
indexes on the table..
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
21/129
ORAMAIN Consulting Services,LLC 21
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 fixesonly
and no new functionality will be added to RBO.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
22/129
ORAMAIN Consulting Services,LLC 22
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 oimportance for any setup; e.g. IOTs, bitmap indexes, Function-
based indexes, reverse-key indexes, Partitioning, Hash joins,
Materialized views, parallel query, star joins, etc.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
23/129
ORAMAIN Consulting Services,LLC 23
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
24/129
ORAMAIN Consulting Services,LLC 24
INITORA PARMS THAT AFFECT THE OPTIMIZER
OPTIMIZER_MODE
Valid values are RULE, CHOOSE, ALL_ROWS, FIRST_ROW(_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 ofstatistics. Therefore, if the tables present in the query have
statistics generated on them, CBO (ALL_ROWS only) is
preferred else RBO is used.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
25/129
ORAMAIN Consulting Services,LLC 25
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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
26/129
ORAMAIN Consulting Services,LLC 26
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 to2000. Dynamic.
e.g.: optimizer_max_permutations = 3000
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
27/129
ORAMAIN Consulting Services,LLC 27
INITORA PARMS THAT AFFECT THE OPTIMIZER
COMPATIBLE
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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
28/129
ORAMAIN Consulting Services,LLC 28
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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
29/129
ORAMAIN Consulting Services,LLC 29
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
30/129
ORAMAIN Consulting Services,LLC 30
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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
31/129
ORAMAIN Consulting Services,LLC 31
INITORA PARMS THAT AFFECT THE OPTIMIZER
CURSOR_SHARING
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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
32/129
ORAMAIN Consulting Services,LLC 32
INITORA PARMS THAT AFFECT THE OPTIMIZER
PGA_AGGREGATE_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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
33/129
ORAMAIN Consulting Services,LLC 33
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 totrue when upgrading from 8i to 9i. They may have impact on
response time. Negative or positive.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
34/129
ORAMAIN Consulting Services,LLC 34
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_COMPLEX_VIEW_MERGING
This 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 togrouping it, thus giving better performance.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
35/129
ORAMAIN Consulting Services,LLC 35
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_PUSH_JOIN_PREDICATE
This 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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
36/129
ORAMAIN Consulting Services,LLC 36
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_PUSH_JOIN_UNION_VIEW
Same 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, inorder to eliminate ties between a full table scan on a small
lookup table and unique or range scan on the lookup table.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
37/129
ORAMAIN Consulting Services,LLC 37
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
38/129
ORAMAIN Consulting Services,LLC 38
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_USE_COLUMN_STATS_FOR_FUNCTION
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 useof indexes.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
39/129
ORAMAIN Consulting Services,LLC 39
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_ORDERED_NESTED_LOOP
This 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. Newordering directives have been added to CBO for better
processing of joins, setting this parameter will allow use of
these directives.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
40/129
ORAMAIN Consulting Services,LLC 40
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_B_TREE_BITMAP_PLANS
Enables 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 queriesmay undergo MERGE join operations.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
41/129
ORAMAIN Consulting Services,LLC 41
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Changed default (cont.).
_INDEX_JOIN_ENABLED
Enables the use of index joins wherever feasible, rather than at
table level.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
42/129
ORAMAIN Consulting Services,LLC 42
INTERNAL INITORA PARMS THAT AFFECT THE
OPTIMIZER. Response time implications.
_SQLEXEC_PROGRESSION_COST
This controls the population of V$SESSION_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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
43/129
ORAMAIN Consulting Services,LLC 43
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 allSQL processing carried out will be done in the same optimizer
mode.
Default is false.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
44/129
ORAMAIN Consulting Services,LLC 44
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
45/129
ORAMAIN Consulting Services,LLC 45
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).
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
46/129
ORAMAIN Consulting Services,LLC 46
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_STATS.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
47/129
ORAMAIN Consulting Services,LLC 47
Generate adequate statistics at proper intervals (cont.).
SQL> exec dbms_stats.set_table_stats(ownname => 'SYS',
tabname => EMP', numrows => 3000, numblks => 300,
avgrlen => 50);
PL/SQL procedure successfully completed.
SQL> 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
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
48/129
ORAMAIN Consulting Services,LLC 48
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
49/129
ORAMAIN Consulting Services,LLC 49
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.
The Dos and Donts of CBO
h d f
-
8/3/2019 Jim Gillespie - Cbo
50/129
ORAMAIN Consulting Services,LLC 50
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. Thefirst table is the driver in CBO.
The Dos and Donts of CBO
h d f C O
-
8/3/2019 Jim Gillespie - Cbo
51/129
ORAMAIN Consulting Services,LLC 51
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 andhints.
The Dos and Donts of CBO
Th D d D f CBO
-
8/3/2019 Jim Gillespie - Cbo
52/129
ORAMAIN Consulting Services,LLC 52
Plan stability using stored outlines.
Stored outlines store existing execution plans.
Using stored outlines tells optimizer to consider the execution
path specified explicitly.
The Dos and Donts of CBO
Th D d D f CBO
-
8/3/2019 Jim Gillespie - Cbo
53/129
ORAMAIN Consulting Services,LLC 53
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 statisticspresent. But this doesnt gaurantee the hints will be used.
The Dos and Donts of CBO
Th D d D f CBO
-
8/3/2019 Jim Gillespie - Cbo
54/129
ORAMAIN Consulting Services,LLC 54
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 onI/O.
The Dos and Donts of CBO
Th D d D f CBO
-
8/3/2019 Jim Gillespie - Cbo
55/129
ORAMAIN Consulting Services,LLC 55
FGAC changes
Execution plan may change if you are using Fine Grained
Access control (FGAC).
FGAC adds additional predicates to an existing query that maysometimes 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.
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
56/129
ORAMAIN Consulting Services,LLC 56
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_LENwill have their values derived.
Thus NUM_ROWS may not reflect actual row count.
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
57/129
ORAMAIN Consulting Services,LLC 57
HOW MUCH TO ESTIMATE?
No perfect number of rows or percent.
Varies by table.
Derive statistics using different percents and report on results.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
58/129
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
59/129
ORAMAIN Consulting Services,LLC 59
HOW MUCH TO ESTIMATE? (cont.)Final statistics:
MODE |NUM_ROWS|SAMPLE_SIZE|Time taken
--------------------------------------------------------------
compute | 4591474 | 4591474 | 2 hr
at 5% | 4582460 | 229123 | 8 mtsat 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.
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
60/129
ORAMAIN Consulting Services,LLC 60
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.
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
61/129
ORAMAIN Consulting Services,LLC 61
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 statisticsgeneration phase.
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
62/129
ORAMAIN Consulting Services,LLC 62
UTILITIES TO GENERATE STATISTICS
DBMS_UTILITY
ANALYZE command
DBMS_DDL
DBMS_STATS
The Dos and Donts of CBO
Th D d D t f CBO
-
8/3/2019 Jim Gillespie - Cbo
63/129
ORAMAIN Consulting Services,LLC 63
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 statisticsProviding user statistics
Retrieving statistics
Exporting and importing statistics
The Dos and Donts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
64/129
ORAMAIN Consulting Services,LLC 64
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 associatedindexes.
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.
The Dos and Donts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
65/129
ORAMAIN Consulting Services,LLC 65
DBMS_STATS.GATHER_TABLE_STATS(cont.)
PARAMETERS
ownname - owner
tabname - table name
partname - partition nameestimate_percent - sample percent ratio
block_sample - consider random blocks sampling rather than
rows sampling. TRUE/FALSE
method_opt - method options. FOR ALL COLUMNS/FORALL INDEXED COLUMNS. Append the phase SIZE 1 if
it is required to generate statistics in parallel.
The Dos and Donts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
66/129
ORAMAIN Consulting Services,LLC 66
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.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
67/129
ORAMAIN Consulting Services,LLC 67
DBMS_STATS.GATHER_TABLE_STATS examples.
e.g.: Estimate statistics for a table and its columns
SQL> exec dbms_stats.gather_table_stats
(ownname => SCOTT', tabname => EMP', estimate_percent
=> 5);
e.g.: Estimate statistics for a table, its columns and indexes.
SQL> exec dbms_stats.gather_table_stats(ownname => SCOTT', tabname => 'EMP',
estimate_percent => 5, cascade => true);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
68/129
ORAMAIN Consulting Services,LLC 68
DBMS_STATS.GATHER_TABLE_STATS examples(cont.).
e.g.: Estimate statistics in parallel, the following uses 8 threads
to complete the task.
SQL> exec dbms_stats.gather_table_stats
(ownname => 'SCOTT', tabname => 'EMP_TRX',estimate_percent => 5, degree => 8);
When the above process is running;
select * from v$px_process; produces -
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
69/129
ORAMAIN Consulting Services,LLC 69
DBMS_STATS.GATHER_TABLE_STATS examples(cont.).
SERV|STATUS | PID|SPID |SID| SERIAL#
P000 | IN USE | 50|9684 | 7| 50586
P001 | IN USE | 65|9686 | 60| 51561
P002 | IN USE | 66|9688 | 17| 2694
P003 | IN USE | 67|9690 | 30| 39243P004 | IN USE | 68|9692 | 74| 11017
P005 | IN USE | 69|9694 | 48| 4253
P006 | IN USE | 70|9696 | 76| 17
P007 | IN USE | 71|9698 | 68| 12858 rows selected.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
70/129
ORAMAIN Consulting Services,LLC 70
DBMS_STATS.GATHER_INDEX_STATS examples.
Call syntax
dbms_stats.gather_index_stats(ownname, indname, partname,
estimate_percent, stattab, statid, statown);
e.g.:
SQL> exec dbms_stats.gather_index_stats
(ownname => 'SCOTT', indname => EMP_IDX');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
71/129
ORAMAIN Consulting Services,LLC 71
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);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
72/129
ORAMAIN Consulting Services,LLC 72
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.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
73/129
ORAMAIN Consulting Services,LLC 73
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 objectswithout any statistics.
objlist - table of type DBMS_STATS.OBJECTTAB, returns an
empty or stale list.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
74/129
ORAMAIN Consulting Services,LLC 74
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.).
e.g.: Gather schema statistics, for tables and indexes at 5%
estimate.
SQL> exec dbms_stats.gather_schema_stats(ownname =>'SCOTT', estimate_percent => 5, cascade => true,
options => 'GATHER');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
75/129
ORAMAIN Consulting Services,LLC 75
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.
SQL> exec dbms_stats.gather_schema_stats
(ownname => 'SCOTT', estimate_percent => 5,
options => 'GATHER EMPTY');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
76/129
ORAMAIN Consulting Services,LLC 76
DBMS_STATS.GATHER_SCHEMA_STATS examples
(cont.).
e.g.: To identify a list of objects without any statistics.
SQL>declare
l_owner varchar2(30) := 'SCOTT';
l_emptylst dbms_stats.objecttab;begin dbms_stats.gather_schema_stats(ownname =>
l_owner options => 'LIST EMPTY', objlist =>
l_emptylst);
for i in nvl(l_emptylst.first, 0) .. nvl(l_emptylst.last, 0)loop dbms_output.put_line(l_emptylst(i).objtype || '/' ||
l_emptylst(i).objname); end loop; end; /
INDEX/EMP_N1
TABLE/EMP
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
77/129
ORAMAIN Consulting Services,LLC 77
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.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
78/129
ORAMAIN Consulting Services,LLC 78
DBMS_STATS.DELETE_TABLE_STATS
Call Syntax
dbms_stats.delete_table_stats
(ownname, tabname, partname,
stattab, statid, cascade_parts,cascade_columns, cascade_indexes,
statown);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
79/129
ORAMAIN Consulting Services,LLC 79
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.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
80/129
ORAMAIN Consulting Services,LLC 80
DBMS_STATS.DELETE_TABLE_STATS examples (cont.)
e.g.: Delete statistics for a table and its columns and indexes.
SQL> exec dbms_stats.delete_table_stats
(ownname => 'SCOTT', tabname => 'EMP');
e.g.: Delete statistics for table only. Column and index statistics
will be preserved.
SQL> exec dbms_stats.delete_table_stats(ownname => 'SCOTT', tabname => 'EMP',
cascade_columns => false, cascade_indexes => false);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
81/129
ORAMAIN Consulting Services,LLC 81
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.
SQL> exec dbms_stats.delete_column_stats
(ownname => SCOTT', tabname => 'EMP',colname => ENAME');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
82/129
ORAMAIN Consulting Services,LLC 82
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.
SQL> exec dbms_stats.delete_index_stats
(ownname => 'SCOTT', indname => 'EMP_N1');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
83/129
ORAMAIN Consulting Services,LLC 83
DBMS_STATS.DELETE_SCHEMA_STATS examples
Call Syntax
dbms_stats.delete_schema_stats(ownname, stattab, statid,
statown);
e.g.: Deleting statistics for schema SCOTT.
SQL> exec dbms_stats.delete_schema_stats(SCOTT');
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
84/129
ORAMAIN Consulting Services,LLC 84
DBMS_STATS.DELETE_SCHEMA_STATS examples
Call Syntax
dbms_stats.delete_schema_stats(ownname, stattab, statid,
statown);
e.g.: Deleting statistics for schema SCOTT.
SQL> exec dbms_stats.delete_schema_stats(SCOTT');
DBMS_STATS.DELETE_DATABASE_STATS
Call Syntax
dbms_stats.delete_database_stats(stattab, statid, statown);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
85/129
ORAMAIN Consulting Services,LLC 85
DBMS_STATS.SET_TABLE_STATS examples
Call Syntax
dbms_stats.set_table_stats(ownname, tabname, partname,
stattab, statid, numrows, numblks, avgrlen, flags, statown);
Parameters
numrows - number of rows.
numblks - blocks in the table.avgrlen - average row length.
flags - currently for internal use only.
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
86/129
ORAMAIN Consulting Services,LLC 86
DBMS_STATS.SET_TABLE_STATS examples
e.g.:
SQL> exec dbms_stats.set_table_stats
(ownname => 'SCOTT', tabname => EMP,
numrows => 12422, numblks => 100, avgrlen => 124);
SQL> select owner, num_rows, blocks, avg_row_len
from dba_tables
where table_name = EMP;
OWNER | NUM_ROWS| BLOCKS|AVG_ROW_LEN
SCOTT | 12422| 100| 124
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
87/129
ORAMAIN Consulting Services,LLC 87
DBMS_STATS examples (cont.)
ALSO
dbms_stats.set_column_stats
dbms_stats.set_index_stats
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
88/129
ORAMAIN Consulting Services,LLC 88
DBMS_STATS.GET_TABLE_STATS examples
Call syntax
dbms_stats.get_table_stats(ownname, tabname,
partname, stattab, statid, numrows,
numblks, avgrlen, statown);
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
89/129
ORAMAIN Consulting Services,LLC 89
DBMS_STATS.GET_TABLE_STATS examples (cont.)
e.g.: getting table statistics data.
SQL> declare l_numrows number; l_numblks number;
l_avgrlen number;
begin
dbms_stats.get_table_stats(ownname => 'SCOTT',
tabname => 'EMP', numrows => l_numrows,numblks => l_numblks, avgrlen => 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
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
90/129
ORAMAIN Consulting Services,
LLC 90
DBMS_STATS examples (cont.)
ALSO
dbms_stats.get_column_stats
dbms_stats.get_index_stats
The Do s and Don ts of CBO
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
91/129
ORAMAIN Consulting Services,
LLC 91
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.
e o s d o s o C O
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
92/129
ORAMAIN Consulting Services,
LLC 92
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
93/129
ORAMAIN Consulting Services,
LLC 93
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.CREATE_STAT_TABLE (cont.)
e.g.: creating a user statistics table.
SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'STAT_AT_5PC',
tblspace => 'SYSTEM');
The table looks like this.
Desc stat_at_5pc
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
94/129
ORAMAIN Consulting Services,
LLC 94
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.CREATE_STAT_TABLE (cont.)
Name Null? Type
---------------------- -------- -----------------------
STATID VARCHAR2(30)
TYPE CHAR(1)VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
95/129
ORAMAIN Consulting Services,
LLC 95
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.CREATE_STAT_TABLE (cont.)
Name Null? Type
---------------------- -------- -----------------------
N1 NUMBER
N2 NUMBERN3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBERN7 NUMBER
N7 NUMBER
N8 NUMBER
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
96/129
ORAMAIN Consulting Services,
LLC 96
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.CREATE_STAT_TABLE (cont.)
Name Null? Type
---------------------- -------- -----------------------
N9 NUMBERN10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATER1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
-
8/3/2019 Jim Gillespie - Cbo
97/129
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
98/129
ORAMAIN Consulting Services,
LLC 98
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.
SQL> exec dbms_stats.export_table_stats
(ownname => SCOTT', tabname => EMP',
stattab => 'STAT_AT_5PC', cascade => true,
statown => SYS');
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
99/129
ORAMAIN Consulting Services,
LLC 99
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);
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
100/129
ORAMAIN Consulting Services,
LLC 100
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_DATABASE_STATS
Call syntax
dbms_stats.export_database_stats(stattab, statid, statown);
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
101/129
ORAMAIN Consulting Services,
LLC 101
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.IMPORT_TABLE_STATS
Retrieves 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);
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
102/129
ORAMAIN Consulting Services,
LLC 102
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.IMPORT_TABLE_STATS (cont.)
e.g.: importing statistics for table emp, including column and
indexes.
SQL> exec dbms_stats.import_table_stats
(ownname => 'SCOTT', tabname => EMP',
stattab => 'STAT_AT_5PC', cascade => true,
statown => 'SYS');
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
103/129
ORAMAIN Consulting Services,
LLC 103
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);
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
104/129
ORAMAIN Consulting Services,
LLC 104
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_DATABASE_STATS
Call syntax
dbms_stats.import_schema_stats(stattab, statid, statown);
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
105/129
ORAMAIN Consulting Services,
LLC 105
Exporting and importing statistics with DBMS_STATS
DBMS_STATS.DROP_STAT_TABLE
Drops a user statistics table.
Call syntax
dbms_stats.drop_stat_table(ownname, stattab);
e.g.: dropping my stat table.
SQL> exec dbms_stats.drop_stat_table(ownname => 'SCOTT',
stattab => 'STAT_AT_5PC');
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
106/129
ORAMAIN Consulting Services,
LLC 106
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 andpost 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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
107/129
ORAMAIN Consulting Services,
LLC 107
DML MONITORING (cont.)
SQL> alter table EMP monitoring;
SQL> select monitoring from dba_tables where table_name =
EMP';
MON---
YES
e.g.: Monitoring option for table EMP.SQL> exec dbms_stats.gather_table_stats(SCOTT', EMP')
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
108/129
ORAMAIN Consulting Services,
LLC 108
DML MONITORING (cont.)
Statistics on tables that had > 10% modifications can be seen in
the DBA_TAB_MODIFICATIONS view.
SQL> select * from dba_tab_modifications;
no rows selected
--DML activities were carried on the table.
SQL> select table_owner, table_name, inserts, updates,
deletes, timestamp, truncated
from dba_tab_modifications;
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
109/129
ORAMAIN Consulting Services,
LLC 109
DML MONITORING (cont.)
TABLE_OWNER TABLE_NAME INSERTS UPDATES
DELETES TIMESTAMP TRU
----------- ---------- ------- ------- ------- --------- ---
SCOTT EMP 1028577 0
19999 19-SEP-03 NO
SQL> exec dbms_stats.gather_schema_stats
(ownname => SCOTT',
options => 'GATHER STALE');
SQL> select * from dba_tab_modifications;
no rows selected
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
110/129
ORAMAIN Consulting Services,
LLC 110
DML MONITORING (cont.)
No known SGA issues when using montoring.
Does not result in performance issues.
Use on tables that are being changed significantly andoptimizer results fluctuate significantly.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
111/129
ORAMAIN Consulting Services,
LLC 111
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/*+ */
--+
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
112/129
ORAMAIN Consulting Services,
LLC 112
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. Theoptimizer will not notify the user about such hints.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
113/129
ORAMAIN Consulting Services,
LLC 113
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, theaccess path, the join order, the join method used etc..
-
8/3/2019 Jim Gillespie - Cbo
114/129
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
115/129
ORAMAIN Consulting Services,
LLC 115
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
116/129
ORAMAIN Consulting Services,
LLC 116
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 descendingorder of values.
INDEX_JOIN : use index join as an access path. Two indexes
could be joined to return the required values.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
117/129
ORAMAIN Consulting Services,
LLC 117
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
118/129
ORAMAIN Consulting Services,
LLC 118
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 andCHOOSE 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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
119/129
ORAMAIN Consulting Services,
LLC 119
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;
St ti ti f SYS h
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
120/129
ORAMAIN Consulting Services,
LLC 120
Statistics for SYS schema.
One issue that has always been in doubts is whether to generatestatistics 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 ofperformance issues. Oracle 10 and above would require
statistic generation for SYS schema as RBO will be
desupported.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
121/129
ORAMAIN Consulting Services,
LLC 121
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
122/129
ORAMAIN Consulting Services,
LLC 122
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 RULEhints for dictionary queries that are slow.
This way, Dictionary related queries will still be on RBO and
the application can run on CBO.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
123/129
ORAMAIN Consulting Services,
LLC 123
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
124/129
ORAMAIN Consulting Services,
LLC 124
PLAN STABILITY
- most useful when you cannot riskperformance changes
- preserves execution plans in storedoutlines
- the optimizer generates equivalentexecution plans from the stored outlines.
- facilitates migration from rule to costbased optimization when you upgrade.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
125/129
ORAMAIN Consulting Services,
LLC 125
PLAN STABILITY
- will use hints
- needs exact text matching
- degradation can occur if datatypeschange
- contrary to CBO.
- uses system table OL$ and OL$HINTS
- outlines are retained indefinetly
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
126/129
ORAMAIN Consulting Services,
LLC 126
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.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
127/129
ORAMAIN Consulting Services,
LLC 127
USING STORED OUTLINES
- set system parameterUSED_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 managingstored outlines and their categories, see
Oracle9i Supplied PL/SQL Packages Ref.
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
128/129
ORAMAIN Consulting Services,
LLC 128
VIEWING STORED OUTLINES
- select outline_category from v$sql
where sql_text like your sql text%;
- select name, sql_text
from user_outlines
where category = yourcategory;
The Dos and Donts of CBO
-
8/3/2019 Jim Gillespie - Cbo
129/129
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.
top related