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

matti_at_sedonacorp.com (Matthew Inger) wrote:

>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?

It sounds to me that you are simply overloading the system that has dire results. If one query is already struggling, just imagine how two more will worsen the situation. Buffer contention. Vying for CPU resources and memory.

As for what Oracle is doing with the queries - explain plan and check. It has been said more than once in this forum, and I feel it is needs to be repeated every other day. Full table scans (FST) are _not_ necessarily bad! It can be much faster than an index range scan. Sometimes magnitudes faster if the FST is done in parallel. So I will be hesitant to point to FST as the problem, wihout any hard evidence.

My suggestion is not to focus only on CPU utilisation as a measurement trying to determine what is happening - use NT's peformance monitor and at least check for memory and disk contention too.

If the query is complete (or it looks like it), and the Oracle shadow process is not idle, then have a look at that process's details in the V$ session related tables.

--
Billy