Re: A challenge...

Following on from Richard's response, I suspect he is pointing in the right direction. Additional thoughts:

Are the redo files big enough for the volume of transactions?

Are the redo log buffers big enough to support the throughput (I seem to remember that the default values for these are always far too small for a busy database)?

Are you checkpointing too frequently? (Less likely)

Len

>Hi there everyone,
>
>Has anyone seen this type of problem before?
>
>- Oracle 7.3 DB
>- Digital Alpha 8400 etc.
>- Fairly simple database. Two main tables that for a supertype-subtype
>association.
>- Main table has 9 million rows, growing at 1 million per month, the other
>is one-tenth the size
>- High transaction rate during peak hours - 74% reads via index, 24%
>inserts, 2% updates
>- Application has 1000 concurrent users
>- 3 tier client server architecture
>- Middleware maintains 5 database connections, shared amongst all users
>- average transaction time is < 100 milliseconds in database
>
>All appears normal 99% of the time.
>However, there is a problem:
>
>*********
>About one in every 1000 insert transaction stalls (or ?) in the database -
>get response time peaks of 40 to 180 secs. -- WHY??
>*********
>
>I've done all the usual application tuning exercises. Examined trace files
>to try and figure it out, but so far no solution. All the simple instance
>tuning stuff looks OK. My only option at the moment is to ask management for
>funding for low-level tuning with no guarantee of success - since I can't
>define the solution.
>
>In truth, the database hums along beautifully except for this minor hitch.
>Problem for me is that when it happens it hogs one of the 5 connections
>until it is finished. If it happens on two connections simultaneously, then
>two are hogged etc.
>
>So, your mission, should you choose to accept it, is to try and figure out
>what could cause such a problem in an Oracle database on unix.
>
>This message will self destruct when you press your delete key.
>
>Thanks, and good luck.
>
>
Received on Sun Jan 03 1999 - 09:04:16 CST