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');

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.