In Oracle Database, the shared pool is a key component that stores SQL and PL/SQL code for efficient reuse. Over time, specific SQL plans may become inefficient or outdated. Flushing these bad SQL plans from the shared pool can help restore optimal performance. This article will show you how to flush a bad SQL plan from the shared pool using Oracle's DBMS_SHARED_POOL package.
DBMS_SHARED_POOL
Flushing the entire shared pool clears all cached SQL and PL/SQL code, forcing Oracle to recompile and reparse everything. This is a heavy operation that can cause a temporary performance hit.
To flush the entire shared pool, use the following command:
ALTER SYSTEM FLUSH SHARED_POOL;
This command discards all cached SQL plans and forces Oracle to rebuild the shared pool. Use this method only when absolutely necessary, as it can impact overall database performance.
If a specific SQL plan is causing performance issues, it's more efficient to flush just that plan rather than the entire shared pool.
First, find the SQL_ID, address, and hash value of the SQL plan you want to flush from the V$SQLAREA view. For example:
SQL_ID
V$SQLAREA
SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID = '3x8fmwtfybtsr';
This query returns the address and hash value needed to flush the specific SQL plan.
Once you have the address and hash value, use the DBMS_SHARED_POOL.PURGE procedure to flush the SQL plan:
DBMS_SHARED_POOL.PURGE
EXEC DBMS_SHARED_POOL.PURGE ('00000000637A5368, 1575347991', 'C');
In this scenario, 'C' stands for cursor and 'S' for SQL. When querying cursor information, 'C' is utilized. Conversely, 'S' pertains to SQL-related details. If operating within a RAC (Real Application Clusters) environment, it's prudent to adapt the query to employ GV$SQLAREA. This ensures comprehensive coverage, accounting for instances where the SQL statement of interest might have been executed on a different instance than the one currently accessed.
GV$SQLAREA
To confirm that the plan has been purged, rerun the query to check that the address and hash value are no longer present:
SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID = '3x8fmwtfybtsr'; -- No rows should be selected if the purge was successful
To minimize the need for manual flushing, consider the following best practices:
AWR
SQL Tuning Advisor
Flushing a bad SQL plan from shared pool is a powerful technique to resolve performance issues. However, it should be done selectively to avoid unnecessary impact on the database. By following the steps and best practices outlined in this guide, you can maintain optimal performance in your Oracle Database.