What’s your Preference?

Gathering stats in Oracle has come a long way from the simple Analyze command we used back in V7 days.  Today the DBMS_STATS package has a lot of procedures and functions in it.  And on top of that many of these procedures and functions have many parameters.  Setting these correctly can make a big difference for the stats collected, which in turn can have a huge impact on performance.  There is a set of procedures in DBMS_STATS that can help you bring some sanity to the chaos. 

These procedures (listed below) allow you to set many of the parameters used by the different statistic collection procedures to a value you want.  And even let you set up defaults, called global settings.  Here are the procedures.

SET_TABLE_PREFS – Set a parameter for a particular table

SET_SCHEMA_PREFS – Set a parameter all current tables in a schema*

SET_DATABASE_PREFS – Set a parameter all current tables in the database*

SET_GLOBAL_PREFS – Set a parameter to a "default" at the global level

* Note: The schema and database calls actually call SET_TABLE_PREFS for all tables currently defined at that level (schema or database).  These setting do not affect new tables.  But SET_GLOBAL_PREFS does!

Another note worth mentioning is that the SET_DATABASE_PREFS procedure doesn’t change a setting for Oracle owned tables by default.  There is a parameter ADD_SYS for this procedure which is set to false by default. Set this to true if you wish to have the setting applied to Oracle owned tables as well.

Common to all these procedures are 2 parameters, PNAME (parameter name) and PVALUE (parameter value).  The list of possible PNAME values is here:

AUTOSTATS_TARGET *

CASCADE

CONCURRENT *

DEGREE

ESTIMATE_PERCENT

GLOBAL_TEMP_TABLE_STATS

GRANULARITY

INCREMENTAL

INCREMENTAL_LEVEL

INCREMENTAL_STALENESS

METHOD_OPT

NO_INVALIDATE

PUBLISH

STALE_PERCENT

TABLE_CACHED_BLOCKS

OPTIONS

* Note: Only valid at the global level

As you can see there are plenty of things you can set.  As a simple example if you wanted to set the STALE_PERCENT for all current tables to 30% in you database you could do something like this:

SQL> EXEC DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT','30');

The key to note here is that this explicitly sets STALE_PERCENT at the table level to 30.  After running this you could do something like this to see it’s setting on a table (here the good old EMP table).

SQL> DECLARE

  2   V_PVALUE VARCHAR2(100);

  3  BEGIN

  4   V_PVALUE := DBMS_STATS.GET_PREFS('STALE_PERCENT','OP','EMP');

  5   DBMS_OUTPUT.PUT_LINE('STALE PERCENT IS '||V_PVALUE);

  6  END;

  7  /

STALE PERCENT IS 30

You can also set the global preferences if you like.  Setting these will only take affect when there isn’t a setting at the table level, at create time.  Let’s take a look at how this works.  So if you were to do this:

EXEC DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','42');

And run the little block from above again you’d see that the values stays at 30 for the EMP table.  Now if you did this to the EMP table:

EXEC DBMS_STATS.SET_TABLE_PREFS('OP','EMP','STALE_PERCENT',NULL);

And checking the STATLE_PERCENT on EMP you’ll see its set to:

STALE PERCENT IS 10

Which is the Oracle system default.  OK, so what did setting the global preference really do?  Now create a new table as a copy of EMP.

SQL> create table emp2 as select * from emp;

Now check the STALE_PERCENT on EMP2 and you’ll see:

SQL> DECLARE

  2   V_PVALUE VARCHAR2(100);

  3  BEGIN

  4   V_PVALUE := DBMS_STATS.GET_PREFS('STALE_PERCENT','OP','EMP2');

  5   DBMS_OUTPUT.PUT_LINE('STALE PERCENT IS '||V_PVALUE);

  6  END;

  7  /

STALE PERCENT IS 42

So what’s the take-away here?  One setting the preferences is easy to do for a single table, all tables in a schema and the entire database.  And two you can have them set for new tables as well (global).  The thing to keep in mind is that if you set a preference to NULL, it will take on the Oracle default value not the setting at the global level.