A challenge... 
Author Message
 A challenge...

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.



Wed, 20 Jun 2001 03:00:00 GMT
 A challenge...



Quote:
> About one in every 1000 insert transaction stalls (or ?) in the database -
> get response time peaks of 40 to 180 secs.  -- WHY??

Two reasonably long shots:

1. Check your DNS setup. Failing DNS lookups can produce this type of
stalling and 40-180s would be typical.

2. If you are running in archivelog mode, have you looked at how the redo
log is getting archived. Do the delays correspond to log switching for
example?

--
Regards,

Richard Senior
London, England

Mail: richard at r-senior dot demon dot co dot uk
WWW : www.r-senior.demon.co.uk



Wed, 20 Jun 2001 03:00:00 GMT
 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.

Quote:

> 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.



Wed, 20 Jun 2001 03:00:00 GMT
 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

Quote:
>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.



Thu, 21 Jun 2001 03:00:00 GMT
 A challenge...



Quote:
>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?
>...

Chiming in with Len: we have four groups of 100 MB
logs, which are pretty big logs by most people's
standards.  But I've seen those suckers rotate
every three to five minutes during heavy loads,
such as an import.  We used to have three groups;
I added a fourth one to give the archive writer a
little slack.  Look in your alert log and see if
there are any messages like "Unable to allocate
log file..." following a series of quick log
rotations.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to

quidnunc.net    | - Frank Zappa



Fri, 22 Jun 2001 03:00:00 GMT
 A challenge...

Rather than conjecturing, why not just look at v$session_wait when the
pause is happening? That will tell you why it is pausing.

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah


Quote:

> 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.


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

> > Are you checkpointing too frequently?  (Less likely)

> > >Has anyone seen this type of problem before?

> > >*********
> > >About one in every 1000 insert transaction stalls (or ?) in the database -
> > >get response time peaks of 40 to 180 secs.  -- WHY??
> > >*********



Sun, 24 Jun 2001 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Sybase challenge...(but maybe general SQL challenge)

2. Sybase challenge...(but maybe general SQL challenge)

3. Challenging newbie question

4. Complex Constraint Challenge

5. MILLION DOLLAR CHALLENGE FROM ORACLE

6. Query Optimizer Challenge

7. SQL Server Security Challenge

8. SQL Stud Challenge!

9. SQL Server Stored Procedure Challenge !!

10. Relational Table & SQL Challenge

11. SQL Stored Procedure CHALLENGE: Please help.

12. Challenge


 
Powered by phpBB® Forum Software