Oracle Scratchpad

May 9, 2013


Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 8:14 am BST May 9,2013

Cost Based Oracle – Fundamentals (November 2005)

But the most interesting function for our purposes is sys_op_countchg(). Judging from its name, this function is probably counting changes, and the first input parameter is the block ID portion (object_id, relative file number, and block number) of the table’s rowid, so the function is clearly matching our notional description of how the clustering_factor is calculated. But what is that 1 we see as the second parameter?

When I first understood how the clustering_factor was defined, I soon realized that its biggest flaw was that Oracle wasn’t remembering recent history as it walked the index; it only remembered the previous table block so that it could check whether the latest row was in the same table block as last time or in a new table block. So when I saw this function, my first guess (or hope) was that the second parameter was a method of telling Oracle to remember a list of previous block visits as it walked the index.

And finally, Oracle Corp. had implemented an official interface to the second parameter of sys_op_countchg() – provided you install the right patch – through a new table (or schema, or database) preference type available to the dbms_stats.set_table_prefs() procedure.

I’ve been meaning to write this post for two or three months, ever since Sean Molloy sent me an email about short blog note from Martin Decker describing the contents of MOS Doc ID 13262857.8 titled “Bug 13262857  Enh: provide some control over DBMS_STATS index clustering factor computation”. Unfortunately I’ve not yet had time to investigate the patch, but I don’t think I need to any more because Richard Foote has written it up in his latest blog post.

Read Richard’s post – it’s important.

Update 10th May

Richard’s post has, unsurprisingly, produced a buzz of excitement among his readers – and started up the discussion of how best to use this capability; so here’s another quote from the book (p.111 – available in the zip file of chapter 5 in the Code download):

So using Oracle’s own function for calculating the clustering_factor, but substituting the freelists value for the table, may be a valid method for correcting some errors in the clustering_factor for indexes on strongly sequenced data. (The same strategy applies if you use multiple freelist groups—but multiply freelists by freelist groups to set the second parameter.)

Can a similar strategy be used to find a modified clustering_factor in other circumstances? I think the answer is a cautious “yes” for tables that are in ASSM tablespaces. Remember that Oracle currently allocates and formats 16 new blocks at a time when using automatic segment space management (even when the extent sizes are very large, apparently). This means that new data will be roughly scattered across groups of 16 blocks, rather than being tightly packed.

Calling Oracle’s sys_op_countchg() function with a parameter of 16 could be enough to produce a reasonable clustering_factor where Oracle currently produces a meaningless one. The value 16 should, however, be used as an upper bound. If your real degree of concurrency is typically less than 16, then your actual degree of concurrency would probably be more appropriate.

Whatever you do when experimenting with this function—don’t simply apply it across the board to all indexes, or even all indexes on a particular table. There will probably be just a handful of critical indexes where it is a good way of telling Oracle a little more of the truth about your system—in other cases you will simply be confusing the issue.

Note particularly the comments about how the best value depends on the data in the indexed columns, the table configuration, and the degree of concurrency – you don’t necessarily want to use the same value for every index on a given table. That’s a shame, since Oracle has defined the interface as a TABLE preference, so if you set it then you get the same for every index on that table. Despite this, if you’re prepared to put in a little control work, it does mean that you can use an official Oracle mechanism to play the game I was suggesting in the book – for each “special” index, set the preference, collect the stats, then clear the preference.


My suggestion of 16 doesn’t allow for the effects of RAC, and I wrote a note a couple of years after this one to suggest that when running RAC a better choice for table_cached_blocks would be 16 * number of instances.


  1. […] the clustering_factor for indexes. The juxtaposition of these two topics made me realise that the advice I had given in “Cost Based Oracle – Fundamentals” 10 years ago was (probably) incomplete, and […]

    Pingback by Clustering_factor | Oracle Scratchpad — November 2, 2015 @ 10:27 am GMT Nov 2,2015 | Reply

  2. […] the ext_id values were arriving in a fairly well ordered fashion then setting the table preference table_cached_blocks to a value around 16 (or 16 x N for an N-node RAC cluster) and re-gathering stats on the indexes […]

    Pingback by Merge Precision | Oracle Scratchpad — June 6, 2016 @ 12:40 pm BST Jun 6,2016 | Reply

  3. […] another little note on the clustering_factor for an index and the table preference table_cached_blocks that can be set with a call to dbms_stats.set_table_prefs(). I might be repeating a point that […]

    Pingback by Clustering_Factor | Oracle Scratchpad — July 2, 2018 @ 1:24 pm BST Jul 2,2018 | Reply

  4. […] not a mechanism that lends itself to calculating the clustering_factor if you’ve set the table_cached_blocks preference to a value other than […]

    Pingback by Clustering_Factor | Oracle Scratchpad — October 16, 2019 @ 2:07 pm BST Oct 16,2019 | Reply

  5. […] Initial introduction. […]

    Pingback by CBO Oddities – 1 | Oracle Scratchpad — October 18, 2019 @ 6:10 pm BST Oct 18,2019 | Reply

  6. […] forget that it’s a good idea to change think about setting the table preference “table_cached_blocks” to allow Oracle to produce a better value for the clustering_factor. This is another mechanism that […]

    Pingback by Index Upgrade | Oracle Scratchpad — April 11, 2022 @ 5:02 pm BST Apr 11,2022 | Reply

  7. […] table_cached_blocks (May 2013): At last, a “legal” way to adjust the clustering_factor to a realistic value. […]

    Pingback by Statistics catalogue | Oracle Scratchpad — August 17, 2022 @ 11:03 am BST Aug 17,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: