how to flush a plan that is on RAC? i tried this and it did not work.
get the address and hash value of the sql id
select ADDRESS, HASH_VALUE from gv$sqlarea where SQL_Id = '2pfky1pq64ca2';
then purge the plan
exec sys.DBMS_SHARED_POOL.PURGE ('00000003248F6E58,1818374466','C');
it throws an error:
Error starting at line : 7 in command -
BEGIN sys.DBMS_SHARED_POOL.PURGE ('00000003248F6E58,1818374466','C'); END;
Error report -
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 51
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 120
ORA-06512: at line 1
06570. 00000 - "shared pool object does not exist, cannot be pinned/purged"
*Cause: The specified shared pool shared cursor could not be found,
therefore it cannot be pinned/purged.
*Action: Make sure that a correct shared cursor name is given. Names
are a string of the form 'HHHHHHHH,SDDDDDDDDDD' where the H's
are an 8 digit hex number from the 'address' column of v$sqlarea,
and the D's are a 1 to 10 digit decimal number with an optional
leading sign (from the 'hash_value' column)
*Action: Remove the procedure from the calling stored procedure.
the plan does not exists on v$sqlarea but exists on gv$sqlarea. please advise.
thank you.