Oracle Scratchpad

October 2, 2017


Filed under: latches,mutex,Oracle,Troubleshooting — Jonathan Lewis @ 8:42 am BST 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 on the 2007 article linked above 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 backportsto 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 its “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() it may be some time before all those sessions release the hot parent and child and acquire a “cool” parent and child. Unfortunately you can’t call markhot() until at least one session has opened the relevant cursor – but that isn’t a problem if you’ve got the hidden parameter set.
  • Secondly: although your hot cursor(s) will eventually drop out of use, if you try to get rid of hot cursors with a 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 BST Oct 2,2017 | Reply

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


    Comment by Pavol Babel — October 3, 2017 @ 4:20 pm BST 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 BST 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 BST 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 BST 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 BST 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 BST Apr 19,2018 | Reply

  5. We have a case of somewhat severe library cache contention due to invalidations related to partition maintenance. We are not yet on 12.2 where fine grained invalidations were introduced, which should address this issue (since maintenance is not done on partitions used for the transactions in question), so I was wondering if marking the high-volume SQL as hot with this mechanism would help in the interim.

    Comment by Johan Snyman — March 15, 2019 @ 8:01 am GMT Mar 15,2019 | Reply

    • Johan,

      It’s hard to predict (and even to test properly) what will happen in highly concurrent cases, but I suspect marking hot will make things worse.

      Imagine you have a statement that is executed with a high leve of concurrency. When you do your partition exchange the cursor is invalidated and one of the sessions starts to re-optimise it. Any other sessions that want to execute it will now wait for the first session (would you be seeing lots of sessions on cursor: pin S wait on X) ?

      Here’s my thinking on why markhot won’t help:

      If you mark the statement hot you now have N copies of the statement as separate cursors, where N = cpu_count/2. If you do your exchange partition, you now have N sessions all trying to optimize that statement at the same time – which is a lot of CPU, plus contention on other bits of the library cache and row cheache as the sessions check objects, privileges etc. for optimisation. At best the optimisation will take just as long, at worst it could take much longer (see for an extreme case); and all the other sessions will still be waiting. Bottom line: none of the sessions will start executing any faster, but they may have to wait longer while you waste a load of CPU.

      Comment by Jonathan Lewis — March 15, 2019 @ 1:01 pm GMT Mar 15,2019 | Reply

  6. In oracle doc for V$DB_OBJECT_CACHE ,They mentioned for execution column in view V$DB_OBJECT_CACHE “EXECUTIONS Not usedSee Also: “V$SQLAREA” to see actual execution counts”


    So does that mean “exections” column value which we get from this view is wrong. Then why does oracle recommend to identify candidate Objects for Keeping in the Shared Pool based on execution.

    select substr(owner,1,10)||’.’||substr(name,1,35) “ObjectName”, type, sharable_mem,loads, executions, kept from v$db_object_cache where type in (‘TRIGGER’,’PROCEDURE’,’PACKAGE BODY’,’PACKAGE’) and executions >0 order by executions desc,loads desc,sharable_mem desc

    Comment by Vimal Shukla — July 15, 2019 @ 10:42 am BST Jul 15,2019 | Reply

    • It’s always possible for the manual to get out of date. In this case note that it points you to v$sqlarea, which is only a subset of the library cache – and that’s a strong clue that the manual IS out of date.

      If you find that v$db_object_cache.executions is ever non-zero that’s another clue that the manual is wrong: and you can always create a test to see if the value is maintained properly as you re-execute some simple SQL statement.

      Comment by Jonathan Lewis — July 16, 2019 @ 9:34 pm BST Jul 16,2019 | Reply

      • somehow my comment is not visible

        Comment by Vimal Shukla — July 17, 2019 @ 5:20 pm BST Jul 17,2019 | Reply

        • Hi Jonathan,

          I ran below test case and number of execution is same in v$db_object_cache and v$sqlarea. It seems value is maintained properly.

             (	CUSTOMER_ID  NUMBER(10,0) NOT NULL ENABLE, 
          	CITY  VARCHAR2(50 BYTE)
             ) ;
           insert into customers values(1,'GAURAV','ALWAR');
           insert into customers values(2,'YASH','MURADABAD');
           insert into customers values(3,'AMANPRIT','JALANDHAR');
           insert into customers values(4,'SAURAV','SULTANPUR');
           insert into customers values(5,'JAYANT','PATNA');
          SQL> commit;
          Commit complete.

          Comment by Vimal Shukla — July 17, 2019 @ 5:21 pm BST Jul 17,2019

        PROCEDURE find_name (
            c_id customers.customer_id%TYPE
    END cust_name;
    PACKAGE created.
          PROCEDURE find_name(c_id customers.customer_id%TYPE) IS
       c_name customers.customer_name%TYPE;
          SELECT customer_name INTO c_name
          FROM customers
          WHERE customer_id = c_id;
          dbms_output.put_line('customer_name: '|| c_name);
       END find_name;
    END cust_name;
    Package body created.

    Comment by Vimal Shukla — July 17, 2019 @ 5:21 pm BST Jul 17,2019 | Reply

         code customers.customer_id%type := &cc_id; begin
      Enter value for cc_id: 4
      old   2:    code customers.customer_id%type := &cc_id;
      new   2:    code customers.customer_id%type := 4;
      pl / sql procedure successfully completed.
      SQL> SELECT SUBSTR(owner,1,10) Owner,
             SUBSTR(type,1,12)  Type,
             SUBSTR(name,1,20)  Name,
             sharable_mem       Mem_used,
             SUBSTR(kept||' ',1,4)   "Kept?"
       FROM v$db_object_cache
       WHERE  name='CUST_NAME' and owner='TESTNEERAJ'
           ORDER BY executions desc;
      OWNER                TYPE              NAME                 EXECUTIONS       MEM_USED                Kept?
      TESTNEERAJ           PACKAGE BODY        CUST_NAME             6               36840                   NO
      TESTNEERAJ           PACKAGE             CUST_NAME             0               45032                   NO

      Comment by Vimal Shukla — July 17, 2019 @ 5:24 pm BST Jul 17,2019 | Reply

      • SQL> select SQL_FULLTEXT , EXECUTIONS from v$sqlarea where SQL_TEXT like '%find_name%' and SQL_TEXT not like '%sqlarea%';
        SQL_FULLTEXT                                                                     EXECUTIONS
        -------------------------------------------------------------------------------- ----------
        DECLARE                                                                                   1
           code customers.customer_id%type := 5;
        DECLARE                                                                                   3
           code customers.customer_id%type := 2;
        DECLARE                                                                                   1
           code customers.customer_id%type := 4;
        DECLARE                                                                                   1
           code customers.customer_id%type := 1;

        Comment by Vimal Shukla — July 17, 2019 @ 5:25 pm BST Jul 17,2019 | Reply

  8. Jonathon,
    Thanks for the article. Very helpful.
    Using the dbms_shared_pool.markhot() procedure, I successfully implemented marking a plsql procedure call and it’s SQL as hot and it was spread over 12 (CPU/2) “hotcopy” versions for each.
    However, I am having trouble unmarking them using the dbms_shared_pool.unmarkhot().

    exec sys.dbms_shared_pool.markhot('79c09a1336a4dffc4deb941236b862de');
    exec sys.dbms_shared_pool.markhot('3d50ae5c02306f95055e47ef7dc2bade');
    exec sys.dbms_shared_pool.unmarkhot('79c09a1336a4dffc4deb941236b862de');
    exec sys.dbms_shared_pool.unmarkhot('3d50ae5c02306f95055e47ef7dc2bade');
    BEGIN sys.dbms_shared_pool.unmarkhot('79c09a1336a4dffc4deb941236b862de'); END;
    Error report -
    ORA-04043: object  does not exist
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 153
    ORA-06512: at line 1
    04043. 00000 -  "object %s does not exist"
    *Cause:    An object name was specified that was not recognized by the system.
               There are several possible causes:
               - An invalid name for a table, view, sequence, procedure, function,
               package, or package body was entered. Since the system could not
               recognize the invalid name, it responded with the message that the
               named object does not exist.
               - An attempt was made to rename an index or a cluster, or some
               other object that cannot be renamed.
    *Action:   Check the spelling of the named object and rerun the code. (Valid
               names of tables, views, functions, etc. can be listed by querying
               the data dictionary.)

    I am thinking this is the same as when marking it hot, that the target hash must be in the shared pool.
    But the original PLSQL and SQL are no longer in the shared pool and won’t be since they will be divided up into the “hotcopy” versions with different full_hash_values.

    I tried purging the hotcopies using dbms_shared_pool.purge(). They successfully were removed from the shared pool but when the procedures got called again (every 30 min) the hot copies returned.
    Is there a view to query that is recording these hash values as hot? Since the hotcopies come back there must be some setting that dbms_shared_pool used.

    I queried the _kgl_debug parameter thinking perhaps dbms_shared_pool was just setting that but that does not seem to be the case.

    KSPPINM                                            KSPPSTVL                                 KSPPSTVL
    -------------------------------------------------- ---------------------------------------- ----------------------------------------
    _kgl_bucket_count                                  9                                        9
    _kgl_cluster_lock                                  TRUE                                     TRUE
    _kgl_cluster_lock_read_mostly                      FALSE                                    FALSE
    _kgl_cluster_pin                                   TRUE                                     TRUE
    _kgl_fixed_extents                                 TRUE                                     TRUE


    Comment by Andrew Markiewicz — May 4, 2021 @ 2:02 pm BST May 4,2021 | Reply

    • Andrew,

      Thanks for the comment.

      I am a little puzzled – in my example I’ve used namespace 0 where you’ve left the second parameter to default which means namespace 1.
      Namespace 0 is “SQLAREA”, Namespace 1 is “Table/Procedure” – so I wouldn’t have expected your calls to markhot() to do what you’re reporting. I’ll have to do a couple of experiments to see what happens.

      The fact that the original “hot” hash value for the procedure and the SQL are no longer visible should not be a problem, they should still be in v$db_object_cache (and v$sql and v$db_object_cache both sit on top of x$kglob anyway). So we should be able to discount that as the source of the problem.

      I’d try two things:

      a) execute the unmarkhot calls using namespace 0

      b) enable extended SQL tracing so that you can see is some interesting SQL statement executed and errored (or returned zero just before the error was reported) in v$sql. With a level 4 trace you should get the bind values used and this might point you to where the problem lies.

      Which version of Oracle ?

      Jonathan Lewis

      Comment by Jonathan Lewis — May 4, 2021 @ 3:10 pm BST May 4,2021 | Reply

      • Running “Oracle Database 19c Standard Edition 2 Release – Production Version”.

        I noticed the namespace difference after I ran the markhot procedure. I wasn’t sure of the impact since the procedure documentation did not cover it in great detail.
        I ran the unmarkhot() procedure with the same namespace thinking it used that value in some calculation instead of it being divided by object type.
        Is there documentation of what the values for namespace are?

        Perhaps it worked with namespace 1 since the start of the hot spot is this parse of a plsql procedure.

        call readonly_user_pkg.syncgrants(:1 , :2 , :3 );

        The second hotspot is SQL in the procedure. I marked them both hot with those full_hash_values.

        Interestingly I could not see the original sql in v$db_object_cache after marking it hot..
        But running the unmarkhot() with the namespace=>0 parameter successfully unmarked them and a subsequent use of the plsql returned the full_hash_values to v$db_object_cache.

        property hot_flag,
        –where hash_value in (‘2109913822’, ‘918053598’)
        where full_hash_value in (’79c09a1336a4dffc4deb941236b862de’, ‘3d50ae5c02306f95055e47ef7dc2bade’)

        no rows selected

        SQL> exec sys.dbms_shared_pool.unmarkhot(’79c09a1336a4dffc4deb941236b862de’, namespace=>0);

        PL/SQL procedure successfully completed.

        SQL> exec sys.dbms_shared_pool.unmarkhot(‘3d50ae5c02306f95055e47ef7dc2bade’, namespace=>0);

        PL/SQL procedure successfully completed.


        SQL> select
        2 hash_value,
        3 full_hash_value,
        4 namespace,
        5 child_latch,
        6 property hot_flag,
        7 executions,
        8 invalidations
        9 from
        10 v$db_object_cache
        11 –where hash_value in (‘2109913822’, ‘918053598’)
        12 where full_hash_value in (’79c09a1336a4dffc4deb941236b862de’, ‘3d50ae5c02306f95055e47ef7dc2bade’)
        13 ;

        2109913822|3d50ae5c02306f95055e47ef7dc2bade|SQL AREA | 0| | 1| 0
        2109913822|3d50ae5c02306f95055e47ef7dc2bade|SQL AREA | 47838| | 2| 0

        This effectively turned off the markhot.
        So it looks like the namespace value was the overall issue.
        Thank you very much.


        Comment by Andrew Markiewicz — May 5, 2021 @ 2:00 pm BST May 5,2021 | 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