Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_STATS.DELETE_INDEX_STATS does _not_ delete index statistics ??
"mommy dba" <mommydba_at_netscape.com> schrieb im Newsbeitrag
news:vptiqas9nonr3e_at_corp.supernews.com...
> Have you tried removing the statistics on the table as well?
No, because the table is vital for the app, it has (too) many columns and 10
indexes,
and many parts of the app are basing on it.
The ERM maybe suboptimal, but cannot be changed without heavy side effects
(but of course my fingers tickle to do so ..)
to the rest of the app (Forms and Reports).
> This may
> make a difference. I would try the following: Run the statistics on
> the table (and indexes)
The entire schema is analyzed regularly.
>
> Set the session into CBO and run an explain plan.
System Init parameter is set to CHOOSE.
>Now do the same thing
> in Rule base.
I tried /*+rule*/ ... but to no avail.
>In theory (don't you like that....?) You should see some
> significant differences.
I did, and the best result I got was when the index had no statistics - the
rest of course was analyzed.
Strange ...
>It may be that the query is not very efficient
> and may need "tweaking." CBO is not as intelligent as we may like it to
> be.
q.e.d.
>At times, you may actually need to remove the statistics off of a
> table in order to get it to perform better. Welcome to the world of
> Oracle :-)
>
OK, it is not a science, it is an art :-)
Thanks for the comments, I was just curious about
DBMS_STATS.DELETE_INDEX_STATS ...
The index tuning is another question ...
>
>
> Jan Gelbrich wrote:
...
Received on Wed Oct 29 2003 - 02:12:34 CST