Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:
* Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.
* Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.
If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired. The mechanisms and issues relating to maintenance of internal statistics are explained below:
Using dbms_stat procedure:
dbms_stat procedure gather statistics for DB object, after gathering them, oracle build right sql_plans and optimize SQL statement.
Example gathering statistic for schema, this statement built SQL text.
select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner)
|| ''', tabname=> ''' || upper(table_name) || ''', degree=>10, cascade=> true);' from DBA_TABLES
where upper(owner) in ('SCOTT') and table_name not in
('TABLE1','TABLE2') order by owner, table_name;
note:
if you have very large tables you can exclude it for quickly gathering statistics
ownname means schema name
degree means that 10 oracle process will start gather information
cascade means that statistics gather for all indexes on tables
