Re: Oracle Performance -- Possible Disk Bottleneck

On Jun 21, 7:42 pm, beth.sto...@xxxxxxxxx wrote:
On Jun 21, 1:24 pm, joel garry <joel-ga...@xxxxxxxx> wrote:





On Jun 21, 9:22 am, beth.sto...@xxxxxxxxx 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.

.

Relevant Pages

  • Re: Please Help
    ... RDBMS developers hang themselves. ... update-type query cannot violate the RI defined in the database. ... There is often not such a thing as a DBA in the Pick world and when we ...
    (comp.databases.pick)
  • Re: need access to read to SQL db and Access frontend
    ... I succeeded in getting to the actual .mde file on the network and linking to ... valuable since I can now see what tables are being used in SQL. ... tool tip with the path to the source database or DSN for the ODBC ... separate module/entity from dbA and doesn't need to be related but I ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Restored to New server and cannot logon to STS_Config
    ... > The Application Pools user is "Network Services" ... > the database access tab. ... you must first determine the application pool identity. ... Right-click the virtual server that is running Windows SharePoint ...
    (microsoft.public.sharepoint.portalserver)
  • Re: need access to read to SQL db and Access frontend
    ... Now I cannot print anything in the documentor. ... tool tip with the path to the source database or DSN for the ODBC ... If the tables are linked to a SQL Server database or even another Access ... separate module/entity from dbA and doesn't need to be related but I ...
    (microsoft.public.access.tablesdbdesign)
  • Interview Questions(Oracle9i DBA), help me
    ... The DBA is attempting to back up the Oracle database control file. ... The request queue is common, and the response queue is different ...
    (comp.databases.oracle.server)