Post-Install Database Tasks - Oracle Exadata Recipes: A Problem-Solution Approach - page 613

AutoTask Client Window Group Window Next Start
-------------------------------- -------------- ----------------- --------------------
auto optimizer stats collection ORA$AT_WGRP_OS SUNDAY_WINDOW 07-OCT-12 06:00:00
auto optimizer stats collection ORA$AT_WGRP_OS SATURDAY_WINDOW 13-OCT-12 06:00:00
auto optimizer stats collection ORA$AT_WGRP_OS FRIDAY_WINDOW 12-OCT-12 22:00:00
auto optimizer stats collection ORA$AT_WGRP_OS THURSDAY_WINDOW 11-OCT-12 22:00:00
auto optimizer stats collection ORA$AT_WGRP_OS WEDNESDAY_WINDOW 10-OCT-12 22:00:00
auto optimizer stats collection ORA$AT_WGRP_OS TUESDAY_WINDOW 09-OCT-12 22:00:00
auto optimizer stats collection ORA$AT_WGRP_OS MONDAY_WINDOW 08-OCT-12 22:00:00
SQL>

The above information shows us the following:

• Automatic statistics collection is enabled using an 11gR2 AutoTask job.

• The job is implemented by a program called

gather_stats_prog , which runs the procedure
dbms_stats.gather_database_stats_job_proc .
The automatic statistic collection job is scheduled to run in the

•

ORA$AT_WGRP_OS
scheduler window group and inside a scheduling window on each day of the week.

The dbms_stats.gather_database_stats_job_proc is an Oracle internal statistics collection program that
gathers statistics for tables with missing or stale optimizer statistics. When executed, the procedure will operate in a
similar manner to running dbms_stats.gather_database_stats, dbms_stats.gather_schema_stats , or
dbms_stats.gather_table_stats . When the procedure runs, it calls DBMS_STATS to gather statistics using
predefined statistics preferences, which is an Oracle 11g feature designed to set statistics collection preferences at
the database, schema, or table level.

■ prior to oracle 11g, global statistics collection settings were defined using statistics parameters as set using
dbms_stats.set_param and retrieved using dbms_stats.get_param. these statistics parameters are deprecated in 11g
and starting with 11g, preferences should be used.

Note

Displaying and Setting Your Optimizer Statistics Preferences
Run the script in Listing 21-18 to see your current statistics preferences:

Listing 21-18. lst21-18-stats-prefs.sql

SQL> select dbms_stats.get_prefs('CASCADE') prefs from dual;
DBMS_STATS.AUTO_CASCADE
SQL> select dbms_stats.get_prefs('DEGREE') prefs from dual;
NULL
SQL> select dbms_stats.get_prefs('ESTIMATE_PERCENT') prefs from dual;
DBMS_STATS.AUTO_SAMPLE_SIZE
SQL> select dbms_stats.get_prefs('METHOD_OPT') prefs from dual;
FOR ALL COLUMNS SIZE AUTO
SQL> select dbms_stats.get_prefs('NO_INVALIDATE') prefs from dual;
DBMS_STATS.AUTO_INVALIDATE
SQL> select dbms_stats.get_prefs('GRANULARITY') prefs from dual;
AUTO