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);

1. How often to re analyze with dbms_stats?
   

2. Oracle histogram analysis with dbms_stats
   

3. Using the analyze command and DBMS_STATS

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:

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.