Re: How to find the Overall cost of a query?

Howdo Dean,

Switch on AUTOTRACE in SQLplus (SET AUTOTRACE ON). I'll give you an example below:

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM DUAL; D
-
X

Execution Plan

   0 SELECT STATEMENT Optimizer=RULE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

          0  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        363  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

In the stats output above, check the "Consistent Gets" measure. Each consistent get represents a latch on a data structure in the buffer cache. In plain English, this means that if you can reduce the number of consistent gets, your query will run faster, and be less likely to cause any contention in the buffer cach.

The "Physical Reads" stat is a funny one, as queries will run faster when this is lower as it represents Disk IO. However, all this means in reality is that the data blocks your query needs to read are not in the buffer cache, and Oracle must therefore fetch them from disk. Whether or not the blocks you need are in the buffer cache is not really something you have control of under normal circumstances, unless you've already been running lots of queries against the table concerned on a very regular basis.

If anyone reading this disagrees or thinks I've garbled any of this, please don't hesitate to shoot me down in flames. ;o)

In terms of query timing, you can use the SET TIMING ON command in SQLplus before you run your query. If running from PL/SQL, use the DBMS_UTILITY.GET_TIME function.

I don't think you need to resort to a stopwatch. ;o)

Hope this helps.

James Received on Tue Dec 20 2005 - 09:45:50 CST