How do I assign more than one CPU core to one Oracle request?

Posted on 2009-04-09
Medium Priority
Last Modified: 2013-12-06
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?
Question by:yylin
  • 3
  • 3
  • 2
  • +1
LVL 40

Expert Comment

ID: 24109293
Yes, if the process is parallelisable.

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.
LVL 40

Expert Comment

ID: 24109296
Please add, which edition of Oracle?
LVL 36

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 750 total points
ID: 24109385
No, you cannot directly configure either Oracle or the O/S to use more than one processor (or core) for a single Oracle query or procedure.  They will always be executed in a single stream.  But depending on what that query or procedure is doing, it is possible for it to use multiple processors *IF* it includes one or more sub-queries of tables that have multiple extents in multiple datafiles on separate physical disks or multiple partitions that are in separate tablespaces on separate disks.

Usually the best way to speed up a long-running query or procedure is to first make sure that all affected tables have up-to-date statistics, then adjust the query/procedure or adjust the index(es) on the table(s) to allow the query or procedure to use indexes efficiently.  If that is not possible, or doesn't help, then consider building a materialized than can be used instead of the table(s) and/or view(s) currently used,
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 24109414
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?
LVL 36

Expert Comment

by:Mark Geerlings
ID: 24109507
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!
LVL 48

Assisted Solution

schwertner earned 750 total points
ID: 24110129
There are many sides of parallel executions.
If you could parallelize a query - thats wonderful.
If you have more cores it will be excellent to use the computing power
over the common RAM (known as SGA plus background processes in Oracle).

Speaking 'Alta Vista' (from the top) Oracle Real Application Cluster is the pick of such architecture .... shared SGA ... many processors ... load balancing

But let us step on the ground.

To parallelize a SELECT statement, the following conditions must be met:

At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions.

If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition.

If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition.

The following two sections explain how the degree of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature.

Setting the Degree of Parallelism

Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules:

Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings.

Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.

You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use the NOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed.


alter table emp parallel (degree 4);
select degree from user_tables where table_name = 'EMP';
select count(*) from emp;
alter table emp noparallel;

SELECT /*+ PARALLEL(emp,4) */ COUNT(*)
FROM emp;



Author Comment

ID: 24117066
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.
LVL 48

Accepted Solution

schwertner earned 750 total points
ID: 24117477
Parallel operations are designed to fully maximize the resources of a machine.
They are designed so that a single user can use all of the disks, CPU, and memory on the machine if this is appropriate. In a data warehouse with lots of data and few users this is something you may want to achieve, but in an OLTP environment it would not make sense.

Therefore one should thoroughly test this change of  CBO approach in a representative test database, and make appropriate changes to one or more of the following which determine the degree of parallelism :-

1) Table definitions using the keyword PARALLEL n

2) Hints

3) PARALLEL_AUTOMATIC_TUNING and associated parameters


Database Parameter Setup for Parallel Execution
Maximum slaves possible per instance. A value of 0 means no parallel query.

Minimum slaves spawned at instance startup. These will remain for the duration of the instance.

If a the optimizer has determined that a query should run in parallel, but at execution time there is insufficient resource available to meet the desired parallel degree then by default, the query will run in serial with no message presented to the user. This could easily result in very long execution times.<Parameter:PARALLEL_MIN_PERCENT> provides the facility to prevent the query running and displays an error if insufficient resource is available. It defines the cut off point for the minimum amount of resource that is acceptable for PX usage. It is expressed as a percentage of the available parallel execution slaves.
If this parameter is not set and resources are unavailable then queries will serialize.
If this parameter is set and the desired percentage of query slaves are unavailable,  then an error (ORA-12827) is raised rather than serializing.


PARALLEL_ADAPTIVE_MULTI_USER, when set to TRUE, enables an adaptive algorithm designed to improve performance in multi-user environments that use parallel execution. The algorithm automatically reduces the requested degree  of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree of the table or hints, divided by a reduction factor.
Example: On a 17 CPU machine the default degree of parallelism could be set to 32. If one user issues a parallel query, that user gets a degree of 32, effectively using all of the CPUs and memory in the system. When a second user enters the system, that user gets a degree of 16. As the number of users on the system increases, this algorithm will continue to reduce the degree until the users are running using degree 1, when there are 32 users on the system.

We have the following hints to influence the use of parallel execution

LVL 36

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 750 total points
ID: 24129297
Yes, there are lots of conditions and restrictions for parallel operations in Oracle.  No, there isn't an easy "go fast" setting you can use in either the O/S or the database to encourage the use of multiple CPU cores for a single Oracle query or process.  It looks like your basic problem is the fact that Oracle's E-Business Suite software is *NOT* designed to be efficient.  (But the Oracle E-Business Suite sales people don't mention that.)  It is designed to be feature-rich, and somewhat flexible so it can accomodate most businesses without customization (at least if you believe how it is advertised).  This means though, that it is *NOT* optimized for any particular business or industry.
LVL 48

Expert Comment

ID: 24401541
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:
   -  SQL>select * from v$option;

        PARAMETER           VALUE
        Parallel execution   TRUE


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This installment of Make It Better gives Media Temple customers the latest news, plugins, and tutorials to make their Grid shared hosting experience that much smoother.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question