Another very useful thread has developed on the oracle-l mailing list. On a side note, this is really _the_ list to be subscribed to-there are so many experienced Oracle DBAs out that it’s pure joy.

But I digress, back to the problem I often face when performance troubleshooting systems is to purge a SQL statement from the shared pool. Most often, bind variable peeking in conjunction with an empty partition (or otherwise inappropriate stats) cause the optimiser to choose a suboptimal plan for subsequent executions of the same SQL Statement.

Oracle 11.1 extended DBMS_SHARED_POOL to include a “purge” function which can be used to manage the shared pool. It’s actually a lot more powerful than just purging SQL statements but that’s for another blog entry :)

Prior to the availability of the new package I usually used the “comment on table <owner>.<table_name> is '<some comment>'” trick to force an invalidation of the statement in the cursor cache. The next time the statement is executed (after the statistics are updated/corrected!) the optimiser should pick the new (corrected & better) statistics up and produce a better execution plan. However, that approach should not be used. It’s forcing a re-parse of all cursors touching the table, with the potential to cause a lot of grief. I’m glad we have a better way of purging cursors from the shared pool now.

Kerry Osborne, a great source for inspiration has posted a great example for using DBMS_SHARED_POOL.purge(). The following is shamelessly taken from the mailing list, I won’t take any credit for it (but I’ll use it whenever I can!). The code has been updated on 251208 to enforce Oracle 19c and later and was tested inside an Oracle 21c Pluggable Database (PDB). It’s also been tested on Autonomous Database 19c and 26ai, and it worked with both.

Here’s the script I use (because I don’t like the funky “address,hash_value” format that the purge procedure requires:

--flush_sql.sql

set serveroutput on
accept v_sql_id prompt "enter the SQL ID to purge from the database: "

declare
    l_version    varchar2(50);
    l_compatible varchar2(50);
    l_name       varchar2(255);
begin
    -- sanity checking, the script requires oracle 19c or later.
    dbms_utility.db_version(
        l_version,
        l_compatible
    );

    if to_number ( regexp_substr(l_compatible, '[0-9]+') ) < 19 then
        raise_application_error(
            -20001,
            'this script requires *.compatible to be set to at least 19.x'
        );
    end if;

    begin
        select
            address
            || ','
            || hash_value
        into l_name
        from
            v$sqlarea
        where
            sql_id like '&v_sql_id';
    exception
        when no_data_found then
            raise_application_error(
               -20002,
               'no cursor found in the shared pool for sql id &v_sql_id'
            );
        raise;
    end;

    sys.dbms_shared_pool.purge(
        name  => l_name,
        flag  => 'c',
        heaps => 1
    );

    dbms_output.put_line('SQL ID ''&v_sql_id'' successfully purged');
end;
/

Credit where credit is due:

Kerry Osborne, Enkitec (at the time this blog post was written)