Re: Performance tuning

Azhar,

Just two thoughts off the top of my head to improve loading of records: 1. Put database in noarchivelog mode to avoid redo generation (if possible).
2. Drop all indexes on target tables (if possible), then load data, then recreate all indexes on target tables.

Jim

Jim Hawkins
Lead SAPR/3 Oracle Database Administrator MEMC Electronic Materials, Inc.
600 Pearl Drive
St. Louis, MO 633376
9636) 474-7832
jhawkins_at_memc.com (work)
jhawkins_at_primary.net (home)

>
> HI ALL,
> We have to load almost 3 millions records of average row size of 150
bytes.
> We are importing data using sqloader with ROWS=4000 and bindsize=8450000 .
> We have adjusted the rollback segment to almost 10 m with 8 extents
enough
> for single transaction size and considering 30% rollback overhead. We
> adjusted the OPTIMAL TO 10 M to have avoid rollback extension
> Rollback segment, databuffer cache have hit ratio of 100%.
>
> The loading was fast only for first 10 commits but then it slowed like
> snail. LOADING TOOK 22 hours in the first run on ORACLE8i NT4 128 megs RAM
> .
> SGA figures in M :
> NAME VALUE
> -------------------- ---------
> Fixed Size .0676384
> Variable Size 239.02734
> Database Buffers 39.0625
> Redo Buffers 7.8203125
> ---------
> sum 285.97779
> ( we can't use direct path due to functions in sqlldr controlfile). .
> Couldn't figure out the bottleneck yet.
> Any ideas.
> TIA
> Azhar Siddiq,
> DBA
> LMK Resources
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: azhar_at_mathtech-pk.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>
>

-- 
Jim Hawkins
Lead SAPR/3 Oracle Database Administrator
MEMC Electronic Materials, Inc.
600 Pearl Drive
St. Louis, MO  633376
9636) 474-7832
jhawkins_at_memc.com (work)
jhawkins_at_primary.net (home)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jim Hawkins
  INET: jhawkins_at_primary.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).