Re: Slow SQL performance
Date: Tue, 22 Apr 2008 11:32:33 -0700 (PDT)
Bug 5614566 says event 5614566 has to be set in 10.2.0.4. Indeed, I have to run
alter session set events '5614566 trace name context forever';
before calling dbms_shared_pool.purge. (It doesn't matter whether there's a space after the hash value in the first argument.)
11g doesn't need this event.
- Yong Huang <yong321_at_yahoo.com> wrote:
> I've been waiting for this single cursor invalidation procedure for years!
> Thanks for this info.
> About the auto no_invalidate, you can check your current setting with
> SQL> set serverout on
> SQL> begin if (DBMS_STATS.AUTO_INVALIDATE) then dbms_output.put_line('True');
> else dbms_output.put_line('False'); end if; end;
> 2 /
> PL/SQL procedure successfully completed.
> This is a 10.2.0.1 toy database with no real work, with minimum manual
> of parameters.
> Yong Huang
> > Date: Tue, 22 Apr 2008 09:39:14 +0900
> > From: "=?EUC-KR?B?wba1v7/t?=" <ukja.dion_at_gmail.com>
> > Subject: Re: Slow SQL performance
> > Congratulations.
> > "no_invalidate=>auto" is actually good one except the fact that you don't
> > know when your SQL is invalidated.
> > But at least within 5 hours. right?
> > The maximum delay is controllable by "_optimizer_invalidation_period"
> > parameter. The default value is 180000(s) = 5 hour.
> > Anyway, as of 10.2.0.4, Oracle provides a way of purge one specific cursor
> > from shared pool. And patch for 10.2.0.2 and 10.2.0.3 is also available.
> > What a news.
> > Visit metalink doc# 457309.1
> > Dion Cho
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ Received on Tue Apr 22 2008 - 13:32:33 CDT