Flush sqlid from cache #oracle #sql

Flush sqlid from cache.md

If you already know the SQL_ID of the statement you want to purge, you can look up the cursor info you need to build the name parameter value using the following query:

select address, hash_value from v$sqlarea where sql_id like '97y7vc8x7jhmc';

ADDRESS          HASH_VALUE
---------------- ----------

00000000A9F34F98 1682024353

The 2nd parameter value is a flag. The flag defaults to 'P'. The valid values for flag are:

P name of a package/procedure/function T name of a type R name of a trigger

Q name of a sequence

Note that you don't see a value for a SQL cursor address specifically listed. If you're going to set the name value to a cursor address, the flag parameter should be set to any character except one of the 4 listed. So, I typically use 'C' (for cursor) or 'S' (for SQL) as the flag parameter value.

The final parameter is for the heaps/locations to be purged. The default of 1 means to purge the whole object so you'll always use the default for a single SQL statement flush.

Now that you have these values, you simply execute the call to the purge procedure:

exec dbms_shared_pool.purge('00000000A9F34F98, 1682024353','C');

PL/SQL procedure successfully completed.

If you check the shared pool again after the purge successfully completes, you'll find that the query returns no rows.

select address, hash_value from v$sqlarea where sql_id like '97y7vc8x7jhmc';

no rows selected

SPOOL ON flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/

PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;
SPOOL OFF;