Checking for high version counts

"Versions" of a statement occur where the SQL is character for character identical but the underlying objects or binds or etc.. are different.
For example:
User scott has a dept table:
SQL>select * from dept
User Tom has his own dept table:
SQL>select * from dept

When Scott and Tom executes the above same SQL in terms of characters, because the base object "dept" is different, Oracle will treat the two statements as two different versions of SQL.

SELECT address, 
hash_value,
version_count,
users_opening,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10;

SQL Invalidation

Use the following query to identify cursors that are invalidated:
*****************************
set lines 300
col sql format a50
select substr(sql_text, 1, 40) "SQL", sql_id, invalidations 
from v$sqlarea 
where invalidations > &value 
order by invalidations;

Note: use the "&value" to dynamically enter a value to limit the output

Or use the following SQL to observer invalidations:

select sql_text,version_count,loads,invalidations,parse_calls
from v$sqlarea 

where sql_text like '%xxxxx%';

*****************************
What will cause cursor invalidation?
Activities like TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on unerlying objects.  The associated cursors will stay in the SQL area but when it will be reference next time, it should be reloaded and reparsed fully, so it will have negative impact on library cache.
****************************
Related metalink notes

1. [ID 123214.1]: Truncate - Causes Invalidations in the LIBRARY CACHE 
2. WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK [ID 115656.1]

Query shows SQL in the SGA where there are a large number of similar statements

The following query shows SQL in the SGA where there are a large number of similar statements:

SELECT substr(sql_text,1,40) "SQL",count(*),sum(executions) "TotalExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

Note: If there is latch contention for the library cache latches the above statement may cause yet further contention problems.

v$sql vs. v$sqlarea, v$sql_shared_cursor and Cursor Sharing

V$SQL vs. V$SQLAREA
Each SQL statement has 1 parent cursor, 1 or more child cursors.

V$SQLAREA: parent cursor, it contains one row for each parent cursor.

V$SQL: each parent cursor can have one or more child cursors.

-- Compared to parent cursor, child cursors have heap 6 (execution plan).

-- Each parent requires at lest 1 child cursor.

-- Multiple parent cursors are created because of differences in sql statement text.
The following statements all require separate parent cursors:
1 SELECT COUNT(*) FROM tab1;
2 select count(*) from TAB1;
3 SELECT /*comment*/ COUNT(*) FROM tab1;
4 SELECT COUNT(*) FROM tab1;


Cursor Sharing: EXACT, FORCE and SIMILAR
-- If the dynamic initialization parameter cursor_sharing is set to "EXACT", this cursor sharing feature is disabled.  If it's set to "FORCE" or "SIMILAR", the feature is enabled.


-- This parameter can be set at session or system level.


-- You can explicitly disable cursor sharing at the SQL statement level by specifying the hint "cursor_sharing_exact".


--Use "SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE '.....%' to check if two sql statements are the same: use same parent and child cursor. 
--The following two similar sql statements will have different results when running the above query to check parent and child cursors, when the cursor sharing paramenter is set to different values: EXACT and FORCE.
select col1 from tab1 where col1=0;
select col1 from tab1 where col1=1;


cursor_sharing=exact: 1 parent cursor with 2 child cursors


cursor_sharing=force:  1 parent cursor with 1 child cursor.  That means Oracle treats the above two sql statements as the same statement. 


cursor_sharing=similar:
The histogram factor on the column statistics will have different impacts on the parent and child cursors. 
Without histogram, Oracle will treat the following 4 sql statements as one statement: 1 parent cursor vs. 1 child cursor:
select col1 from tab1 where col1=0;

select col1 from tab1 where col1=1;

select col1 from tab1 where col1=2;

select col1 from tab1 where col1=3;
****************************************************************
cursor_sharing=force
Problems: a single child cursor is used for all SQL statements sharing the same text after the replacement of the literals.
"Consequently, the literals are peeked only during the generation of the execution plan related to the first submitted SQL statement. Naturally, this could lead to suboptimal execution plans because literals used in subsequent SQL statements will lead to different execution plans" - Christian Antognini's book (Troubleshooting Oracle Performance).
****************************************************************

========================================================================
According to Christian Antognini's book (Troubleshooting Oracle Performance):
"if an application uses literals and cursor sharing is set to similar, the behavior depends on the existence of relevant histograms. If they do exists, similar behaves like exact.  If they don't exist, similar behaves like force.  This means that if you are facing parsing problems, more often than not, it is pointless to use similar."
=================================================================================


With histogram, the above 4 sql statements will have 2 parent cursors and 3 child cursors.  


V$SQL_SHARED_CURSOR
--describes each loaed child cursor
--contains set of boolean values describing why cursors could not be shared.
--boolean values for 1st child of each parent will always be false.


SQL> select address, child_address, sql_text from v$sql where sql_text like '.....%';
SQL> select child_number, child_address, optimizer_mode_mismatch from v$sql_shared_cursor where address='xxxxxx';