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

From: Daniel Fink <danielwfink_at_yahoo.com>
Date: Fri, 28 Apr 2006 07:16:24 -0700 (PDT)
Message-ID: <20060428141624.95768.qmail@web37210.mail.mud.yahoo.com>

Bertrand,

One thing to consider is the impact of these processes on queries. If a query is reading the data (or other data in the same block) you are updating, there might be a significant performance penalty. I have seen reporting queries run up to 42 times slower when they are generating read consistent views of the data because a poorly designed and coded update process is running at the same time.

Regards,
Daniel Fink

GUILLAUMIN Bert Ext ROSI/SI CLIENT <bguillaumin.ext_at_francetelecom.com> wrote: From: "GUILLAUMIN Bert Ext ROSI/SI CLIENT" <bguillaumin.ext_at_francetelecom.com> To: "'Oracle-L Freelists'" <oracle-l_at_freelists.org> Subject: Batch and OLTP at the same time : Mission possible ? Date: Fri, 28 Apr 2006 15:43:55 +0200

Hi !
I've been assigned the task to develop a batch process which is supposed to run while users are using the database(and not a small number of them : 3 000 on a 12 CPU machine).

The batch in itself mainly inserts(sometimes updates) records into 15 huge tables(between 20 and 50M lines, no partition) and expected volumes are 10 000 thousands records/hour/table. There may be inserts/updates on those tables while the batch is running.

I am a little bit worried about what is the best way to implement such a thing knowing I have to check the existence/non existence of the objects/links sent and to get/generate internal ids(I have 5 files of about 10 000 lines and external ids in the files are only mapped on 5 of the tables). For now the batch only runs during the night(using parallel query to go faster) and uses staging temporary tables to control fonctionnal and technical coherence as well as getting internal ids(when exists).

My first thoughts were to keep the same way of functionning without the parallel mode but I fear it may cause contentions on the tables(inserts are quite slow already). So I thought maybe doing it record by record and then committing but I wonder whether it could be to slow.

The batch is coded in what can be considered as PL/SQL. The database is in 9.2.0.7.

Any thoughts/remarks appreciated.

Regards,
Bertrand Guillaumin

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 - 09:16:24 CDT