Re: Execution time of statements

From: <michael_bialik_at_my-deja.com>
Date: Fri, 16 Jul 1999 19:15:42 GMT
Message-ID: <7mo0ci$oat$1@nnrp1.deja.com>

Hi.

 Try looking at bugger_gets instead of disk_reads :

 select sql_text, bugger_gets,

        executions ,
        bugger_gets/ executions "Avg_bufgets",
        disk_reads,
        disk_reads / executions "Avg_diskreads"

  from v$sqlarea
  where executions > 1
  order by 3 desc;

  Good luck. Michael.

In article <7mkq57$cfq_at_ar4dec01.ar4.fra.dlh.de>,   "Martin Pfingstl" <mpfingstl_at_lhsystemsas.de> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 16 1999 - 14:15:42 CDT