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
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
