Flushing a Bad SQL Plan from Shared Pool

Introduction
Flushing the Shared Pool
Flushing a Single Bad SQL Plan

Introduction

In Oracle databases, the shared pool is a critical component where SQL and PL/SQL code reside for efficient reuse. While flushing the entire shared pool is a common practice, there are scenarios where you may need to invalidate or flush out a specific SQL statement's execution plan from the shared pool. This ensures subsequent executions undergo a hard parse, potentially resolving performance issues related to stale or suboptimal execution plans. Oracle 11g introduced a solution to this specific problem: the PURGE procedure in the DBMS_SHARED_POOL package.

Flushing the Shared Pool

To clear the entire shared pool, execute the following command as a privileged user:

ALTER SYSTEM FLUSH SHARED_POOL;
This action is quite aggressive as it discards all parsed SQL statements. Consequently, the database will need to re-populate the shared pool with commonly used statements, requiring significant system resources and potentially impacting performance. It's advisable to exercise caution and avoid this operation whenever possible.

Flushing a Single Bad SQL Plan

Find ADDRESS AND HASH_VALUE OF SQL_ID

To flush a single SQL statement from the object library cache, you first need to identify the address of the handle and the hash value of the cursor associated with that statement. This information can be obtained from the V$SQLAREA view.
Here's an example:

SQL> SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID = '3x8fmwtfybtsr';

ADDRESS          HASH_VALUE
---------------- ----------
00000000637A5368 1575347991

PURGE the PLAN from SHARED POOL

Once you have the address and hash value, you can use the PURGE procedure to flush out the specific SQL statement. Here's how:

SQL> 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.

Validate


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = '3x8fmwtfybtsr';

no rows selected


Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access