Good Plan Hash Value Not Showing in One of the RAC Node for Sqlid Even After Forcing Plan - SQL TUNING (Doc ID 2422536.1)

 

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

GOAL

Using "START coe_xfr_sql_profile.sql sqlid  plan hash value"" on 3 node RAC, but do not see the new plan hash value on one off the nodes.

Using this ""SELECT * FROM TABLE(dbms_xplan.display_cursor('xxxxxxxxxx')) "" to check the plan hash value.

 

SOLUTION

Purging the sql id in question is a generic way to  Since you were using the display_cursor command , it will show the plan which is in cursor.


Using DBMS_SHARED_POOL.PURGE procedure, you can flush a single SQL statement from the shared pool quite easily. The procedure call is overloaded to allow you to flush a named object using the address and hash_value found in V$SQLAREA. There are a couple of other call signatures that allow you to identify an object by schema and object name or hash value and namespace (to flush an object like a procedure or function) but the one we want for flushing a single SQL statement is the following:

DBMS_SHARED_POOL.PURGE
( name varchar2,
flag char default 'P',
heaps number deafult 1
)

If the SQL_ID of the statement is known, then can look up the cursor information to build the name parameter value using the following query:

SQL> select address, hash_value from gv$sqlarea where sql_id like 'XXXXXXXXX';

ADDRESS HASH_VALUE
---------------- ----------
00000000A9F34F98 1799024353

With the values, simply execute the call to the purge procedure:

SQL> exec dbms_shared_pool.purge('00000000A9F34F98, 1799024353','C'); >> C is for Cursor

PL/SQL procedure successfully completed.

Check the shared pool again after the purge successfully completes which should show no rows.

SQL> select address, hash_value from gv$sqlarea where sql_id like 'XXXXXXXXX';

no rows selected