Gather Stat in Oracle

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather stats periodically using DBMS_STATS package.

DBMS_STATS 

The DBMS_STATS package was introduced in Oracle 8i and is Oracle's preferred method of gathering statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. The functionality of the DBMS_STATS package varies greatly between database versions, as do the default parameter settings and the quality of the statistics they generate. It is worth spending some time checking the documentation relevant to your version.

You must gather statistics on a regular basis to provide the optimizer with information about schema objects. New statistics should be gathered after a schema object’s data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, you should collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows but you might need new statistics on the average row length.

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table 
SQL> EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES'); 
SQL> EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE); 
Cascade will gather Index stats associated with the table. 

DBMS_STATS.GATHER_INDEX_STATS is used to gather index stats.
SQL> EXEC DBMS_STATS.gather_index_stats('HR','EMPLOYEES_PK');

DBMS_STATS.GATHER_SCHEMA_STATS package is used to gather entire schema stats.
SQL>EXEC DBMS_STATS.gather_schema_stats('SCOTT');

Gather Other Database Objects Statistics

DBMS_STATS.GATHER_DATABASE_STATS package is used to gather entire database stats.
SQL> EXEC DBMS_STATS.gather_database_stats;

DBMS_STATS.GATHER_DICTIONARY_STATS package will gather dictionary statistics.
SQL> EXEC DBMS_STATS.gather_dictionary_stats;

Gather System and Fixed Object Stats:

There is no specific need to gather system (hardware, CPU, memory) and Fixed objects (X$ views) stats on a regular basis. You should only gather system & fixed objects stats when there is a major change on server hardware or major update to the database level.

SQL> EXEC DBMS_STATS.gather_system_stats;
SQL> EXEC DBMS_STATS.gather_fixed_objects_stats;

Check Stale Statistics: DBA_TAB_STATISTICS allows you to check stale statistics on a specific table:
SELECT owner, table_name, to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS'), stale_stats FROM dba_tab_statistics WHERE table_name='EMPLOYEES' and owner='HR';

DBA_IND_STATISTICS allows you to check stale statistics on specific index:
SELECT owner, table_name, index_name last_analyzed, stale_stats FROM dba_ind_statistics WHERE table_name='EMPLOYEES' and owner = 'HR';

Note:

Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package.

However, you must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

• To use the VALIDATE or LIST CHAINED ROWS clauses
• To collect information on freelist blocks   

Please feel free to ask. thank you 🙂

Toufique Khan