Re: What accounts for performance issues?

In article <4ehD2.52$N62.3590587_at_nnrp1.tor.metronet.ca>,   "Stephane Viau" <viaust_at_rmoc.on.ca> wrote:
> What accounts for performance issues with Oracle 804 and NT4 SP3. We are
> running queries that are running for a very long time. We are trying to
> pinpoint the cause of severe performance issues. We had a Oracle DBA look at
> and reconfigure our database, but did not improve performance.
>
> --
>
> Thanks,
>
> Stephane Viau
> Ottawa, Canada
>

Most performance issues boil down to tuning the SQL, not the database. Tuning the database rarely has a noticable effect on more than one or two jobs that may have encountered a bottleneck unless most of the tuning involves re-arranging the disk layout. Disk bottlenecks can kill performance at the system wide level, but even then it is usually one or two jobs or tasks that suffer the most. This is not to say the tuning the database isn't important or worthwhile, it is just the big gains come from tuning the SQL.

Here is a script that can be used to find the SQL statements that cause the most physical I/O. Change disk_reads to buffer_gets (both places) and you are looking at the heavy logicial I/O SQL's on your system. Adjust the factor. 10, to be a larger number if you get too many SQL statements back, and to a smaller number if you get too few back. You can monitor the SQL being executed by a session in the v$sqlarea and evaluate the SQL using explain plan. If you are new to tuning you will want to look up explain plan, SQL trace, and tkprof in the manuals.

set echo off
rem
rem file: io_hog_phy.sql
rem SQL*Plus script to display the Oracle user sql requiring the most rem physical i/o.
rem
select disk_reads, sql_text
  from v$sqlarea
 where disk_reads > ( select ( (avg(disk_reads)) * 10 )

                         from  v$sqlarea )

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 04 1999 - 08:17:54 CST