Re: Slow SQL performance
- From: Maureen English <sxmte@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- 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'
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:
