To explain how this can happen: When a SQLstatement is executed, we find the matching text in the shared pool, if itexists. Then we try to reexecute an existing child cursor if possible. If allof the already existing children for that parent cursor cannot be reused (i.e.cannot be shared), Oracle needs to hard parse and thereby creates another childcursor. If we continue not-sharing existing child cursors, at the point when aparent has 1024 child cursors, the next time a user attempts to execute thatSQL text and there is a failure to share any of these cursors, the parent with1024 cursors is marked as obsolete, and a new parent is created.
sql_address
sql_hash_value
V$SQLTEXT
V$SQLTEXT
displays the text of SQL statements belonging to shared SQL cursors in the SGA.
Column | Datatype | Description |
---|---|---|
ADDRESS |
RAW(4 | 8) |
Used with HASH_VALUE to uniquely identify a cached cursor |
HASH_VALUE |
NUMBER |
Used with ADDRESS to uniquely identify a cached cursor |
SQL_ID |
VARCHAR2(13) |
SQL identifier of a cached cursor |
COMMAND_TYPE |
NUMBER |
Code for the type of SQL statement (SELECT , INSERT , and so on) |
PIECE |
NUMBER |
Number used to order the pieces of SQL text |
SQL_TEXT |
VARCHAR2(64) |
A column containing one piece of the SQL text |
V$SQL
V$SQL
lists statistics on shared SQL areas without the GROUP BY
clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL
are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
Column | Datatype | Description |
---|---|---|
SQL_TEXT |
VARCHAR2(1000) |
First thousand characters of the SQL text for the current cursor |
SQL_FULLTEXT |
CLOB |
Full text for the SQL statement exposed as a CLOB column. The full text of a SQL statement can be retrieved using this column instead of joining with the V$SQLTEXT dynamic performance view. |
SQL_ID |
VARCHAR2(13) |
SQL identifier of the parent cursor in the library cache |
SHARABLE_MEM |
NUMBER |
Amount of shared memory used by the child cursor (in bytes) |
PERSISTENT_MEM |
NUMBER |
Fixed amount of memory used for the lifetime of the child cursor (in bytes) |
RUNTIME_MEM |
NUMBER |
Fixed amount of memory required during the execution of the child cursor |
SORTS |
NUMBER |