Steps to Purge SQL Plan from the Shared Pool

🧱 Steps to Purge SQL Plan from the Shared Pool

In some cases, after assigning an SQL profile for a particular SQL_ID, it may not be used as expected. This guide will help you ensure that the SQL profile is being used and, if necessary, purge the SQL plan from the shared pool to force the system to reoptimize and apply the assigned SQL profile.

---

🔧 Step-by-Step Instructions

1️⃣ Verify if the SQL Profile is Being Used

Before purging any SQL plan, you should first check if the assigned SQL profile is being used. Run the following query to check:

select distinct
    p.name sql_profile_name,
    s.sql_id
from
    dba_sql_profiles p,
    DBA_HIST_SQLSTAT s
where
    p.name = s.sql_profile
    and sql_id = '5dj81jnf8t03a';  -- Replace with your SQL_ID

If the profile is being used, the output will display the associated SQL profile name. Alternatively, you can use the DBMS_XPLAN function to check the SQL profile:

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('&sqlid'));

If the profile is being used, you will see a note like the following:

Note
-----
   - SQL profile SYS_SQLPROF_02630dc612e60001 used for this statement

2️⃣ Find the SQL Plan's Address and Hash Value

If the SQL profile is not being applied, the next step is to find the address and hash value of the SQL plan for your SQL_ID. Run the following query to retrieve this information:

SELECT ADDRESS, HASH_VALUE 
FROM V$SQLAREA 
WHERE SQL_ID = '5dj81jnf8t03a';  -- Replace with your SQL_ID

The output will provide the address and hash value, as shown below:

ADDRESS          HASH_VALUE
----------------  ----------
00000010B774F9D8  478969962

3️⃣ Purge the SQL Plan from the Shared Pool

Once you have the address and hash value, you can purge the SQL plan from the shared pool by executing the following command:

EXEC DBMS_SHARED_POOL.PURGE('00000010B774F9D8, 478969962', 'C');

In this command:

• '00000010B774F9D8' is the ADDRESS of the plan.
• '478969962' is the HASH_VALUE.
• 'C' specifies that the cursor (SQL plan) should be purged.

4️⃣ Verify if the SQL Plan is Flushed from Memory

After purging the plan, check again to see if it has been removed from memory by running this query:

SELECT ADDRESS, HASH_VALUE 
FROM V$SQLAREA 
WHERE SQL_ID = '5dj81jnf8t03a';  -- Replace with your SQL_ID

If no rows are returned, the plan has been successfully flushed out from memory.

5️⃣ Recheck if the SQL Profile is Now Being Used

Finally, go back to Step 1 and verify again if the SQL profile is now being applied. If the profile is being used, you have successfully ensured that the assigned SQL profile is applied to the SQL_ID.

---

🧠 Why Purge SQL Plans?

• Forces the database to reoptimize a query, applying the latest SQL profile and any optimizations.
• Helps in situations where the database isn't using the assigned profile as expected.
• Clears any plans that might have been stuck in memory, preventing performance issues.

🚨 Best Practices

• Only purge SQL plans when necessary to avoid unnecessary performance overhead.
• Ensure that the SQL profile is correctly created and assigned before purging the plan.
• Use DBMS_SHARED_POOL.PURGE carefully as it affects the shared pool memory.

---

🎯 This process helps ensure that your Oracle database is using the correct SQL profile and that the system is optimized for query performance.