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

Written by Diego Morales | Oct 20, 2020 4:00:00 AM

Background

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:

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:

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.
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.
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.
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.
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:

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.
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).
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.
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.
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).
Once the low-order bits are set to zero then the hard parse can proceed.
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

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

View full post