The latest update on Delayed Block Cleanout

Well, well, well.  Apparently delayed block cleanout doesn’t happen during a direct path read. Which makes a lot of sense, if several folks are doing direct path reads at the same time on the same object, then how do you control the updates to the blocks?  In case you don't know, a driect path read is done into the PGA (Program Global Area) which is basically private space to your process.  No one else knows you have those blocks.  So if two (or more) folks are reading the same block at the same time into their own PGA, then each one could make a change to the block at the same time and really mess things up.  Kind of blows up the entire read consistency and locking model.

 What is quite interesting to me is that on windows it’s not doing direct path and on UNIX it is.   So the blocks do get cleaned out on windows BUT not on UNIX.  Not my test is at least unlikely to happen frequently in “real life”.  

My test was simple: Create a big table.  Select from it.  Delete it all. Commit. Select from it again.  Select from it another time.

In 11 and earlier,  you’d see the clean outs happen on the first select after the delete, and then not on the next.  But starting in 12 (on UNIX), you’d see the clean out every time after the delete.  Because it's always doing direct path reads, hence it has to clean out the blocks each time it reads them in.  It's cleaning out the locks and such left over from the delete, since most of the blocks are not cleaned out at the time of the commit. The commit does clean out some of them.  But this table is big enough that there is no way it can get to all of them.

On windows the behavior was like it was in 11. It's not doing direct path it's doing "normal" reads in to the buffer cache.

So change in the game for how reads are done in 12. 

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.