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: joel garry <joel-garry_at_home.com>
Date: Thu, 07 Jun 2007 14:51:36 -0700
Message-ID: <1181253096.622293.200080@r19g2000prf.googlegroups.com>


On Jun 7, 12: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.- Hide quoted text -
>
> - Show quoted text -

On top of bdbafh's suggestions, looks like you should get Cary Milsap's book and follow it through to determine the exact set of problems.

Besides that, you might want to post init.ora parameters that are not default, 9 databases (assuming you are using the Oracle definition of "database," it's different than the one many MS-oriented folk use - do the terms ORACLE_HOME and ORACLE_SID mean anything to you?) could be a major part of the problem. As Paul mentioned, the cache buffers may be set too low, causing many reads to load them, where if there were more, the buffers would already be loaded. If you just had one db rather than nine, you could easily increase the buffer size. But they are additive across databases. There is an Oracle product called OEM (I've forgotten where it is, perhaps on a supplemental cd) that has some decent visualizations of what is happening, and what will likely happen with certain changes - but only per db. The data comes from system tables named something like %ADVISOR%, which you can learn about in the Performance manual, learning much about what's going on while you are at it.

Besides that, as you will no doubt discover as you work through these things, most performance problems come from the application code.

It's only after all that you can prove that more spindles will help. Of course, proof to a boss may not need to be so rigorous, and many people have no problem saying more spindles=better performance. It's just a bit embarrassing after spending all that money to discover the bottleneck was elsewhere, or some simple code change or adding an index could have fixed 90% of the problem. The statspack may tell you that, or it may mask the problem, depending... but you won't know by not running it. There should be some "top" information in there you may want to post. It's possible that you would be doing more writing if you weren't doing so much reading, the wait info may be informative. Some people in this group are willing to go through an entire statspack if you post it.

I'm guessing storage processor peaking while disks are not may indicate loads satisfied by storage cache, if they were satisfied by Oracle's cache instead you may see some improvement. Google about for papers about reducing logical I/O, too.

jg

--
@home.com is bogus.
"There is no try in production." - bdbafh
Received on Thu Jun 07 2007 - 16:51:36 CDT

Original text of this message

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