Check the SQL having high version count in Oracle

 

SQL query having high version count in Oracle

Check the highest version count queries

-- Fetch top 10 queries causing high version count
select * from (
select  sql_text,
        version_count,
        executions,
        address
from    v$sqlarea 
order by version_count desc) where rownum <= 10;

Check the cursor shared view and reason to describe type of mismatch

select  reason
from    v$sql_shared_cursor
where   address in (
        select address from (
select address
from    v$sqlarea 
order by version_count desc) where rownum <= 10 );

Query to check excessive child cursors:

select a.cursors, a.sql_id,b.sql_text
from
(
select count(*) as cursors, ssc.sql_id
from v$sql_shared_cursor ssc
group by ssc.sql_id
order by cursors desc
) a,
(
select sa.sql_id, sa.sql_text from v$sqlarea sa
) b
where a.sql_id=b.sql_id;

Script for Purge the version count statement
Script will purge from shared pool if the version count is greater than 100. you can change the value according to your need.

set serveroutput on
DECLARE
V_SQL_ADDRESS VARCHAR2(100) := '';
BEGIN
For x_cur in (select sql_id,address,hash_Value,version_count from v$sqlarea where version_count >= 100 order by version_count desc)
loop
v_sql_address := ''''||x_cur.address||','||x_cur.hash_value||'''';
dbms_output.put_line(x_cur.sql_id||','||v_sql_address||','||x_cur.version_count);
sys.dbms_shared_pool.purge (''||x_cur.address||','||x_cur.hash_value||'','C');
end loop;
END;
/

Note:
Use dbms_shared pool package for purge from shared pool.

dbms_shared_pool.purge('ADDRESS,HASH_VALUE','C'); 
example:
EXEC sys.DBMS_SHARED_POOL.purge('000000010182AE70,1862304678', 'C');