Thanks for the question, Tiberiu.
Asked: July 12, 2016 - 9:43 am UTC
Last updated: July 12, 2016 - 2:05 pm UTC
Version: 11.2.0.1.0
Viewed 10K+ times! This question is 
You Asked
Hi
I am running Oracle Database 11g Release 11.2.0.1.0 (Standard Edition) - 64bit Production on Windows 2008 R2, on a physical machine with a 4 core CPU.
Whatever I do, the maximum CPU utilization of oracle.exe process is 25%. This is annoying because some database tasks (queries, PL/SQL blocks etc.) take quite a long time and I guess with 100% CPU tasks would finish faster.
I tried to stop all other processes thus oracle.exe being the only process with CPU > 0%, so the total load on the processor was 25%.
I set the CPU_COUNT to 4 but still no effect.
I have not created any resource plan nor altered the number of available CPUs. As far as I know there is no restriction on OS side.
Unfortunately all the posts I found were about decreasing the available CPU not about increasing it, therefore my question is: what can I do to determine Oracle to use 100% of the CPU?
Below some more info from system catalog.
Thank you.
select * from V$OSSTAT where stat_name like '%CPU%';
"STAT_NAME" "VALUE" "OSSTAT_ID" "COMMENTS" "CUMULATIVE"
"NUM_CPUS" "4" "0" "Number of active CPUs" "NO"
"RSRC_MGR_CPU_WAIT_TIME" "0" "14" "Time (centi-secs) processes spent in the runnable state waiting""YES"
"NUM_CPU_CORES" "4" "16" "Number of CPU cores" "NO"
"NUM_CPU_SOCKETS" "1" "17" "Number of physical CPU sockets""NO"
select name, sub_plan, mgmt_method, pdl_method, num_plan_directives from RESOURCE_PLAN$;
"NAME" "SUB_PLAN" "MGMT_METHOD" "PDL_METHOD" "NUM_PLAN_DIRECTIVES"
"DSS_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""8"
"ETL_CRITICAL_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""8"
"MIXED_WORKLOAD_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""6"
"ORA$AUTOTASK_SUB_PLAN" "1" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""3"
"ORA$AUTOTASK_HIGH_SUB_PLAN" "1" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""4"
"DEFAULT_MAINTENANCE_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""4"
"DEFAULT_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""4"
"INTERNAL_QUIESCE" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""2"
"INTERNAL_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""1"
"APPQOS_PLAN" "0" "EMPHASIS" "PARALLEL_DEGREE_LIMIT_ABSOLUTE""7"
select name, value, isdefault, ismodified from v$parameter where name like '%cpu%';
"NAME", "VALUE","ISDEFAULT","ISMODIFIED"
"cpu_count", "4","FALSE","FALSE"
"resource_manager_cpu_allocation","4","TRUE","FALSE"
"parallel_threads_per_cpu", "2","TRUE","FALSE"
and Chris said...
If you want your code to run faster, you're asking the wrong question! Instead of "how can I get this to use more CPU?" you should ask "how can I get the code to do less work?"
To figure this out, profile your code. Find what it's spending most of its time doing. Then change the code so it does less of these things.
The hierarchical profiler will show you what your PL/SQL is doing:
https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1 If you want to understand what your SQL is doing, you need an execution plan:
https://blogs.oracle.com/sql/entry/how_to_create_an_execution If you need help analyzing the output from these, please post a new question with details of what you're doing, the code and output from these.
To your question about Oracle only using 25% CPU:
A single session in the database will only use one core. So if you're only running one thing, 25% utilization is the maximum you would expect.
To get a single session to use more, you need to use parallel processing:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel.htm#VLDBG010 But you should look into methods for reducing the work
first. Save parallel processing for cases when you've eliminated everything you can and performance is still "too slow".
Note: You should leave CPU_COUNT at zero:
"If CPU_COUNT is set to 0 (its default setting), then Oracle Database continuously monitors the number of CPUs reported by the operating system and uses the current count."
http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams040.htm#REFRN10023