Re: Oracle Performance -- Possible Disk Bottleneck

On Jun 21, 7:42 pm, beth.sto..._at_gmail.com wrote:
> On Jun 21, 1:24 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > On Jun 21, 9:22 am, beth.sto..._at_gmail.com wrote:
>
> > > Thanks so much to everyone who replied. It seems apparant that we do
> > > have a disk bottleneck here. The question is why are there so many
> > > reads on the database.
>
> > > I disabled AV scanning, and this didn't improve things -- reads are
> > > still way high. They peak at 8000 Reads/sec during busy times.
>
> > > I went through the Metalink article, and we are beginning to identify
> > > some "hotspots." The DBA was aware of a problem with a particular
> > > table, and this showed up at the top of the list, so we'll start
> > > looking there.
>
> > If you are aware of particular tables or indices that have problems,
> > you may have some luck examining v$bh and creating multiple buffer
> > pools - seehttp://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/m...
> > . This doesn't necessarily fix the root problem, but it may reduce
> > unnecessary thrashing in the default pool due to those objects.
> > "Unnecessary thrashing" means it is asking too often for things from
> > disk. I've only used a recycle pool, for my particular situations it
> > has been quite helpful. Some people find it hard to get their head
> > around the concept that a warm segment can have such an effect on
> > other processes' hot blocks, though this fits with what Jonathan
> > mentioned about concurrent tablescans.
>
> > > "Choose" mode will be implemented in new version of the application
> > > which will be installed later this summer. Optimization modes have
> > > caused problems before, so hopefully this will fix those issues.
>
> > > There are plans to upgrade to 10g in the not-so-near future. The
> > > vendor is testing that, and we're doing testing of our own.
>
> > > We're also looking at giving the LUN more spindles. We'd like to
> > > avoid doing this if possible because of the cost.
>
> > > Thanks again for your help. I've been very impressed with this group
> > > and its willingness to help out.
>
> > jg
> > --
> > @home.com is bogus.http://www.snopes.com/photos/crime/graphics/drugraid01.jpg
>
> Voila! I used a combination of perfmon, Navisphere Analyzer and
> Statspack to identify the periods of high read IO, and those times
> corresponded to the times that a batch program runs for processing
> forms. We cross referenced this by using the scripts to find hot
> blocks, and the trigger table for processing forms showed up time
> after time after time. The DBA put an index on the trigger table and
> removed old entries. We also changed the batch processing to cycle
> every 10 minutes during the processing periods instead of every
> minute.
>
> Read IO has now dropped from thousands of reads per second to hundreds
> of reads per sec. and less.
>
> We'll test with the ned usesrs to find out what they see from their
> end.

I think it's time for you and your dba to purchase and read the Cary Millsap book now at least that's my recommendation.

Many of the "apparent performance problems" that oracle systems experience that point to possible hardware limitations are really application and database schema design problems.

Having a repeatable methodology is something not to be underemphasized. Received on Fri Jun 22 2007 - 13:40:29 CDT