Library Cache : Causes of Multiple Version Count for an SQL (Day 1 of AIOUG Event)
September 12, 2009 10 Comments
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 :
- select * from emp where ename=:b1;
- SELECT * FROM EMP WHERE ENAME=:b1;
- select * from emp e where e.ename=:b1;
- 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.
- select /*+ VIVEK */ * from emp;
- select /*+ VIVEK */ * from dept;
- SELECT /*+ VIVEK */ * FROM EMP;
- 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 :
- Optimizer & Bind Mismatch – DAY 1
- Bind Graduation (Different Bind Length) – DAY 1
- Cursor_Sharing=SIMILAR (further divided into two scenarios) – DAY 2
- 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.
About Vivek Sharma
Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma 
Hai Vivek,
I have attended AIOUT Technical conference, all the sessions were good, your Three sessions were very interesting and useful for me, I am new to Performance tuning, Is There any Standard document available on Requiremts collection to Identify performance bottlenecks?
we got a call from client and saying Your reports are taking lot of time?
Thank you,
Venkat.
LikeLike
Hi Venkat,
AWR normally collects most of the information and the first page itself reveals about the health of an Instance. As explained during my presentation, look for the Parses and Logical reads, as these consume most of the CPU. Even a single query that does huge Logical reads, can cause a performance bottleneck.
Regards
Vivek
LikeLike
Hi Vivek,
I have read your blog explaining LiberaryCache and it is really helped me to understand internals of library cache. However I have a query.
Declare
I number;
m varchar2(100):=’XXXtab’;
begin
Select (count1) into i from where =m;
end;
This Select statements is not found in either v$SqlArea or v$Sql and information regarding variable ‘m’ is not found in v$sql_bind_metadata and v$Sql_Shared_Cursor did not return any rows.
May i know how oracle has treated this select statement? and is not variable “m” is treated like a bind variable.
I will be really thankfull.
Thanks
Jai
(jaidevs@kpitcummins.com) one of your regular reader.
LikeLike
Hi Jai,
The pl/sql block that you had pasted had errors, for eg. the table name in the select statement is missing. I modified it, so that it works on my test database. The code is :
When you run a pl/sql, be it an anonymous block or database procedure, oracle automatically converts the sql’s into upper case. Also, the name of the bind variable is converted into :b1 and so on. Therefore, when I execute my block on SCOTT schema, it executed and the query, alongwith the value of the bind variable is clearly visible. See below :
LikeLike
Hi vivek,
Thanks a lot.
Thanks
LikeLike
Hi Vivek,
I have few question regarding this. Can you please make my doubt clear
1. How did you get number 67 for child library caches ?
2. Can you please elaborate more on this ?
“Library Cache is a parent Latch with multiple child latches. In this case, we have One Parent Library Cache Latch and 29 Child Latches.”
What i understand is 1 parent library cache can have maximum 29 child latches. After this it has to create new parent library cache latch to acquire more data ?
3. You have said that
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
But when i tested on my 11.0.7 instance on window it shows child_latch 0 for each sql. So, my question is why here we are using child_latch is that because this sql’s are in library cache and to get hold of this we need some lock and that is latch. And here for getting hold of this we required sometime 3 or 2 latch. Then why in my case there are no latch.
LikeLike
Taral,
Reply to 1 :
Number of Child Latches depend on the number of CPU’s visible to Oracle database. Therefore, on the production environment where I executed the query to count the number of child latches from v$latch_children, there were 24 CPU’s and the number of child latches were 29 (Next Prime Number). There’s an underscore parameter “_kgl_latch_count” that can be set to a higher value and the maximum number is 67. For example, on my local database, I have a 2 CPU machine, the number of library cache latches are 3. When I set _kgl_latch_count to 67, the child latches increase as well. See below :
SQL> select count(*) from v$latch_children where name='library cache'; COUNT(*) ---------- 3 SQL> show parameter latch SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 163577856 bytes Fixed Size 1247852 bytes Variable Size 100664724 bytes Database Buffers 54525952 bytes Redo Buffers 7139328 bytes Database mounted. Database opened. SQL> show parameter latch NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _kgl_latch_count integer 67 SQL> select count(*) from v$latch_children where name='library cache'; COUNT(*) ---------- 67Reply to 2
There is only 1 Parent Latch which is a Library Cache Latch and it has multiple child latches. If there is a contention on any of these child latches, the contention (wait event) will be posted as Library Cache Latch contention. Based on the number of CPU’s (24 in my example), the number of child latches will be set to next prime, that is 29. Once done, no more child latches are created at runtime. All the Cursors are protected by these 29 latches.
The functionality that you mention is applicable for Mutexes, but until 11g, We have Library Cache lacthes. Mutex Implemention are for Library Cache Pin, Lock etc. Shared Pool and Library Cache latch still use the same latching algorithm.
Reply to 3
Library Cache Latches are now replaced with Mutexes and therefore, therefore you do not see any value in the Child Latch column. With Mutextes, “Latch : Library Cache” has also been replaced by “Library Cache :Mutex X” waits.
Regards
Vivek
LikeLike
Thanks Vivek For the wonderful article and explanation.
LikeLike
Vivek,
Thanks a ton for the posts, just a quick one mate. Are you the same Vivek Sharma who worked in ICICI Infotech and if yes, do you remember me? 😉
Jaideep
LikeLiked by 1 person
Yes Jaideep. I worked for ICICI Infotech and I do remember you.
Regards
Vivek
LikeLiked by 1 person