Re: Virtual Memory Setting for Oracle on NT

I've tried to find good tuning info on Oracle/NT, and here's a few things I've found (I'm assuming that the server isn't used for any other purposes):

• Keep your SGA size down to approximately 50% of your installed RAM. Then ensure the SGA is tuned well. One of the biggies to look at is DB_BLOCK_BUFFERS. Make sure it's not some miniscule number (like the default setting in a starter database). Keeping the SGA down to 50% of installed RAM should provide enough headroom for user processes and such.
• If you think Oracle's having a hard time with virtual memory, use NT Diagnostics (winmsd.exe) and look at memory and Paging File usage. Also, use the Performance Meter and keep an eye on things like process/oracle and pagefile usage.

If your SGA is sized well, your pagefile shouldn't even twitch when Oracle's doing some hard work. If performance is still a problem, you may have to throw more memory at the problem (cheap solution), or add more disks and use a RAID solution. Spreading lots of I/O over many disks helps (btw -- do you have IDE or SCSI, and what kind of layout?) Also, what kind of activity is causing the thrashing? Queries? Updates?

Look at SQL executed during heavy thrashing, and run explain plans on them. It's possible you're doing a lot of full table scans. (refer to Oracle docs on explain plans).

Hope this helps...

rockcogar <rock_cogarNOroSPAM_at_my-deja.com.invalid> wrote:

>I have an Oracle 8.1.5 server on a 500 mhz Pentium III with
>320MB of RAM that does a great deal of (slow) disk thrashing on
>most any query.
>
>I wonder if anybody knows where I can find the Oracle
>documentation that describes what are "the best" Virtual Memory
>Setting for Oracle on NT ?
>
>Thanks,
>Rock.
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!
Received on Tue Feb 22 2000 - 08:10:30 CST