Library Cache Latch Contention due to Multiple Version Count – Day 2 of AIOUG

Day 2 of “All India Oracle User Group” was continuation of where I left on Day 1 – Library Cache and Multiple Version Count. On Day 2, I had a real life example on the issue that I presented. On Day 1, the conceptual knowledge of Library Cache, Parent & Child Cursors and some of the fundamental, but not to be ignored, causes of Multiple Child Cursors was shared.

One of the major cause of Multiple Version for an SQL is CURSOR_SHARING. When cursor_sharing is set to SIMILAR, optimizer checks for either of these :

Is the Operator NEITHER of ‘=’ or ‘!=’

OR

Are there Column Histograms present on the Columns

When either of these are true, then, irrespective of whether the execution plan of the new child cursor is already stored, a new Child cursor is created. The only exception is, if the runtime value of the bind variable matches that of an already stored child cursor. We will see this with an example.

Before we begin, as a prerequisite, we will create our test case that will be used for both the cases mentioned above. The data in this table is populated in a skewed manner and we create an Index on the skewed column.

PREREQUISITE

exec dbms_random.seed(0);

drop table test_hist;

create table test_hist as 
with n as
(select /*+ materialize */ round(dbms_random.normal,0) n1 from all_objects where rownum<=10000)
select decode(sign(n1),-1,n1+9,n1) empid,
       round(dbms_random.value(1000,2000),0) sal,
       case when mod(round(dbms_random.value(101,110),0),2)=0 then 'M' else 'F' end gender
from	n
where rownum<=10000;

create index test_hist_idx on test_hist(empid);

select empid, cnt, round(cnt/final_total*100,2) percentage from (
select empid, cnt, sum(cnt) over() final_total
from (
select empid, count(*) over(partition by empid) cnt, 
row_number() over (partition by empid order by empid) rno 
from test_hist)
where rno=1);

     EMPID        CNT PERCENTAGE
---------- ---------- ----------
         0       3937      39.37
         1       2289      22.89
         2        630        6.3
         3         55        .55
         4          4        .04
         5          4        .04
         6         64        .64
         7        646       6.46
         8       2371      23.71

9 rows selected.

Once the test case is ready, we will walk through the two issues, mentioned above, introduced by Cursor_Sharing set to SIMILAR. For this purpose, we will generate statistics in two different ways: With & Without Histograms.

TEST CASE 1 : With Histogram and Equality Predicates

Gather Stats on this table with method_opt as ‘FOR COLUMNS EMPID SIZE 74’. With this, we generate Frequency Histogram on Empid column. Since the column has only 9 distinct values, creating histogram will generate different execution plans for the values with less data.

Combination of Cursor_Sharing to SIMILAR and unwanted Histograms can cause a major performance bottleneck, with contention on Library Cache related waits. Therefore, it is always recommended to generate Histograms only when required. Oracle 10g changed the default value of method_opt from ‘FOR ALL COLUMNS SIZE 1’ to ‘FOR ALL COLUMNS SIZE AUTO’ and with this change, any database with CURSOR_SHARING set to SIMILAR, would face a severe latch contention issue after upgrading to 10g, as unwanted histograms are generated on columns with large distinct values. As you will see in this test case, these unwanted histograms introduce more child cursors, further causing a hash chain to grow longer. We all know that latches are to be held for brief duration, but, if number of child cursors grow, inspecting a hash chain for a matching cursor can take significant amount of time preventing others to get the latch.

alter system set cursor_sharing=similar scope=memory;
select /*+ VIVEK */ sal from test_hist where empid=0;
select /*+ VIVEK */ sal from test_hist where empid=2;
select /*+ VIVEK */ sal from test_hist where empid=4;
select /*+ VIVEK */ sal from test_hist where empid=7;
select /*+ VIVEK */ sal from test_hist where empid=8;

Set CURSOR_SHARING to SIMILAR and execute 5 queries with different values passed to empid. Once these are run, check against v$sqlarea, v$sql and finally, v$sql_shared_cursor to get the cause of multiple versions. The output would be similar to that of mine, except for the plan_hash_value. In my case, for all the cases above, the optimizer opted for Full Table Scan. In your case, the plans would differ. Even though the execution plans may or may not match, it is irrelevant for this discussion. After execution of the 5 queries, the output would look like:

SQL> select sql_id, sql_text, executions, child_latch, version_count, plan_hash_value, hash_value fr
om v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH VERSION_COUNT PLAN_HASH_VALUE HASH_VALUE
------------- ------------------------------ ---------- ----------- ------------- --------------- --
baz378z2m5q42 select /*+ VIVEK */ sal from t          5           2             5      3529380458 3308443778
              est_hist where empid=:"SYS_B_0
              "


Elapsed: 00:00:00.10
SQL>select sql_id, sql_text, executions, child_latch, plan_hash_value, child_number, child_address from v$sql where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH PLAN_HASH_VALUE CHILD_NUMBER CHILD_AD
------------- ------------------------------ ---------- ----------- --------------- ------------ ---
baz378z2m5q42 select /*+ VIVEK */ sal from t          1           2      3529380458            0 699F048C
              est_hist where empid=:"SYS_B_0
              "

baz378z2m5q42 select /*+ VIVEK */ sal from t          1           2      3529380458            1 68A65B38
              est_hist where empid=:"SYS_B_0
              "

baz378z2m5q42 select /*+ VIVEK */ sal from t          1           2      3529380458            2 68A47B00
              est_hist where empid=:"SYS_B_0
              "

baz378z2m5q42 select /*+ VIVEK */ sal from t          1           2      3529380458            3 69519264
              est_hist where empid=:"SYS_B_0
              "

baz378z2m5q42 select /*+ VIVEK */ sal from t          1           2      3529380458            4 6A3B1AE0
              est_hist where empid=:"SYS_B_0
              "

Elapsed: 00:00:00.25

SQL> select * from v$sql_shared_cursor where sql_id='baz378z2m5q42';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U 
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - -
M B M R O P M F L
- - - - - - - - -
baz378z2m5q42 684B2D44 699F048C            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
N N N N N N N N N

baz378z2m5q42 684B2D44 68A65B38            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
N N N N N N N N N

baz378z2m5q42 684B2D44 68A47B00            2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
N N N N N N N N N

baz378z2m5q42 684B2D44 69519264            3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
N N N N N N N N N

baz378z2m5q42 684B2D44 6A3B1AE0            4 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 
N N N N N N N N N

Surprisingly, v$sql_shared_cursor does not provide a justification for the cause of multiple child cursors. If you again run a query with EMPID set to any of the values passed earlier (eg empid=0), the version_count will not be incremented. This means, a child cursor, in this case, will only be shared if a value passed to the FILTER Predicate matches any of the bind value previously executed.

TEST CASE 2 : Without Histogram and Range Predicates

For this test case, regather the statistics on the TEST_HIST table with method_opt changed to ‘FOR ALL COLUMNS SIZE 1’, which means, no histograms. Without Histograms, multiple child cursors will be created for any queries that does not contain EQUALITY (=) or NOT EQUAL (!=) predicates. For demonstration, make sure the histograms are removed and then execute following queries:

select /*+ VIVEK */ empid, sal from test_hist where sal between 1000 and 1100;

select /*+ VIVEK */ empid, sal from test_hist where sal between 1200 and 1500;

select /*+ VIVEK */ empid, sal from test_hist where sal between 1700 and 1800;

select /*+ VIVEK */ empid, sal from test_hist where sal between 1500 and 2000;

The output should look like:

SQL> select sql_id, sql_text, executions, child_latch, version_count, plan_hash_value, hash_value fr
om v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH VERSION_COUNT PLAN_HASH_VALUE HASH_VALUE
------------- ------------------------------ ---------- ----------- ------------- --------------- --
2xk8mywyq0z69 select /*+ VIVEK */ empid, sal          4           2             4      1054764382 1029733577
               from test_hist where sal betw
              een :"SYS_B_0" and :"SYS_B_1"


Elapsed: 00:00:00.04
SQL> select sql_id, sql_text, executions, child_latch, plan_hash_value, child_number, child_address 
from v$sql where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH PLAN_HASH_VALUE CHILD_NUMBER CHILD_AD
------------- ------------------------------ ---------- ----------- --------------- ------------ ---
2xk8mywyq0z69 select /*+ VIVEK */ empid, sal          1           2      1054764382            0 69BCD9A0
               from test_hist where sal betw
              een :"SYS_B_0" and :"SYS_B_1"

2xk8mywyq0z69 select /*+ VIVEK */ empid, sal          1           2      1054764382            1 69FF769C
               from test_hist where sal betw
              een :"SYS_B_0" and :"SYS_B_1"

2xk8mywyq0z69 select /*+ VIVEK */ empid, sal          1           2      1054764382            2 693BED94
               from test_hist where sal betw
              een :"SYS_B_0" and :"SYS_B_1"

2xk8mywyq0z69 select /*+ VIVEK */ empid, sal          1           2      1054764382            3 68F50694
               from test_hist where sal betw
              een :"SYS_B_0" and :"SYS_B_1"

Again, in this case, if you re-execute a query with sal between 1200 and 1500, the version_count will not be incremented as a shared version of a query with same bind values, is already stored in the library cache. In both the cases, v$sql_shared_cursor does not provide the justification for the cause of multiple child cursors and therefore, it is very difficult to diagnose this.

These two test cases should help you understand the side effect of setting Cursor_Sharing parameter to SIMILAR. With this setting, even if you take utmost care about not generating unwanted histograms, any range predicate queries can cause Library Cache contention issue. I am sure that while Histograms are avoidable, Range Based Queries are unaviodable. 🙂

A Child Library Cache Latch protects multiple objects (cursors). This means, two or more entirely different queries can be hashed to a same child latch. Time Consuming match for a query with several child cursors can also cause other sessions to wait on Library Cache Latches for other queries, protected by this same latch, with 1 child cursor. Therefore, Library Cache Latch contention are also called as False Contention and utmost care has to be taken while diagnosing contention on these latches.

Real Life Example

During the AIOUG event and after the completion of these test cases, I presented on an Real Life Example. One of my customer complained for severe performance issues and one of the cause of this slow performance was identified as Contention on Library Cache Latches. These are some of the statistics, before and after eliminating Library Cache Latch Contention.

BEFORE

Optimizing Performance Part 21

ISSUE

The combination of Database Version 10.2.0.4 and Cursor_Sharing=SIMILAR was enough to predict the issue which was due to multiple version count. The table below shows the list of top 20 Queries, with Executions and their Version Counts.

select * from (
select sql_id, version_count, executions, child_latch from v$sqlarea
order by 2 desc)
where rownum<=20;

SQL_ID        VERSION_COUNT EXECUTIONS CHILD_LATCH
------------- ------------- ---------- -----------
5w93sms4xr70b         25775      59303          42
4upsrgs9r0z8h         19982      41372          10
cpk0xaggwc9q7         15316      41929          56
fx0662kfns5mj         12060      23058          17
c8brg2z6ytxjk          8991      19637          21
6t87d75m0hby0          8863      46765          37
cnsttwq7y79tc          8549      42953           7
76hw2b3ujar2z          8290        988          61
dh8yhs5pjh0dt          7670      10890          28
8xjwbmfsn0s2y          7057        996          53
dfp62t3qy92fm          6176     192357          26
2d1p0dmyaapv3          4718      16237          64
0a8bvn7zn8u26          4026       9192          39
a2qn47hh1ypqk          3982       7017           8
c1dq1hg17y3xg          3520       2255          62
08q94ja2kmqs3          3304       6143           2
4gm2wn99nk1mk          3190       6566          15
6s2cp8hk4bych          2907      48162          20
8z1b906qc3tnx          2752       6060          47
bg5gftj0r3s4m          2630      10283          52


20 rows selected.

The text of 1st Query from the above list is as under :

SQL_ID : 5w93sms4xr70b
Module : GI6_03
Sharable_mem : 337,369,920  

SELECT :"SYS_B_0" FROM GIT_LOP_COVER 
WHERE 	ROWNUM < :"SYS_B_1" 
AND 	LOP_SYS_ID = :"SYS_B_2" 
AND 	END_N_IDX = :"SYS_B_3"
AND 	END_S_NO  = :"SYS_B_4" 
AND 	ATTACH_L_NO = :"SYS_B_5" 
 
OWNER             PAR   NUM_ROWS     BLOCKS LAST_ANALYZED        GLO T
----------------- --- ---------- ---------- -------------------- --- -
LOP_PROD          NO    86222715    4334060 02-Sep-2008 10:05:26 YES N
 
 
COLUMN_NAME       NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
----------------- ------------ ---------- ---------- ---------------
LOP_SYS_ID            12529993          0 1.8931E-07 HEIGHT BALANCED 
END_N_IDX                   23          0 5.8944E-09 FREQUENCY
END_S_NO                     1          0 5.8944E-09 FREQUENCY
ATTACH_L_NO                  3   11880179 6.8543E-09 FREQUENCY

The Query uses Equality Predicates and we have already seen the issue due to Histograms and Cursor_Sharing SIMILAR. To validate this further, check for the column statistics, as shown above, for the queried table and it uses Histograms on the columns used in this query.

SOLUTION

To resolve this latch contention issues, the default method_opt was changed to ‘FOR ALL COLUMNS SIZE 1’ and the statistics were recollected. This gave some relief, but the version count for some of the queries were still high due to Range Predicates. Therefore, CURSOR_SHARING was set to FORCE. After this change, the version_count dropped significantly and the benefit can be seen from the statistics pasted below.

Optimizing Performance Part 24

With Multiple Version Counts, Hard Parses were more than 100 per second. After changing from SIMILAR to FORCE, it came down to 60. Further implementation of Bind Variables, in place of Literals, brought this down to less than 10.

Production Statistics, post optimization


Optimizing Performance Part 25

In the next blog, I would write on another test case alongwith a real life example on the issue due to Improper Implementation of an Oracle feature that caused a severe Row Cache Latch Contention. This discussion was also a part of my Day 2 presentation at AIOUG.

Library Cache : Causes of Multiple Version Count for an SQL (Day 1 of AIOUG Event)

I am just back from the 3 day technical event at Bangalore, India. This event gave me an oppurtunity to meet and speak to a legend, and one of my Guru, Thomas Kyte. His presentations were remarkable. This event also gave me an oppurtunity to meet many of my readers. My purpose of initiating blogs (I started in Dec 2006) was to educate Oracle Community and It is very satisfying to know that, whatever you write, reaches the Community. Many readers were disappointed since my previous blog url is still inaccessible, and I had to assure them, that all those posts, would be re-posted on this blog. This means, in India, I too have a good fan following. 🙂

I had three sessions span across each of the three days, and Day 1 of the event, I discussed mostly on Library Cache and some of the issues that have an impact on performance, causing Library Cache related Latch Contention. As usual, to prove the point, I demonstrated each of these with some scripts executed on my 10.2.0.1 database. Day 2, also began with a brief discussion on this issue and I demonstrated some more facts, including an issue due to “Improper Implementation of Virtual Private Database”. This post is a detailed discussion on what I presented and demonstrated at the Event on Day 1, as it gives a brief understanding of Parent & Child Cursors. Day 2, I started with the same topic and my next few blogs will cover the test cases and explanations that I present on the second day.

In short, this blog should give you a conceptual knowledge on Library Cache, Parent Cursors, Child Cursors and will also explain about Bind Graduation. Once the concepts of these are cleared, the next blog will feature some more issues that cause creation of multiple child cursors and relate to a Real Life Experience.

We all know that the purpose of Library Cache is to maximize sharing of resources required to execute SQL statements from multiple sessions. In Oracle, these SQL statements are called as CURSORS and therefore, cursors should not be misinterpreted as implicit and explicit cursors defined in pl/sql blocks.This Shareability reduces the memory requirements and the parse time because parsed representations of an SQL may already be stored in the Library Cache. Two SQL statements will not be shared unless their SQL text is completely identical. Any difference in spacing or casing will make two SQL statements be considered different and none of their data structures can be shared. Assume following queries :

  1. select * from emp where ename=:b1;
  2. SELECT * FROM EMP WHERE ENAME=:b1;
  3. select * from emp e where e.ename=:b1;
  4. select * from emp where ename=:b1;

Although the four queries are using Bind Variables, only two of them are sharable, while the other two (2 & 3) will not be shared. The shared representation of a SQL cursor is stored in two parts: the Parent Cursor and the Child Cursor.

PARENT CURSOR

It stores the name i.e. the SQL text of the Cursor. When two statements are identical textually, they will share a same Parent Cursor. As an example, query no.1 and 4 above, will share a same Parent Cursor.

CHILD CURSOR

The Child Cursor holds other required information, like: the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used; the session value of the session parameters that have an impact on the optimizer decisions and the execution plan of the Cursor.

What this means is, two textually similar statements, sharing same parent cursor, will also share a same child cursor if they share each of the above mentioned information. This also infers that a parent cursor, will have atleast (minimum) one or more than one child cursor. The ratio is 1:1 (one parent : one child) or 1:M (one parent : multiple child).

The demonstration in this blog are all the same that I presented during the event and are run on 10.2.0.1 database. The output of some of the queries may differ in 10.2.0.2 and above, but the issue remains same.

Number of Library Cache CHILD Latches
SQL> show parameter cpu


SQL> show parameter cpu

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cpu_count                            integer                          24

select count(*) from v$latch_children where name='library cache';

  COUNT(*)
----------
        29

I executed this query on a production system with 24 CPU, and the number of Child Latches, automatically pre-decided and allocated at run time by Oracle kernel, was set to next smallest prime number to the number of CPU’s and can go upto 67 max. Therefore, even if I have a 80 CPU machine, the number of Child Library Cache latches would be limited to 67. Library Cache is a parent Latch with multiple child latches. In this case, we have One Parent Library Cache Latch and 29 Child Latches.

DEMONSTRATION OF 1:1 ratio

I will execute 4 queries out of them, 3 will be on EMP table and 1 on DEPT. While a query on DEPT will fetch different resultset, the other three will fetch the same resultset, but, will share different parent cursors. Therefore, even though, the execution plans of these three are same (FTS on EMP), these will be mapped to different parent cursor as these do not match textually. Also note that these 4 hashed (internal algorithm) to different child latches.

  1. select /*+ VIVEK */ * from emp;
  2. select /*+ VIVEK */ * from dept;
  3. SELECT /*+ VIVEK */ * FROM EMP;
  4. select /*+ VIVEK */ * from emp e;

Dynamic view V$SQLAREA maintains single row per parent cursor, whereas, V$SQL maintains each row for a Child Cursor. One the four queries, mentioned above, were executed, the output form V$SQLAREA and V$SQL was as under :

SQL> column sql_text for a40 wrap
SQL> set lines 132
SQL> select sql_id, sql_text, executions, child_latch from v$sqlarea where upper(sql_text) like 'SELECT /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH
------------- ---------------------------------------- ---------- -----------
bsmb6z005wgyr select /*+ VIVEK */ * from emp e                  1           1
c9qspg124hjk4 SELECT /*+ VIVEK */ * FROM EMP                    1           3
00fdja7qcpswr select /*+ VIVEK */ * from dept                   1           3
2p65wk92f3vpq select /*+ VIVEK */ * from emp                    1           2

SQL> select sql_id, sql_text, executions, child_latch from v$sql where upper(sql_text) like 'SELECT /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH
------------- ---------------------------------------- ---------- -----------
bsmb6z005wgyr select /*+ VIVEK */ * from emp e                  1           1
c9qspg124hjk4 SELECT /*+ VIVEK */ * FROM EMP                    1           3
00fdja7qcpswr select /*+ VIVEK */ * from dept                   1           3
2p65wk92f3vpq select /*+ VIVEK */ * from emp                    1           2

This demonstration also shows that after we executed these queries, a child cursor automatically, these can be mapped from SQL_ID. For each SQL_ID in V$SQLAREA, we have a corresponding row in V$SQL (1:1).

DEMONSTRATION OF 1:M ratio

While there are multiple reasons for the scenario where 1 Parent Cursor can have Multiple Child Cursors, I demostrated few of them and these were :

  1. Optimizer & Bind Mismatch – DAY 1
  2. Bind Graduation (Different Bind Length) – DAY 1
  3. Cursor_Sharing=SIMILAR (further divided into two scenarios) – DAY 2
  4. Improper Implemention of Oracle features VPD – DAY 2

While this blog covers DAY 1, the third scenario will feature in my next blog, and the fourth on VPD will follows after that. Day 2 Blogs relates to some of my Real Life Challenges and I will relate these to those experiences.

OPTIMIZER & BIND MISMATCH

We all know and understand the importance of bind variables. Bind Variables are crucial in a Transaction processing (OLTP) databases, and one of the primary responsilibility of an Application Developer is to make sure that application queries make use of Bind Variables.

This demonstration uses Bind Variables, therefore, during the presentation, one of the Attendee asked me a question (during Q&A session) that since we have too many child cursors with bind Implementation, does it mean that we need to change these to Literals ? Please note that, Literals are very bad for Transaction Processing System and this test case demonstrates an issue, not due to Bind Variables, but the way these are either implemented or some other settings that make a sharable parent cursor partially sharable.

In this test case, I will execute textually similar query, with different Bind Length and Optimizer Settings. Since the queries are textually same, they will be mapped to a single SQL_ID and to a same child latch, but will have multiple child cursors (version_count).

FLUSHING OF SHARED_POOL IS NOT RECOMMENDED ON PRODUCTION. THIS IS A TEST INSTANCE, I AM FLUSHING TO DEMONSTRATE THE EXECUTIONS AND VERSION_COUNT THAT MATCH THE TEST CASE.

SQL> alter system flush shared_pool;

System altered.

SQL> connect x/x
Connected.
SQL> variable b1 varchar2(10);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> REM Same Query with Different Optimizer Settings
SQL> alter session set optimizer_index_cost_adj=10;

Session altered.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> connect x/x
Connected.
SQL> REM Optimizer Setting Default
SQL> variable b1 varchar2(40);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> connect y/y
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

SQL> variable b1 varchar2(10);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> connect vivek/vivek
Connected.
SQL> column sql_text for a30 wrap
SQL> set lines 132
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ------------------------------ ---------- ----------- -------------
f2q4bgycc2b1y select /*+ VIVEK */ * from emp          4           1             4
               where ename=:b1

SQL> select sql_id, sql_text, executions, plan_hash_value, child_latch from v$sql where sql_text lik
e 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS PLAN_HASH_VALUE CHILD_LATCH
------------- ------------------------------ ---------- --------------- -----------
f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1      3956160932           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1      3956160932           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1      3956160932           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1      3956160932           1
               where ename=:b1

SQL> select sql_id, address, child_address, optimizer_mismatch, bind_mismatch, translation_mismatch
  2  from v$sql_shared_cursor where sql_id='f2q4bgycc2b1y';

SQL_ID        ADDRESS  CHILD_AD O B T
------------- -------- -------- - - -
f2q4bgycc2b1y 6A2CF72C 69334384 N N N
f2q4bgycc2b1y 6A2CF72C 6A25D5DC Y N N
f2q4bgycc2b1y 6A2CF72C 684F5684 Y Y N
f2q4bgycc2b1y 6A2CF72C 69144D8C Y N Y

One Parent Cursor, check the SQL_ID and ADDRESS from V$SQLAREA & V$SQL, multiple Child Cursors with Child_Number in V$SQL. The CHILD_ADDRESS from V$SQL can be mapped to CHILD_ADDRESS of V$SQL_SHARED_CURSOR to get the reason for the mismatch between already stored child cursor.

In this test case, we executed 4 queries. While each of these were textually similar, they shared same library cache parent cursor. When the first query was executed, it created a parent cursor and a child cursor. When second query was executed, as it was textually similar, it shared a same parent cursor but, since optimizer_index_cost_adj was set to a non-default value, optimizer assumed that this setting might require another plan and creates a new child cursor. Another query was again textually same, but was executed on a different object, schema Y and the fourth one, was with a modified bind length. In this case, while optimizer assumed that the plan would be different, it created a new child cursor, but if you look at the PLAN_HASH_VALUE form V$SQL, the plan for each of these is same i.e. FTS.

Now, for the another test on BIND GRADUATION. In order to limit the creation of number of child cursors, Oracle limits and maps the size of a Bind Length (for VARCHAR2 only) to a maximum limit. Lets execute the queries with different bind length and check the number of child cursors for each of these.

SQL> alter system flush shared_pool;

System altered.

SQL> variable b1 varchar2(10);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> variable b1 varchar2(30);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> variable b1 varchar2(40);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> variable b1 varchar2(80);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> variable b1 varchar2(140);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> variable b1 varchar2(2040);
SQL> exec :b1:='KING';

PL/SQL procedure successfully completed.

SQL> select /*+ VIVEK */ * from emp where ename=:b1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> column sql_text for a30 wrap
SQL> set lines 132
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ------------------------------ ---------- ----------- -------------
f2q4bgycc2b1y select /*+ VIVEK */ * from emp          6           1             4
               where ename=:b1

SQL> select sql_id, sql_text, executions, child_latch from v$sql where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                       EXECUTIONS CHILD_LATCH
------------- ------------------------------ ---------- -----------
f2q4bgycc2b1y select /*+ VIVEK */ * from emp          2           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          2           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1           1
               where ename=:b1

f2q4bgycc2b1y select /*+ VIVEK */ * from emp          1           1
               where ename=:b1


SQL> select * from v$sql_shared_cursor where sql_id='f2q4bgycc2b1y';

SQL_ID        ADDRESS  CHILD_AD B
------------- -------- -------- -
f2q4bgycc2b1y 6A15BC84 694FF7E8 N
f2q4bgycc2b1y 6A15BC84 68768DC8 Y
f2q4bgycc2b1y 6A15BC84 6A258CF8 Y
f2q4bgycc2b1y 6A15BC84 69BA5E00 Y

SQL> select address, position, datatype, max_length, bind_name
  2  from v$sql_bind_metadata
  3  where address in
  4  (select child_address from v$sql where sql_id='f2q4bgycc2b1y');

ADDRESS    POSITION   DATATYPE MAX_LENGTH BIND_NAME
-------- ---------- ---------- ---------- ------------------------------
694FF7E8          1          1         32 B1
68768DC8          1          1        128 B1
6A258CF8          1          1       2000 B1
69BA5E00          1          1       4000 B1

In this case, I executed the queries with different bind length. For six executions, there were 4 child cursors and the bind graduation limited these to the maximum value as exposed by V$SQL_BIND_METADATA.

This was the end of DAY 1 of my presentation and I tried to give a conceptual knowledge on CURSORS. More on this in my next 2 blogs.

%d bloggers like this: