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 Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: bdbafh <bdbafh_at_gmail.com>
Date: Thu, 07 Jun 2007 20:44:12 -0000
Message-ID: <1181249052.332114.277800@k79g2000hse.googlegroups.com>


On Jun 7, 3:49 pm, beth.sto..._at_gmail.com wrote:
> On May 25, 7:42 am, EscVector <J..._at_webthere.com> wrote:
>
>
>
> > On May 24, 5:56 pm, beth.sto..._at_gmail.com wrote:
>
> > > My apologies if this subject has been discussed. I searched the
> > > groups, and I couldn't find a good thread.
>
> > > We've been having performance problems with our Client/Server
> > > application for months. Users contantly complain of slow response
> > > times to their queries.
>
> > > Here's the environment:
>
> > > Oracle 9i on Windows. Poweredge 6850. 4, 3 GHz Quad Core
> > > Processors. 8 GB RAM. 9i databses are stored on an EMC Clarrion
> > > CX500 LUN -- RAID 10. 6, 15K 146GB dedicated disks. Oracle logs are
> > > stored on a separate RAID 10 LUN on dedicated disks. 1 GBE switched
> > > backend. Users connect using FastEthernet. XP clients. All disks on
> > > the SAN are fibre channel.
>
> > > CPU utilization is fine. RAM utilization is fine. Throughput on the
> > > NIC is fine -- maxes out at 50 Mbps for a short while when users first
> > > log in in the morning. Averages are 20 Mbps.
>
> > > Using perfmon in Windows, If I look at Disk Reads/Sec and Disk Writes/
> > > sec., here's what I see for averages:
>
> > > Disk Reads/sec = 2200 Avg
> > > Disk Writes/sec = 10 Avg
>
> > > Our reads/sec seem EXTREMELY high for only 80 users.
>
> > > Can someone help me understand if this is truly a disk bottleneck?
>
> > > Thanks in advance!
>
> > Don't use the windows tools to check on SAN performance. Talk with
> > the storage admins. They will have a much better perspective on both
> > throughput and utilization. The perfmon tool might indicate an issue,
> > but I doubt it will tell much. I like the statspack suggestion if
> > done accurately and snap are scoped correctly.
>
> > Do you have virus software running? This will typically bump up I/O
> > reads in perfmon.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks for all of the information and suggestions.
>
> I did some comparison of Windows tools (Perfmon) and EMC tools
> (Navisphere analyzer). Perfmon was actually reporting the IO
> correctly. Average IO reads are 2000, Average Write IO is @ 10.
> There are some interesting metrics that I don't understand from Nav.
> Analyzer: The storage processors are showing peaks close to 100% at
> certain times of day, but the disks are not at 100%. I'm not sure
> what that means. LUN % utilization is the same, so it looks like the
> Storage processors are responsible for the high % utilization.
>
> STATSPACK analysis shows something similar to Perfmon and Nav.
> Analyzer: Physical reads are 2552, physical writes are 52. There's a
> ton of information in the statspack report, so I'm not sure what else
> to look at.
>
> There is anti-virus software on this machine, but I don't think it
> accounts for a significant part of the 2000+ IOPs per second. We use
> the same product on all of our servers. IO is not a problem on any
> other server. On this machine, there are peaks of 10,000 IOPs. This
> seems abnormally high.
>
> We're a very small shop, so I'm the storage person. This is a new
> technology for us, so I'm in a learning curve here.
>
> There don't appear to be many good SAN related groups that are active,
> so I'm hoping someone here has experience with SANs and ORACLE to help
> out.
>
> EMC support is NOT what we were expecting. For the amount we pay for
> support, the response is very very disappointing. I've had a ticket
> open on this for MONTHS. Literally.
>
> I guess the bottom line here is that I need to prove to my boss that
> this is a disk bottleneck and that giving the database more spindles
> will help. Also, I'd like to understand why IO reads are so high for
> Oracle.
>
> Any information would be appreciated.

Congratulations. So you're the new DBA. Don't panic. http://www.ooug.org/presentations/newDBAexpanded.pdf

Ok, that one is a little bit dated.
Someone posted that you want to install and use the Oracle-provided tool "Statspack" to determine what is performing poorly or impacting performance of business-critical usage. Some level 7 statspack snapshots would be a good start (if statistics_level='TYPICAL').

Here is a place to start:
http://www.oracle.com/technology/deploy/performance/pdf/statspack_opm4.pdf

There is a great deal of reference material regarding Oracle's Statspack.
Install it, take a few snapshots, generate a report and the top statements should jump right off of the page at you. What you do with it after that is largely a matter of what can be changed in the environment and in the application code.

If the database is configured with default parameters, there may be a great deal of low-hanging fruit - in terms of:

If the budget and timeframe allows take classes. If this is urgent and there isn't time to learn it on your own, hire in a consultant.

Check out the documentation at:

http://www.oracle.com/pls/db102/homepage http://www.oracle.com/technology/documentation/database10gr2.html

The "2 Day DBA" might be good for you:

About this Book

Oracle Database 2 Day DBA is a database administration quick start guide that teaches you how to perform day-to-day database administrative tasks. The goal of this book is to help you understand the concepts behind the Oracle Database. It teaches you how to perform all common administration tasks needed to keep the database operational, including how to perform basic troubleshooting and performance monitoring activities.

The primary administrative interface used in this book is Oracle Enterprise Manager in Database Console mode, featuring all the selfmanagement  capabilities introduced in the Oracle Database.

If not, the Concepts Guide is located on that same page.

good luck.

-bdbafh

. Received on Thu Jun 07 2007 - 15:44:12 CDT

Original text of this message

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