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

Raphael,

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
choice"

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 :)

Cheers,

Ian






For the latest data on the economy and society 
consult National Statistics at http://www.statistics.gov.uk

**********************************************************************
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 http://www.messagelabs.com/email 
______________________________________________________________________
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: