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