Re: 100+ Gigabyte IBM RS/6000 ORACLE DSS

In article <38635_at_oasys.dt.navy.mil> air4114a_at_oasys.dt.navy.mil (Steve Carr) writes:

> 1. I come to you seeking advice and counsel regarding technical
> and performance concerns of an architecture for building a large
> Decision Support System (DSS) based upon ANSI SQL calls to an
> RDBMS with a very large data base in a UNIX environment.
>
> 2. More specifically, we are dealing with a centralized IBM
> Systems Command (AIR-4114A)

Hello air4114a, and everybody else dealing with large tables

We are running Oracle 6.0.34 on a IBMRS6000/550.

THe greatest of our tables sizes 3 GB and 2 GB index, 30,000,000 records. We 're using a embedded C program to insert data on-line from our realtime-system.

Take a close look on the segmentation and fragmentation of tablespaces. If your tablespace is fragmented you cannot continue even if you have a few gigs free. To defragmentate you have to rebuild your tablespace!!

On tablespace over multiple files (disks) works fine.

We have two rollback segments a 100MB.

The system runs with 110 transactions/second when doing INSERTS in -our- application. Be aware that DELETING FROM ... is -much slower-. We measured a constatnt throughput of 400kb/sec with the 550 and 250kb/sec with a 530 when doing complete table scans.

Answer times are < 1 sec -if you use the key- (For somthing like select * from pdat where pnr = '1234';

Answer time is about 1 hour:
select count(*) from wert;
select * from wert where abndat>'1-feb-93';

We are not able to use views in non trivial statments. I have seen a statement that wanted to produce a larger temporary file than all referenced tables in sum.

With great tables be award that a logical backup of tables is difficult. (Create table and index before INP, because create index on large tables needs immense space).

Do you want to make numerous small tables or a few big one's ?

If you have questions or are interested in details please feel free to email to

Institut fuer Klinische Chemie Klinikum Grosshadern. Ludwig Maximilians Universitaet Muenchen.

dischner_at_med.uni-muenchen.de Received on Fri Jul 02 1993 - 16:09:17 CEST