RE: Batch and OLTP at the same time : Mission possible ?

From: GUILLAUMIN Bert Ext ROSI/SI CLIENT <bguillaumin.ext_at_francetelecom.com>
Date: Fri, 28 Apr 2006 18:28:35 +0200
Message-Id: <004501c66ae0$ccbffab0$ab068c0a@lille.francetelecom.fr>

I think you understood perfectly well. The thing is I wrote the batch to be able to use the power of the machine(Hash join and full table scans of 20M record table etc..). So I'm obliged to rewrite most of it.

I will be able to test it with a performance tester but since it is quite complicated to code (we have numerous functional rules to enforce) I prefer to start in the right direction and not be obliged to restart from scratch if my conception choices reveal erroneous.

I must add that the tables I insert into are heavily indexed for most of them and that some keys are just horrid(generated random number(31)), which doesn't help to be able to insert fast.

Regards,
Bertrand Guillaumin

PS : David I just had your mail before sending this one. The solution you propose is the one that has my preference for now. The advantage of going row to row only is that you may get one record into memory and then have no access to the database in order to work on it.   -----Message d'origine-----
  De : Dennis Williams [mailto:oracledba.williams_at_gmail.com]   Envoyé : vendredi 28 avril 2006 17:53
  À : danielwfink_at_yahoo.com
  Cc : GUILLAUMIN Bert Ext ROSI/SI CLIENT; Oracle-L Freelists   Objet : Re: Batch and OLTP at the same time : Mission possible ?

Bertrand,

Okay, to put it into simple terms:

You currently do the batch task during off-hours and now you are being asked to move the task to the times when the database is busy?
Most of the batch activity is inserts, with just a few updates?

If your site is equipped to do perfomance testing of the database, that would be ideal. Simulate the interactive load, then start the batch process. But if you are like most of us, that doesn't exist.

My advice would be to "start slow". Get baseline performance readings with just the interactive load. STATSPACK does a good job for that sort of thing. Also, (per Cary Millsap) try to measure current interactive user response from the user's terminal as accurately as possible. If the batch job is running off-hours now, get baseline performance readings while it is running also. You may be able to get a rough idea of the combined database load

Ce message et toutes les pieces jointes (ci-apres le "message") sont confidentiels et etablis a l'intention exclusive de ses destinataires.
Toute utilisation ou diffusion non autorisee est interdite. Tout message electronique est susceptible d'alteration. Le Groupe France Telecom decline toute responsabilite au titre de ce message s'il a ete altere, deforme ou falsifie. Si vous n'etes pas destinataire de ce message, merci de le detruire immediatement et d'avertir l'expediteur.

This message and any attachments (the "message") are confidential and intended solely for the addressees. Any unauthorised use or dissemination is prohibited.
Messages are susceptible to alteration. France Telecom Group shall not be liable for the message if altered, changed or falsified.
If you are not the intended addressee of this message, please cancel it immediately and inform the sender.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 28 2006 - 11:28:35 CDT