Re: Slow SQL performance
- From: "조동욱" <ukja.dion@xxxxxxxxx>
- To: sxmte@xxxxxxxxxxxxxxxx
- Date: Tue, 22 Apr 2008 09:39:14 +0900
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
2008/4/22, Maureen English <sxmte@xxxxxxxxxxxxxxxx>:
>
> THANK YOU to all who responded!
>
> In trying to make my question concise, I used the table that I thought was
> causing the problem. It turns out that there were many tables on which I
> had generated statistics using the 'FOR ALL INDEXED COLUMNS' clause. I
> got
> a list of all of the tables involved and regenerated the statistics on
> them
> using *both*:
>
> method_opt=>'FOR ALL COLUMNS SIZE 1'
>
> and
>
> no_invalidate=>FALSE
>
> What a difference it made! The query (Oracle Forms searching last names)
> went from taking about 5 minutes to taking about 5 seconds!
>
> The 'no_invalidate=>FALSE' was the answer to my question...although, I'm
> sure
> that the other suggestions regarding granting select on the table, or
> creating
> a comment on the table, would also have worked.
>
> Thanks again to all who responded.
>
> - Maureen
>
> Allen, Brandon wrote:
>
> > The default for no_invalidate in 10g (10.2.0.2 at least) is not TRUE, it
> > is DBMS_STATS.AUTO_INVALIDATE:
> >
> > http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.h
> > tm#i1036461
> >
> > Regards,
> > Brandon
> >
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Oxnard Montalvo
> >
> > <snip>
> > assuming using dbms_stats the "no_invalidate" option defaults to TRUE
> > in 10 but FALSE in 9. <snip>
> >
> > Privileged/Confidential Information may be contained in this message or
> > attachments hereto. Please advise immediately if you or your employer do not
> > consent to Internet email for messages of this kind. Opinions, conclusions
> > and other information in this message that do not relate to the official
> > business of this company shall be understood as neither given nor endorsed
> > by it.
> >
> > --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
