The DBMS_STATS package in Oracle optimizes statistics on your database. The system benefits from cost-based optimization because it builds many queries dynamically, depending on user input.
With the cost-based optimizer, Oracle determines which indexes to use based on the distribution of data. Oracle 9i and 10 g documentation recommend against using ANALYZE to collect statistics for the Cost Based Optimizer; use DBMS_STATS instead.
If your database is large, run the Oracle update statistics. You can use a database-specific command, or you can run update statistics from the actions menu in the Database Configuration application. This calls dbms_stats.gather_table_stats with cascade true.
dbms_stats.gather_table_stats
(ownname => 'MAXIMO', tabname => 'ASSET', cascade => true)
Oracle has two optimizer modes: cost-based and rule-based.
By default, the optimizer mode is set to CHOOSE. To determine the mode in effect, select from the v$parameter table:
select value from v$parameter where name='optimizer_mode';
If the mode is CHOOSE, you use the rule-based optimizer unless statistics exist. Statistics do not exist if you never analyzed your tables. Do not set the optimizer mode to RULE.