Re: testing sql and perf

From: David Green <thump_at_cosmiccooler.org>
Date: Mon, 3 May 2004 11:27:44 -0500 (CDT)
Message-ID: <56678.127.0.0.1.1083601664.squirrel@www.cosmiccooler.org>

> David,
>
> By output differences, do you mean time and stats related to
> logical i/o and physical i/o? Or do you mean execution plans,
> row counts, etc.

Elapsed: 00:05:22.29 versus subsequent runs taking 9 and 10 seconds First Run
Statistics

       1818  recursive calls
          4  db block gets
     718045  consistent gets
      68575  physical reads
          0  redo size
      12003  bytes sent via SQL*Net to client
       1466  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         24  sorts (memory)
          0  sorts (disk)
         16  rows processed

Subsequent runs with altered join conditions. Statistics

          0  recursive calls
          0  db block gets
     717503  consistent gets
       5479  physical reads
          0  redo size
      12003  bytes sent via SQL*Net to client
       1470  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

Statistics

          0  recursive calls
          0  db block gets
     717503  consistent gets
          0  physical reads
          0  redo size
      12003  bytes sent via SQL*Net to client
       1466  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

> In the first case, you can flush the buffer cache if you want.
> By flushing the cache between executions, your stats will be
> close to worst-case, but not quite as the blocks are probably
> going to be read from the storage cache and not disk on
> subsequent operations. If the sql is to be reexecuted by the
> application/users, the blocks needed may be still in the
> database buffer cache.

Unless I am mistaken the flush shared_pool only flushes the shared pool and not the buffer caches. The have Xylogic, so there is no storage cache in the equation to speak of. I did try flush shared pool and get a 9 second execution time implying all data was in the buffer cache.

I am on 8174.
I can't take tablespace offline as other are accessing. How do developers perform the same without the ability to offline tbs and even flush caches, etc.

The row count are the same, but type of join changes per adding (+) on different joins. I'm referring strictly to going by execution time and tkproff output for the sake of this discussion. I also use explain plan but sometimes you can't just look at plan, and tell the best one. - David

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 Mon May 03 2004 - 11:26:06 CDT