OCP Blog
Super DBA
Flush Single SQL statement from shared pool
by vazha mantua Monday, September 16, 2013 4:04 PM

Sometime on database we observed SQL statements which change SQL_Plans, cause of this may be various situation, for example recalculation  table statistics, or bind mismatch, etc. Sometime we simple want to delete bad SQL_Plan from shared pool, of course we can do it with flushing whole shared pool, but the best way is flushing single SQL statement.

First of all we should find address and hash value of statement, with this command:

select address||','||hash_value
  from v$sqlarea
  where sql_id like '1u1hcw5c5t8r3';

after that, we copy results to argument to procedure sys.dbms_shared_pool.purge

Example

begin
   sys.dbms_shared_pool. purge('00000004FA8BA948,1482466019','C',1);
end;  

That’s all.

Tags:

Comments (2) -

9/17/2013 8:32:31 PM #

gochu

კარგი პოსტია "ადმინა", საღოლ Smile

gochu Georgia

9/18/2013 2:08:43 AM #

Brian Carignan

In a RAC environment, don't forget to run this for each instance.  You could get all the values using gv$sqlarea instead of v$sqlarea.

-BC

Brian Carignan United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  December 2017  >>
MoTuWeThFrSaSu
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar

TextBox