Re: design question, use of partitioned tables?

From: Manuela Mueller <mueller_m_at_fiz-chemie.de>
Date: Wed, 06 Aug 2003 11:26:59 +0200
Message-ID: <bgqhgl$qssm6$1@uni-berlin.de>

Dear Nuno,
thanks for your input and your time, answers embedded. I totally agree, at least I have 2 porblems, fragmentation and very poor load performance.

Greetings
Manuela Mueller

Nuno Souto wrote:
> Manuela Mueller <mueller_m_at_fiz-chemie.de> wrote in message news:<bgo51g$q4in4$1_at_uni-berlin.de>...
>
>

>>OS: SuSe Enterprise Server  SLES-8
>>RDBMS: Oracle Standard Edition 9.2.0.3.0
>>DB Size: 1 TB
>>Tablespaces: Locally managed, uniform size 8 MB
>>Datafiles: all 32 GB

>
>
> I'm assuming a db_block_size of 8K based on above numbers?
Yes
>
>

>>Currently I perform a load of 17 Mio records, which will take about 5 
>>days. This is far beyond my time frame.

>
>
> That is FAR beyond anything I'd expect! WAY too slow.
> Something else is the matter and it's got nothing to do
> with partitioning, fragmentation, etc.
The data to load reside a seperate raid array on a 720 MB partition. The partition currently holds about about 50 million small files. Filesystem is Reiser FS, partition is almost full and mounted via NFS. Unfortunately this machine is under responsibility of my associate and due to shortage of space he can only migrate the data piecewise to other, smaller partitions. I observe the bad load performance since 2 months.
We suppose problems with this partition, top shows constant load average of 4.0, the 2 CPUs are 97% idle and there are no other processes running except nfsd.
On the DB Server machine, the data via eth0 come in at a 'speed' with 400 K/sec (I expect 1.2-1.6 M/sec). The 4 CPUs are idle, no RAM shortage. A trace of one of the oracle processes performing the load, shows that he mostly asks of time (gettimeofday). Before dropping the main table, load performance was 24-40 K/sec.
>
> How is this data loaded? Third party program or some form
> of Oracle untility?

sqlldr conventional path, commit every 256 rows.

> Do you drop all indexes on table before load?
Yes
>
> Partitioning may help but without knowing a lot more
> about the specifics of your application it's difficult
> to suggest any given partitioning rule. Date is obvious,
> but things might not be that linear.

I got the idea with partitioning in order to scale up the application. Within 2 years total size should be 10 TB, one main table is impossible to handle.

>
> Anyways, you have some other problem now: there is NO WAY
> that loading 17 million rows can take 5 days unless
> there is something else very serious going on...

>
> Cheers
> Nuno Souto
> wizofoz2k_at_yahoo.com.au.nospam
Received on Wed Aug 06 2003 - 04:26:59 CDT