Re: A challenge...

Since the 'event' occurs every 1000 inserts or so, I suspect automatic rebalancing of the index b-tree(s)(depending on number of indexes). This would put locks on the table, etc. Duration could be function of table size and number of indexes.

In article <368f8664.15900023_at_news.prestel.co.uk>,   leonard_at_lf-clark.prestel.co.uk (Leonard F. Clark) wrote:
> 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.
> >
> >
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 05 1999 - 07:59:44 CST