Oracle-L: Query comparison statistics

From: Biddell, Ian <ian.biddell_at_hp.com>
Date: Wed, 30 Apr 2003 16:41:37 -0800
Message-ID: <F001.0058D1EB.20030430164137@fatcity.com>

Hi All,

I have two queries that basically get me the same result in the end but have totally different statistics, so I thought I would ask the list on what they thought of the differences and why I should go one over the other.
The test queries only return one row as I was doing a count(*) from the actual query as I didn't want millions of rows returned to the screen. I used set timing on & set autotrace on statistics.

real: 30245848

Statistics

          0  recursive calls
          3  db block gets

251310017 consistent gets
5515078 physical reads

          0  redo size
        188  bytes sent via SQL*Net to client
       1626  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

real: 7296246

Statistics

     233016 recursive calls
   17475900 db block gets
    3569377 consistent gets
    4093680 physical reads
    4444391 redo size

        188  bytes sent via SQL*Net to client
       1259  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          4  sorts (disk)
          1  rows processed

Thanks for your feedback :-)

Ian Biddell

Oracle DBA

NonStop Development and Integration

HP Services

--

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

Author: Biddell, Ian
INET: ian.biddell_at_hp.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 Wed Apr 30 2003 - 19:41:37 CDT