 |
|
Tuning with Cost-Based Optimization (CBO)
Oracle Tips by Burleson
|
The cost-based optimizer uses “statistics” that
are collected from the table using the analyze table and
analyze index commands. Oracle uses these metrics about the tables
in order to intelligently determine the most efficient way of
servicing the SQL query. It is important to recognize that in many
cases the cost-based optimizer may not always make the proper decision
in terms of the speed of the query, and Oracle has provided numerous
hints to allow the DBA and developer to tune the execution plan. The
cost-based optimizer is constantly being improved, but there are still
many cases where the rule-based optimizer will result in faster Oracle
queries. One of the first things a seasoned Oracle DBA does when
tuning an SQL statement is to add a RULE hint, or use the alter
session set optimizer goal = rule statement in order to change the
default optimizer mode from cost-based to rule-based optimization.
Here is a list of common hints that are used to
change the execution plan in the cost-based optimizer:
·
ALL_ROWS This
is the cost-based approach designed to provide the best overall
throughput and minimum resource consumption.
·
AND_EQUAL(table_name index_name1) Causes
merge scans of two to five single-column indexes.
·
CLUSTER(table_name) Requests
a cluster scan of the table_name.
·
FIRST_ROWS This
is the cost-based approach designed to provide the best response time.
·
FULL Requests
the bypassing of indexes, doing a full table scan.
·
HASH(table_name) Causes
a hash scan of table_name.
·
HASH_AJ This
hint is placed in a NOT IN subquery to perform a hash anti-JOIN.
·
INDEX(table_name index_name) Requests
the use of the specified index against the table. If no index is
specified, Oracle will choose the best index.
·
INDEX_ASC(table_name index_name) Requests
to use the ascending index on a range scan operation.
·
INDEX_COMBINE(table_name index_name) Requests
that the specified bitmapped index be used.
·
INDEX_DESC(table_name index_name) Requests
to use the descending index on a range scan operation.
·
MERGE_AJ This
hint is placed in a NOT IN subquery to perform an anti-JOIN.
·
NO_EXPAND Requests
the query not to perform OR expansion (i.e., OR concatenation).
·
NO_MERGE This
hint is used in a view to prevent it from being merged into a parent
query.
·
NOCACHE This
hint causes the table CACHE option to be bypassed.
·
NOPARALLEL This
hint turns off the Parallel Query option.
·
ORDERED Requests
that the tables should be joined in the order that they are specified
(left to right). For example, if you know that a state table has only
50 rows, you may want to use this hint to make state the driving
table.
·
PARALLEL(table_name degree) For
full table scans, this hint requests that the table_name query
be executed in parallel mode with degree processes servicing
the table access.
·
PUSH_SUBQ This
hint causes all subqueries in the query block to be executed at the
earliest possible time.
·
ROWID Requests
a rowid scan of the specified table.
·
RULE Indicates
that the rule-based optimizer should be invoked (sometimes due to the
absence of table statistics).
·
STAR This
hint forces the use of a star query plan, provided that there are at
least three tables in the query and a concatenated index exists on the
fact table.
·
USE_CONCAT Requests
that a UNION ALL be used for all OR conditions.
·
USE_HASH(table_name1 table_name2) Requests
a hash JOIN against the specified tables.
·
USE_MERGE Requests
a sort merge operation.
·
USE_NL(table_name) Requests
a nested loop operation with the specified table as the driving table.
It is beyond the scope of this chapter to go
into all of these hints, so for now just consider hints to be the
tools you use to tune cost-based execution plans.
Invoking the Cost-Based Optimizer
Before retrieving any rows, the cost-based optimizer
must create an execution plan that tells Oracle the
order in which to access the desired table and indexes. The cost-based
optimizer works by weighing the relative costs for different access
paths to the data, and choosing the path with the smallest relative
cost. Once the statistics have been collected, there are three ways to
invoke the cost-based optimizer:
·
Setting the init.ora parameter optimizer_mode all_rows,
first_rows
or choose
·
ALTER SESSION SET optimizer_goal all_rows
or first_rows
·
Cost-based hints /*+
all_rows */ or --+ all_rows
These costs for a query are determined with the
aid of table and index statistics that are computed with the
analyze table and analyze index commands in Oracle.
L 11-9
ANALYZE TABLE xxx ESTIMATE STATISTICS SAMPLE
500 rows;
ANALYZE INDEX xxx COMPUTE STATISTICS;
Note that there are documented problems when sampling
less than a 25–30 percent sample of the rows in a table. This is due
to the way Oracle calculates row counts. Contrary to early
documentation, row counts are done using an average row size vs. total
occupied blocks and not a full count. In tables of several million
rows, the row counts can be off by as much as 15 percent if a sample
of less than 25 percent was used for the analysis.
Gathering Statistics for the CBO
It is important that the statistics be
refreshed periodically, especially when the distribution of data
changes frequently. As such, the following SQL may be used to run the
analyze statements for all of the tables and indexes. There
are two methods for analyzing statistics, and this first example uses
the dbms_utility function to perform the analysis. The code below
estimates the statistics for a schema called CPM. Also, the FOR TABLE
FOR ALL INDEXES analyzes the indexes, but without creating entries in
the dba_histograms view.
CREATE PROCEDURE
get_stats
IS
BEGIN
dbms_utility.analyze_schema
('CPM','ESTIMATE',null,20,'FOR TABLE FOR ALL INDEXES');
END;
/
Once the get_stats procedure os created, it can
be scheduled to run weekly using the dbms_job utility.
exec dbms_job.submit
(:jobno,'GET_STATS;',SYSDATE,'TRUNC(SYSDATE + 7)+9/24');
However, it is not always a good idea to use
Oracle's dbms_utility.analyze_schema or the dbms_ddl.analyze_object
packages to perform this task, since a failure on one of the
statements can affect the results of subsequent statements. The
following script will generate the proper SQL syntax.
analyze.ksh
L 11-10
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=prodb2
export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2
-d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus /<<!
set pages 999
set heading off
set echo off
set feedback off
connect internal;
spool /export/home/oracle/analyze.sql;
select
'analyze table ' ||owner||'.'||table_name||' estimate statistics
sample 5000 rows;'
from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT')'DONALD';
select
'analyze index '||owner||'.'||index_name||' compute statistics;'
from dba_indexes
where owner not in ('SYS','SYSTEM','PERFSTAT');
spool off;
set echo on
set feedback on
@/export/home/oracle/analyze
exit
!
Most shops schedule a script like this to run
weekly, or whenever there have been significant changes to the table
data. However, it is not necessary to reanalyze tables and indexes
that remain relatively constant. For example, a database where the
tables have a constant number of rows and indexes where the
distribution of values remain constant will not benefit from frequent
analysis.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
