Re: Pentium M and Oracle Performance

Christoph Breitkopf  wrote:
> Sorry, I'm not sure what you are talking about. By SQL cache
> do you mean the shared pool? Miss ratio is much below 1% there.

You can have a miserable life even if you have a 100 % hit rate.

You need to check v$sqlarea.

Below is two of the Access queries I have used to determine some of the
problems we have had. It is called sql_hogs_3_m_optimizer.

SELECT SQL_TEXT, DISK_READS AS reads, BUFFER_GETS AS gets,
cint(disk_reads*100/buffer_gets) AS prosent, executions AS exe, "  " &
mid(first_load_time,12) AS first, optimizer_mode
FROM [V$SQLAREA]
WHERE (DISK_READS > 20000)
ORDER BY disk_reads DESC;

sql_hogs_3_avg:

SELECT SQL_TEXT, DISK_READS AS reads, BUFFER_GETS AS gets,
cint(disk_reads*100/buffer_gets) AS prosent, executions AS exe, "  " &
mid(first_load_time,12) AS first, clng((disk_reads+buffer_gets)/executions)
AS average
FROM [V$SQLAREA]
WHERE (DISK_READS > 20000)
ORDER BY disk_reads DESC;


>
> > So unless you say explicitly that you have checked the I/O count in the
sql
> > cache, then I assume that you have not and then there is nothing that
can be
> > said about the causes of the performance difference.
>
> But if you are talking about IO, you probably mean the buffer cache?

No. You want to avoid all I/O. Not only the I/O that is physical. Removing
physical I/O is just the first step on the road. Logical I/O is also your
enemy.

This is my Oracle resource list. There is a number of books which deals with
tuning and explains how to interpret what you are measuring. If my memory is
right, the Oracle Tuning book from O'Reilly is all right. There is also at
least one book from the man behind http://asktom.oracle.com/  (Thomas Kyte)
which is supposed to be very good on tuning.

http://www.orafaq.org/faq.htm
http://technet.oracle.com
http://oracle.oreilly.com/
http://asktom.oracle.com/pls/ask/

Oracle tuning (It is worthwhile to register for the first two sites)

http://www.hotsos.com
http://www.orapub.com
http://www.oracledba.co.uk/
http://www.miracleas.dk/

Oracle User group

http://www.ioug.org
http://www.selectmagazine.org/

Script, misc:

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
http://www.jlcomp.demon.co.uk/#Index
http://www.orsweb.com/
http://www.oracletuning.com/
http://www.ixora.com.au/tips/index.html

greetings,