Execution time of statements

From: Martin Pfingstl <mpfingstl_at_lhsystemsas.de>
Date: Thu, 15 Jul 1999 16:10:46 +0200
Message-ID: <7mkq57$cfq@ar4dec01.ar4.fra.dlh.de>

Hello,

we have an Oracle 8.0.4 database (IBM AIX 4.2, 1GB RAM, SP2, Silver node, 2 SSA-Adapters, 2x20x4.5 GB) here which we want to tune. Now I thought about looking at v$sqlarea to find untuned SQL statements. As described in the book Oracle Performance Tuning I executed the script diskread.sql which looks as follows:

column "Average" format 999,999,999.99 column "Response" format 999,999,999.99 ttitle 'List Statements in Shared Pool with the Most Disk Reads' select sql_text, disk_reads ,

        executions ,
        disk_reads / decode(executions, 0,1,executions) "Average",
        ' Estimated Response = ',
        disk_reads / decode(executions, 0,1,executions) / 50 "Response"

  from v$sqlarea
 where disk_reads / decode(executions, 0,1,executions) > 200 order by disk_reads;

Now I had a look at the average number of disk reads and the estimated response time. This value was about 50 seconds, i.e. the average disk reads for
that statement was about 1000 (number of executions around 20 ==> disk_reads in v$sqlarea displayed something around 20000). But the real execution time of that statement was just about 2 seconds.

I know about buffer_gets as well, so I restarted the database (perhaps all values
were in the buffers, ánd that's the reason why it is so fast?), so all buffers
were empty and again, the statement only took about 2 seconds, and again, disk reads per statement execution was something around 1000.

Interestingly, other statements had less disk reads but lasted longer.

Question: Is the value disk_reads an estimated value, inserted into v$sqlarea
by the cost based optimizer (some worst case study, perhaps?), or does this value really mean, there were 1000 physical disk reads for that statement?
Why are other statements with 300 disk reads three times slower than that with 1000 disk reads?

What is the correct way to find out untuned SQL statements?

Best regards,

   Martin Pfingstl Received on Thu Jul 15 1999 - 09:10:46 CDT