A challenge...

From: Steven James Ryan <smirnow_at_bigpond.com>
Date: Sat, 2 Jan 1999 01:07:49 +1100
Message-ID: <368cd64d.0@139.134.5.33>

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 Fri Jan 01 1999 - 08:07:49 CST