Re: stats on tables - analyze for all indexed columns
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Mar 2004 21:03:09 -0000
The analyze command comes in many flavours,
for example.
analyze table X compute statistics;
analyze table X compute statistics for table;
analyze table X compute statistics for table for indexes;
analyze table X compute statistics for table for indexes for indexed
columns;
As soon as you go past the first basic syntax,
(which internally is equivalent to 'for table for indexes')
you have to be precise about what you want
analyzed.
It's always a problem when you have to bite
the bullet and change something that doesn't
appear to need fixing. You might like to consider
the fact that analyze is deprecated, and use a future
move to 9i as the excuse for migrating to dbms_stats
and getting the correct statistics.
The only other point to consider is that 'for all indexed
columns' is often creating some histograms that are
not needed, and not creating some that could be useful.
But again, if it seems to work well enough, and you've
got a window that lets you generate the histograms,
why change ? (It might reduce the chances of a future
catastrophic performance hit, but that's usually too much
of a hypothetical for most people to lose sleep over).
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Fedock, John (KAM.RHQ)" <John.Fedock@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 04, 2004 7:27 PM
Subject: stats on tables - analyze for all indexed columns
Looking for some advice on gathering stats on my tables.
I analyze estimate statistics on most tables and analyze table for all
indexed columns on a few others (also known as Histograms). I then have a
job that goes through and copies num_rows, blocks, etc and stores this in
another table. I'm sure most shops have similar jobs set up like this.
I have noticed that when doing the histograms, no data is gathered at the
table level, only at the index level. I am reluctant to also include the
table, as things are running great right now. Any ideas .... or any harm
in gathering table stats AND histogram info.
We are using the CBO and running v 8.1.7.4 on HP-UX.
TIA.
John
John Fedock
"K" Line America, Inc.
www.kline.com
* john.fedock@xxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
