Gather Statistics

On SYS Objects
If your database encounters a lot of changes (DMLs) for SYS schema objects, then it is recommended to collect SYS schema statistics.  The collection of statistics on SYS Schema objects will optimize the performance of internal recursive queries and application queries on SYS schema objects.

To gather the dictionary stats, execute one of following:-

 

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

'Fixed' Objects
Gather_fixed_objects_stats also gathers statistics for dynamic tables e.g. the X$ tables which loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommended if poor performance is encountered while querying dynamic views e.g. V$ views.
Since fixed objects record current database activity; statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity .

• Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
• Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
• Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
• RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.

To gather the fixed objects stats use:-

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

you can use the following query to view the last analyzed for a table.

select OWNER, TABLE_NAME, LAST_ANALYZED 
     from dba_tab_statistics where table_name='X$KGLDP';

Delete Statistics
you can delete statistics if required 

exec dbms_stats.delete_fixed_objects_stats(); 

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS'); 

Statistics on an individual table
To achieve a quick delete and recreate of the statistics on an individual table and it's indexes you can use the following.
exec dbms_stats.delete_table_stats(ownname=>'user_name',-
  tabname=>'table_name',cascade_indexes=>true);

exec dbms_stats.gather_table_stats(ownname=>'user_name',-
   tabname=>'table_name',-
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,-
   cascade=>true,-
   method_opt=>'for all columns size AUTO'); 

Restoring table statistics
Gathering new optimizer statistics should maintain or improve existing execution plans, but it is possible that some queries performance may degrade. Note that from 10gR1 previous copies of statistics are maintained by default for the last 31 days and can be restored in the case of problems.
The default period for which statistics are retained is 31 days but this can be altered with:-

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

Return the number of days stats are currently retained for.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

Return the oldest possible date stats can be restored from

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

Show the times statistics were regathered for a given table. 

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history

 
Restore the statistics
Having decided what date you know the statistics were good for, you can use:-
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
Examples:execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');

exec dbms_stats.gather_table_stats( -

ownname => ' Schema_name ', -

tabname => ' Table_name ', -

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -

cascade => TRUE, -

method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

exec dbms_stats.gather_schema_stats( -

ownname => ' Schema_name ', -

cascade => TRUE, -

method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

exec dbms_stats.gather_database_stats( -

cascade => TRUE, -

method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Incremental statistics

Introduced in Oracle Database 11g to improve the performance of gathering statistics on large partitioned table. When incremental statistics maintenance is enabled for a partitioned table, oracle accurately generated global level statistics by aggregating partition level statistics. incremental statistics maintenance needs to gather statistics on any partition that will change the global or table level statistics. For instance, the min or max value for a column could change after just one row is inserted or updated in the table.

Note: Gathering new optimizer statistics may invalidate cursors in the shared pool so it is prudent to restrict all gathering operations execution to periods of low activity in the database, such as the scheduled maintenance windows.



Ref:798257.1,457926.1,452011.1, 749227.1