How to Flush a Specific SQL Statement from the Oracle Database Shared Pool

YouTube video

Introduction

In this article, we will explore how to flush a single SQL statement from the shared pool of an Oracle database. Sometimes, we encounter issues with the execution plan of a particular query, and by flushing that specific statement, we can either force a new plan or tune the existing plan. We will walk through the steps to accomplish this task.

Step-by-Step Process

  1. Connect to the Oracle Database: First, establish a connection to the Oracle Database. Ensure you have the necessary privileges to perform the task.

  2. Query the V$SESSION View: Once connected, query the V$SESSION view to find the SQL ID of the specific statement that needs to be flushed. The SQL ID is a unique identifier for each SQL statement in the shared pool.

sql
SELECT sql_id FROM v$session;

Note down the SQL ID for the statement you would like to flush.

  1. Find the Hash Value: Next, you need to find the hash value of the query’s execution plan. To do this, query the V$SQLAREA view and retrieve the address and hash value for the given SQL ID.

sql
SELECT address, hash_value FROM v$sqlarea WHERE sql_id LIKE '<SQL_ID>';

Make a note of the address and hash value for later use.

  1. Flush the SQL Statement: Now, execute the following command to flush the specific SQL statement from the shared pool using the address and hash value obtained in the previous step.

sql
EXECUTE dbms_shared_pool.purge('<ADDRESS>', '<HASH_VALUE>', 'C');

This command will remove the execution plan of the specified SQL statement from the shared pool.

  1. Verify the Flush: To check if the flush was successful, query the V$SQL view again and search for the SQL ID.

sql
SELECT * FROM v$sql WHERE sql_id = '<SQL_ID>';

If no rows are returned, it means the plan for the SQL ID no longer exists in the shared pool.

  1. Rerun the Query: Once the flush is complete, you can rerun the query. The database will generate a new execution plan for the statement. You can also validate the new plan by checking the V$SQL view again.

sql
SELECT address, hash_value FROM v$sql WHERE sql_id = '<SQL_ID>';

Ensure that the execution plan has been regenerated.

Understanding the Process

When we encounter issues with the execution plan of a specific SQL statement, flushing it from the shared pool can be an effective solution. The shared pool in Oracle database stores shared SQL and PL/SQL areas, including the execution plans. By removing a particular statement’s execution plan from the shared pool, we can force the database to generate a new plan or tune the existing one.

By connecting to the database and querying the V$SESSION view, we can identify the SQL ID of the problematic statement. Then, by querying the V$SQLAREA view, we can retrieve the address and hash value of the execution plan corresponding to the SQL ID. These values are essential for the flushing process.

Using the DBMS_SHARED_POOL package’s PURGE function, we can execute the purge command by passing the address and hash value as parameters. Once executed, this command removes the execution plan for the specified SQL statement from the shared pool.

To verify the success of the flush, we can query the V$SQL view and look for the SQL ID. If no rows are returned, it means the execution plan no longer exists in the shared pool.

After flushing the statement, we can rerun the query. The database will generate a new execution plan for the statement. We can validate the new plan by querying the V$SQL view once more and ensuring that the execution plan has been regenerated.

Conclusion

Flushing a specific SQL statement from the shared pool of an Oracle database can be beneficial when encountering issues with the execution plan. By following the steps outlined in this article, you can successfully flush a problematic statement and force the database to generate a new execution plan. Ensure you have the necessary privileges and exercise caution when manipulating the shared pool.