Forum Stats

  • 3,874,528 Users
  • 2,266,750 Discussions
  • 7,911,880 Comments

Discussions

Changing CPU_COUNT

Moonlight
Moonlight Member Posts: 226 Bronze Badge
edited Nov 24, 2022 1:14PM in General Database Discussions

Hi,

I changed cpu_count parameter:

sql> Alter system set cpu_count=16 scope=spfile

sql> Alter system set parallel_threads_per_cpu = 2 scope=spfile

when I check the spfile, the parameters are realy changed... but when I check form database

sql> show parameter cpu_count

12 (the old number) 😵


I understand that cpu_count and parallel_threads_per_cpu are dynamic parameter ... don't need to restart the instance... so what's wrong 😖 ?

My database version is :

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -Version 19.13.0.0.0

Best Answer

«1

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 Red Diamond
    edited Nov 24, 2022 1:25PM

    scope=spfile means make a change in parameter file only. Use scope=both.

    SY.

  • Moonlight
    Moonlight Member Posts: 226 Bronze Badge
  • JohnWatson2
    JohnWatson2 Member Posts: 4,515 Silver Crown
    Answer ✓

    In recent releases, you cannot set cpu_count to greater than the number of CPUs (ie, threads) you actually have. If you do, it gets set back implicitly.

    Moonlight
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 Red Diamond
    lscpu | grep -E '^Thread|^Core|^Socket|^CPU\('
    

    will give you the limits (on linux).

    SY.

    Moonlight
  • Moonlight
    Moonlight Member Posts: 226 Bronze Badge

    Yes, My machine is AIX with 12 CPU !!

    sql> select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat 

    NUM_CPUS 12 Number of active CPUs

    NUM_CPU_CORES 3 Number of CPU cores

    so the parameter parallel_threads_per_cpu = 2 changed but cpu_count still = 12!!

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 Red Diamond

    What happens when you issue:

    alter system set cpu_count=18 scope=both;
    

    SY.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond

    If you want to change the CPU_COUNT to a value larger than the actual number of CPUs that Oracle thinks are available then you can set the hidden parameter "_disable_cpu_check" = true.

    It needs an instance restart after updating the spfile.

    Regards

    Jonathan Lewis

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 Red Diamond

    Jonathan,

    I believe OP will not be able to set cpu count to 16 no matter what:

    CPU_COUNT specifies the number of CPUs available for Oracle Database to use. On CPUs with multiple CPU threads, it specifies the total number of available CPU threads.

    In OP's case there aer 12 CPUs with 3 threads per cpu. My understanding is valid cpu_count in this case must be multiple of 3. That's why I asked OP to test cpu_count=18.

    SY.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Nov 25, 2022 10:20AM

    SY,

    From the OP:

    sql> select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat 
    
    NUM_CPUS 12 Number of active CPUs
    
    NUM_CPU_CORES 3 Number of CPU cores
    

    That looks like Oracle thinks it's got 3 cores with 4 CPUs per core, single-threaded.

    EDIT: Changed my mind - it would be useful to know how many sockets were in use. 3 cores on a single socket is strange (I nearly said odd), multi-core tends to be 2,4,6,8 or 12; but it is AIX which is a law unto itself and has its own concepts of CPU-slicing, virtual CPUs, para-CPUs and who knows what else.

    If we take the numbers at face value the OP's environment does, indeed, seem to have 3 cores, with 4 threads per core. But I'd want to talk to the system admins to find out what virtualization had been put in place.

    Is this a domain running on the same server from a few days ago that reported 2 sockets, 24 cores and 48 CPUs? I hope not, but if it is it's a good demonstration of how tricky AIX can be when you're working from inside the database to figure out what's going on in the hardware.


    From me:

    If you want to change the CPU_COUNT to a value larger than the actual number of CPUs that Oracle thinks are available then you can set the hidden parameter "_disable_cpu_check" = true.

    From an instance of 19.11 I've just started - cut and paste from SQL*Plus session:

    QL> select stat_name, value from v$osstat where stat_name like 'NUM_CPU%';
    
    STAT_NAME                                                             VALUE
    ---------------------------------------------------------------- ----------
    NUM_CPUS                                                                  2
    NUM_CPU_CORES                                                             2
    NUM_CPU_SOCKETS                                                           1
    
    3 rows selected.
    
    SQL> show parameter cpu_c
    
    NAME                                 TYPE        Value
    ------------------------------------ ----------- ----------------------------------------
    _disable_cpu_check                   boolean     TRUE
    cpu_count                            integer     11
    SQL> select count(*) from v$latch_children where name = 'redo copy';
    
      COUNT(*)
    ----------
            22
    
    1 row selected.
    
    
    


    Oracle "knows" from the O/S that I've got two cores that are single-threading to show as 2 CPUs, but I've told it to behave as if it's got 11 CPUs. It doesn't have to be a multiple of the cores or of the CPUs.


    Regards

    Jonathan Lewis

  • Moonlight
    Moonlight Member Posts: 226 Bronze Badge

    With cpu_count=18 didn't work ..