For OLTP applications, you
should reanalyze the objects on a time-based schedule (such as via a weekly or nightly process).
NOTE
As of Oracle Database 10g Release 1, the rule-based optimizer is
desupported.
Statistics on objects are gathered via executions of the DBMS_STATS package??™s procedures. If
you analyze a table, its associated indexes are automatically analyzed as well. You can analyze a
schema (via the GATHER_SCHEMA_STATS procedure) or a specific table (via GATHER_TABLE_
STATS). You can also analyze only the indexed columns, thus speeding the analysis process. In
general, you should analyze a table??™s indexes each time you analyze the table. In the following
listing, the PRACTICE schema is analyzed:
execute DBMS_STATS.GATHER_SCHEMA_STATS('PRACTICE', 'COMPUTE');
You can view the statistics on tables and indexes via DBA_TABLES, DBA_TAB_COL_
STATISTICS, and DBA_INDEXES. Some column-level statistics are still provided in DBA_TAB_
COLUMNS, but they are provided there strictly for backward compatibility. The statistics for the
columns of partitioned tables are found in DBA_PART_COL_STATISTICS.
NOTE
As of Oracle Database 10g, statistics are automatically gathered
in a default installation using the automated maintenance tasks
infrastructure (AutoTask) during maintenance windows.
Chapter 8: Database Tuning 257
When the command in the preceding listing is executed, all the objects belonging to the
PRACTICE schema will be analyzed using the compute statistics option.
Pages:
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433