Re: Slow SQL performance
Date: Tue, 22 Apr 2008 11:32:33 -0700 (PDT)
Message-ID: <729730.86591.qm@web80608.mail.mud.yahoo.com>
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
- 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 /
> False
>
> PL/SQL procedure successfully completed.
>
> This is a 10.2.0.1 toy database with no real work, with minimum manual
> setting
> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 22 2008 - 13:32:33 CDT