Automatic Oracle Statistics

Some Information and Queries on "

Automatically Collecting Statistics on Tables

"

--will show you what kind of statistics level and whether AUTO is enabled  
   
    select * from V$STATISTICS_LEVEL;
    select * from v$parameter where name like '%statistics%';
   
--Query to Find which level of statistics Set in DB:
    select * from v$parameter where name like '%statistics%';

--Two Scheduler windows are predefined upon installation of Oracle Database:
--WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
--WEEKEND_WINDOW covers whole days Saturday and Sunday.

--Query to find When statistics were last gathered? :
    SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.
    SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

--Query to find Status of automated statistics gathering?:
    SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

--Query to find whether the statistics have been collected or not  for particular table, and also gives information like when was the last Analyze done:
    select * from DBA_TAB_STATISTICS where table_name = '<TABLE NAME>'


--Query to complete History of table analyze done:
    select * from DBA_TAB_STATS_HISTORY where table_name = '<TABLE NAME>' order by stats_update_time desc

--Query to find Windows Groups? :
    SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

--Query to Window Schedules? :
    SELECT window_name, start_time, duration FROM dba_autotask_schedule;

--How to Manually gather Database Statistics in this Schema:
    EXEC dbms_stats.gather_schema_stats(ownname=>NULL, cascade=>TRUE); -- cascade=>TRUE means include Table Indexes too.

--How to Manually gather Database Statistics in all Schemas!
-- Probably need to CONNECT / AS SYSDBA
    EXEC dbms_stats.gather_database_stats;

--Some Additional Information given by Mark D Powell  and  John Thorton
--**********************************************************************
--Query to find which JOB responsible for Collecting statistics.

    select job_name from dba_scheduler_jobs order by 1;

-- Oracle updates the CBO statistics every night during the DBMS_SCHEDULER job maintenance window and every weekend based on the
-- staleness setting for the optimizer or object if you have set table level DBO parameters.  You should not need to make any changes.
--- -
--You can collect the statistics via dbms_stats for any table when some event has made the statistics incorrect or
--sub-optimal and you can use SET_TABLE_PREFS to adjust the CBO statistics collection parameters for a specific table where the defaults are not working as well as you would like.