Re: Slow SQL performance

From: Yong Huang <>
Date: Tue, 22 Apr 2008 10:23:17 -0700 (PDT)
Message-ID: <>

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 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?=" <>
> 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, Oracle provides a way of purge one specific cursor
> from shared pool. And patch for and 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.;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Received on Tue Apr 22 2008 - 12:23:17 CDT

Original text of this message