method to remove the execution plan for the specified SQL from the shared pool

If the Oracle optimizer produces some kind of wrong execution plan, or if we want Oracle to re-parse a SQL, then it needs the SQL execution plan to expire in the shared pool, and the simple method will appear after 10.2.0.4.   for previous versions, the most obvious way is to flush the shared pool directly, but if there is a problem with the vast majority of SQL in the database, then this approach is understandable or the quickest way to do it, and if the database is just an individual SQL problem, Then this approach is too violent.  SQL> Select COUNT (*) from Dual; count (*)----------1 sql> Select sql_id, Address, Hash_value, Executions, loads, parse_calls, invalidations2 from v$sqlarea3 where Sql_text = ' SELECT count (*) from dual ';  sql_id A ddress hash_value executions LOADS parse_calls invalidations------------------------------------------------------- ----------------------------4m94ckmu16f9k 00000000b6c61fc0 4094900530 1 1 1 0 sql> Select COUNT (*) from V$sqlare A; count (*)----------3061 sql> alter system flush Shared_pool; system altered. sql> Select sql_id, address, hash_value, executions, loads, parse_calls, invalidations2 from v$sqlarea3 where Sql_text = ' SELECT Count (*) from dual ';  no rows selected sql> Select COUNT (*) from V$sqlarea;  count (*)----------37 emptying the entire shared pool for one SQL is really too expensive, and for a busy OLTP system, the risks and consequences of this refresh of the shared pool are compared to the direct shutdown of the database. There's no big difference.   So there is no fine-grained way, in fact, there are many methods, any DDL on the related table will lead to the failure of SQL execution plan, but the DDL itself risk is high, if you want to minimize the system impact, then this DDL is not grant. You only need the current user to grant this table permissions to themselves, you can achieve the desired effect: sql> select COUNT (*) from Dual; count (*)----------1 sql> Select sql_id, address, hash_value, executions, loads, parse_calls, invalidations2 from v$sqlarea3 where sql_text = ' Selec T Count (*) from dual ';  sql_id ADDRESS hash_value executions LOADS parse_calls invalidations----------------------- ------------------------------------------------------------4m94ckmu16f9k 00000000b6c61fc0 4094900530 1 1 1 0 s Ql> Select 1 from dual; 1----------1 sql> select * from dual; d-x sql> select ' A ' from dual;& nbsp; ' -a sql> Select COUNT (1) from Dual; count (1)----------1 sql> Select sql_id, Address, Hash_value, Executions, loads, parse_calls, invalidations2 from v$sqlarea 3 where lower (sql_text) like '%dual% ';  sql_id ADDRESS hash_value executions LOADS parse_calls Invalidations-----------------------------------------------------------------------------------GR7S3J0CG8PR6 00000000b6a5a470 418666214 1 1 1 040p7rprfbt1as 00000000b69bdc38 3703342424 1 1 1 0520mkxqpf15q8 00000000B6DD9610 28668453 1 1 1 0ak90gdq0udv37 00000000b6e3c6b0 2175200359 2 2 2 14m94ckmu16f9k 00000000b6c61fc0 4094900530 1 1 1 0a5ks9fhw2v9s1 00000000b698da88 942515969 1 1 1 0800hwktjz3zuc 00000000b6999268 1676803916 1 1 1 0 7 rows selected. sql> gra NT Select on dual-sys;grant select on dual-sys*error at line 1:ora-01749:you could not grant/revoke privileges To/fro M yourself sql> Grant Select on dual to Public; grant succeeded. sql> select sql_id, Address, Hash_va Lue, executions, loads, parse_calls, invalidations2 from V$sqlarea3 where lower (sql_text) like '%dual% ';  sql_id ADDR ESS hash_value executions LOADS parse_calls InvalidatiONS-----------------------------------------------------------------------------------GR7S3J0CG8PR6 00000000b6a5a470 418666214 2 1 2 0ak90gdq0udv37 00000000b6e3c6b0 2175200359 2 2 2 1  for other users, you can use the query permissions of the table to authorize the method, but the test user itself is sys, so another user authorization is required and the way to grant public is used for convenience. As can be seen, this approach can also be effective, but there is still a problem of too big a hit. For a frequently accessed table in the system, it is possible that this authorization operation, resulting in less than dozens of, more than hundreds of SQL is invalid, the risk is still not underestimated.   So is there a way to control the granularity on the SQL itself? In 11g, Oracle's Dbms_shared_pool package adds purge capabilities to solve this problem perfectly, This method is also added in the 10.2.0.4 and 10.2.0.5 patch set, so that the 10.2 high version can also implement this function, the use of the method is very simple: sql> select COUNT (*) from Dual; count ( *)----------1 sql> Select sql_id, address, hash_value, executions, loads, parse_calls, invalidations2 from v$ Sqlarea3 Where Sql_text = ' SELECT count (*) from dual ';  sql_id ADDRESS hash_value executions LOADS parse_calls INVALID ations-----------------------------------------------------------------------------------4m94ckmu16f9k 00000000B6C61FC0 4094900530 1 2 1 1 sql> Select 1 from dual; 1----------1&nbsP Sql> SELECT * from Dual; d-x sql> Select sql_id, address, hash_value, executions, loads, parse_calls, Inva Lidations2 from V$sqlarea3 where lower (sql_text) like '%dual% ';  sql_id ADDRESS hash_value executions LOADS parse_cal LS invalidations----------------------------------------------------------------------------------- GR7S3J0CG8PR6 00000000b6a5a470 418666214 3 1 3 0520mkxqpf15q8 00000000b6dd9610 2866845384 1 2 1 1ak90gdq0udv37 00000000B6E 3c6b0 2175200359 3 2 3 14m94ckmu16f9k 00000000b6c61fc0 4094900530 1 2 1 1a5ks9fhw2v9s1 00000000b698da88 942515969 1 2 1 1& nbsp sql> exec dbms_shared_pool.purge (' 00000000b6c61fc0,4094900530 ', ' C ')  pl/sql procedure successfully completed . sql> Select sql_id, address, hash_value, executions, loads, parse_calls, invalidations2 from V$sqlarea3 where lo Wer (Sql_text) like '%dual% ';  sql_id ADDRESS hash_value executions LOADS parse_calls invalidations---------------- ------------- ---------- ---------- ----------------------------------gr7s3j0cg8pr6 00000000b6a5a470 418666214 4 1 4 0520mkxqpf15q8 00000000b6dd9610 2866845384 1 2 1 1ak90gdq0udv37 00000000b6e3c6b0 2175200359 3 2 3 1a5ks9fhw2v9s1 00000000b698da88 942515969 1 2 1 1  The first parameter of a process purge is v$ Sqlarea the value of the Address column and the Hash_value column, separated by commas, the second argument ' C ' indicates that the purge object is the cursor, but in fact it can be used in addition to P (procedure/function/package), Any value of T (TYPE), R (TRIGGER), and Q (SEQUENCE)  www.2cto.com    Using this method, you can precisely delete a SQL from the shared pool. This allows Oracle to regenerate the execution plan for this SQL. This method only targets a single SQL statement, which makes it possible to solve the problem without causing any accidental injury.   However, it is important to note that in 10.2.0.4, although the purge process already exists, you must set a event: sql> alter system set event = ' 5614566 to make the process truly effective. Trace name Context forever ' scope = Spfile; system altered.  A restart is required after setting the event, Dbms_shared_pool purge can take effect. In other words, unless set up in advance, this purge feature must be in the 10.2.0.5 version for a product environment.

Method to remove the execution plan for the specified SQL from the shared pool