Oracle and multiple processor machines.

[Steven E. Protter]

How do I know that oracle is using both processors on a two processor machine?

Is there sql code to check it?

Are there specail entries in init.ora that need to be made?

Is there anything I need to do the HP-UX to make Oracle recognize and take advantage of processor #2?

Points for all efforts, rabbits for full step by step procedures.

SEP

[Bryan D. Quinn]

Hey SEP,

I am not a big Oracle guy, but I do support an Oracle/SAP environment and I beleive there is a setting in the init.ora file.

Hope this helps!
-Bryan

[Bryan D. Quinn]

SEP-

It looks like that setting might be the parallel_max_servers field. There is some reference at that field to number of CPU's

Hope this helps!
-Bryan

[Ian Dennison_1]

SEP,

I did a benchmarking exercise on Oracle 8.1.7.4.0 last year on a Superdome. It used all 32 CPUs that were available.

I believe that under Oracle 8, any use of multiple processors capacity is accomplished by designing the application to be multi-threaded, with different Oracle Processes (PIDS) on different CPUs.

I have noticed that single process queries (really big queries) always tie themselves to a CPU and max it out at 100% until completed.

Share and Enjoy! Ian

[Ross Zubritski]

SEP,

On Oracle 8 and above on an SMP system the software(oracle) will use all available processors without intervention.

Regards.

RZ

[Steven E. Protter]

For a bunny, I'm going to need the actual line from init.ora or a link to the software and documentation at metalink.oracle.com or otn.oracle.com

HP-UX 64 Bit 11.11
Oracle 8.1.7.4.0 64 bit version.

SEP

[James A. Donovan]

From Metalink.

Doc ID: Note:1052756.6
Subject: HOW DOES ORACLE HANDLE LOAD BALANCING ON SMP SYSTEMS WITH MULTIPLE CPU'S?
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 12-JUN-1998
Last Revision Date: 29-APR-1999


Problem Description: ==================== How does Oracle handle load balancing on Symmetric Multi Processor (SMP) systems with multiple CPU's? Search Words: ============= memory, multiple processors, Background processes, scheduler Solution Description: ===================== Any load balancing between processors is handled by the Scheduler, not Oracle. Solution Explanation: ===================== SMP (Symmetric Multi Processor) systems have a common store of memory which is shared by multiple processors. The determination of which processor will handle a specific task is handled by the scheduler. NOTES: ------ 1. This discussion relates to scheduling of the Oracle Background processes, such as pmon, smon, etc. If parallel query is used, specific options may be used to tie a query to a specific processor. 2. Upward scaling in such an architecture tends to be relatively linear on Unix systems.

[Bryan D. Quinn]

Hey SEP,

I beleive this is the line you are concerned with in init.ora:

parallel_max_servers = 5 # SMALL
# parallel_max_servers = 4 x (number of CPUs) # MEDIUM
# parallel_max_servers = 4 x (number of CPUs) # LARGE


-Bryan

[James A. Donovan]

one more Metalink note on Parallel query parameters..

Doc ID: Note:201799.1
Subject: Relevant Parallel Excution INIT.ora Parameters
Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 02-JUL-2002
Last Revision Date: 17-MAY-2003


PURPOSE -------- To provide a list of parameters that are relevant to parallel query, Parallel DML and other operation which can be parallized. We also suggest values for some init.ora parameters to have a good starting point. SCOPE & APPLICATION ------------------- This note is intended for DBAs who are considering using PX Initialization parameters ------------------------- The following parameters are currently affecting the behavior of Parallel Execution in Oracle: FAST_START_PARALLEL_ROLLBACK [NOTE:68932.1] LARGE_POOL_SIZE [NOTE:47334.1] OPTIMIZER_PERCENT_PARALLEL (Obsolete in 9i) [NOTE:39027.1] PARALLEL_ADAPTIVE_MULTI_USER [NOTE:68594.1] PARALLEL_AUTOMATIC_TUNING [NOTE:68595.1] PARALLEL_BROADCAST_ENABLED [NOTE:68596.1] PARALLEL_EXECUTION_MESSAGE_SIZE [NOTE:68598.1] PARALLEL_MAX_SERVERS [NOTE:30790.1] PARALLEL_MIN_PERCENT [NOTE:39034.1] PARALLEL_MIN_SERVERS [NOTE:30791.1] PARALLEL_THREADS_PER_CPU [NOTE:68604.1] PARALLEL_TRANSACTION_RECOVERY [NOTE:68605.1] RECOVERY_PARALLELISM [NOTE:30795.1] REPLICATION_DEPENDENCY_TRACKING [NOTE:47355.1] SORT_AREA_SIZE [NOTE:30918.1] In a Parallel Server or RAC environment, the behavior of parallel query and PDML is affected by the following parameters also: PARALLEL_INSTANCE_GROUP [NOTE:47351.1] PARALLEL_SERVER_INSTANCES [NOTE:68603.1] Values for PX init.ora Parameter -------------------------------- We would suggest the following for a good starting point with PX. Maybe some parameter have to be tuned after a while. set parallel_automatic_tuning=true unset parallel_max_servers unset parallel_min_servers unset parallel_execution_message_size* maybe set parallel_adaptive_multi_user=false** * Machines with enough memory can afford to have higher values here like 8k and 16k but the performance "knee" is at 8k, so higher values don't give as much benefit. **This parameter defaults to false unless parallel_automatic_tuning = true. Users may want to set it to false if they wish to perform first come first served resource allocation and/or have a queuing mechanism which submits jobs to the db. REFERENCES ----------- Oracle8i Migration Release 3 (8.1.7) Part Number A86632-01 Oracle9i Database Migration Release 2 (9.2) Part No. A96530-02

[Steven E. Protter]

My dba is investigating.

I will try and get points assigned by close of business, but it depends on the dba.

SEP

[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