Solved

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

Posted on 2009-04-09
10
2,685 Views
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?
0
Comment
Question by:yylin
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24109296
Please add, which edition of Oracle?
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 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,
0
 

Author Comment

by:yylin
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?
0
 
LVL 34

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!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 250 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.

Example

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;

Details:

http://www.akadia.com/services/ora_parallel_processing.html
0
 

Author Comment

by:yylin
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.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 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

http://download.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#64041

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

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

PARALLEL_MIN_PERCENT
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.

OPTIMIZER_PERCENT_PARALLEL

PARALLEL_ADAPTIVE_MULTI_USER
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

PARALLEL
NOPARALLEL
PQ_DISTRIBUTE
PARALLEL_INDEX
NOPARALLEL_INDEX
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 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.
0
 
LVL 47

Expert Comment

by:schwertner
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:
       
   At SQLPLUS
   ==========
   -  SQL>select * from v$option;

        PARAMETER           VALUE
        -------------------------
        Parallel execution   TRUE

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The purpose of this article is to show how we can create Linux Mint virtual machine using Oracle Virtual Box. To install Linux Mint we have to download the ISO file from its website i.e. http://www.linuxmint.com. Once you open the link you will see …
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now