Re: Oracle 9i tunning

It's a pity about formatting in news group mail.

According to my reader, the fastest, but noisiest, option is the smallest sort_area_size. I can live with a little noise (or perhaps I could suppress it with sqlplus -s ?)

Nice post ...

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Marc Blum wrote in message ...


>hmmm...several months ago I made some tests with 8.1.7.3 on NT4, Intel box

with


>two PIII 1GHz and 1GB memory. Build several indexes on a 74.000.000 rows

table.


>It was pretty cool to *hear* the sorting:

>

>SORT_AREA_SIZE = 2M

>

>rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

>

>

>SORT_AREA_SIZE = 10M

>

>

>rrrrrrrrrrrrr.............rrrrrrrrrrrrr.............rrrrrrrrrrrrr..........

...rrrrrrrrrrrrr.............


>

>

>SORT_AREA_SIZE = 100M

>

>

>...........................................................................

.......................


>

>

>

>:-)

>

>

>

>

>

>On Tue, 25 Jun 2002 06:30:01 GMT, Herman de Boer <h.de.boer_at_itcg.nl> wrote:

>

>>Hello,

>>

>>please note that the oracle internal sorting routines in 9i have been

>>rewritten. Before 9i, for very large sorts, I personally never set

>>sort_area_size higher than 2Mb - agree with sg. But, in 9i, more

>>memory *does* shorten the sorting time.

>>

>>

>>Kind Regards,

>>

>>Herman de Boer

>>sr consultant

>>IT Consultancy Group bv.

>>

>>sg wrote:

>>

>>>hi

>>>

>>>I tested it in Oracle 8.1.7, Tru64 5.0. I did the test when I had to

>>>rebuild around 120 indexes in two databases (same database but different

>>>  for different purpose). Tested one with 100MB sort_area_size and the

>>>other with 50MB and the time was not much difference, one took around 6

>>>hours, the other 6 hours 20 minutes or so

>>>

>>>

>>>

>>>Howard J. Rogers wrote:

>>>

>>>> I'd be interested in the details of those tests. Even operating system

and


>>>> version would be a start.

>>>>

>>>> Regards

>>>> HJR

>>>>

>>>> "sg" <s4v4g3_at_europe.com> wrote in message

>>>> news:3D1775F6.6070604_at_europe.com...

>>>>

>>>>>hi

>>>>>

>>>>>dont set sort_area_size too big, I have tested and setting it higher

>>>>>than 50MB does NOT get you extra benefits

>>>>>

>>>>>Parallelism normally 2xCPU does fine for me, may be you can try it

>>>>>

>>>>>Joe wrote:

>>>>>

>>>>>

>>>>>>Hi,

>>>>>>

>>>>>>Environment: Oracle 9i running on Windows 2000.

>>>>>>Hardware:    Dual AMD, 2 Gig Ram, RAID 5 disk array (ATA/100)

>>>>>>Application:    Datawarehousing

>>>>>>

>>>>>>I am process of evaluating Oracle 9i and SQL server for a

datawarehouse


>>>>>>application and have some questions on how to tune 9i to maximize

>>>>>>performance on index building and sorting in general.

>>>>>>

>>>>>>There is a large fact table (80 million rows) that I am testing in

both


>>>>>>environments (SQL and Oracle).  I am attempting to build an index on

the


>>>>>>table want to insure that I have things set up so that Oracle will use

>>>>>>

>>>> as

>>>>

>>>>>>much memory and processor as possible.  When building indexes Oracle

is


>>>>>>using about 400MB or the 2 Gigs of RAM and only uses 10%-15% of one

>>>>>>processsor and 0% of the other.

>>>>>>

>>>>>>Here is what I have done so far.

>>>>>>    - Created a temporary 4GB tablespace and set it to default for the

>>>>>>

>>>> user

>>>>

>>>>>>account I use.

>>>>>>     - increased the sort_area_size for the session to 1GB.

>>>>>>     - specify nologging and paralell 2 in the create index DDLs

>>>>>>

>>>>>>Is this the best I can do to maximize sort procesess?

>>>>>>

>>>>>>Joe

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>>>

>>>>

>>>>

>>>

>

>

>Marc Blum

>mailto:marc_at_marcblum.de

>http://www.marcblum.de