 |
|
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.
|