Oracle Scratchpad

October 2, 2017


Filed under: latches,mutex,Oracle,Troubleshooting — Jonathan Lewis @ 8:42 am GMT Oct 2,2017

How can a single piece of SQL text – checked very carefully – end up with multiple SQL_IDs ? There are probably quite a lot of people who know the answer to this question but won’t think of it until they’re reminded and, thanks to a question that came up on the forum formerly known as OTN a couple of days ago, I was reminded about it recently and rediscovered an article I had drafted on the topic a few years ago.

The specific problem on the forum was about having a huge number of child cursors for a single parent thanks to a frequently executed update statement that updated all 50 columns of a table using bind variables to do so. The reason why a single statement could produce so many child cursors seemed to be due to the variation in the lengths of the bind variables supplied – which could well be the consequence of an internal library mechanism rather than an explicit design mechanism written into the client code. One of the comments to my 2007 article suggested event  10503 as a damage limitation mechanism, but there was some problem with it not working as expected at the time. A quick check on MoS now reports bug 10274265 : “EVENT 10503 NOT WORKING ON THE SESSION LEVEL” as fixed in 12.1 with lots of backport patches to various versions of 11.2

Moving on from the back-story, the case that prompted my draft note in 2014 was the simpler one of having lots of sessions constantly executing exactly the same SQL statement and child cursor, so rather than having a latch/mutex type of problem because of a large number of child cursors I was seeing a problem purely because of the level of concurrent access to the same child cursor.  The solution in the version of Oracle the client was using at the time was to tell Oracle to mark the SQL statement as “hot” by setting a hidden parameter; but the mechanism is now officially exposed in a procedure called dbms_shared_pool.markhot() that I learned about a few months ago when I was at a client who had a similar problem with highly concurrent execution of a small set of statements – with the extra twist that the table referenced in the critical statements was a partitioned table which suffered a fairly regular partition exchange.

When a statement (through it’s “full_hash_value”) is marked as hot, an extra value visible as the property column in v$db_object_cache is set to a value that seems to be dependent on the process id of the session attempting to execute the statement, and this value is used as an extra component in calculating a new full_hash_value (which leads to a new hash_value and sql_id). With a different full_hash_value the same text generates a new parent cursor which is (probably) going to be associated with a new library cache hash bucket and latch. The property value for the original parent cursor is set to “HOT”, and the extra copies become “HOTCOPY1”, “HOTCOPY2” and so on. Interestingly once an object is marked as HOT and the first HOTCOPYn has appeared the original version may disappear from v$sql while still existing in v$db_object_cache.

The number of “HOTCOPYn” versions of the statement is limited by the hidden parameter “_kgl_hot_object_copies” which (according to my notes) defaults to either cpu_count or cpu_count/2. On my most recent test on it seemed to be the latter.

Marking a cursor hot

There are three options:

  • set a hidden parameter in the startup file
  • execute an “alter system” command to set the hidden parameter
  • from onwards (possibly earlier in 11.2) call dbms_shared_pool.markhot()


Startup file:

_kgl_debug="hash='cc7d5ecdcc9e7c0767456468efe922ea' namespace=0 debug=33554432"

Alter system call with multiple targets:

alter system set "_kgl_debug" =
        "hash='cc7d5ecdcc9e7c0767456468efe922ea' namespace=0 debug=33554432",
        "hash='59a1f6575a5006600792ee802558305b' namespace=0 debug=33554432"

markhot() procedure:

                hash            =>'71fc6ccf9a3265368492ec9fc05b785b',
                namespace       =>0,
                global          =>true

The namespace identifies the object as an SQL Cursor (you can mark other types of object as hot if you need to), and for those of a mathematical bent you’ll work out that the debug values is power(2,25).

The value supplied as the hash is the full_hash_value and you can find this in v$db_object_cache either by searching on some string that easily identifies your statement, or by searching v$sql on a string to get the (short) hash value of the statement and using that to search v$db_object_cache on the hash_value column.

        property        hot_flag,
        name like '{some part of your critical SQL statement}'

I ran into two problems using the markhot() approach. The first not terribly serious – the second fatal, except I’m not going to do it again and I wouldn’t have run into it if I hadn’t been impatient working around the first.

First: if you’ve already got lots of sessions executing the statement and holding cursors open in some way before you call markhot() then it may be some time before all those sessions release the hot parent and child and acquire a “cool” parent and child and unfortunately you can’t call markhot() until at least one session has opened the relevant cursor – and that’s a problem that isn’t relevant if you’ve got the hidden parameter set.

Secondly: although eventually your hot cursor(s) will drop out of use, if you try to get rid of them early by a cunning call to dbms_shared_pool.purge() you may find that you don’t manage to purge them; if you decide to try again, and again (as I did) you may find that your session goes into an infinite CPU spin and no-one can get at the hot cursor.  Be patient, once you’ve marked a cursor as hot your application will (probably) end up spreading itself over the copies.

One last detail – if, for any reason, you decide that a cursor no longer needs to be marked hot there is a procedure dbms_shared_pool.unmarkhot() that takes the same three parameters to clear the property and allow the copies to disappear.


The OTN problem that prompted me to write this note wasn’t about high concurrency levels, it was about mutex contention while searching for the right child cursor. The markhot() procedure doesn’t really look as if it’s designed to address this issue but, as a side-effect of having multiple parent cursors for the same statement text, there should be fewer sessions searching each child-cursor chain at any one moment and this may be enough to reduce the contention. Statistically, of course, every child chain is likely to end up the same length so the amount of shared pool memory used by the SQL statement will eventually grow by a factor matching the number of hot copies produced – but if the problem is contention it may be better (e.g.) to have 16 times the memory used so that 100 concurrent sessions can be spread across 16 different chains rather than having 100 sessions all trying to search the same chain at the same time.



  1. Thank you for sharing this post

    Comment by fouedgray — October 2, 2017 @ 12:35 pm GMT Oct 2,2017 | Reply

  2. it seems I’m not able to add comment :(


    Comment by Pavol Babel — October 3, 2017 @ 4:20 pm GMT Oct 3,2017 | Reply

    • Hi Jonathan,

      thank you for some interesting points, the most interesting was the issue with calling dbms_shared_pool.purge on cursor which was previously marked as hot. On the other hand, I’m not sure if dbms_shared_pool.markhot could be helpful in described case of “cursor pin: S” / “cursor pin:S wait on X”. If it is true that update sql statment generated by application framework procuced many child cursors (and the number of executions was evenly distrubuted between those childs), there should not occur heavy mutex contention. In general, if you have sql cursors with two childs,oracle will protect pinning of them by two different “cursor pin:S” mutexes (as both childs would have probably end in different library cache buckets, each protected by separate mutex). As far I know, when mutexes kick in, there is no need to pin parent cursor when a child is pinned.I think dbms_shared_pool would rather help in case of “library cache: mutex X”. Of course it could help even in case of heavy “cursor pin: S” contention, but in generaly when only few childs exist (or only small set of them is being accesed frequently)

      Pavol Babel
      Oracle Certified Master 10g/11g

      Comment by Pavol Babel — October 3, 2017 @ 4:22 pm GMT Oct 3,2017 | Reply

      • OK, now it works, maybe there was some strange hidden character.


        Comment by Pavol Babel — October 3, 2017 @ 4:23 pm GMT Oct 3,2017 | Reply

      • I’m still thinking of this. For sure there still exist situations, when oracle has to search correct child cursor. It the list is long (should not be longer as 1024 in, I think it was 11.2.03 version where _cursor_obsolete_threshold was introduced with default value of 100, and I think in it was raised to 1024).
        For sure it happens during new child cursor creation. It could happen also during soft parse (library cache lookup). However, oracle has plenty of ways to use KGL lock/pin and it is able to pin directly the child cursor. As I wrote already, 2 child cursors can be (and probably will) be protected by diffeent mutex.
        To summarize I mean, maybe lowering _cursor_obsolete_threshold (which I use to set back to 100 or 128 on busy OLTP systems) and increasing session_cached_cursors to value > _cursor_obsolete_threshold could be smarter soulution as using mark_hot. I must find a while to create little testcase for this

        Pavol Babel

        Comment by Pavol Babel — October 8, 2017 @ 12:04 am GMT Oct 8,2017 | Reply

  3. […] I had assumed was due in part to the degree of concurrent execution of the statement – was to mark the cursor as “hot” This resulted in 36 differet sql_ids for the same statement (the client machine had 72 CPUs). This […]

    Pingback by Histogram Threat | Oracle Scratchpad — January 30, 2018 @ 8:07 am GMT Jan 30,2018 | Reply

  4. Hi Jonathan,
    Since original cursor eventually disappears from v$db_object_cache (The property value for the original parent cursor which is set to “HOT” doesn’t exist anymore), how do I find which hotcopies of the cursor are related to my original sqlid which had concurrency issues to begin with?
    This is because if code changes from application side, I want to take that SQLID/hash value out of my database trigger to mark it hot.
    Thank you

    Comment by Gurvinder Singh — April 16, 2018 @ 4:27 pm GMT Apr 16,2018 | Reply

    • Gurvinder,

      My approach would be to include documentation with the code that said something like:

      “This statement is marked as hot because ….; it’s current hash_value (use in script XXXX that calls the markhot() procedure) is currently NNNNNNN. If you modify the text of this statement in any way you must derive the new hash_value and edit the markhot() script to replace the old value before promoting the change to production.”

      Comment by Jonathan Lewis — April 19, 2018 @ 8:55 am GMT Apr 19,2018 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply to Pavol Babel Cancel reply

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.

Powered by