Re: Performance Advice Wanted

In message <4apjgm$9gq_at_ccnet2.ccnet.com> - Rich Holoch <holoch_at_ccnet.com> writes:

:>I have found that server side data loading is almost always better than
:>client server data loading. (Certainly on UNIX). You want to use the
:>fastest processor(s) that you can,

In this case, the faster processor is on the client (newer machine) than the server. The client is a 133Mhz Pentium. The server is 66Mhz. I still believe you are correct though.

:> and even more importantly, you want
:>to tune out all disk I/O contention. I highly recommend Bradmarks
:>DBGENERAL Performance Monitor and SQLTools, Inc. ExplainSQL to help you
:>monitor performance. Oracle Performance Tuning by O'Reilly is a great
:>book . . . .

Got that one. Some many options, so little time...

:>I would also try different array sizes for your bulk data loads (buffer
:>sizes) at the application level (SQL*Loader). You might want to tune
:>DB_BLOCK_BUFFERS, LOG_BUFFER, SORT_AREA_SIZE and SHARED_POOL_AREA in
:>your init.ora file. The goal would be to do more work in memory and less
:>on your PC disks.

I'm working on that. I have been able to increase the DB_BLOCK_BUFFERS by 2.5. I'm working on making sure the SHARED_POOL_AREA area is the optimal size, and that I'm not going to cause too much swapping. (None of the Oracle data areas.)

The problem is that the application is a datawarehouse and the book mentioned above, and others, are more applicable to transaction processing.

:>If you are using a PC server with your database spread across one or two
:>disks, you are probably I/O bound.

I know I'm I/O bound. I have to manage that as I won't get any relief for several months.

The biggest gain we have got was changing the optimizer to a default of FIRST_ROWS. Took one query from hours (not sure how long; the user ran it overnight) to 2.5 minutes!

All-in-all performance is not a problem, more that I now have more options and which one will work best for us.

Thanks for your help.

+---------------------------
| Simon Thompson