flush single SQL plan on RAC [message #686214] |
Sat, 02 July 2022 06:50 |
wtolentino
Messages: 421 Registered: March 2005
|
Senior Member |
|
|
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.
|
|
|
Re: flush single SQL plan on RAC [message #686215 is a reply to message #686214] |
Sat, 02 July 2022 07:59 |
John Watson
Messages: 8962 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The gv$ views show you all the instances, the v$ views only the instance to which your session is attached. You need to include the inst_id column to see which instance(s) has the cursor.
|
|
|
Re: flush single SQL plan on RAC [message #686216 is a reply to message #686215] |
Sat, 02 July 2022 19:25 |
wtolentino
Messages: 421 Registered: March 2005
|
Senior Member |
|
|
thanks John. can you give an example of how can i include the inst_id to use on the DBMS_SHARED_POOL.PURGE?
i tried this:
exec sys.DBMS_SHARED_POOL.PURGE ('00000003248F6E58,1818374466@1','C');
and it did not work it appears to be that doesn't look like the passing parameter is correct.
|
|
|
|