Oracle-L: Re: Query comparison statistics

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Thu, 01 May 2003 01:16:38 -0800
Message-ID: <F001.0058D3DF.20030501011638@fatcity.com>

Ian,

Always go for the fastest, if the plan doesnt't point to a likely degradation of performance over time, as tables grow ... Very often, although not absolutely always, it also means the smaller (consistent gets + db block gets) (as is the case here). Don't get obsessed with statistics, we are not in the business of statistics, we are in the business of ensuring that the data users need is correct and returned to them within an acceptable time frame.

Beware, though, of the cache effect. It may be that the first query takes longer to execute because it has to load things in memory. However, since PIOs are in the same order of magnitude, I dont't think that you have to worry much here.
For your information, it's probably better to use

set autotrace on traceonly
if you don't want to see the rows (which are nevertheless fetched with the traceonly) than replacing the select list with a COUNT(*). As the optimizer becomes smarter, it may induces changes to the execution plan (some accesses to a table may become unnecessary, for instance).

All this being said, both execution times and logical I/Os are monstruous values. Are you sure you cannot do better? Compare the number of blocks you are getting through to the number of blocks of tables and indexes involved in the query, it may help you decide whether it is reasonable to widely of the mark.

--

Regards,

Stephane Faroult
Oriole Software
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stephane Faroult
INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 01 2003 - 04:16:38 CDT