Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle 8.0.6 running aweful!!!!! need tuning advice!
Long post, forgive me.
My advice is that you leave ratios be until you've put your finger at the problem. Why spend time tuning buffer cache hit ratio or whatever if your problem is of a totally different nature?
Set timed_statistics=true and bounce the instance just prior to a period of high activity. Let the database work for a while and then run the following:
SCRIPT1: (run as system)
set echo off
set verify off
set feedback off
set pagesize 80
select
sumbusy.total/100 "Busy wait time (secs)",
sumidle.total/100 "Idle wait time (secs)",
(sumidle.total+sumbusy.total)/100 "Time waited (secs)"
from (select sum(time_waited) total from v$system_event
where event not in ('pmon timer', 'smon timer', 'rdbms ipc message',
'rdbms ipc reply', 'SQL*Net message from client',
'SQL*Net more data from client',
'SQL*Net message from dblink',
'SQL*Net more data from dblink',
'Null event',
'inactive session',
'rdbms ipc reply',
'rdbms ipc message',
'slave wait',
'lock manager wait for remote message',
'pipe get')) sumbusy,
'rdbms ipc reply', 'SQL*Net message from client',
'SQL*Net more data from client',
'SQL*Net message from dblink',
'SQL*Net more data from dblink',
'Null event',
'inactive session',
'rdbms ipc reply',
'rdbms ipc message',
'slave wait',
'lock manager wait for remote message',
'pipe get')) sumidle
If the idle waits are a large percentage of the total waits, this is an indication of a problem outside the database. If not, SCRIPT2 which measures events waited for and accounted for by the Oracle instance itself, will point you in the right direction.
SCRIPT2: (run as system)
set echo off
set verify off
set feedback off
set pagesize 80
set linesize 100
column "Event" format a30
select event "Event",
total_waits "Total waits",
lpad(to_char(round(average_wait, 2),'99999999D00'),25) "Average wait
(secs/100)",
lpad(to_char(round(time_waited/100,2),'9999999D00'),18) "Time waited (secs)",
lpad(to_char(round((time_waited/decode(sumses.total,0,0.0001,sumses.total)*1
00), 2),'999D00'),8) "Pct wait"
from v$system_event,
(select sum(time_waited) total from v$system_event where event in ('pmon timer', 'smon timer', 'rdbms ipc message',
'rdbms ipc reply', 'SQL*Net message from client',
'SQL*Net more data from client',
'SQL*Net message from dblink',
'SQL*Net more data from dblink',
'Null event',
'inactive session',
'rdbms ipc reply',
'rdbms ipc message',
'slave wait',
'lock manager wait for remote message',
'pipe get')) sumses
where event in ('pmon timer', 'smon timer', 'rdbms ipc message', 'rdbms ipc reply', 'SQL*Net message from client',
'SQL*Net more data from client',
'SQL*Net message from dblink',
'SQL*Net more data from dblink',
'Null event',
'inactive session',
'rdbms ipc reply',
'rdbms ipc message',
'slave wait',
'lock manager wait for remote message',
'pipe get')
I also advice you to take a look at Steve Adams' (Ixora) script response_time_breakdown which gives you a breakdown of service times as opposed to wait times.
Hth,
Ivan Bajon, ocp
"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3abd133f_at_news.iprimus.com.au...
> You could do worse than visit www.ixora.com.au. > > It's about the best performance tuning resource there is. Links fromthere
> are also available to Jonathan Lewis' site, which is the only other one > worth visiting. > > Concentrate first on tuning the Instance -Library Cache hit ratio, Buffer > Cache hit ratio etc. Then start on physical file issues. > > Regards > HJR > > > "oskar" <pheonix1t_at_home.com> wrote in message > news:Li8v6.421783$w35.66665103_at_news1.rdc1.nj.home.com... > > hello people, > > I have a strange situation at work ( somehow I get the feeling I shouldget
> > but at least 6 disks....space isn't a problem! The processor is > > 120MHZ.....don't laugh!!!!!!! :) > > That's what we have to work with! > > I'm pretty sure no one has ever done any sort of performance tuning onthis
> > of Lawson until I started working at this place. > > Anyway, I tried suggesting that the owner hire an oracle specialist > > (Dulcian.com) to tune this thing but he turned that down..........too > > expensive and wants to let this "partner" ( consultants ) do this. Theyare
> > they were Oracle specialists....even their website doesn't even mention > > Oracle. ( comvision.com ). > > So, until they get around to it I need to start taking somemeasurements
> > oracle professionals have to say about quest software or Kyle's monitor > > software? > > I started doing some research at Oracle and found some basic > > documentation on performance tuning the version 8.0.6 database as wellas
> > site called oracle.ittoolbox.com that has a lot of very good info. > > Basically, since I'm going through a crash course of database > > admininstration in real life! What or where are the best places to goso
> > to quickly learn the things I need to do in order to start analysing the > > performance of this database so I can have an idea of what is going > > on??......so if or when the changes start to be put into place by the > > "partner" ( consultants).....I'll have an idea of what the consequenceswill
> > from what I can see.........this is very sophisticated stuff and > > trouble-shooting it is hard because it interacts with so many different > > things (software and hardware). From what I'm quicklylearning...........
> > environment. > > > > forgive the long message but I'd really appreciate some accurate adviceon
> > what I should be reading or looking at to solve this problem! > > > > Thank you, > > > > Oskar > > > > > >Received on Mon Mar 26 2001 - 05:19:30 CST
![]() |
![]() |