{"id":73090,"date":"2016-12-12T14:09:36","date_gmt":"2016-12-12T14:09:36","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/understanding-sql-query-parsing-part-2-parent-and-child-cursors\/"},"modified":"2021-07-14T13:06:59","modified_gmt":"2021-07-14T13:06:59","slug":"understanding-sql-query-parsing-part-2-parent-and-child-cursors","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/understanding-sql-query-parsing-part-2-parent-and-child-cursors\/","title":{"rendered":"Oracle Parent and Child Cursors: The Library Cache Sharing Model, Soft Parse Deep Dive, and Cursor Version Count Diagnostics"},"content":{"rendered":"

In the first article of this series<\/a>, we learned what query parsing is and what impact it has on query performance. In this part of the series, we shall continue the discussion and look at Soft Parsing of the query.<\/p>\n

Query Processing Workflow Revisited<\/h2>\n

As we saw in the previous installment, a query has to undergo Hard Parsing at least once. It\u2019s important because only after hard parsing first occurs can we expect to find the cursor in memory. Once it\u2019s in memory, the cursor can be reused for the execution of the query as shown below. This process of finding the cursor in the memory (Shared Pool) is called Soft Parsing.<\/p>\n

\"Oracle<\/a><\/p>\n

Soft Parsing<\/h2>\n

Soft parsing happens when the two subsequently-executed statements are deemed identical by the query optimizer. It\u2019s important to note that the statements must not just be identical but shareable<\/em> as well. Here is a simple example to demonstrate this. What we will do is to execute a very simple query, first in lower case and then in upper case. This is exactly same query, the only difference being the sentence case. Let\u2019s see how Oracle treats it.<\/p>\n

    DEPTNO  DNAME\t  LOC\r\n---------- -------------- -------------\r\n\t10 ACCOUNTING\t  NEW YORK\r\n\t20 RESEARCH\t  DALLAS\r\n\t30 SALES\t  CHICAGO\r\n\t40 OPERATIONS\t  BOSTON\r\n\r\nSQL> SELECT * FROM SCOTT.DEPT;\r\n\r\n    DEPTNO DNAME\t  LOC\r\n---------- -------------- -------------\r\n\t10 ACCOUNTING\t  NEW YORK\r\n\t20 RESEARCH\t  DALLAS\r\n\t30 SALES\t  CHICAGO\r\n\t40 OPERATIONS\t  BOSTON<\/pre>\n
So as expected, the result is exactly the same (no surprise there). Oracle treats the queries as identical. But are they also shareable? Let\u2019s find out by looking into two views: V$SQLAREA, V$SQL. This first result is from V$SQLAREA.<\/p>\n
SQL> l \r\n  1  select sql_text, loaded_versions, plan_hash_value\r\n  2  from V$sqlarea\r\n  3* where sql_text like 'select * from scott%' or sql_text like 'SELECT * FROM SCOTT%'\r\nSQL> \/\r\n\r\nSQL_TEXT\t\t\t\t\t   LOADED_VERSIONS PLAN_HASH_VALUE\r\n-------------------------------------------------- --------------- ---------------\r\nSELECT * FROM SCOTT.DEPT\t\t\t\t\t 1\t3383998547\r\nselect * from scott.dept\t\t\t\t\t 1\t3383998547<\/pre>\n
Before we explain the output, let\u2019s look at the V$SQL view as well.<\/p>\n
SQL> select sql_id, sql_text, plan_hash_value, child_number\r\n  2  from v$sql\r\n  3  where sql_text like 'select * from scott%' or sql_text like 'SELECT * FROM SCOTT%'\r\n  4  \/\r\n\r\nSQL_ID\t      SQL_TEXT\t\t\t\t\t\t PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- -------------------------------------------------- --------------- ------------\r\n4db4txmchwqqh SELECT * FROM SCOTT.DEPT\t\t\t\t      3383998547\t    0\r\nf6hhpzwv5jrna select * from scott.dept\t\t\t\t      3383998547\t    0<\/pre>\n
So what do we see here? It\u2019s quite clear that both queries choose to use the same execution plan (they have the same PLAN_HASH_VALUE). This is understandable as both of them are supposed to only use full table scan, which we can see if we view the execution plan. But despite this similarity (both queries being identical), there are certain differences which mean that these two queries are not sharable. For example, you can see that both queries create two different SQL IDs. Also, both queries are loaded independently (LOADED_VERSIONS is 1 for both) and both have an individual \u201cchild cursor created\u201d for them. So, despite the fact that the two statements were identical, Oracle didn\u2019t consider them as sharable. Where this is the case, both of the statements are executed as standalone statements. As we discussed in the previous part of this series, this means both the queries would be Hard Parsed.<\/p>\n
What is Hard Parsing?<\/strong><\/h2>\n
When it\u2019s not possible for a server process to find a matching cursor from the Shared Pool (Library Cache (LC) in particular), or if the cursor that\u2019s present in the LC is not sharable, Oracle will decide that the cursor has to be created (or re-created). In other words, the statement needs to be compiled, and this mechanism is called Hard Parsing.<\/p>\n
The decision between hard or soft parsing is based on whether, for a given statement, a matching parent and an executable and sharable Child cursor is found or not.<\/p>\n
At this point, you must be wondering what the terms \u201cparent\/child cursor\u201d mean. Good that you asked – it\u2019s what we will be looking at next. But before we proceed, it\u2019s important to know a little about the memory structure where these Parent and Child cursors reside \u2013 the Library Cache. So let\u2019s look at Library Cache, from a distance.<\/p>\n
A closer look at Library Cache<\/strong><\/h2>\n
Before we can get to the discussion of the parent and child cursors, we must understand where they reside in the System Global Area (SGA). SGA is a cumulative term and contains numerous shared memory structures inside it, i.e. Data Buffer Cache, Shared Pool, Large Pool etc. Of these, probably the most important memory structure is the Shared Pool. Though SGA contains many sub-structures inside, one of the most important and most-used is the Library Cache.LC is the overarching structure which contains different types of objects inside it. The category of such objects is Namespace, and it\u2019s exposed in the public view of V$LIBRARYCACHE\u2019s NAMESPACE column. The following output is taken from an unpatched 12102 database running over EL 6.5.<\/p>\n
SQL> select namespace from V$librarycache;\r\n\r\nNAMESPACE\r\n----------------------------------------------------------------\r\nSQL AREA\r\nTABLE\/PROCEDURE\r\nBODY\r\nTRIGGER\r\nINDEX\r\nCLUSTER\r\nDIRECTORY\r\nQUEUE\r\nRULESET\r\nEDITION\r\nDBLINK\r\nOBJECT ID\r\nSCHEMA\r\nDBINSTANCE\r\nSQL AREA STATS\r\nSQL AREA BUILD\r\nPDB\r\n\r\n17 rows selected.<\/pre>\n
Of the above categories, the most highly-used is SQLAREA. Each category represents the objects which are loaded in the LC known as Library Objects (LO). LOs for the SQLAREA namespace are SQL statements which we upload when we execute a query.<\/p>\n
Now Parent and Child cursors come into the picture.<\/p>\n
Understanding Parent and Child Cursors<\/strong><\/h2>\n
A query is represented in the library cache in the form of a shared cursor. This shared cursor is further categorized into two distinct types – Parent & Child cursors. Though both cursor types are important, it\u2019s the child cursor in the library cache which governs the decision over whether a query will undergo a hard parse or a soft parse. If a query will undergo soft-parsing, then a matching child cursor for that query must be found – and not just found, the cursor must be identical and sharable. Of course, as we have seen in the example given above, the statement that we are currently executing is identical to the one we have executed before. But it might not be a sharable statement and if that\u2019s the case, we have to reoptimize it. If in this case you are confused by the word \u201ccursor\u201d, it is the memory area that represents the statement.<\/p>\n
A Parent cursor is essentially created for every query that\u2019s uploaded in the LC You can say that this form of the cursor (or query) is like a wrapper or a template given to the statement. But just like a wrapper, it might not contain the same ingredients inside. The parent cursors are located in the LC using the Library Cache latch and the parent cursors are represented in the view V$SQLAREA. If we look at the example statement that we saw before, we have two separate or distinct Parent cursors in the LC. But a parent cursor has to execute as well. And that\u2019s where the concept of Child Cursor comes into play.<\/p>\n
A child cursor represents a particular form or type of parent cursor. In other words, a child cursor represents what will finally be executed. Since every child cursor must belong to a parent, thus every parent would execute with at least one child cursor created for it. Several forms of the parent cursor may exist in the Library Cache.<\/p>\n
A simplified depiction of the LC and the parent and child cursors is given below:<\/p>\n
\"Library<\/a><\/p>\n
Let\u2019s see an example of the situation where a query is will have a single parent cursor but the child cursors are not shared. For this example, we will alter the settings of the optimizer mode used in the execution of the query.<\/p>\n
We start by flushing the Shared Pool and then move on to alter the OPTIMIZER_MODE parameter to FIRST_ROWS (the default value is ALL_ROWS).<\/p>\n
SQL> alter system flush shared_pool; \r\nSystem altered.\r\nSQL> alter session set optimizer_mode=first_rows;\r\nSession altered.\r\nSQL> select * from t;\r\nDEPTNO          DNAME           LOC\r\n---------- \t-------------- \t-------------\r\n10 \t        ACCOUNTING\tNEW YORK\r\n20 \t        RESEARCH\tDALLAS\r\n30\t        SALES\t  \tCHICAGO\r\n40\t        OPERATIONS\tBOSTON<\/pre>\n
Now, let\u2019s alter the parameter again to a different mode.<\/p>\n
SQL> alter session set optimizer_mode=choose;\r\nSession altered.\r\nSQL> select * from t;\r\nDEPTNO          DNAME           LOC\r\n---------- \t-------------- \t-------------\r\n10 \t        ACCOUNTING\tNEW YORK\r\n20 \t        RESEARCH\tDALLAS\r\n30\t        SALES\t  \tCHICAGO\r\n40\t        OPERATIONS\tBOSTON<\/pre>\n
So we have executed the same query twice now. Let\u2019s check the parent cursor created for this query in the Library Cache:<\/p>\n
SQL> select sql_id, sql_text, version_count, hash_value\r\n  2  from V$sqlarea where sql_text like 'select * from t'\r\n  3  \/\r\nSQL_ID\t      SQL_TEXT\t\tVERSION_COUNT HASH_VALUE\r\n------------- ---------------- -------------  ----------\r\n89km4qj1thh13 select * from t\t2 \t      1134051363<\/strong><\/pre>\n
So we can see that for this query there are two versions that are loaded in the memory. This shows that there are two distinct versions of the query available – these are the child cursors. Let\u2019s see them now:<\/p>\n
SQL> select sql_id, sql_text, hash_value, plan_hash_value, child_number CSR#\r\n  2  from V$sql\r\n  3  where sql_text like 'select * from t\u2018;\r\n   \r\nSQL_ID\t                   SQL_TEXT\t              HASH_VALUE          PLAN_HASH_VALUE               CSR#\r\n-------------\t           ------------ ----------    -----------------   ---------------------------   -------\r\n89km4qj1thh13              select * from t            1134051363          1601196873\t                0<\/strong>\r\n89km4qj1thh13              select * from t            1134051363          1601196873\t                1<\/strong><\/pre>\n
As expected, we see that two child cursors have been created. Both statements are completely identical as we have already seen. So why are these statements not be shared? Instead of the statement being considered for Soft Parsing, why did the database engine decide to treat the second statement as a newly-created one? The answer lies in the V$SQL_SHARED_CURSOR view. This view is very useful as it literally contains the reasons why the optimizer decided to mark the newly-created cursor as an unshared one. For the given example, we know it\u2019s the optimizer mode\u2019s mismatch. But in the real world, it won\u2019t be this straightforward to find out the reason why the cursors couldn\u2019t be shared. To find out, you need to query the V$SQL_SHARED_CURSOR view. Here is an output of this view for the above statement:<\/p>\n
SQL>  select s.sql_text, s.child_number, s.child_address, c.OPTIMIZER_MODE_MISMATCH\r\n    from   v $sql s, v$sql_shared_cursor c\r\n    where s.sql_text like 'select * from t'\r\n    and s.child_address = c.child_address;  \r\nSQL_TEXT\t          CHILD_NUMBER             CHILD_ADDRESS    \t         O\r\n------------------------- -----------------------  ----------------              ------  \r\nselect * from t \t  0 \t                   00000000815A8728 \t         N\r\nselect * from t \t  1 \t                   00000000815600A0\t         Y<\/pre>\n
It\u2019s clearly visible here that the optimizer mismatch was the reason for the new child cursor being created.<\/p>\n
Conclusion<\/h2>\n
Understanding the parent and child cursor concept is of paramount importance in order to have a clear picture of the Hard Parsing concept. In the next part of the series, we will look at the concept of the No-Parsing! Stay tuned!<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle parent and child cursors explained – how the library cache shares SQL execution plans across identical statements while maintaining per-session context differences, the soft parse workflow, version counts, and diagnosing cursor-sharing issues via V$SQL_SHARED_CURSOR. Part 2 of the series.…<\/p>\n","protected":false},"author":316201,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48375,48469,48482,48484],"coauthors":[],"class_list":["post-73090","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-child-cursor","tag-parent-cursor","tag-query-optimization","tag-query-processing"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316201"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73090"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73090\/revisions"}],"predecessor-version":[{"id":91593,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73090\/revisions\/91593"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73090"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}