yylin
asked on
How do I assign more than one CPU core to one Oracle request?
Our database server has two CPUs with dual core processor which is running on Red Hat Enterprise Linux 4.7 platform. When we ran one Oracle request, it only take one core of CPU with 98% usage. This request has taken one hour and fifteen minutes to complete. My question are:
1. Can Oracle request not take more than one CPU core to run? so the request can complete in a shorter time.
2. If this is possible, how can I configure on OS or Oracle parameter file to enable it to take two cores of CPU?
1. Can Oracle request not take more than one CPU core to run? so the request can complete in a shorter time.
2. If this is possible, how can I configure on OS or Oracle parameter file to enable it to take two cores of CPU?
Please add, which edition of Oracle?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The query is a standard report from Oracle E-Business Suite 12, which involves the DML like Update, Insert and Select. We are using Oracle Database 10g R2. Do I need to set the parameter for parallel in order to run parallelism?
The parallelism parameter is an indiividual table parameter, not a system-wide parameter.
Welcome to the (wonderful?) world of Oracle E-Business Suite! That product may have some good features, but good performance is *NOT* one of them!
Welcome to the (wonderful?) world of Oracle E-Business Suite! That product may have some good features, but good performance is *NOT* one of them!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Beside the use of parallelism, are there any way to use multiple core of CPU for one Oracle request? As I see there are quite a number of conditions when use for parallelism.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thank you and good luck!
Your question was a starter for me to study the problem.
The rason is that from one side "there are lots of conditions and restrictions for parallel operations in Oracle" (as Markgeer stated and this is the holy truth!) BUT from other hand Oracle charges for every additional core and processor.
The simple question is for which serveces our customer (or employeer) pay?
For services we could not use?
How will we ask our customer to buy multiprocessor systems and pay for
Oracle licensing and after that ... saying that the Oracle works with only single core or processor.
Investigation shows that everything begins here:
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
Since Oracle8, Parallel Execution is part of the Enterprise Edition.
A extra installation is not needed.
To verify that Parallel Execution is part of the Software:
At SQLPLUS
==========
- SQL>select * from v$option;
PARAMETER VALUE
-------------------------
Parallel execution TRUE
Your question was a starter for me to study the problem.
The rason is that from one side "there are lots of conditions and restrictions for parallel operations in Oracle" (as Markgeer stated and this is the holy truth!) BUT from other hand Oracle charges for every additional core and processor.
The simple question is for which serveces our customer (or employeer) pay?
For services we could not use?
How will we ask our customer to buy multiprocessor systems and pay for
Oracle licensing and after that ... saying that the Oracle works with only single core or processor.
Investigation shows that everything begins here:
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
Since Oracle8, Parallel Execution is part of the Enterprise Edition.
A extra installation is not needed.
To verify that Parallel Execution is part of the Software:
At SQLPLUS
==========
- SQL>select * from v$option;
PARAMETER VALUE
-------------------------
Parallel execution TRUE
Oracle will automatically use multi-CPUs if the query and/or tables are tuned & designed for that.
It is possible the case in point is you may have a serial operation. Please describe what your query is doing.