Re: Oracle on NT and Physical File Caching

Thanks, I ran the Performance Monitor and noticed that there was hardly any I/O. My Processor, however, was running at 100%. I monitored everything related to Oracle and noticed the following:

  redo log space requests was large and increasing   PageFile.sys was small but increasing
  recursive calls/sec was large but not increasing   Data Dictionary Cache getmisses/gets was large but not increasing

I do not understand why the redo log space requests would be increasing, since I am issuing a commit after each insert. Any ideas?

-Nathan

Billy Verreynne wrote:

> Nathan McEachen wrote in message <38DAAFD1.7234AB03_at_integware.com>...
>
> >of about 60 megabytes. When I initially ran the procedure, rows were
> >being inserted into the database at about 200 records a second. I
> >increased my SGA from 50 megabytes to 96 megabytes and records were
> >being inserted at about 8000 a second.
 <snipped>
> >I ran the same procedure the other day and, once again, records were
> >being inserted at about 200 per second. I increased the SGA from 96
> >megs to 128 megs but that had no affect. I ran the NT Task Manager and
> >noticed that only 16 megabytes of my physical memory were being
> >allocated to file caching. Since the file that my PL/SQL procedure is
> >reading is over 60 megabytes, not much of it is being cached.
>
> I think you could be diagnosing the problem incorrectly because of what
> seems to be relatingg symptoms, but are in fact not.
>
> Performance went up from Y to X because you did Z.
>
> Now suddenly performance if back at Y even though you are still doing Z and
> even Z+1. This does not make sense. Doing Z+2 or Z+3 is not going to solve
> the problem.
>
> Maybe doing Z was not the sole reason for the performance increase from Y to
> Z.
>
> Or maybe Z was not even responsible at all!
>
> Or maybe Z was responsible, but is no longer because A happened in the mean
> time that is negating the performance increase that Z has caused.
>
> To trouble shoot performance problems, you need to understand what the
> symptoms are in fact saying.
>
> There is various way to get data faster off disk. Inherently, there is delay
> when reading data from a disk. The CPU needs to wait for the data to arrive.
> So even if disk i/o is only 1%, you can have a very severe disk i/o
> bottleneck.
>
> Let me explain. You want to read 1 million records from disk. Ignore caching
> for the moment. This means that there will be a sum of 1 million "delays" in
> getting the data off the disk. Let's say this delay is 1 something-second
> per i/o. So this will cost us in 1 million something-seconds in total
> elapsed time to complete the read.
>
> How can you make this faster? Using a cache? OK, but even so, it needs to
> still get that 1 million records of the disk. Whether the o/s cache reads it
> (and cache it), or whether you read it - there's still the 1 million
> something-seconds elapsed time.
>
> The only way to make it faster is to use the "bandwidth" from the disk to
> computer better. To reduce the amount of time the CPU has to wait for data.
> This means not doing 1 million reads in serial, but rather doing something
> like 250,000 reads using 5 parallel reading processes. Assuming that the
> pipe from the disks to the computer can handle that (let's say disk i/o %
> rises to 90%), we will now reduce the elapsed time to 250 thousand
> something-seconds.
>
> In Oracle this is called parallel processing - I believe Microsoft uses the
> term "read ahead threads" for this. So having a "disk utilisation" of 1% may
> in fact point to performance problem where a "disk utilisation" of 90%
> points to excellent performance. What is meant with "disk utilisation"?
> EXACTLY. You need to UNDERSTAND the symptoms in order to diagnose the
> problem.
>
> Anyway, the assumption you have made is that disk i/o performance is the
> problem. Quite likely you are right. But first you need to prove it.
> Performance Manager here is a must. Monitor the processes and disks. Check
> the number of physical i/o by NT vs. the number of logical i/o's. What about
> memory usage? Maybe there's a above normal amount of page swapping? Also,
> something must have happened after you have managed to achieve performance
> X. Or something must be different. System performance dropping back to Y
> "just out of the blue" does not happen. Undo Z and check performance.
> Implement Z, then Z+1 and check performance snapshots each time.
>
> The only way you ever get to solve performance problems IMHO is to pop the
> hood and get your hands dirty.. :-)
>
> regards,
> Billy
Received on Fri Mar 24 2000 - 00:00:00 CST