Re: problem with mult datafiles and local mng tblspaces

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Sat, 20 Dec 2003 15:59:24 -0800
Message-ID: <F001.005DA8DF.20031220155924@fatcity.com>

John Blake <jblake_at_arrow.com> wrote:
> I have a 9.2.0.3 DB running on AIX 4.3.3
> An application running on W2K using SQLLDR to load
> approx 6g of data
> using local managed tablespaces
> ie..
> CREATE TABLESPACE PARENTDAT
> DATAFILE '/vol01/oradata/e450dev/parentdat01.dbf'
> SIZE 2000M REUSE,
> '/vol01/oradata/e450dev/parentdat02.dbf' SIZE 2000M
> REUSE,
> '/vol01/oradata/e450dev/parentdat03.dbf' SIZE 2000M
> REUSE,
> '/vol01/oradata/e450dev/parentdat04.dbf' SIZE 2000M
> REUSE
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
> NOLOGGING
> ONLINE;
>
> It was taking 8 hours to load this data
> 1) copied the data to the AIX box and ran SQLLDR
> local to the DB and
> received similar results
> 2) changed the tablespace to contain 1 8g datafile
> 3) ran SQLLDR from the W2K machine ..completed in
> 30minutes
>
> Also
> ran the same test on AIX 5.1 running oracle 9.2.0.3
> and obtained the same
> results.
>
>
> I really would like to be able to create the
> tablespaces with multiple data
> files but it seems whenever I add a datafile the
> performance of the load degrades.
>
> Any ideas??
>
> Thanks in advance
> John
>
> Author: John Blake
> INET: jblake_at_arrow.com
>

John,

any chance that you can create those 4 datafiles on 4 different mount points and re-test?

at first glance, without knowing a thing about AIX, I'd say that you are having thrashing problems across the multiple datafiles.

As Jared indicated, the 10046 trace should show you each wait. Usually, you'll see small waits when working on a drive track, and then a larger wait when a seek across tracks occurs. The waits should list what file# is being written to (or read from).

This may sound funny, but what does the server sound like when this load is running? Its pretty easy to tell the difference between a drive writing serially, and one that is covering 8 GB of surface area every second.

How many segments are you loading into, are you sure that 1 MB extents are a good size?

I'm a fan of keeping files to 1 GB, so that compression and transfer aren't such a pain, but then again the largest db that I work with is under 100 GB. A File size of 2000 MB scare me, bugs in the past with 2 GB files.

Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Drake
  INET: discgolfdba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat Dec 20 2003 - 17:59:24 CST