Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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_ROWSThis 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_ROWSThis is the cost-based approach designed to provide the best response time.

·        FULLRequests the bypassing of indexes, doing a full table scan.

·        HASH(table_name)Causes a hash scan of table_name.

·        HASH_AJThis 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_AJThis hint is placed in a NOT IN subquery to perform an anti-JOIN.

·        NO_EXPANDRequests the query not to perform OR expansion (i.e., OR concatenation).

·        NO_MERGEThis hint is used in a view to prevent it from being merged into a parent query.

·        NOCACHEThis hint causes the table CACHE option to be bypassed.

·        NOPARALLELThis hint turns off the Parallel Query option.

·        ORDEREDRequests 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_SUBQThis hint causes all subqueries in the query block to be executed at the earliest possible time.

·        ROWIDRequests a rowid scan of the specified table.

·        RULEIndicates that the rule-based optimizer should be invoked (sometimes due to the absence of table statistics).

·        STARThis 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_CONCATRequests 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_MERGERequests 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_modeall_rows, first_rows
or choose

·        ALTER SESSION SET optimizer_goalall_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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter