Can Oracle Database Release 2 (11.2.0.3) Properly Count Cores? No. Does It Matter All That Much? Not Really..

…and with a blog post title like that who would bother to read on? Only those who find modern platforms interesting…

This is just a short, technically-light blog post to point out an oddity I noticed the other day.

This information may well be known to everyone else in the world as far as I know, but it made me scratch my head so I’ll blog it. Maybe it will help some wayward googler someday.

AWR Reports – Sockets, Cores, CPUs
I’m blogging about the Sockets/Cores/CPUs reported in the top of an Oracle AWR report.

Consider the following from a Sandy Bridge Xeon (E5-2680 to be exact) based server.

Note: These are AWR reports so I obfuscated some of the data such as hostname and instance name.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
SLOB          3521916847 SLOB                1 29-Sep-12 05:27 11.2.0.3.0  NO

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
NNNN             Linux x86 64-bit                   32    16       2      62.87

OK, that’s simple enough. We all know that E5-2680 is an 8-core part with SMT (Simultaneous Multi-threading) enabled. Further, this was a 2U 2-socket box. So, sure, 2 sockets and a sum of 16 cores. However, with SMT I get 32 “CPUs”. I’ve quoted CPU because they are logical processors.

The next example is a cut from an old Harpertown Xeon (Xeon 5400) AWR report. Again, we all know the attributes of that CPU. It was pre-QPI, pre-SMT and it had 4 cores. This was a 2-socket box—so no mystery here. AWR is reporting 2 sockets, a sum of 8 cores and since they are simple cores we see 8 “CPUs”.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
XXXX          1247149781 xxxx1               1 27-Feb-13 11:32 11.2.0.3.0  YES

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
xxxxxxxx.mmmmmm. Linux x86 64-bit                    8     8       2      62.88

Now The Oddity
Next I’ll show a modern AMD processor. First, I’ll grep some interesting information from /proc/cpuinfo and then I’ll show the top of an AWR report.

$ cat  /proc/cpuinfo | egrep 'processor|vendor_id|model name'
processor       : 31
vendor_id       : AuthenticAMD
model name      : AMD Opteron(TM) Processor 6272

$ head -10 mix_awr_16_8k.16.16

WORKLOAD REPOSITORY report for

DB Name         DB Id    Instance     Inst Num Startup Time    Release     RAC
------------ ----------- ------------ -------- --------------- ----------- ---
XXXXXX         501636137 XXXXXX              1 24-Feb-13 12:21 11.2.0.3.0  NO

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
oel63            Linux x86 64-bit                   32    16       2     252.39

The system is, indeed, a 2-socket box. And cpuinfo is properly showing the processor model number (Opteron 6200 family). Take note as well that the tail of cpuinfo output is CPU 31 so the Operating System believes there are 32 “CPUs”. However, AWR is showing 2 sockets, a sum of 16 cores and 32 CPUs. That’s where the mystery arises. See, the Operton 6200 16-core parts (such as the 6272) are a multi-chip module (MCM) consisting of two soldered dies each with 4 “bulldozer modules.” And never forget that AMD does not do multithreading. So that’s 2x2x4 cores in each socket. However, AWR is reporting a sum of 16 cores in the box. Since there are two sockets, AWR should be reporting 2 sockets, a sum of 32 cores and 32 CPUs. Doing so would more accurately follow the convention we grew accustomed to in the pre-Intel QPI days—as was the case above with the Xeon 5400.

In summary, none of this matters much. The Operating System knows the cores are there and Oracle thinks there are 32 “CPUs”. If you should run across a 2-socket AMD Operton 6200-based system and see this oddity, well, it won’t be so odd any longer.

Multiple Multi-Core Modules on Multiple Dies Glued Together (MCM)?
…and two of them in one system? That’s the “N” In NUMA!

Can anyone guess how many NUMA nodes there are when a 2-Socket box with AMD 6272 parts is booted at the BIOS with NUMA on? Does anyone know what the model is called when one boots NUMA x64 hardware with NUMA disabled in the BIOS (or grub.conf numa=off)? Well, SUMA, of course!

About these ads

13 Responses to “Can Oracle Database Release 2 (11.2.0.3) Properly Count Cores? No. Does It Matter All That Much? Not Really..”


  1. 1 goryszewskig March 6, 2013 at 2:50 am

    Kevin, just want to know what do You think about Oracle on Power7 processors can You confirm that modern Intel cpu are competitive ?
    Regards
    GregG

    • 2 kevinclosson March 6, 2013 at 9:04 am

      @GregG
      I admire Power-based servers and P7 as well. I don’t, however, get any access any such systems. Call it “admiration by specification” perhaps :-)

      I think a stroll down SPECint lane might prove interesting for you regarding apples-apples comparisons between modern Xeons and Power. Personally, I’d like to see a Power7 DB2 (9.7) TPC-C number. I suspect, however, that IBM will not do a 9.7 DB2 number and will instead publish Power 7+ with DB2 version 10 to avoid comparisons to their x64-based products. Interestingly, the most recent DB2 9.7 result was System x with E5-2690 but running the database in a VM (KVM). That could be a “sandbag” move to avoid a direct comparison between Xeon and P7 should there be a forthcoming P7 Xeon-based TPCC result. Just guessing.

      Answer: They are good CPUs but I don’t know how good :-)

  2. 3 Johnny March 6, 2013 at 8:56 am

    Please, let me know what are your considerations about Oracle database running on top of a Simultaneous Multi-threading core cpu. In the past people talked about underuse of this feature (bad use). Is it worth use?
    Thanks, your blog is awesome.

    • 4 kevinclosson March 6, 2013 at 9:10 am

      @Johnny,

      I recommend leaving it on. I’ve never yet detected a use case where SMT harmed Oracle except for edge cases at very low levels of CPU utilization and I have a blog post in the queue on that matter. Don’t confuse SMT with that old Netburst HT stuff. Wow, that was bad technology. Sorry, it’s true. Most people turned that old HT off.

      Now, having said all that, the HPC community always turns of SMT but that is because of the sorts of workloads they run. Very homogenous, symmetrical, parallel instructions with very consistent memory access patterns. A workload like that does not favor SMT. I spoke a few words on the matter in my Oaktableworld 2012 preso:

      http://kevinclosson.wordpress.com/2013/03/03/my-oaktable-world-2012-video-session-is-now-online/

      • 5 jlskrock February 26, 2014 at 7:15 pm

        Very interesting Kevin,
        But I still have some questions about CPU cores/threads and Oracle:

        I can’t understand why Oracle use CPU_COUNT parameter including the number of CPU threads, even if some tests (like https://communities.intel.com/community/itpeernetwork/datastack/blog/2013/08/23/hyper-threading-on-or-off-for-oracle) show that at some specific scenario a gain of about 30% is possible. Would be a bad idea change this parameter to number of CPU cores instead?

        In some versions of OEM/Grid Control its possible to choise between CPU cores or CPU threads as threshold in the Average Active Sessions screen, if the workload is not CPU bound I think that does not make sense to use the thread option, does? It would not be artificially inflating the avaiable resources?

        Thanks, sorry my english

        • 6 kevinclosson February 27, 2014 at 9:42 am

          @jlskrock : I do not recomment setting cpu_count unless it is for lab purposes such as overriding Oracle’s minimum size db_cache_size as I sometimes do with SLOB on *really* small systems such as SoC with db_cache_size=10M.

          Note: this post is about AMD which has no SMT…only cores.

  3. 7 hourim March 7, 2013 at 7:10 am

    Hi Kevin

    As far as it concerns number of CPU /Cores/Sockets reported into the AWR report I would like to ask you a question that is intriguing me when analyzing CPU time in Top 5 Timed Events. When you want to calculate the available CPU times will you be considering this formula :

    Available CPU time = number of CPU x duration of the AWR report

    Or this one:

    Available CPU time = number of CPU x number of Cores x duration of the AWR report

    Thanks

    Mohamed Houri
    http://www.hourim.wordpress.com

    • 8 kevinclosson March 14, 2013 at 11:58 am

      Hello Hourim,

      There is N seconds of CPU per second where N is the number of logical CPUs reported in AWR. So, for example, there is 32 CPU seconds per second in the following system.

      Host Name Platform CPUs Cores Sockets Memory(GB)
      ---------------- -------------------------------- ---- ----- ------- ----------
      XXXX Linux x86 64-bit 32 16 2 62.87

      In the following AWR snippet I have 15.1s of DB CPU per second on an E5-2680 + SMT (32 logical CPUS). Why only 15.1? Well, this was a SLOB run with only 16 sessions so the max would be 16.

      Load Profile Per Second Per Transaction Per Exec Per Call
      ~~~~~~~~~~~~ --------------- --------------- ---------- ----------
      DB Time(s): 15.1 292.1 0.00 42.85
      DB CPU(s): 15.1 291.5 0.00 42.76

  4. 9 Dontcheff April 4, 2013 at 7:21 pm

    Hi Kevin,

    What do you get and what do you think of this query (run it against the “odd” database)?

    SELECT
    MAX(DECODE(STAT_NAME,’NUM_CPUS’,VALUE,1)) CPUS,
    MAX(DECODE(STAT_NAME,’NUM_CPU_CORES’,VALUE,1)) CPU_CORES,
    MAX(DECODE(STAT_NAME,’BUSY_TIME’,VALUE,0)) BUSY_TIME,
    MAX(DECODE(STAT_NAME,’IDLE_TIME’,VALUE,0)) IDLE_TIME,
    (MAX(DECODE(STAT_NAME,’BUSY_TIME’,VALUE,0))/MAX(DECODE(STAT_NAME,’NUM_CPUS’,VALUE,1)))/24/
    3600/100 BUSY_DAYS,
    (MAX(DECODE(STAT_NAME,’IDLE_TIME’,VALUE,0))/MAX(DECODE(STAT_NAME,’NUM_CPUS’,VALUE,1)))/24/
    3600/100 IDLE_DAYS
    FROM
    V$OSSTAT;

    Best,
    Julian

    • 10 kevinclosson April 10, 2013 at 8:56 am

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options

      SQL> @osstat_info

      CPUS CPU_CORES BUSY_TIME IDLE_TIME BUSY_DAYS IDLE_DAYS
      ---------- ---------- ---------- ---------- ---------- ----------
      32 16 1065327 6230355 .003853179 .022534559

      SQL>

  5. 11 george May 2, 2013 at 11:07 pm

    So I’ll ask for a tech explanation, you know I always do…

    Can anyone guess how many NUMA nodes there are when a 2-Socket box with AMD 6272 parts is booted at the BIOS with NUMA on?


  1. 1 Latest data Industry news round up, Log Buffer #310 Trackback on March 8, 2013 at 5:49 am

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




EMC Employee Disclaimer

The opinions and interests expressed on EMC employee blogs are the employees' own and do not necessarily represent EMC's positions, strategies or views. EMC makes no representation or warranties about employee blogs or the accuracy or reliability of such blogs. When you access employee blogs, even though they may contain the EMC logo and content regarding EMC products and services, employee blogs are independent of EMC and EMC does not control their content or operation. In addition, a link to a blog does not mean that EMC endorses that blog or has responsibility for its content or use.

This disclaimer was put into place on March 23, 2011.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,052 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

Follow

Get every new post delivered to your Inbox.

Join 2,052 other followers

%d bloggers like this: