Oracle Scratchpad

February 12, 2014

Caution – hints

Filed under: Exadata,Hints,Oracle,Performance — Jonathan Lewis @ 6:57 pm GMT Feb 12,2014

Here’s a little example of why you should be very cautious about implementing undocumented discoveries. If you take a look at the view v$sql_hints in you’ll discover a hint (no_)cluster_by_rowid; and if you look in v$parameter you’ll discover two new parameters _optimizer_cluster_by_rowid and _optimizer_cluster_by_rowid_control.

It doesn’t take much imagination to guess that the parameters and hint have something to do with the costs of accessing compressed data by rowid on an Exadata system (see, for example, this posting) and it’s very easy to check what the hint does:

rem     Script:         cluster_by_rowid.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2014

create table t1
	rownum					id,
	trunc(dbms_random.value(0,1000))	n1,
	trunc(dbms_random.value(0,1000))	n2,
	rpad('x',100,'x')			padding
	rownum <= 10000

create index t1_i1 on t1(n1,n2);

-- collect stats

set autotrace on explain

	n1 = 50

I’ve created an index on the two randomly generated columns (n1, n2) and then forced Oracle to walk the index searching for n1 = 50. Because of the presence of n2 in the index the order in which Oracle will visit the rows will be randomised relative to the order in which they are stored in the table – but if I can get Oracle to sort the rowids into order before visiting the table I could (in theory) get a performance benefit from minimising the number of consistent gets I do (some will turn into “buffer is pinned count”), and in the Exadata case I could get a big improvement by ensuring that I decompressed each compression unit no more than once. That’s what the hint does and to prove the point I can select the id column – if the rows are being visited in order of rowid then the output will be in order of id:


11 rows selected.

| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT            |       |    10 |    80 |    12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    10 |    80 |    12 |
|   2 |   SORT CLUSTER BY ROWID     |       |    10 |       |     2 |
|*  3 |    INDEX RANGE SCAN         | T1_I1 |    10 |       |     2 |

Predicate Information (identified by operation id):
   3 - access("N1"=50)

Sure enough – with the hint the data arrives in sorted order, without it the results are not sorted. And, of course, we can check the execution plan and see that the optimizer has inserted a SORT operation between the index range scan and the table access by rowid.

So here’s the fun bit – the parameters are still there in (and _optimizer_cluster_by_rowid is now true by default when it used to be false) but the hint is no longer available [ed: wrong, it’s there but not working – see comment #1]. So if you want the sort to take place, you have to depend (presumably) on the luck of the statistics – when does Oracle think it’s worth doing automatically – or you still have to write the query as a two-part job with an inline, non-mergeable view that accesses and sorts the rowids before visiting the table.


With the arrival of the cluster_by_rowid() hint is once again working.



  1. Hello Jonathan,

    but the hint is no longer available

    Well the hint is there in v$sql_hint in it just does not work. If you check fix control of there is a bug for that:

    16582322     1 QKSFM_CLUSTER_BY_ROWID_16582322          enable CBRID when table lookup via index uses prefetching

    I think there is a simple explanation to this situation. 12c release date was July 1 2013, and it included all bugs of up to #14843189. was released about half a year later and the number of bugs included into it was higher than fitted into a base 12c release. I think that your test case will start working as expected in the next 12c patch set, when the bug fix 16582322 will re-appear.

    Comment by Timur Akhmadeev — May 2, 2014 @ 7:56 pm BST May 2,2014 | Reply

    • Timur,

      Thanks for the comment, sorry about the late reply.

      Added to your observation I’ve discovered that my catalogue of 12c hints was actually from the beta, which didn’t have the cluster_by_rowid hint.

      Here’s another quirky little detail on hints: in v$sql_hints has ENABLE_PARALLEL_DML as a hint introduced in, and CLUSTER_BY_ROWID introduced in But in, v$sql_hints doesn’t have ENABLE_PARALLEL_DML at all, and claims CLUSTER_BY_ROWID as introduced in

      Comment by Jonathan Lewis — May 12, 2014 @ 7:03 pm BST May 12,2014 | Reply

  2. […] tablescan path it would have taken if I hadn’t included the hint; finally you might note the cluster_by_rowid() hint in the SQL – there’s no matching “Sort cluster by rowid” operation in […]

    Pingback by Delete Costs | Oracle Scratchpad — June 19, 2014 @ 6:19 pm BST Jun 19,2014 | Reply

  3. […] thinking of when I posed the question was based on a follow-up to some experiments I had done with the cluster_by_rowid() hint. and (there was a little hint in the “several indexes” and more particularly the […]

    Pingback by Quiz Night | Oracle Scratchpad — September 10, 2014 @ 12:39 pm BST Sep 10,2014 | Reply

  4. […] CLUSTER_BY_ROWID […]

    Pingback by Oracle SQL | 12c: New SQL PLAN OPERATIONS and HINTS — July 7, 2016 @ 7:53 pm BST Jul 7,2016 | 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 )

Google photo

You are commenting using your Google 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