Re: Oracle on NT and Physical File Caching

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