How does cpu_count parameter affect instance?

Parameter cpu_count is determined by Oracle Software when instance started, But we can set it manually. This parameter can affect lots of  hidden initialization parameters,see:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> host cat /proc/cpuinfo|grep processor
processor       : 0
processor       : 1

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     2

create table  cpu_2_parameters as 
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV ('Instance')
 AND y.inst_id = USERENV ('Instance')
 AND x.indx = y.indx;

SQL> alter system set cpu_count=128 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1702887424 bytes
Fixed Size                  2093192 bytes
Variable Size             486543224 bytes
Database Buffers         1073741824 bytes
Redo Buffers              140509184 bytes
Database mounted.
Database opened.

SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     128


SQL> create table  cpu_128_parameters as 
  2  SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
  3   FROM SYS.x$ksppi x, SYS.x$ksppcv y
  4   WHERE x.inst_id = USERENV ('Instance')
  5   AND y.inst_id = USERENV ('Instance')
  6   AND x.indx = y.indx; 

Table created.


SQL> col name for a35
SQL> set linesize 200 pagesize 2000;
SQL> col "while_cpu_count=   2" for a20
SQL> col "while_cpu_count= 128" for a02
SQL>  col "while_cpu_count= 128" for a20
SQL> SELECT a.name,
  2    a.value "while_cpu_count=   2",
  3    b.value "while_cpu_count= 128",
  4    a.describ
  5  FROM cpu_2_parameters a,
  6    cpu_128_parameters b
  7  WHERE a.name=b.name
  8  AND a.value!=b.value 
  9  ORDER BY 1;

NAME                                while_cpu_count=   2 while_cpu_count= 128 DESCRIB
----------------------------------- -------------------- -------------------- --------------------------------------------------------------------------------
__db_cache_size                     33554432             536870912            Actual size of DEFAULT buffer pool for standard block size buffers
_cursor_db_buffers_pinned           445                  841                  additional number of buffers a cursor can pin at once
_db_block_buffers                   67184                126464               Number of database blocks cached in memory: hidden parameter
_db_block_lru_latches               8                    512                  number of lru latches
_enqueue_hash_chain_latches         2                    128                  enqueue hash chain latches
_enqueue_locks                      2300                 2490                 locks for managed enqueues
_flashback_generation_buffer_size   8388608              536870912            flashback generation buffer size
_log_parallelism_max                2                    8                    Maximum number of log buffer strands
_log_simultaneous_copies            4                    256                  number of simultaneous copies into redo buffer(# of copy latches)
_num_longop_child_latches           2                    128                  number of child latches for long op array
_parallel_min_message_pool          903840               1076000              minimum size of shared pool memory to reserve for pq servers
_small_table_threshold              1343                 2529                 threshold level of table size for direct reads
cpu_count                           2                    128                  number of CPUs for this instance
db_cache_size                       33554432             536870912            Size of DEFAULT buffer pool for standard block size buffers
db_writer_processes                 1                    16                   number of background database writer  processes to start
log_buffer                          6104064              136249344            redo circular buffer size
parallel_max_servers                40                   135                  maximum parallel query servers per instance
sga_max_size                        1065353216           1702887424           max total SGA size


SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1016M

It looks like sga may grow while the instance has more processors. Oracle Parallel Servers can has more differ with cpu_count . Sometimes this parameter make performance issues.


Posted

in

by

Tags:

Comments

One response to “How does cpu_count parameter affect instance?”

  1. maclean Avatar

    What is CPU_COUNT, and how should it be set on an HP/UX platform?

    Solution Description
    ——————–

    CPU_COUNT is a parameter that lists the number of CPU’s available to Oracle.
    Oracle uses it to set the default value of the LOG_SIMULTANEOUS_COPIES
    parameter.

    On computers with only 1 CPU, the CPU_COUNT is 0 (zero). On most platforms,
    Oracle automatically sets the CPU_COUNT parameter to the number of CPU’s
    available to Oracle. Do not change the value of this parameter. If
    contention for latches exist, change the value of LOG_SIMULTANEOUS_COPIES to 2
    times the number of CPU’s on the server instead of changing CPU_COUNT.

    This parameter typically is used in the calculation of the default value
    of other parameters in various releases, and to decide if ‘latch spinning’
    is appropriate. Eg: It is used to derive a value for
    .

    Warning:
    On most platforms Oracle automatically sets the value of CPU_COUNT to
    the number of CPUs available to your Oracle instance. Do not change
    the value of CPU_COUNT unless specifically advised to do so.

    If there is heavy contention for redo latches, change the value of
    LOG_SIMULTANEOUS_COPIES to twice the number of CPUs you have. Do not
    change the value of CPU_COUNT.

    See for information on the number of CPU’s Oracle
    thinks you are using.

Leave a Reply

Your email address will not be published. Required fields are marked *