Re: analyze table versus DBMS_STATS.GATHER_TABLE

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Tue, 6 Jul 2004 11:25:41 +0100
Message-ID: <OF6D74AE5C.ECBC950D-ON80256EC9.00343D3E-80256EC9.0039465F@ons.gov.uk>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Tue Jul 06 2004 - 05:24:03 CDT

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