Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Bottleneck?, Insert and select on the same table.....

Re: Bottleneck?, Insert and select on the same table.....

From: hpuxrac <>
Date: 8 Nov 2006 06:40:36 -0800
Message-ID: <> wrote:
> Hi all!
> I should make an analysis about an infrastructure that a customer would
> be organize for its business.
> The goal of the activities is to make available, for online
> consultation a big amount of data.
> The problem is that the data should be availabile in a table
> simultaneously to the insertion of the same ones.
> >From an initial analysis, we have to manipulate about 6Gb of data for a
> day.
> There are 12 server which will produce, every x minutes, a text file .
> These files will have to be converted and inserted into a table,
> probably through SQLLoader, meanwhile users
> could perform queries on the same table.
> Initially this table is empty.
> Every second will be produced 14 rows for each server(so 14*12=168 rows
> in total); considering each file
> it would have to be generated every x minutes, we have 168*60*x=10080*x
> rows every x minutes.
> A row size is about 420 byte, so in total we have 10080*x*420=4233600*x
> byte (4134,375 kbyte) every x minutes.
> Considering this amount of data, are there any condition that could
> produce a bottleneck?
> What is an approach to solve the problem related to make insert and
> select at the same time on the same table?
> I refer to the amount of data manipulates on disk I/O.
> I thought that a great amount of data could create slowing down and
> therefore decay of the performances.
> I hope to have introduced clearly the situation, so you can help to
> relieve the customer to make the right choice.

>From what you have described, this sounds to me like a very modest
requirement for an oracle database to handle. Caching on the disk IO subsystem should help the write performance.

Using sqlldr you can use the direct insert path. This allows the sqlldr utility to format new blocks that are being inserted that go past the current high water mark of data that is allocated to the table. So ( until the batch load thru sqlldr completes ) the current set of select statements doesn't see that data.

Even without using the sqlldr direct option oracle protects select statements so that they only see committed data. Or in other words oracle out of the box provides read consistency and transaction isolation. Received on Wed Nov 08 2006 - 08:40:36 CST

Original text of this message