Performance Monitoring

[SincySaji]

We are running Oracle 7.x on AIX Server. We need to monitor the performance of Oracle server.
The performance of our front-end application which is accessing this oracle database, varies over the time. So we want to monitor the performance of the oracle server over the given time interval.

Please guide us on the above problem(what tools have to be used and what parameters are to be monitored).

Thanks in advance
Sincy.

[drishti]

YOU CAN CHECK U'R BUFFERCACHE HIT RATIO ,LIBCACHE HIT RATIO AND SEE WHEN LAST YOUR INDEXES AND TABLES WERE ANALYZED.SEE THE EXECUTION PATH OF THOSE QUERIES WHICH ARE SLOW USING EXPLAIN PLAN.

RUN UTLBSTAT AND UTLESTAT TO GET STSTISTICS OF DATABASE

[denevge]

on the AIX level.

Gatherer statistics :

sar -u : gives you an idea about the cpu usage
> % idle
> % waiting for i/o. If high, how come ?? ( full table scans, paging ??? )
==> CPU problem ? waiting for i/o problem ?

sar -q : gives you an idea about the run queue
> if run queue is high, it means you have a cpu problem. ( may be cause by high waiting for i/o )

vmstat : Is there paging ? ( generates high waiting for i/o )

vmtune : using vmtune you can see if your system is trying to use memory from the file cache. If so, the AIX starts paging.

Is the dbsnmp daemon of 7.x started. If so, it's possible it consumes a whole cpu for 100%. There's a patch available.

Do you have nmon ? It's a utility you can install to see how the system is performing. ( cpu, disks , adapters , processes,... ) If not, let me know you email adress. I will mail it.

Hope this helps
Gert

[jlakhani]

denevge
can you please send me that nmon software? I have a sun solaris box.
my email is
Jigarwala@yahoo.com


thanks
Jigar

[SincySaji]

Denevge

Could you please send me that nmon softmare ?
My Email id is : Sincyjph@hotmail.com
And also, please give me some idea or any material about how to use this tool

Thanks
sincy.

[gram2000]

In addition to the other recommendations so far,

Take a look at V$SQLArea and isolate queries with high disk reads
and high buffer gets that might require tuning.

Check to see if bind variables are used for queries to prevent re-parsing
This should be reflected by your library cache hit ratio.

Check for any hot spots on your disk (any one disk/datafile getting most I/O).

Check Rollback segment usage (you can use the following script).

select a.name, b.extents, b.rssize, b.xacts "#Trans",
b.waits, b.gets, optsize, status
from v$rollname a, v$rollstat b
where a.usn = b.usn;

/*
If the #Trans or waits is consistently > 1 then consider adding new
roll back segments
*/