Oracle and multiple processor machines.

[Yogeeraj_1]

hi,

In 9i, we track CPU by querying in the V$SQL view -- before that, cpu/query doesn't exist.

FYI.
Oracle7.1 introduced parallel query in 1994. You do not need more then one CPU, you frequently use a degree of parallelism greater then the number of cpus on the system. Consider this -- when doing an index build you read alot, sort alot, write alot. When you are reading, you are not using the CPU (hopefully you don't have ide drives!). So, if there were another process sorting -- that would be great. Likewise when you are sorting, you are not using the disk -- if someone else could be reading or writing, that would be great.

The OPERATING system is responsible for scheduling
the processes. It'll use whatever it feels like. Don't forget, when they are
doing IO, they are not on a CPU -- a full scan is not a CPU bound thing, its IO
bound. For many operations, you don't expect your CPU monitors to light up like a
christmas tree or anything.

For me, A process is a process is a process. On a single CPU machine, you can have hundreds of processes running. On a 10 cpu machine you can have hundreds of processes. A cpu is a resource (like a printer, or disk space) that a process will reques an the OS will coordinate access to.


IMHO, Oracle does not care about the number of CPUs available. It just does an abstraction and uses them.

I often sit down and watch the Glance/Top outputs and my Oracle processes running. You do see both CPUs running oracle processes.

hope this helps!

regards
Yogeeraj

[Brian Crabtree]

Steven,

Ian is correct. Oracle will use all of the available processors on the system. Each of the database processes will open up with its own process, which will be assigned on the server to a process through the normal processes. The parallel_max_servers does not matter in this case, as this only relates to the parallel query option.

Thanks,

Brian

[Yogeeraj_1]

hi again,

Brian said it above using much simpler terms. parallel query options has nothing to do with available CPUs.

thanks you.

regards
Yogeeraj

[Steven E. Protter]

I'm going to get someone to run some oracle jobs and start watching on glance. That was a good idea.

Where did this thread come from?

It came from our D380 with 11.00 32 bit 1 Gig of Ram beating our rp5450 with 2 Gig of Ram and 11.11 64 bit in a head to head, same test, same data same scripts.

Obviously I have a performance problem going on here. My dba felt that the second cpu might not be getting used. Tests thus far seem to show that it is.

I will sort of hold this thread for a bit so I can run my tests and collect my data. Then, I'm going to post up kmtune and init.ora and let the Oracle vetrans see if they can find something. I may just open a new thread.

Thanks for your help.

SEP

Thread is still open for business.

[Brian Crabtree]

Steven,

If you are going to take that route, go ahead and turn sql_trace = true for your process and run it through tkprof. That will tell you what queries used the most CPU and I/O (normally), and will give you an explain plan.

Please note that the explain plan is not based on the time of the query, but the time that the tkprof is run, and also that the explain plan uses bind variables which may not be representative of your data.

Thanks,

Brian

[Yogeeraj_1]

hi,

The Instance Activity Stats section of a STATSPACK reports can also help in this analysis.

Look for:
CPU used by this session
CPU used when call started

In the 9i version, this appears in the "Top 5 timed events" section where the total elapsed time is computed using the total wait time for all events added to the total CPU time used for the interval

i.e.
total elapsed time = total wait time + total CPU time

Normally, we reduce Oracle CPU usage by tuning SQL.


hope this helps too!

regards
Yogeeraj

[R. Allan Hicks · Accepted solution]

The parameter cpu_count is described as follows:

Description : The number of CPUs available to Oracle (used to
calculate other parameter values). Do not change this value.
Range of Values: 0 - unlimited.
Default Value : Automatically set by Oracle



If you log in to sqlplus and

show parameter cpu_count

You should see two. I have four processors and see four.

I have heard (and therefore can be wrong) that you can alter this value to reduce the number of cpus available to oracle, but as the note says that's not recommended.


I don't have glance, but I use top and look at the CPU column. Oracle will take everything available to it and then some.

-Good Luck