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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

   Rank       

Path                         

      1          

Single row by ROWID

      2          

Single row by Cluster join

      3          

Single row by hash cluster key with unique or primary key

      4          

Single row by unique or primary key

      5          

Cluster join

      6          

Hash cluster key

      7          

Indexed cluster key

      8          

Composite Index

      9          

Single Column index

      10        

Bounded range search on indexed columns

      11         

Unbounded range search on indexed columns

      12         

Sort-merge join

      13         

MAX or MIN on indexed column

      14         

ORDER BY indexed columns

      15         

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.

CREATE OR REPLACE PROCEDURE check_tables (
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);
--
BEGIN
  OPEN get_tab_count (owner_name);
  LOOP
   BEGIN
        FETCH get_tab_count INTO tab_name, rows;
        tab_name:=owner_name||'.'||tab_name;
        IF rows=0 THEN
        rows:=1;
        END IF;
   EXIT WHEN get_tab_count%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Table name: '||tab_name||' rows: '||to_char(rows));
   DBA_UTILITIES.GET_COUNT(tab_name,row_count);
   IF row_count=0 THEN
        row_count:=1;
   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
         BEGIN
           IF (row_count<break_rows) THEN
             string :=
              'ANALYZE TABLE '||tab_name||' COMPUTE STATISTICS ';
           ELSE
              string :=
       'ANALYZE TABLE '||tab_name||' ESTIMATE STATISTICS SAMPLE 30 PERCENT';
           END IF;
           cur := DBMS_SQL.OPEN_CURSOR;
           DBMS_OUTPUT.PUT_LINE('Beginning analysis');
           DBMS_SQL.PARSE(cur,string,dbms_sql.v7);
           ret := DBMS_SQL.EXECUTE(cur)  ;
           DBMS_SQL.CLOSE_CURSOR(cur);
           DBMS_OUTPUT.PUT_LINE(' Table: '||tab_name||' had to be analyzed.');
           EXCEPTION
            WHEN OTHERS THEN
             raise_application_error(-20002,
              'Error in analyze: '||to_char(sqlcode)||' on '||tab_name,TRUE);
            IF dbms_sql.is_open(cur) THEN
             dbms_sql.close_cursor(cur);
            END IF;
         END;
        END IF;
    EXCEPTION
     WHEN others THEN
       null;
   END;
  COMMIT;
  END LOOP;
  CLOSE get_tab_count;
END;

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.