Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









The Optimizers & the Analyze Command

Oracle Tips by Burleson Consulting

Update.  The dbms_stats procedure should always be used to analyze Oracle tables, and the "analyze" command is only for Oracle 8i and earlier.

exec dbms_stats.gather_table_stats(MYOWNER, 'MYTABLE', cascade => TRUE);


The Optimizers & the Analyze Command

Optimizers are required to ensure that a query is processed in an optimal manner. In earlier versions of Oracle the only available optimizer was the RULE based optimizer. In later versions of Oracle7 and all versions of Oracle8 both RULE and COST based optimizers are available.

RULE Based Optimizer

As its name implies the rule based optimizer uses a set of 15 rules to determine how to best process a query. The rule chosen is based strictly on query structure and pays no attention to the number of rows, partitioning or other query specific features and statistics. The query optimization paths used by the cost based optimizer are:




Single row by ROWID


Single row by Cluster join


Single row by hash cluster key with unique or primary key


Single row by unique or primary key


Cluster join


Hash cluster key


Indexed cluster key


Composite Index


Single Column index


Bounded range search on indexed columns


Unbounded range search on indexed columns


Sort-merge join


MAX or MIN on indexed column


ORDER BY indexed columns


Full table scan

The major benefit of the rule based optimizer was that for a specific query the execution plan was stable regardless of how big the table or its associated indexes grew as long as the index structure didn?t change. The major disadvantages of the rule based optimizer are that the execution plan didn?t change no matter how large the table or its indexes grew, and, it can?t take advantage of new features as easily as a cost based optimizer.

COST Based Optimizer

The COST based optimizer was introduced in later versions of Oracle7. The cost based optimizer uses a proprietary algorithm to assign a cost to each step of an execution plan for a query, and then, by looking at equivalent plans the optimizer can choose the one with the lowest cost for a specific query. The cost algorithm is dependent upon accurate table statistics in order to come up with accurate cost information. The table statistics are created and maintained by use of the ANALYZE command.

The major benefit of the cost based optimizer is that for changes in table and index sizes the optimizer can re-evaluate the execution plan and adjust to obtain an optimal performance profile. However, the cost based optimizer is only as good as the statistics fed to it, and, in tests a fully qualified DBA using the rule based optimizer can get better performance. However, unless you like to spend your free time using the tools tkprof, explain plan or third query party analyzers, I suggest just using the cost based optimizer and an ?intelligent? table analyzer procedure such as the one shown in Source 36.

owner_name in varchar2,
break_rows in number,
percent_change in number) AS
CURSOR get_tab_count (own varchar2) IS
        SELECT table_name, nvl(num_rows,1)
        FROM dba_tables
        WHERE owner = upper(own);
  tab_name VARCHAR2(64);
  rows            NUMBER;
  string          VARCHAR2(255);
  cur             INTEGER;
  ret             INTEGER;
  row_count       NUMBER;
  com_string      VARCHAR2(255);
  OPEN get_tab_count (owner_name);
        FETCH get_tab_count INTO tab_name, rows;
        IF rows=0 THEN
        END IF;
   EXIT WHEN get_tab_count%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Table name: '||tab_name||' rows: '||to_char(rows));
   IF row_count=0 THEN
   END IF;
   DBMS_OUTPUT.PUT_LINE('Row count for '||tab_name||': '||to_char(row_count));
   DBMS_OUTPUT.PUT_LINE('Ratio: '||to_char(row_count/rows));
        IF (row_count/rows)>(1+(percent_change/100))
         OR (rows/row_count)>(1+(percent_change/100)) THEN
           IF (row_count<break_rows) THEN
             string :=
              'ANALYZE TABLE '||tab_name||' COMPUTE STATISTICS ';
              string :=
           END IF;
           cur := DBMS_SQL.OPEN_CURSOR;
           DBMS_OUTPUT.PUT_LINE('Beginning analysis');
           ret := DBMS_SQL.EXECUTE(cur)  ;
           DBMS_OUTPUT.PUT_LINE(' Table: '||tab_name||' had to be analyzed.');
            WHEN OTHERS THEN
              'Error in analyze: '||to_char(sqlcode)||' on '||tab_name,TRUE);
            IF dbms_sql.is_open(cur) THEN
            END IF;
        END IF;
     WHEN others THEN
  CLOSE get_tab_count;

Source 36: Example PL/SQL Procedure to do a ?Smart? analysis of tables.

The procedure in Source 36 allows you to specify the schema that owns the tables to be analyzed, it then proceeds to check the actual row count and compare that value to the row count as stored in the DBA_TABLES view. The row count stored in the DBA_TABLES view is the calculated row count from the last analyze operation. If the percent difference (either positive or negative) exceeds the value input (I usually use 10) then the table will be analyzed. If the table exceeds a predetermined row threshold (depending on table row size and the size of your sort area) then an estimate statistics using 30% sampling is used, otherwise a full compute of statistics is performed.

Using a smart analysis procedure analysis time dropped from several hours to 30 to 40 minutes in one PeopleSoft environment. By only analyzing those tables that actually require it you can produce a considerable time saving. In the version of this program provided with the RevealNet Oracle Administrator program all activities are also logged in a database table for later review and use in trending analysis.


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.