Re: Slow SQL performance

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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] 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.
>

--
http://www.freelists.org/webpage/oracle-l