RE: Performance tuning

Indeses are not critical for the application to run..It just helps them run faster at times. You could build you indexes with the "no logging" or "unrecoverable" option and that will save on the archivelog creation. Just be sure to take a backup after the indexes are rebuilt because you will not have archived the index info in archivelogs for recovery. I do it all the time for my files that have a large data load each day and it saves log writing time and log space. ROR mª¿ªm

>>> Alex.Hillman_at_usmint.treas.gov 05/30/01 01:21PM >>> Why putting DB in noarchivelog will avoid redo generation? There will be no archiving, but redo will continue to be generated.

Alex Hillman

-----Original Message-----
Sent: Wednesday, May 30, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Hillman, Alex
  INET: Alex.Hillman_at_usmint.treas.gov 

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).