Invalidating a Plan

Background & Overview

This real life example looks invalidating plans for a given SQL statement.

In the past the easiest way to invalidate a SQL plan was to put a comment on an object being referenced by the SQL. For example, to invalidate the plan for select order_id from orders you could issue the following statement comment on table orders is 'test'. The problem with this method however, was that the comment invalidated all SQL statements that referenced the orders table. Clearly, not ideal!!

However, since 10.2.0.4 the package dbms_shared_pool has been enhanced to allow DBAs to purge individual statements. The following example shows how to achieve this.

Example

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> --Create a test table

SQL> create table plan_invalidation_test(col1 char(10));

Table created.

SQL> insert into plan_invalidation_test(col1) values('Test Data');

1 row created.

SQL> insert into plan_invalidation_test(col1) values('Test Data');

1 row created.

SQL> commit;

Commit complete.

SQL> --Run a SQL statement against the table

SQL> select * from plan_invalidation_test;

COL1
----------
Test Data
Test Data

SQL> --Find the SQL ID

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_text like '%plan_invalidation_test%';

SQL_TEXT                                                                              SQL_ID        LOADED_VERSIONS OPEN_VERSIONS
------------------------------------------------------------------------------------- ------------- --------------- -------------
insert into plan_invalidation_test(col1) values(:"SYS_B_0")                           51m9m5tm0qh0k               1             0
select * from plan_invalidation_test                                                  9hf530kxgatsz               1             0

SQL> --Review v$sql and v$sqlarea

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_id='9hf530kxgatsz';

SQL_TEXT                                                                              SQL_ID        LOADED_VERSIONS OPEN_VERSIONS
------------------------------------------------------------------------------------- ------------- --------------- -------------
select * from plan_invalidation_test                                                  9hf530kxgatsz               1             0

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='9hf530kxgatsz';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
0000000A4F2F6298 3136644895          1          1             1             0           1

SQL> --Purge the SQL from the shared pool

SQL> exec dbms_shared_pool.purge('0000000A4F2F6298,3136644895','Z');

PL/SQL procedure successfully completed.

SQL> --Note in the purge command above the 'Z' parameter.
SQL> --In 11.2, the second parameter can be set to any value apart from P, p, Q, q, R, r, T or t.

SQL> --Review v$SQL and V$sqlarea to see that the statement has been purged.

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_id='9hf530kxgatsz';

no rows selected

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='9hf530kxgatsz';

no rows selected

SQL> --Select the data again.

SQL> select * from plan_invalidation_test;

COL1
----------
Test Data
Test Data

SQL> --Review v$SQL and V$sqlarea again to see that the statement the statement is back in the cache.

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_id='9hf530kxgatsz';

SQL_TEXT                                                                              SQL_ID        LOADED_VERSIONS OPEN_VERSIONS
------------------------------------------------------------------------------------- ------------- --------------- -------------
select * from plan_invalidation_test                                                  9hf530kxgatsz               1             0

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='9hf530kxgatsz';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
0000000A4F2F6298 3136644895          1          2             1             1           1

SQL> --Purge and re-run the select a few times changing the second parameter from Z to L.

SQL> exec dbms_shared_pool.purge('0000000A4F2F6298,3136644895','L');

PL/SQL procedure successfully completed.

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_id='9hf530kxgatsz';

no rows selected

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='9hf530kxgatsz';

no rows selected

SQL> select * from plan_invalidation_test;

COL1
----------
Test Data
Test Data

SQL> select SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS from v$sql where sql_id='9hf530kxgatsz';

SQL_TEXT                                                                              SQL_ID        LOADED_VERSIONS OPEN_VERSIONS
------------------------------------------------------------------------------------- ------------- --------------- -------------
select * from plan_invalidation_test                                                  9hf530kxgatsz               1             0

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_id='9hf530kxgatsz';

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS
---------------- ---------- ---------- ---------- ------------- ------------- -----------
0000000A4F2F6298 3136644895          1          3             1             2           1

SQL> --Notice the loads and the invalidations have increased.

SQL> --Tidy-up.

SQL> drop table plan_invalidation_test;

Table dropped.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License