Home » RDBMS Server » Server Administration » flush single SQL plan on RAC (Oracle database 19c, 19.14.0.0.0, RedHatEnterpriseServer 6.10)
flush single SQL plan on RAC [message #686214] Sat, 02 July 2022 06:50 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: flush single SQL plan on RAC [message #686217 is a reply to message #686216] Sun, 03 July 2022 01:10 Go to previous message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
You need to run the command while logged on to the correct instance.
Previous Topic: external table how to check if currently open
Next Topic: JavaVM invalid
Goto Forum:
  


Current Time: Sat Dec 21 19:18:04 CST 2024