Solved

Oracle Processor Affinity and Usage

Posted on 2009-05-07
5
1,616 Views
Last Modified: 2013-12-18
We have just migrated a database to a new server that has two dual-core CPUs. We are noticing that Oracle seems to max out at 25% of the total CPU power on the machine, even when it is running highly compute intensive processes that would max out the CPUs on our prior machine.  The new server is Windows 2008 64-bit and the old server was Windows 2003 64-bit.  Is there any setting in Oracle or the Operating System that would be limiting the CPU usage for Oracle?  It does run on all four CPUs (CPU affinity) but the total usage does not exceed 25%.  
0
Comment
Question by:charding
  • 2
  • 2
5 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 24329904
It could be the way the CPU usage is begin reported by the tool you are using.  25% or a 4 CPU machine is 100% of 1 CPU.
0
 

Author Comment

by:charding
ID: 24330774
I am using Task Manager and realize that 25% of a 4 CPU machine kind of translates to 100% of 1 CPU; however, I am expecting to see Oracle using alot more of the available resources.  I would expect our CPU intensive PL/SQL to take it upto at least 50% (of 4 CPUs or completely using 2 CPUs) but that is not occurring.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 24335058
A single thread process cannot use more than one processor.  You would need to have multiple processes running to see usage on more than one CPU.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24335170
One way is to use partitioned tables and to paralelize the processing.
Partitioned tables are used but this is not the typical case of usage.

An approach is to to invoke many Oracle background processes and and/or assign them to different
CPUs:


Affinity and Priority Settings

The Oracle database supports the modification of both priority and affinity settings
for the database process and individual threads in that process when running on
Windows.
By modifying the value of the ORACLE_PRIORITY registry setting, a database
administrator can assign different Windows priorities to the individual background
threads and also to the foreground threads as a whole. Likewise, the priority of the
entire Oracle process can also be modified. In certain circumstances, this may
improve performance slightly for some applications. For instance, if an application
generates a great deal of log file activity, the priority of the LGWR thread can be
increased to better handle the load put upon it. Likewise, if replication is heavily
used, those threads that refresh data to and from remote databases can have their
priority bumped up as well.
Much like the ORACLE_PRIORITY setting, the ORACLE_AFFINITY registry
setting allows a database administrator to assign the entire Oracle process or
individual threads in that process to particular CPUs or groups of CPUs in the
system. Again, in certain cases, this can help performance. For instance, pinning
DBW0 to a single CPU such that it does not migrate from one CPU to another can
in some cases provide a slight performance improvement. Also, if there are other
applications running on the system, using ORACLE_AFFINITY can be a way to
keep Oracle confined to a subset of the available CPUs in order to give the other
applications time to run.
Both ORACLE_PRIORITY and ORACLE_AFFINITY are described in more
detail in the Windows-specific documentation that accompanies Oracle Database
10g Release 2 on Windows.
0
 

Accepted Solution

by:
charding earned 0 total points
ID: 24335604
Well, it appears that we have figured out how to unless the power of the other CPUs when running some of our compute-intensive PL/SQL code.  We have certain portions of nightly process that do alot of calculations and work on optimizing the purchasing plan for all the items to be purchased within a each vendor.  We parallelized it by submitting each vendor as a job and now we are using 100% of each of the CPUs.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When experiencing some peculiar problem with the functioning of your PC, how many times has it happened that you look for a solution and even google can’t help? It could be that you are one of the only few people on earth who ma…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

920 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

12 Experts available now in Live!

Get 1:1 Help Now