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

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: bguillaumin.ext@xxxxxxxxxxxxxxxxx
  • Date: 28 Apr 2006 08:40:42 -0700
  • Date: Fri, 28 Apr 2006 09:40:28 -0600

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

GUILLAUMIN Bert Ext ROSI/SI CLIENT wrote:

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



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


Other related posts: