Re: Oracle 8.1.6 (.3.7) Using 100% CPU

From: James Antonie Rees <tony_rees_at_dmcwave.com>
Date: Wed, 6 Feb 2002 12:24:03 -0000
Message-ID: <1012998360.5030.0.nnrp-07.d4f0f429@news.demon.co.uk>

Hi there Group.

I run a reporting server for my company and when I first set up the instance I had the same problem. I would ask the question how much memory is installed on this server ? The what are the memory parameters in the init.ora set to and how many users are using the operating system.

What to do is to kick everyone off and to run one query yourself and look at the task manager and note how much physical memory is being used. Now run another query and so on and watch how the memory usage goes up evry time you run another query. I have 2 GIG of memory on my reports server and the following memory paramters set fora max of 10 users here :-

 shared_pool_size = 30000000                                          #

LARGE
SHARED_POOL_RESERVED_SIZE = 15M sort_area_size = 6000000
sort_area_retained_size = 1000000

The last two parameters are important as every user when they log in take up the sort_area_size in memory and if this figure is large with a large number of users then all of your memory will be gone and the o/s will resort to swapping out to disk all the time. Also as a good point until a user logs off they will hang on to all the memory they used so is a good policy to get users to log off or force them to log if not active for a certain time.

Hope some of this helps to resolve your problems regards

Tony Rees
ITD Hamilton Scotland
DMC Stratex Networks

"Matthew Inger" <matti_at_sedonacorp.com> wrote in message news:53480dd8.0202051128.7c1c6f49_at_posting.google.com...
> I am running Oracle 8.1.6.3.7 on a WinNT SP6, 800Mhz machine.
>
> Some of the queries are doing joins between very large tables.
> These queries take a long time, but do eventually finish. However,
> when multiple user connect and try to execute these same type
> of queries at the same time, the Oracle process consumes 100% of
> the CPU and never relinquishes it. I have tried configuring all
> sorts of oracle parameters (sort_area_size, db_block_buffers), etc...
> but nothing seems to help. The tables being queried, for the most
> part have indexes on the columns used to join the tables, so it
> should not be doing full table scans (though sometimes i see
> this anyway, despite the indexes). Has anyone else run into
> this same sort of problem?
Received on Wed Feb 06 2002 - 06:24:03 CST