Re: Slow SQL performance
Date: Mon, 21 Apr 2008 15:08:51 -0800
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'
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.
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:
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Oxnard Montalvo
> assuming using dbms_stats the "no_invalidate" option defaults to TRUE
> in 10 but FALSE in 9.
> 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.