Re: A challenge...

First of all I see that you are unfair to Oracle Corp. You are having a 1000 user system with the Five user Oracle license!!! You should ask your management to spend more money to get a fair licensing.

I think your database is check pointing after every 1000 inserts.

Steven James Ryan wrote:

> 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 Sat Jan 02 1999 - 13:05:33 CST