Operating System - HP-UX
1748201 Members
2860 Online
108759 Solutions
New Discussion юеВ

Oracle and multiple processor machines.

 
SOLVED
Go to solution
Steven E. Protter
Exalted Contributor

Oracle and multiple processor machines.

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
16 REPLIES 16
Bryan D. Quinn
Respected Contributor

Re: Oracle and multiple processor machines.

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
Respected Contributor

Re: Oracle and multiple processor machines.

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
Honored Contributor

Re: Oracle and multiple processor machines.

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
Building a dumber user
Ross Zubritski
Trusted Contributor

Re: Oracle and multiple processor machines.

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
Exalted Contributor

Re: Oracle and multiple processor machines.

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
James A. Donovan
Honored Contributor

Re: Oracle and multiple processor machines.

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.
Remember, wherever you go, there you are...
Bryan D. Quinn
Respected Contributor

Re: Oracle and multiple processor machines.

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
Honored Contributor

Re: Oracle and multiple processor machines.

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
Remember, wherever you go, there you are...
Steven E. Protter
Exalted Contributor

Re: Oracle and multiple processor machines.

My dba is investigating.

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

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com