RE: Performance tuning

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 30 May 2001 07:55:32 -0700
Message-ID: <F001.003137A6.20010530065647@fatcity.com>

I would not recommend using optimal as it will cause the segment to have to shrink. Your better of doing it manually on lulls of activity and size the segments bigger if need be. Segments should not be frequently shrinking.

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

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

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------