Re: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)

  • From: "Ian Cary" <Ian.Cary@xxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 6 Jul 2004 11:25:41 +0100


If the plan stayed the same then the performance should also stay the same
(lets assume you haven't moved that database to a faster filesystem/box or
truncated the tables - a join of two empty tables always seems to go pretty
quickly whatever the plan :)),
so my guess would be be that the first query queried data that wasn't
cached and the second query re-queried this cached data.

On the subject of DBMS_STATS the advantages are fairly well documented
(e.g. monitor stale objects so only those that need statistics to be
gathered are gathered and gather statistics in parallel to name a couple)
but the fact that Oracle will be deprecating analyze (or at least the
C.B.O. stat gathering part) seems to be a pretty good reason for switching.

On the negative side however most  versions of DBMS_STATS seem to have
contained some unwanted "features" (are we allowed to used the B word?) -
These mainly relate to performance of the dbms_stats job itself rather than
the stats themselves these days so "you pays your money and you takes your

One last point - I have noticed recently that the stats gathered by
DBMS_STATS do seem to favour Nested Loops over Hash joins. As I mainly work
in datawarehousing environments where most queries seem to perform best
using Hash joins this can be a bit irritating - I suspect its something do
with a (false) assumption that the data will cached and its optimising LIO
rather than PIO but I'm guessing really.

 I'm actually currenltly looking at query that insists on doing NL whatever
stats I gather when a Hash Join is 4 times quicker. This seems to leave me
with the option of a USE_HASH which I'm relucatant to do or the interesting
"alter session set optimizer_index_cost_adj=150" - but then again I think I
might want to use some indexes occasionally :)



For the latest data on the economy and society 
consult National Statistics at

Please Note:  Incoming and outgoing email messages
are routinely monitored for compliance with our policy
on the use of electronic communications
Legal Disclaimer  :  Any views expressed by
the sender of this message are not necessarily
those of the Office for National Statistics

This email has been scanned by the MessageLabs Email Security System.
For more information please visit 
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: