Reducing Contention on Hot Cursor Objects (Cursor: Pin S)

Posted in: Technical Track

First, let me offer a little explanation about the wait event “cursor: pin S.”

Oracle states: “A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object.” In other words, two or more sessions are trying to concurrently run the same statement (the same cursor in library cache), which forces them to compete to update a shared mutex pin for the same cursor object.

This wait event provides very useful information to identify why sessions are competing to update a shared mutex pin:

Information identifying why sessions are competing to update a shared mutex pin - cursor: pin S.

Here’s how a mutex works:

If a session wants to use a cursor, it must not disappear from the library cache while in use. The session uses a mutex to ensure the cursor cannot be changed or deleted so, to this end, it logs that there is an interested session by incrementing the mutex usage by one. This is called taking a shared lock.

The process for taking a shared lock:

  1. A session wants to run a cursor and so checks the owning cursor pin mutex to see if there is a session waiting to change the mutex (e.g. performing a hard-parse). It does this by checking the high-order bits to see if they are zero or have a session ID.
  2. If the high-order bits are zero, then it locks and increments by one (this is an atomic action). Waiting to lock and increment causes the “cursor: pin S” wait event. This increment is done on the low-order bits of the mutex.
  3. If the lock and increment fails, then some other session must be updating the mutex, so it’s necessary to sleep and try again, i.e. lock and increment. The “cursor: pin S” wait event will be longer. This can cause extra CPU load on the server as it spins attempting to update the mutex.
  4. If the high-order bits are not zero then there is a session waiting to change the mutex. The current interested session waits on the event “cursor: pin S wait on X.” If this is the case then it sleeps and tries again.
  5. Once the cursor is closed and finished, the shared lock on the mutex must be released by performing a lock and decrementing by one. Once again, if there is a failure to lock and decrement the next step is to sleep and try again.

If a session wants to perform a hard parse on a cursor already existing in the library cache it must acquire the mutex in exclusive mode.

The process for taking an exclusive lock:

  1. A session wants to perform a hard parse on a statement so it checks the cursor pin mutex to see if it’s in use.
  2. It checks the high-order bits and, if zero, updates the high-order bits to the current session ID (this compare-and-swap routine is a CPU atomic action).
  3. If the high-order bits are already set, the process has to wait on the event “cursor: pin X.” The session then sleeps and tries again.
  4. Once the high-order bits are set to the current session ID, it checks the low-order bits to see if the cursor is currently in use.
  5. If the low-order bits are not zero, it must wait for the counter to decrement to zero (Note: the counter cannot be incremented once the high-order bits are set to the session ID).
  6. Once the low-order bits are set to zero then the hard parse can proceed.
  7. The session removes the exclusive mutex lock by resetting the high-order bits to zero.

Real Production Environment

Oracle states that the wait event should be very rare, since updating the shared mutex pin is extremely fast.

This is true in most cases, but we recently ran into a scenario where a vast number of sessions  were waiting on “cursor: pin S” while running different statements (v$session showed different sql_id). As Oracle states, the process to take a shared lock was very fast, but it still generated a big impact on performance. As the load from the application grew (in this case load grew exponentially, as this was an online retailer with increased traffic due to COVID-19), this impact became more and more evident.

In this scenario, we found a number of statements that shared the same characteristic; they all invoked the same function which, among other tasks, ran a simple select statement. As they were all trying to run the select statement in the function, they were all competing for access to the shared mutex pin for that specific select statement.

The best approach to reduce the contention on the shared mutex for the statement is to mark it as hot. This allows Oracle to split the executions of this statement among several “SQL-copies.” Once the statement is marked as hot, Oracle will stop running the original statement and will, instead, split the executions among the different “SQL-copies” of the cursor object. Since each “SQL-copy” has its own shared mutex, load on the single mutex will be split among the copies generated by this mechanism.

Even though it doesn’t eliminate the waits on “cursor: pin S,” this technique proved to be very effective by dramatically reducing waits and improving performance on a real productive environment with around 9000 executions of the same SQL statement per second.

Below is a step-by-step guide to create a scenario very similar to the issue we encountered, along with the solution implemented to overcome its impact on performance. I also cover some important topics to keep in mind when you reboot the database, and when dealing with RAC environments.

Creating a test environment to reproduce waits on “cursor: pin S”

In order to create the test environment, I created a dummy table, a function, two shell scripts and four SQL scripts.

1. Create a dummy table (code_table) and populate it with some simple values using a for loop.

SQL> create table code_table (code_name char(1), low_value number, high_value number);

Table created.

SQL> declare
  2    letters char(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  3    v_num number := 1;
  4  begin
  5    for i in 1..26 LOOP
  6      insert into code_table values (substr(letters,i,1), i*v_num, i*(v_num+1000));
  7      v_num := v_num + 1001;
  8    end loop;
  9    commit;
 10  end; 
 11  /

PL/SQL procedure successfully completed.

2. Create a function (fx_num) that contains the select statement that will force the sessions to wait on “cursor: pin S’.

SQL> create or replace function fx_num (v_name varchar) return number is
  2    v_low number;
  3    v_high number;
  4  begin
  5    select low_value, high_value into v_low, v_high from code_table where code_name=v_name;
  6    return(DBMS_RANDOM.value(low => v_low, high => v_high));
  7 end;
  8 /

Function created.

3. Create a shell script (launch_test.sh) that will spam multiple concurrent sessions to mimic high concurrency on the object cursor.

::::::::::::::::
launch_test.sh
::::::::::::::::

#!/bin/bash

export ORACLE_SID=proddb2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

for i in {1..50}
do
nohup sqlplus -s user/pass @launch_sql1.sql >> launch_sql1.log 2>&1 &
nohup sqlplus -s user/pass @launch_sql2.sql >> launch_sql2.log 2>&1 &
nohup sqlplus -s user/pass @launch_sql3.sql >> launch_sql3.log 2>&1 &
nohup sqlplus -s user/pass @launch_sql4.sql >> launch_sql4.log 2>&1 &
done

exit 0

4. Create four SQL scripts (launch_sql[1-4].sql), containing a simple query that calls the function created previously. Each of these scripts will be executed 50 times; thus simulating 200 concurrent sessions trying to call the function (fx_num).

::::::::::::::
sts_tst1.sql
::::::::::::::

select f_random(substr(to_char(sysdate,'MON'),1,1)) from dual;
exit
::::::::::::::
sts_tst2.sql
::::::::::::::

select object_name from user_objects where object_id < fx_num(substr(object_id,1,1));
exit
::::::::::::::
sts_tst3.sql
::::::::::::::

select name, count(*) from user_source where line < fx_num(substr(name,1,1)) group by name;
exit
::::::::::::::
sts_tst4.sql
::::::::::::::

select trunc(last_analyzed), sample_size, count(*) from user_tab_cols where column_id < fx_num(substr(column_name,1,1)) group by trunc(last_analyzed), sample_size;
exit

5. Finally, create a shell script (check_waits.sh) to list the sessions waiting on “cursor: pin S.” As stated before, the column P1 of gv$session view shows the hash value of the statement protected by the shared mutex. Showing this value will help identify the root cause of the waits on “cursor: pin S.”

:::::::::::::::::
check_waits.sql
:::::::::::::::::

set lines 200 pages 200
prompt - Waits on cursor pin S:
select inst_id, substr(event,1,30) event, p1, sql_id, count(*)
from gv$session
where event = 'cursor: pin S'
group by inst_id, substr(event,1,30), p1, sql_id;
exit

Running the test scenario

First, let’s generate the waits on “cursor: pin S”:

1. In order to check for session waiting on the wait event “cursor: pin S,” I run the SQL script check_waits.sql on an endless while loop as follows:

[oracle@oradb02 ddml]$ while true; do sqlplus -s '/ as sysdba' @check_waits.sql; sleep 1; done

- Waits on cursor pin S:

no rows selected

- Waits on cursor pin S:

no rows selected

2. On a second terminal, I execute the shell script launch_test.sh to generate 200 sessions by executing the four SQL scripts 50 times each. This step forces the database to receive 200 executions of different select statements that invoke the function called fx_num.

[oracle@oradb02 ddml]$ ./launch_test.sh

3. On the first terminal, we can now see a lot of sessions waiting on “cursor: pin S.” As you can see in the following output, instance two registers up to 157 sessions waiting on this event at one point in time.

[oracle@oradb02 ddml]$ while true; do sqlplus -s '/ as sysdba' @check_waits; sleep 1; done

- Waits on cursor pin S:

no rows selected

- Waits on cursor pin S:

no rows selected

- Waits on cursor pin S:

INST_ID EVENT         P1        SQL_ID        COUNT(*)
------- ------------- --------- ------------- -------- 
      2 cursor: pin S 356306711 2ndpau148y2by      14
      2 cursor: pin S 356306711 7tr4jwnamtmsr      55
      2 cursor: pin S 356306711                    23
      2 cursor: pin S 356306711 a3xkbsayc47kq      13

- Waits on cursor pin S:

INST_ID EVENT         P1        SQL_ID        COUNT(*) 
------- ------------- --------- ------------- -------- 
      2 cursor: pin S 356306711 5n3qfbb42gfdr        1
      2 cursor: pin S 356306711 2ndpau148y2by       11
      2 cursor: pin S 356306711 7tr4jwnamtmsr       84
      2 cursor: pin S 356306711                     42
      2 cursor: pin S 356306711 a3xkbsayc47kq       19

- Waits on cursor pin S:

INST_ID EVENT         P1        SQL_ID        COUNT(*)
------- ------------- --------- ------------- --------
      2 cursor: pin S 356306711 2ndpau148y2by        1
      2 cursor: pin S 356306711 7tr4jwnamtmsr        4
      2 cursor: pin S 356306711 a3xkbsayc47kq        6

4. The view gv$sql shows the sessions waiting on “cursor: pin S” when executing the statements in the SQL scripts (launch_sqlX.sql). You can see both the actual problematic SQL statement (SQL_ID 7tr4jwnamtmsr) and its calling statements waiting for the event “cursor: pin S.”

SQL> select sql_id, hash_value, sum(executions) executions, sql_text from gv$sql
  2  where hash_value=356306711
  3     or sql_id in ('2ndpau148y2by','7tr4jwnamtmsr','a3xkbsayc47kq','5n3qfbb42gfdr')
  4  group by sql_id, hash_value, sql_text order by 1;

SQL_ID        HASH_VALUE EXECUTIONS SQL_TEXT
------------- ---------- ---------- -----------------------------------------------------------------------------------------------------
2ndpau148y2by 1217333630        800 select job_name, count(*) from job_masters where job_id > fx_num(substr(owner,1,1)) group by job_name
5n3qfbb42gfdr 3358046647        800 select fx_num(substr(to_char(sysdate,'MON'),1,1)) from dual
7tr4jwnamtmsr 356306711     3854606 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
a3xkbsayc47kq 3166838358        800 select trunc(timestamp), completion_status, count(*) from job_masters where job_id <  
                                    fx_num(substr(job_name,1,1)) group by trunc(timestamp), completion_status

5. If you take a look into column P1, you’ll notice even though the sessions are running different statements (column SQL_ID) they are still trying to access the same shared mutex. All the sessions point to the same hash_value (356306711): the select statement in the function fx_num.

6. In addition, we can see the number of executions for the select statements in the function (sql_id 7tr4jwnamtmsr) is much higher than those from the select statements in the SQL scripts (launch_sqlX.sql). This makes total sense since the statements in the SQL scripts call the function fx_num for each row. This extremely high number of concurrent executions of the function is the root cause of the high waits on “cursor: pin S.” These sessions are struggling to access the shared mutex that protects the select statement in the function.

Implementing the recommended fix

The best approach to fix this performance issue is to mark the select statement in the function as hot. As stated earlier, this tells Oracle to split the executions on this statement among a number of “SQL-copies.” This will also split the load among different cursor objects protected by different shared mutexes.

1. The first step is to set the hidden parameter _kgl_hot_object_copies.

This parameter governs the number of “SQL-copies” that Oracle will create for each statement marked as hot. The recommendation is to set this parameter to half the amount of CPUs in the server. Keep in mind, you will need to reboot for this change to take effect, so plan accordingly.

SQL> alter system set '_kgl_hot_object_copies'=8 scope=spfile sid='*';

System altered

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
(...)
Database mounted.
Database opened.
SQL> select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b, v$instance
  2  where a.indx=b.indx AND substr(ksppinm,1,1) = '_' AND ksppinm = '_kgl_hot_object_copies';

KSPPINM                    KSPPSTVL
-------------------------- ----------
_kgl_hot_object_copies     8

2. Once you set the parameter _kgl_hot_object_copies and restart the database, you have to set the SQL suffering the waits on “cursor: pin S” to hot. Oracle provides the procedure dbms_shared_pool.markhot for this purpose.

3. You need to confirm that the cursor you’re planning to mark as hot is already in the library cache. You can use the following query to confirm this, and also to get the full_hash_value which you must provide to set the statement as hot.

SQL> select kglnahsh, kglnahsv from v$sql, x$kglob where kglhdadr=address and sql_id = '7tr4jwnamtmsr';

KGLNAHSH   KGLNAHSV
---------- --------------------------------
356306711  eb4cdceda1c495cd7cdc91e5153ccf17

4. The following statement marks the SQL as hot.

SQL> begin
  2    dbms_shared_pool.markhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

5. You can query the view gv$db_object_cache to check on which instance the statement has been marked as hot by checking the PROPERTY column.

SQL> select inst_id, hash_value, namespace, child_latch, property, status, sum(executions) executions, name
  2  from gv$db_object_cache where hash_value = 356306711
  3  group by inst_id, hash_value, name, namespace, child_latch, property, status order by hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH PROPERTY STATUS EXECUTIONS NAME
------- ---------- --------- ----------- -------- ------ ---------- ----------------------------------------------------------------
      2 356306711  SQL AREA            0 HOT      VALID     3657733 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA        53015 HOT      VALID     3576660 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

6. Keep in mind the procedure will only mark the statement as hot if it finds it in the library cache. Because of this, you can see that gv$db_object_cache shows the statement marked as hot only in instance two even though I executed the procedure dbms_shared_pool.markhot with the global argument set to true.

Running the test scenario one more time

1. With the statement set to hot, let’s try the same test one more time. Again, I run the check_waits.sql script on a while loop.

[oracle@oradb02 ddml]$ while true; do sqlplus -s '/ as sysdba' @chk; sleep 1; done

- Waits on cursor pin S:

no rows selected

- Waits on cursor pin S:

no rows selected

2. Same as before, let’s launch the test on a separate terminal.

[oracle@oradb02 ddml]$ ./launch_test.sh

3. Instance two now shows just a few sessions waiting on “cursor: pin S.” The total number of concurrent waits dropped to just eight sessions.

[oracle@oradb02 ddml]$ while true; do sqlplus -s '/ as sysdba' @chk; sleep 1; done

- Waits on cursor pin S:

no rows selected

- Waits on cursor pin S:

INST_ID EVENT         P1         SQL_ID        COUNT(*)
------- ------------- ---------- ------------- --------
      2 cursor: pin S 1835995903 ddttvdtqqy4rz        1
      2 cursor: pin S 939937426  2ndpau148y2by        2
      2 cursor: pin S 4244290431 2ndpau148y2by        1
      2 cursor: pin S 939937426                       1
      2 cursor: pin S 939937426 dg9n6z0w0cmnk         2
      2 cursor: pin S 4244290431 61m38g7ygpfvz        1

6 rows selected.

- Waits on cursor pin S: 

no rows selected

- Waits on cursor pin S: 

no rows selected

4. While the view gv$sql shows an increase on the number of executions of the select statements in the SQL scripts (launch_sqlX.sql), the number of executions for the SQL in the function fx_num (sqlid 7tr4jwnamtmsr) remains unchanged (still 3854606).

SQL> select sql_id, hash_value, sum(executions) executions, sql_text from gv$sql
  2  where hash_value=356306711
  3     or sql_id in ('2ndpau148y2by','7tr4jwnamtmsr','a3xkbsayc47kq','5n3qfbb42gfdr')
  4  group by sql_id, hash_value, sql_text order by 1;

SQL_ID        HASH_VALUE EXECUTIONS SQL_TEXT 
------------- ---------- ---------- ------------------------------------------------------------------------------------------------------
2ndpau148y2by 1217333630       1600 select job_name, count(*) from job_masters where job_id > fx_num(substr(owner,1,1)) group by job_name
5n3qfbb42gfdr 3358046647       1600 select fx_num(substr(to_char(sysdate,'MON'),1,1)) from dual
7tr4jwnamtmsr 356306711     3854606 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
a3xkbsayc47kq 3166838358       1600 select trunc(timestamp), completion_status, count(*) from job_masters where job_id < 
                                    fx_num(substr(job_name, 1,1)) group by trunc(timestamp), completion_status

5. If we filter by the SQL signature from the statement in the function fx_num, you can see there are now a group of new statements with a high number of executions. It seems these statements were executed as part of the second test. As a side note, the SQL signature for all these sql_id match because they all share the same SQL text.

SQL> select sql_id, hash_value, sum(executions) executions, sql_text from gv$sql
  2  where force_matching_signature = (select force_matching_signature from gv$sql where hash_value=356306711)
  3  group by sql_id, hash_value, sql_text order by 1;

SQL_ID        HASH_VALUE EXECUTIONS SQL_TEXT
------------- ---------- ---------- ------------------------------------------------------------------------------------------------------
0u9hxt3azayv5 3589634917     125688 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
1far8p5csfrmx 1502043773     142500 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
5bacfy8mwthrj 667730673       90651 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
61m38g7ygpfvz 4244290431     140775 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
7tr4jwnamtmsr 356306711     3854606 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
9pyrc45h3tgg9 1614593513     148272 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
arg5wx08suqm0 294476384      117839 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
ddttvdtqqy4rz 1835995903     104122 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
dg9n6z0w0cmnk 939937426      102875 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

9 rows selected.

6. The view gv$db_object_cache shows these statements have been identified as HOTCOPY of the statement in the function fx_num. We have exactly eight hot copies, as defined by the hidden parameter _kgl_hot_object_copies.

SQL> select inst_id, hash_value, namespace, child_latch, property, status, sum(executions) executions, name
  2  from gv$db_object_cache where hash_value in (select distinct hash_value from gv$sql where force_matching_signature = 5054107626512424923 )
  3  group by inst_id, hash_value, name, namespace, child_latch, property, status order by hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH PROPERTY STATUS EXECUTIONS NAME
------- ---------- --------- ----------- -------- ------ ---------- ----------------------------------------------------------------------
      2 294476384  SQL AREA            0 HOTCOPY4 VALID      117082 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA        88672 HOTCOPY4 VALID      117159 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA            0 HOT      VALID     3657733 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA        53015 HOT      VALID     3576660 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA            0 HOTCOPY2 VALID       90188 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA        49905 HOTCOPY2 VALID       90218 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA            0 HOTCOPY8 VALID      102067 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA        20114 HOTCOPY8 VALID      101647 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA            0 HOTCOPY5 VALID      141347 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA        89725 HOTCOPY5 VALID      141440 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA            0 HOTCOPY6 VALID      147205 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA        48617 HOTCOPY6 VALID      147329 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA            0 HOTCOPY3 VALID      103453 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA        70399 HOTCOPY3 VALID      103484 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA            0 HOTCOPY1 VALID      124746 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA        97125 HOTCOPY1 VALID      125095 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA            0 HOTCOPY7 VALID      139691 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA        47999 HOTCOPY7 VALID      139803 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

18 rows selected.

7. If we keep running this same test over and over again, we will confirm that the number of executions of the original sql_id (marked as hot) remains unchanged. At the same time the executions on the “SQL-copies” continue to grow. In other words, the “SQL-copies” now perform all executions of this statement.

[oracle@oradb02 ddml]$ ./launch_test.sh

SQL> select inst_id, hash_value, namespace, child_latch, property hot_flag, status, sum(executions) executions, name
  2  from gv$db_object_cache 
  3  where hash_value in (select distinct hash_value from gv$sql where force_matching_signature = 5054107626512424923 )
  4  group by inst_id, hash_value, name, namespace, child_latch, property, status order by hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG STATUS EXECUTIONS NAME 
------- ---------- --------- ----------- -------- ------ ---------- ---------------------------------------------------------------------- 
      2 294476384  SQL AREA             0 HOTCOPY4 VALID 240474     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA         88672 HOTCOPY4 VALID 240582     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA             0 HOT 0    VALID 3657733    SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA         53015 HOT 0    VALID 3576660    SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA             0 HOTCOPY2 VALID 206588     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA         49905 HOTCOPY2 VALID 206662     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA             0 HOTCOPY8 VALID 228203     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA         20114 HOTCOPY8 VALID 228118     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA             0 HOTCOPY5 VALID 275321     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA         89725 HOTCOPY5 VALID 275482     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA             0 HOTCOPY6 VALID 280358     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA         48617 HOTCOPY6 VALID 279606     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA             0 HOTCOPY3 VALID 201829     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA         70399 HOTCOPY3 VALID 201773     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA             0 HOTCOPY1 VALID 244295     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA         97125 HOTCOPY1 VALID 244809     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA             0 HOTCOPY7 VALID 253317     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA         47999 HOTCOPY7 VALID 253521     SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

18 rows selected.

Keeping statements as hot on a RAC database

1. As mentioned earlier, statements cannot be marked as hot if they are not first loaded into the library cache. For this reason, the statement in our example was not set as hot in instance one.

2. So now let’s run the load test on instance one to see what happens.

[oracle@oradb01 ddml]$ ./launch_test.sh

3. Below is an example of what you’d see if the statement is used on both instances but only marked as hot on one of the instances (inst_id=2).

SQL> select inst_id, hash_value, namespace, child_latch, property hot_flag, status, sum(executions) executions, name
  2  from gv$db_object_cache 
  3  where hash_value in (select distinct hash_value from gv$sql where force_matching_signature = 5054107626512424923 )
  4  group by inst_id, hash_value, name, namespace, child_latch, property, status order by inst_id, hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG STATUS EXECUTIONS NAME 
------- ---------- --------- ----------- -------- ------ ---------- ----------------------------------------------------------------------
      1 356306711  SQL AREA            0          VALID      899301 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 356306711  SQL AREA        53015          VALID      832325 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA            0 HOTCOPY4 VALID      240474 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA        88672 HOTCOPY4 VALID      240582 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA            0 HOT      VALID     3657733 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA        53015 HOT      VALID     3576660 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA            0 HOTCOPY2 VALID      206588 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA        49905 HOTCOPY2 VALID      206662 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA            0 HOTCOPY8 VALID      228203 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA        20114 HOTCOPY8 VALID      228118 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA            0 HOTCOPY5 VALID      275321 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA        89725 HOTCOPY5 VALID      275482 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA            0 HOTCOPY6 VALID      280358 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA        48617 HOTCOPY6 VALID      279606 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA            0 HOTCOPY3 VALID      201829 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA        70399 HOTCOPY3 VALID      201773 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA            0 HOTCOPY1 VALID      244295 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA        97125 HOTCOPY1 VALID      244809 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA            0 HOTCOPY7 VALID      253317 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA        47999 HOTCOPY7 VALID      253521 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

20 rows selected.

4. After running the procedure dbms_shared_pool.markhot one more time, we can now see that the fix is correctly working in both instances of the RAC.

SQL> select inst_id, hash_value, namespace, child_latch, property hot_flag, status, sum(executions) executions, name
  2  from gv$db_object_cache 
  3  where hash_value in (select distinct hash_value from gv$sql where force_matching_signature = 5054107626512424923 )
  4  group by inst_id, hash_value, name, namespace, child_latch, property, status order by inst_id, hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG STATUS EXECUTIONS NAME 
------- ---------- --------- ----------- -------- ------ ---------- ----------------------------------------------------------------------
      1 294476384  SQL AREA            0 HOTCOPY4 VALID      235617 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 294476384  SQL AREA        88672 HOTCOPY4 VALID      235190 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 356306711  SQL AREA            0 HOT      VALID      899301 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 356306711  SQL AREA        53015 HOT      VALID      832325 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 667730673  SQL AREA            0 HOTCOPY2 VALID      243111 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 667730673  SQL AREA        49905 HOTCOPY2 VALID      243114 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 939937426  SQL AREA            0 HOTCOPY8 VALID      272199 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 939937426  SQL AREA        20114 HOTCOPY8 VALID      272379 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1502043773 SQL AREA            0 HOTCOPY5 VALID      223078 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1502043773 SQL AREA        89725 HOTCOPY5 VALID      223230 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1614593513 SQL AREA            0 HOTCOPY6 VALID      254701 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1614593513 SQL AREA        48617 HOTCOPY6 VALID      254807 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1835995903 SQL AREA            0 HOTCOPY3 VALID      238938 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1835995903 SQL AREA        70399 HOTCOPY3 VALID      238361 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 3589634917 SQL AREA            0 HOTCOPY1 VALID      231836 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 3589634917 SQL AREA        97125 HOTCOPY1 VALID      230782 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 4244290431 SQL AREA            0 HOTCOPY7 VALID      225743 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 4244290431 SQL AREA        47999 HOTCOPY7 VALID      225810 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA            0 HOTCOPY4 VALID      129780 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA        88672 HOTCOPY4 VALID      129861 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA            0 HOT      VALID     4565713 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA        53015 HOT      VALID     4469646 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA            0 HOTCOPY2 VALID      120900 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA        49905 HOTCOPY2 VALID      121150 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA            0 HOTCOPY8 VALID      120642 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA        20114 HOTCOPY8 VALID      120730 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA            0 HOTCOPY5 VALID      117835 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA        89725 HOTCOPY5 VALID      117268 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA            0 HOTCOPY6 VALID      140124 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA        48617 HOTCOPY6 VALID      140374 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA            0 HOTCOPY3 VALID      118871 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA        70399 HOTCOPY3 VALID      118520 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA            0 HOTCOPY1 VALID       98116 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA        97125 HOTCOPY1 VALID       98091 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA            0 HOTCOPY7 VALID      119193 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA        47999 HOTCOPY7 VALID      119279 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

36 rows selected.

Restarting the database

1. It is extremely important to note that the statement’s hot status will not survive a database / instance reboot. This means you need to mark it as hot again each time you restart it.

2. Here are the steps to follow to make sure this setup remains available after an instance / database reboot:

a. Make sure you still have the hidden parameter _kgl_hot_object_copies set to number of CPUs.

b. Place the cursor on the library cache in each instance. This means you need to run the statement with the exact same code on each instance, to ensure it uses the right full_hash_value.

c. Mark the instance as hot using the procedure dbms_shared_pool.markhot. As a side note, you can set the global argument to true if you want to mark the statement as hot on all instances but, as mentioned before, the statement will only be marked as hot on those instances were the statement is present on the library cache.

Unmarking hot statements

1. Finally, you can unmark the hot statement using the procedure dbms_shared_pool.unmarkhot.

SQL> begin
  2    dbms_shared_pool.unmarkhot(hash => 'eb4cdceda1c495cd7cdc91e5153ccf17', namespace => 0, global => true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

2. You can confirm that you’ve unmarked the statement by querying the view gv$db_object_cache.

SQL> select inst_id, hash_value, namespace, child_latch, property hot_flag, status, sum(executions) executions, name
  2  from gv$db_object_cache where hash_value in (select distinct hash_value from gv$sql where force_matching_signature = 5054107626512424923 )
  3  group by inst_id, hash_value, name, namespace, child_latch, property, status order by inst_id, hash_value;

INST_ID HASH_VALUE NAMESPACE CHILD_LATCH HOT_FLAG STATUS EXECUTIONS NAME 
------- ---------- --------- ----------- -------- ------ ---------- ---------------------------------------------------------------------- 
      1 294476384  SQL AREA            0 OBSCOPY4 VALID      235617 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 294476384  SQL AREA        88672 OBSCOPY4 VALID      235190 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 356306711  SQL AREA            0          VALID      899301 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 356306711  SQL AREA        53015          VALID      832325 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 667730673  SQL AREA            0 OBSCOPY2 VALID      243111 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 667730673  SQL AREA        49905 OBSCOPY2 VALID      243114 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 939937426  SQL AREA            0 OBSCOPY8 VALID      272199 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 939937426  SQL AREA        20114 OBSCOPY8 VALID      272379 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1502043773 SQL AREA            0 OBSCOPY5 VALID      223078 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1502043773 SQL AREA        89725 OBSCOPY5 VALID      223230 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1614593513 SQL AREA            0 OBSCOPY6 VALID      254701 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1614593513 SQL AREA        48617 OBSCOPY6 VALID      254807 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1835995903 SQL AREA            0 OBSCOPY3 VALID      238938 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 1835995903 SQL AREA        70399 OBSCOPY3 VALID      238361 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 3589634917 SQL AREA            0 OBSCOPY1 VALID      231836 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 3589634917 SQL AREA        97125 OBSCOPY1 VALID      230782 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 4244290431 SQL AREA            0 OBSCOPY7 VALID      225743 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      1 4244290431 SQL AREA        47999 OBSCOPY7 VALID      225810 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA            0 OBSCOPY4 VALID      385506 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 294476384  SQL AREA        88672 OBSCOPY4 VALID      385414 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA            0          VALID     4565713 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 356306711  SQL AREA        53015          VALID     4469646 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA            0 OBSCOPY2 VALID      359549 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 667730673  SQL AREA        49905 OBSCOPY2 VALID      359951 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA            0 OBSCOPY8 VALID      376019 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 939937426  SQL AREA        20114 OBSCOPY8 VALID      376026 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA            0 OBSCOPY5 VALID      420406 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1502043773 SQL AREA        89725 OBSCOPY5 VALID      420031 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA            0 OBSCOPY6 VALID      455769 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1614593513 SQL AREA        48617 OBSCOPY6 VALID      455326 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA            0 OBSCOPY3 VALID      340070 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 1835995903 SQL AREA        70399 OBSCOPY3 VALID      339658 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA            0 OBSCOPY1 VALID      364612 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 3589634917 SQL AREA        97125 OBSCOPY1 VALID      365127 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA            0 OBSCOPY7 VALID      395629 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1
      2 4244290431 SQL AREA        47999 OBSCOPY7 VALID      395981 SELECT LOW_VALUE, HIGH_VALUE FROM CODE_TABLE WHERE CODE_NAME=:B1

36 rows selected.

3. You will notice that all hot copies will be marked as obsolete, meaning they are no longer valid since the original statement is no longer hot.

I hope this post gives you an idea of how to work around the bottlenecks that can be caused by “cursor: pin s.” Please let me know if you have any questions, or tips of your own.

email

Authors

Interested in working with Diego? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *