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

SQL> select dbms_stats.get_prefs('PUBLISH') prefs from dual;
TRUE
SQL> select dbms_stats.get_prefs('INCREMENTAL') prefs from dual;
FALSE
SQL> select dbms_stats.get_prefs('STALE_PERCENT') prefs from dual;
10
SQL>

On Exadata databases and all Oracle 11gR2 databases, we recommend automating statistics preferences to the
greatest extent possible unless your database or workload justifies changing Oracle's default behavior. Doing so will
lead to more accurate optimizer statistics with the least amount of performance overhead. To set your optimizer
statistics parameters, run the script in Listing 21-19. (Note that many of these will implement the default behavior; text
outlined in bold is not the default behavior and will be discussed after the script output is displayed.)

Listing 21-19. lst21-19-stats-setprefs.sql

SQL> exec dbms_stats.set_database_prefs(pname=>'CASCADE',
pvalue=>'DBMS_STATS.AUTO_CASCADE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'DEGREE',
pvalue=>'DBMS_STATS.AUTO_DEGREE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'ESTIMATE_PERCENT',
pvalue=>'DBMS_STATS.AUTO_SAMPLE_SIZE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'GRANULARITY',
pvalue=>'AUTO');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'METHOD_OPT',
pvalue=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'STALE_PERCENT',
pvalue=>'10');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'NO_INVALIDATE',
pvalue=>'DBMS_STATS.AUTO_INVALIDATE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'PUBLISH',
pvalue=>'TRUE');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_database_prefs(pname=>'INCREMENTAL',
pvalue=>'TRUE');
PL/SQL procedure successfully completed.
SQL>

The following is a summary of these recommended statistics collection preferences:

CASCADE option, DBMS_STATS.AUTO_CASCADE instructs DBMS_STATS to cascade statistics
collection for all dependent indexes on a table.

• For the

DEGREE option, DBMS_STATS.AUTO_DEGREE samples the number of blocks in the
segment and automatically adjusts the degree of parallelism to use when gathering statistics.
In general, the larger the segment, the higher degree of parallelism. If Auto DOP is enabled,
Oracle will use the Auto DOP algorithms as discussed in Recipe 21-2.

• For the