Re: Memory problems, Oracle 10, Win 2003 (32 bit)
Date: Wed, 18 Jun 2008 15:41:31 GMT
Message-ID: <L0a6k.4405$n9.2531@trndny01>
"mike.reid" <chmonkey_at_gmail.com> wrote in message
news:9385d505-f61b-49eb-ac09-b072ebffd8b6_at_j22g2000hsf.googlegroups.com...
> On Jun 15, 1:30 pm, "gym dot scuba dot kennedy at gmail"
> <kenned..._at_verizon.net> wrote:
>> "mike.reid" <chmon..._at_gmail.com> wrote in message >> >> news:5503e537-f8f0-44ad-8fb8-24d4f4924d87_at_y38g2000hsy.googlegroups.com... >> >> >I was hoping to get some assistance from the group.... >> >> > I am working with a client who is running into some memory problems >> > with theirOracledatabase. >> >> > Now I am not an expert by a long shot, but the application vendor has >> > told us that their rule of thumb is to always force a pagefile that is >> > 2.5 times the physical memory (so 10gb in this case). >> >> > My understanding on the subject was that becauseOracleis >> > "Opportunistic" with its memory usage that you actually want to pretty >> > much disable the pagefile altogether so thatOracledoes use it. >> >> > Just taking a look at the server seems to tell thatOracleis using >> > about 800mb of physical and 1.7gb total. Is it reasonable in your >> > opinion to run a test and disable the pagefile all together? >> >> > Any help that anyone can give me would be greatly appreciated! >> >> > Mike >> >> I am not sure what the problem is. You sayOracleis using about half the >> memory in the machine. You say you have memory problems. What is the >> memory problem? Very unclear what problem you are having. >> >> On Windows machines if you allocate more memory than you have RAM then >> you >> will swap some of the memory pages to disk (in the pagefile) If you >> rarely >> access that memory than things might not be too bad. If you keep >> accessing >> memory that has been swapped to disk - and force other pages to be >> written >> to disk - then you can have the machine grind to a halt as it tries to >> use >> disk as if it were RAM. This is true ofOracleusing the memory or Word or >> any program. >> >> Our crystal balls are in the shop, you need to enlighten us as to what is >> going on more specificaly. My first bit of advice would be to NOT >> disable >> the pagefile. >> Jim >
> Thanks for the reply Jim. I agree and I apologize for not being more
> descriptive.
>> - RAID 5 (shared with OS, DB Logs and DB) Write back cache disabled
> - 1gb database
> - 4gb physical memory, 10gb pagefile
> - 20 or so users connecting from Terminal Server to DB server, mostly
> direct calls, some stored procedures
>
> There are some obvious performance improvements to be had with this
> setup. I am not the application vendor, but am just trying to give a
> hand. Performance has recently ground to a halt. There are
> expectations to double the users of the system, the application
> developers are saying that a new server must be purchased now (which
> they do actually need a new sever for other reasons, so I'm not too
> fussed about that). The first thing that we have been told to do is
> create a pagefile that is 20gb in size (8gb mem on the new server x
> 2.5).
>
> The question that I have is (and I do not have a lot of experience
> with Oracle in general):
>
> - Don't you want Oracle to _never_ page to disk? The fact that there
> is 4gb (physical) memory in the old server and that Oracle was only
> using 800mb physical, but 1.7gb total (at the time I was on the
> thing), then is perhaps a course of action to disable the pagefile to
> force Oracle to use only physical memory? As a test at least?
>
> Again, I apologize for the lack of information. I hope this is
> better.
>
> Thanks
> Mike
Mike,
1. Everyone should stop proposing solutions. 2. Determine what the problem is. 3. Measure the largest bottleneck. 4. Consider if you removed that bottleneck then how much would performanceimprove?
For example, I worked on a system written in an interpreted scripting language. The claim by the people who wanted it in C was that C was faster and solve all the performance problems. (never mind it would have taken 10x longer to write the system in C and that we didn't have but a few people who could write quality C code.) The main concern was the calculation time for a pension benefit estimate. On the base system the calculation took 6 seconds. (This was the mid 1980's) The scripting language did have a profiler. So I turned ont he profiler and did the base calculation. I saw that about half the time for the calculation was retrieving data to do the calculation. Therefore if we did the calculation in C and if the new time for the calculation part was 0 seconds then at best a calculation would take 3 seconds. (the database retrieval code was already in C and using bind variables, reusing cursors etc. so it was highly optimized)
The moral of the story is that you need to understand where the time is being taken.
At another job we were able to get 1,500 users running against Oracle with Oracle running on a Windows NT machine. (4 pentium pro 200 mghz CPUs w/ 4 gig of ram and 20 disks in a stripe 4 scsi controllers) With an automated randomized workflow. (simulating a Dr's office, a really big dr's office) We could not get more users on because NT has a per process thread limit and Oracle (8i) on NT used threads for dedicated users. How did we do it? We had a highly optimized application. We looked at where our bottlenecks were and took care of the largest ones first. We read the Oracle Application Developer's guide and used the performance techniques in the documentation.
So take 1 example of a performance problem. It must be specific and reproducable. Investigate what in that process is taking so long. Look at the 1 thing that is taking so long and ask yourself if I eliminated that would the performance problem go away? How about the top 2 things?
If you think it is a memory issue then is the machine swapping? Windows has a bunch of performance counters which ones are outside reasonable bounds. (disk queue lengths?)
I'm sorry Mike, but it sounds like a bunch of people just want to throw
money at a problem before understanding what the problem is. My bet is if
they do the problem will not be solved and they will be out the money. I'm
not against buying a new server especially if the one there is at the end of
life. One thought did cross my mind. I bet they have an active virus
scanner running on that machine. If so turn the damn thing off or at least
exclude the whole Oracle home from it.
Jim
Received on Wed Jun 18 2008 - 10:41:31 CDT