Re: Oracle Performance -- Possible Disk Bottleneck

Thanks for the support and the links. I'll take a look at the links and the book.

Maybe I'm asking the wrong questions.

Maybe a better question would be:

Can 6 15k fibre channel drives in a RAID 10 configuration support 2000 IO's per second?

I've read that 15k drives can perform 180 IO's per second. And that's with no RAID penalty. If this is the case, then my 6 drives should only be able to handle 6 x 180 IOps = 1080 total capacity. If this is true, then my average IO's per second of 2000+ is WAY over the capacity of the drives.

Questions:

1. Is the scenario above a reasonable approach to solving this problem?
2. Can 15k drives really only handle 180 IO's per second?
3. What happens to an Oracle environment (transaction oriented) when drive IO limits are reached. Are there other metrics I can look at to confirm this is a disk bottle neck. Is there something in the statsapack that can show me this is a disk bottleneck?
4. Does 2000 IOps seem really high?

On Jun 7, 1:44 pm, bdbafh <bdb..._at_gmail.com> wrote:
> 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_opm...
>
> 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:
>
> - statistics have not yet been gathered or are stale.
> - buffer cache is set to a very small value and can be increased
> - pga_workarea_target is very small and can be increased
> - optimizer settings can be altered resulting in more favorable
> execution plans and response times.
>
> 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/homepagehttp://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 self-
> management capabilities introduced in the Oracle Database.
>
> If not, the Concepts Guide is located on that same page.
>
> good luck.
>
> -bdbafh
>
> .- Hide quoted text -
>
> - Show quoted text -
Received on Thu Jun 07 2007 - 16:29:02 CDT