SQL with multiple versions in the library cache Oracle
In Oracle, SQL statements are called as CURSORS. Every SQL Statement represents as Cursors. In Library Cache of SGA, we share SQL statement with other sessions for increase
share ability for reduce memory consumption. Two SQL statements are identical then they shared same cursors. If they are different in Casing or parameter length consider as different statement.
Note: IF the SQL Statement is identical then they shareable otherwise not.
The shared representation of a SQL cursor is stored in two parts: the Parent Cursor and the Child Cursor.
PARENT CURSOR: Share the identical cursor statement, it two sql statement present then one is child.
CHILD CURSOR: which is identical SQL Statement, but different in Bind variable length, name,type etc, any optimizer parameter changed, execution plan of cursor changed.
View V$SQLAREA maintains single row per parent cursor, whereas, V$SQL maintains each row for a Child Cursor.
The CHILD_ADDRESS column from V$SQL mapped with the CHILD_ADDRESS column of V$SQL_SHARED_CURSOR to get the reason for the mismatch between already stored child cursor.
check multiple version in oracle of sql query
-- Has parent and child both rows
select parsing_user_id, parsing_schema_id, sql_text, address, child_address from v$sql where address = '8EE280E8' ;
-- Maintain single row as parent cursor
select parsing_user_id, parsing_schema_id, sql_text, address, version_count from v$sqlarea where address = '8EE280E8' ;
-- check child with address column
select * from v$sql_shared_cursor where KGLHDPAR = '8EE280E8'; --child address column from above query in v$sql area
For more detail about
select address, child_address, CHILD_NUMBER , HASH_VALUE, PLAN_HASH_VALUE, PARSING_USER_ID, PARSING_SCHEMA_ID from v$sql where address = '8EE280E8' ;
If difference in bind variable length data type caused child cursor checked with V$SQL_BIND_METADATA.
select address, position, datatype, max_length, bind_name
where address in
(select child_address from v$sql where sql_id='abcdefghijkl');