SQL sharing: parent cursor and child game subject

The resolution operation saves a parent cursor and child cursor to the library cache and saves them to sga to reuse them and avoid hard parsing. Under what circumstances can I reuse the parent cursor and child cursor?
The key information of the parent cursor is the SQL text, and the key information of the Child game subject is the execution plan and execution environment. Query the parent cursor information using v $ sqlarea and v $ SQL query the sub-game subject information. You can determine a cursor through address, hash_value, and child_number. The parent cursor is uniquely determined by address and hash_value, and 10 Gb is more commonly used by SQL _id to replace address and hash_value. The child cursor is uniquely determined by address, hash_value, and child_number.
1. If the parent cursor cannot be shared, the key information of the parent cursor is SQL text. Therefore, multiple SQL statements can share the same parent cursor only when their texts are completely consistent, this is the most basic requirement. Www.2cto.com SQL> select * from zhhtest where id = 1; SQL> SELECT * from zhhtest where id = 1; SQL> select * from zhhtest where id = 1; SQL> select * from zhhtest where id = 1; the first and fourth SQL statements are exactly the same; the second and third SQL statements are different in case and space. By querying v $ sqlarea, we can confirm that the system has created three parent cursors. Pay attention to the number of executions. SQL> select SQL _id, SQL _text, executions from v $ sqlarea where SQL _text like '% id = 100'; SQL _ID SQL _TEXT EXECUTIONS -------------------- 2z33s0z9wp798 select SQL _id, SQL _text, executions from v $ sqlarea w 1 here SQL _text like '% id = 1%' b5v00mn1t6x0v SELECT * FROM ZHHTEST where id = 1 hour select * from zhhtest where id = 1 1ckpuxaxjrm1qq select * from zhhtest where id = 1 2
2. You can share the parent cursor without sharing the child game subject. The key information of the Child game subject is the execution plan and the execution environment. If the execution environment changes, the execution plan may also change. Multiple SQL statements can share the same child cursor only when they have a common parent cursor and the execution environment is compatible. SQL> alter session set optimizer_mode = all_rows; Session altered. www.2cto.com SQL> select count (1) from zhhtest; COUNT (1) ---------- 300SQL> alter session set optimizer_mode = first_rows; Session altered. SQL> select count (1) from zhhtest; COUNT (1) ---------- 300 from v $ SQL query, create a parent cursor (b2kjsg0h8rt08) and two child cursors (0/1 ), note that the two child cursors use the same Execution Plan (PLAN_HASH_VALUE ). SQL> select prev_ SQL _id from v $ session where sid = (select sid from v $ mystat where rownum = 1); PREV_ SQL _ID ----------- b2kjsg0h8rt08SQL> select SQL _id, child_number, SQL _text, optimizer_mode, plan_hash_value from v $ SQL where SQL _id = 'authorization'; SQL _ID CHILD_NUMBER SQL _TEXT OPTIMIZER _ PLAN_HASH_VALUE ------------- ------------ limit -------- limit 0 select count (1) from zhhtest ALL_ROWS limit 1 select count) from zhhtest FIRST_ROWS 2173948436 www.2cto.com
3. v $ SQL _shared_cursor: by querying this view, you can find out which of the following causes multiple sub-cursors to be generated: no sub-cursor (excluding the first sub-cursor 0 ), why can't I share a created sub-cursor. For each incompatible type, the value is N (not caused by this type)/Y (is caused by this Type mismatch) for the above example, A new sub-cursor is generated because of different optimizer modes: SQL> select child_number, optimizer_mode_mismatch from v $ SQL _shared_cursor where SQL _id = 'b2kjsg0h8rt08 '; CHILD_NUMBER OPTIMIZER_MODE_MISMATCH ------------ -------------------------------- 0 N 1 Y in awr SQL Statistics one item is SQL ordered by Version Count, which is the number of child game targets of the same parent cursor. When you use the variable binding program, you can query v $ SQL _shared_cursor to find that multiple child Curlers are generated because of bind_mismatch, which is related to variable classification. This article is from the guest Network Customer Alliance