SQL with multiple versions in the library cache Oracle

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.

V$SQL_SHARED_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
from v$sql_bind_metadata
where address in
(select child_address from v$sql where sql_id='abcdefghijkl');

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s