Re: NT/ORACLE/RAID and Performance PAIN

I ran into the same thing installing Oracle 7.3.4 with a 4 gig database on an NT server 4.0 sp 3 with 256M of memory. Everything was fine except that full table exports and database backups ate all of the physical memory and never gave it back. It turns out that this is a known problem with sp3 and has been corrected in sp4.(??)

The full article on the Microsoft site is Q163880. Summary is that the CreateFile API has a flag FILE_FLAG_SEQUENTIAL_SCAN. If the flag is not set, your physical memory will be eaten by the file copy operation.

The COPY command of cmd.exe does not set this flag. If you have a file which you are creating via this command which is significantly larger than the physical memory, such as those generally created by Oracle backup and export, the cache will grow very large and the process working sets will shrink, killing performance.

The recommended solution is to apply sp4. I'm researching known sp4 problems before I do this.

In article <36C7F3D9.8A1DFCF8_at_rdbms.freeserve.co.uk>,   "Christopher M. Day" <christopher.day_at_rdbms.freeserve.co.uk> wrote:
> Ross,
>
> You didn't mention you filesystem type?(NTFS or FAT).
>
> I would load the Oracle Perfmon extentions.
>
> If you are being constrained to RAID5 by none dba's then I would explain
> the overhead in using RAID5. This usually sways them.
>
> I would monitor the disk queues to illustrate the bottleneck on the disk
> controller.
>
> To bump the performance on NT you can disable any unrequired services
> and set the Control_Panel->Network->Services->Server property to
> 'Maximize performance for Network Apps' (This will reduce the amount of
> memory allocated to the disk cache, which Oracle doesn't use)
>
> Check the sql statements that are being executed, might be worth looking
> at SQLab www.quests.com.
>
> Chris.
>
> Ross Mohan wrote:
> >
> > Folks,
> >
> > I don't know if there is list expertise and/or interest on this, but
> > I am plumbing for both...here goes:
> >
> > I am running 7.3.3 on NT4 sp3.
> >
> > Adaptec 7895 dual port controller. Hardware RAID 05 ( I know,
> > I know, not something I can change ).
> >
> > Truly soft disk performance!
> >
> > NT Perfmon shows, among other things, many Asynchronous and Deferred
> > Procedure Calls.
> >
> > Questions:
> >
> > How can I see what is really going on?
> >
> > What tweaks --- if any -- can I do inside NT to bump performance
> > ( N.B. no "NT is self tuning" responses are needed, thanks! ;-> )
> >
> > What tweaks -- other than dumping RAID 05 -- can I do on the hardware
> > RAID side?
> >
> > I am pulling my hair out.
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Mar 09 1999 - 18:45:59 CST