Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle 8.0.6 running aweful!!!!! need tuning advice!

Re: oracle 8.0.6 running aweful!!!!! need tuning advice!

From: Ivan Bajon <bajon_at_nospam@worldonline.dk>
Date: Mon, 26 Mar 2001 13:19:30 +0200
Message-ID: <99n8k2$ppc$1@news.inet.tele.dk> 






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,





 (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')) 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')





order by 5 desc


/



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 from


 there

> 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 should


 get


> > used to these situations!).


> > The oracle server is running very slow.  It's on an HP-UX 10.20 (
 900/800).


> > It has 1G RAM, a SCSI disk array of several drives....don't recall how
 many

> > 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 on


 this


> > thing.....


> > They are also running Lawson on the oracle database as well....I think
 this


> > adds to the complexity and trouble of tuning this thing!  I've never
 heard

> > 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.  They


 are


> > doing a great job of web developing and programming...but they never
 said

> > 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 some


 measurements


> > of this thing and see just how bad it is and why?  Kyle Hailey posted a
> > monitor utility that he's developed as well as a link to Quest.com.
 What


 do

> > 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 well


 as


 a

> > 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 go


 so


 as

> > 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 consequences


 will


> > be.  I'm the systems admin. of this place so if anything goes
> > wrong........its my butt on the line!  I'm very confident of my
 abilities


> > with windows NT/98/2000 and linux.  I'm starting to get good at
 unix...but


> > still learning.  The oracle database thing is totally new to
 me.........but

> > 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 quickly


 learning...........


> > the way the database is setup and even the way the sql scripts are
 written


> > will have a big effect on the performance of the database under a
 production

> > environment.
> >
> > forgive the long message but I'd really appreciate some accurate advice


 on

> > 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












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US