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:02:36 +0200
Message-Id: <004401c66add$2bda95e0$ab068c0a@lille.francetelecom.fr>

David,
I totally do agree that parallelism has to go(I said so in my first mail). I must say that excessive CPU usage is not what I fear the most(since the batch without parallelism would use at most 1 CPU and we have 12). But thanks for the tip if problems occurs on that level I'll have and idea of what i may use.

My greatest fear is contention in regard to tables and indexes.

Daniel : I totally understand what you say and that's the reason why I advised against this solution. My point of view hasn't prevailed so now I have to do it this way.

Now my idea of record by record solution seems to be endorsed by Tom Kyte in 8i (see http://asktom.oracle.com/pls/ask/f?p=4950:8:18066575999822505476::NO::F4950_P8_DISPLAYID,F4950_P8CRITERIA:4343304784432)

For him if you're in OLTP mode, you do things the OLTP way.

But I wonder whether in 9i there is no other solution since i fear it would be very slow(even slow enough to be unable to complete in time).

Regards,
Bertrand Guillaumin

-----Message d'origine-----

De : David Aldridge [mailto:david_at_david-aldridge.com] Envoyé : vendredi 28 avril 2006 16:51
À : GUILLAUMIN Bert Ext ROSI/SI CLIENT
Cc : 'Oracle-L Freelists'
Objet : Re: Batch and OLTP at the same time : Mission possible ?

The parallelism on the OLTP tables almost certainly has to go --- the query coordinator kicks off a segment-level checkpoint before the query slaves start reading (thx JL), so that could be a significant impact on users.

You might like to look at Resource Manager to control the CPU impact of this job also, if the machine gets hard-pressed.

Row-by-row sounds like a Bad Thing, especially if you can control the impact of the batch job with RM

-----Message d'origine-----

De : Daniel Fink [mailto:danielwfink_at_yahoo.com] Envoyé : vendredi 28 avril 2006 16:16
À : GUILLAUMIN Bert Ext ROSI/SI CLIENT; 'Oracle-L Freelists' Objet : Re: Batch and OLTP at the same time : Mission possible ?

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

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:02:36 CDT