Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Performance -- Possible Disk Bottleneck

Re: Oracle Performance -- Possible Disk Bottleneck

From: <>
Date: Thu, 21 Jun 2007 16:42:19 -0700
Message-ID: <>

On Jun 21, 1:24 pm, joel garry <> wrote:
> On Jun 21, 9:22 am, 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 - see
> . 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
> --
> is bogus.

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. Received on Thu Jun 21 2007 - 18:42:19 CDT

Original text of this message