Search

OakieTags

Who's online

There are currently 0 users and 31 guests online.

Recent comments

Oakies Blog Aggregator

Not all Deadlocks are created the same

I've blogged about deadlocks in Oracle at least once before. I said then that although the following message in deadlock trace files is usually true, it isn't always.

The following deadlock is not an Oracle error. Deadlocks of  
this type can be expected if certain SQL statements are      
issued. The following information may aid in determining the 
cause of the deadlock.

So when I came across another example recently, it seemed worth a quick blog post. Not least for the benefit of other souls who hit the same issue (and probably hit Google moments later).

But while it's easy to say - "Hey! Look! I found an exception! Aren't I clever?" - it occurred to me that actually Oracle's capabilities in this area might be underrated by raising the occasional anomaly. Because the truth is

1) In most cases, deadlock errors are down to the way you've written your application or some documented restriction in Oracle. The type of restrictions that you're more likely to hit if you're handling high degrees of concurrency with lots of DDL, parallel query, partition management and the like. Such activities often have unusually restrictive locking requirements and most locking issues can be turned into deadlock issues quite easily if you have a few sessions running concurrently.

2) It's still the case that Oracle will handle the deadlock situation, at least to the extent of rolling back one of the statements causing the issue. (Although, whilst writing this post, I noticed that Jonathan Lewis raised the question of what exactly people mean when they suggest that Oracle resolves deadlock issues.

3) Deadlock trace files are typically very useful and not the most difficult to read. Yes, they tend to use Oracle kernel terminology (not surprising) but I'd wager that most people could have a rough idea of the root cause with some initial analysis and could have a very detailed idea, given more time. Even if you can't decipher the things yourself, it gives Oracle Support detailed information to help root cause analysis.

So, to the particular issue we hit. Towards the end of a data loading process that loads around a billion rows in a short period of time (30/60 minutes that also includes a bunch of surrounding activities), we would hit the occasional deadlock error. Fortunately, the site I'm working at just now has a very enlightened policy towards developer access to the alert log and trace files, so I can do my own initial investigation. On digging out the relevant deadlock trace file, it looked like this (some details changed)

Trace file /app/ora/local/admin/PRD/diag/rdbms/PRD_prod_server/PRD/trace/PRD_dia0_1627682.trc 

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 
ORACLE_HOME = /app/ora/local/product/11.2.0.3/db_1 
System name:    Linux 
Node name:      prod_server.ldn.orcldoug.com 
Release:        2.6.32-220.13.1.el6.x86_64 
Version:        #1 SMP Thu Mar 29 11:46:40 EDT 2012 
Machine:        x86_64 
Instance name: PRD 
Redo thread mounted by this instance: 1 
Oracle process number: 8 
Unix process pid: 1627682, image: oracle@prod_server.ldn.orcldoug.com (DIA0)
*** 2013-01-16 09:09:10.925 
*** SESSION ID:(201.1) 2013-01-16 09:09:10.925 
*** CLIENT ID:() 2013-01-16 09:09:10.925 
*** SERVICE NAME:(SYS$BACKGROUND) 2013-01-16 09:09:10.925 
*** MODULE NAME:() 2013-01-16 09:09:10.925 
*** ACTION NAME:() 2013-01-16 09:09:10.925 
  
------------------------------------------------------------------------------- 
  
DEADLOCK DETECTED (id=0xd0102292) 
  
Chain Signature: 'library cache lock'<='row cache lock' (cycle) 
Chain Signature Hash: 0x52a8007d 
  
The following deadlock is not an Oracle error. Deadlocks of  
this type can be expected if certain SQL statements are      
issued. The following information may aid in determining the 
cause of the deadlock.                                       
  
Resolving deadlock by signaling ORA-00060 to 'instance: 1, os id: 3443329, session id: 161' 
  dump location: /app/ora/local/admin/PRD/diag/rdbms/PRD_prod_server/PRD/trace/PRD_ora_3443329.trc 
  
Performing diagnostic dump on 'instance: 1, os id: 3443222, session id: 779' 
  dump location: /app/ora/local/admin/PRD/diag/rdbms/PRD_prod_server/PRD/trace/PRD_ora_3443222.trc 
  
------------------------------------------------------------------------------- 
    Oracle session identified by: 
    { 
                instance: 1 (PRD_prod_server.PRD) 
                   os id: 3443222 
              process id: 127, oracle@prod_server.ldn.orcldoug.com 
              session id: 779 
        session serial #: 605 
    } 
    is waiting for 'row cache lock' with wait info: 
    { 
                      p1: 'cache id'=0x8 
                      p2: 'mode'=0x0 
                      p3: 'request'=0x5 
            time in wait: 1 min 58 sec 
           timeout after: never 
                 wait id: 1655 
                blocking: 1 session 
             current sql: Begin run_manager_pkg.finalize_all_values_prc(:v0); End; 
            wait history: 
              * time between current wait and wait #1: 0.002176 sec 
              1.       event: 'enq: PS - contention' 
                 time waited: 0.000082 sec 
                     wait id: 1654            p1: 'name|mode'=0x50530006 
                                              p2: 'instance'=0x1 
                                              p3: 'slave ID'=0x2f 
              * time between wait #1 and #2: 0.000013 sec 
              2.       event: 'PX Deq: Slave Session Stats' 
                 time waited: 0.000001 sec 
                     wait id: 1653            p1: 'sleeptime/senderid'=0x0 
                                              p2: 'passes'=0x0 
              * time between wait #2 and #3: 0.000001 sec 
              3.       event: 'PX Deq: Slave Session Stats' 
                 time waited: 0.000002 sec 
                     wait id: 1652            p1: 'sleeptime/senderid'=0x0 
                                              p2: 'passes'=0x0 
    } 
    and is blocked by 
=> Oracle session identified by: 
    { 
                instance: 1 (PRD_prod_server.PRD) 
                   os id: 3443329 
              process id: 134, oracle@prod_server.ldn.orcldoug.com 
              session id: 161 
        session serial #: 247 
    } 
    which is waiting for 'library cache lock' with wait info: 
    { 
                      p1: 'handle address'=0x101f8eac98 
                      p2: 'lock address'=0xfdef83738 
                      p3: '100*mode+namespace'=0x10f2000010003 
            time in wait: 1.739719 sec 
           timeout after: never 
                 wait id: 508 
                blocking: 1 session 
             current sql: ALTER INDEX "DOUG"."VALUE_PK" REBUILD PARTITION "SYS_P4089"

            wait history:               * time between current wait and wait #1: 0.000973 sec               1.       event: 'enq: CR - block range reuse ckpt'                  time waited: 0.003220 sec                      wait id: 507             p1: 'name|mode'=0x43520006                                               p2: '2'=0x10086                                               p3: '0'=0x1               * time between wait #1 and #2: 0.000008 sec               2.       event: 'reliable message'                  time waited: 0.000107 sec                      wait id: 506             p1: 'channel context'=0x101c5afa98                                               p2: 'channel handle'=0x101c0f2260                                               p3: 'broadcast message'=0x101b5cfd58               * time between wait #2 and #3: 0.003791 sec               3.       event: 'db file sequential read'                  time waited: 0.000321 sec                      wait id: 505             p1: 'file#'=0x5e                                               p2: 'block#'=0x118784                                               p3: 'blocks'=0x1     }     and is blocked by the session at the start of the chain.

I would hope that a few things would be immediately obvious, particularly if it's 'your' application that generated this issue

1) The two sessions are running the following parts of the application.

Session 1: Begin run_manager_pkg.finalize_all_values_prc(:v0); End;
Session 2: ALTER INDEX "DOUG"."VALUE_PK" REBUILD PARTITION "SYS_P4089"

Which happened to fit in with what we were seeing. We have two concurrent runs which perform similar actions using different input files that load into different partitions.

2) The first session is using Parallel Query (note the enq: PS - contention and different PX Deq wait events)

3) The deadlock is a little unusual because it's not between two transactions trying to lock database objects or rows being locked by the other session but between in-memory structures. One session is waiting on 'row cache lock' and the other is waiting on
'library cache lock', as opposed to waiting for specific row or
table-level locks. This is also visible from the chain signature at the start of the trace file.

Chain Signature: 'library cache lock'<='row cache lock' (cycle)

Armed with 2) and 3) in particular, my next step was to go to My Oracle Support, as usual. I find that Google isn't too great with issues like this because some of them are quite specific and might not be affecting too many others. Sure enough, a search turned up :-

Bug 14356507  Deadlock between partition maintenance and parallel query operations

Which is confirmed as affecting versions 11.2.0.2 and 11.2.0.3. The fix is in Bundle Patch 12 for Exadata, in Oracle 12.1 and is also available as one-off patch that we're in the process of applying to different environments.

The issue is that "When a parallel query is hard parsed, first QC hard parses the query and then all the slaves.  When a partition maintenance operation (DDL) comes in between the hard parses of QC and Slaves.", then you can hit the deadlock. There's more detail in the bug notes, but it's worth noting this phrase "This is basically a timing issue, in high concurrency environments.", which means it only affects us very intermittently and is a nightmare to prove we've eliminated without a lot of testing.

What I find a little disconcerting is that there seem to be quite a few of these library cache deadlock issues kicking around in recent versions that I haven't been used to seeing in prior versions. Given some of the library cache madness I've seen in my few years with 11g, I do wonder what on earth they've done to it!

God Emperor of Dune…

God Emperor of Dune is the fourth book in the Dune series by Frank Herbert.

After the randomness of the previous book, this fourth one was a lot more on-the-money. There are a number of scenes in the book I really hooked into, including one I blogged about a few days ago. It’s far from perfect, but it kept me interested. Probably the worst part of the book was then ending, which was rather lackluster.

I’m looking forward to see if this direction continues into the next book.

Cheers

Tim…


God Emperor of Dune… was first posted on March 10, 2013 at 6:29 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Bitten by a Virtual Column, _OPTIMIZER_IGNORE_HINTS Doesn’t Ignore Hints?

March 9, 2013 I had a couple of spare minutes today, so I tried a couple of experiments with Oracle Database 11.2.0.2 just to see if I could produce some unexpected results. First, I will create a simple database table with two indexes: CREATE TABLE T1 (   N1 NUMBER,   V1 VARCHAR2(20),   D1 […]

Virtual DB

I’m heading off to Heathrow airport later on today to fly out to San Francisco for my week of  experimenting with Delphix. I’ve done a little preparation work, of course, including browsing around the Internet to read about related technologies. Some of the material I found was very interesting, so I thought I’d go publish a few of the links that might be useful to other people.

It’s quite surprising to see how long the necessary core technology has been around; and yet there seems to have been minimal follow-up on the possibilities the technology  makes available – perhaps because of the specific  hardware, or special skills needed to put put together a working solution.

Here’s a short series from “Oracle Storage Guy” comparing Netapp and EMC (with a bit of a bias towards EMC)  - it’s a few years old, and the companies mentioned have probably moved on, reviewed strategies, and refined what they do, but I suspect it’s still good background information for the non-specialist.

And an introduction to some OpenWorld presentations on Oracle’s dbClone from the same person.

A couple of posts by Kevin Closson on NFS and CloneDB

And a posting about Oracle’s clonedb from Tim Hall

A couple of (fairly long) Oracle white papers on cloning

And, of course, a couple of items from Kyle Hailey (Delphix) that talk about why Delphix is the right solution at the right time. Both items  come with a useful list of links  to articles on other technologoes

Finally, a couple of posts from end-users:

Bottom line – it looks as if Delphix has created a product that is more flexible, more powerful and easier to use than the competition that has been around for several years, and they’ve done it on generic hardware. I’ll be trying to find out how  reality lines up with expectations over the next week.

Friday Philosophy – Level of Presentations

This FF is a bit of a follow-up to the one I posted last week on PL/SQL skills and a comment made by Noons on how much knowledge you need to be an OakTable member.

I have a question to answer and I would appreciate other people’s opinion. Should there be more intro talks at conferences? If so, should the experts be giving them?

I am an OakTable member (and really quite worryingly proud about that) and I guess that means I know a lot about some aspects of Oracle. But also, and this is a key part of being asked to be a member of the OakTable, I both try and base my “knowledge” on scientific testing of what I think I know (be it by myself or others willing to show their workings) and I try and pass that knowledge on. I don’t think there is a member of the OT that does not abide by those two concepts.

This is not false modesty on my part, but most other people on the OT know a shed load {UK colloquialism for “really quite a lot”} more than I do about the Oracle database and how it works. Some of them are so smart I can’t but secretly dislike them for it :-). But I have a reasonable stash of knowledge in my head and I am a stong proponent of those last two factors. In particular, I want to put what I have in my head about {ohh, let’s pick partition pruning} in other people’s heads. Which is why for the last 4 years most of my presentations have run over horribly. I must get every detail into the audiences’ heads, even if they don’t want to know it!

Of late I have started to feel that I present in the wrong way. I take a subject, I write down what I know about it and I try to present that knowledge. I’ve only picked topics I feel I know well and in which I can handle almost any question the audience asks. For me to be that confident I have to have used that aspect of Oracle technology a lot and had months, if not years of experience. You cannot pass that on in 1 hour. I’ve already said I am not that smart, but I learn over time. So I started to strip out the basics and present just the clever stuff, which shows how fabulous I am. {British self-deprecating sarcasm, please}. Just like so many other experts. Hell, if we are experts, we should be talking expert stuff!!!

To balance that I think there is a gap in what is talked about at conferences. I know this is felt by some conference organisers and attendees too, but there is just way too much “impressive smart stuff” and not enough “how X works in the real world, for people who have never done it”. Not 10,000 feet up sales pitch rubbish that gives no substance, but talks on a topic where you can go from level 1 for knowledge-free beginners to level 5 for the 25 people at the conference who know this stuff inside out – and the talk stops at level 2. I’ve made a decision to try and address that gap, which is why I now offer more talks on “an intro to Exadata” and “how to get going with performance tuning” than the smart stuff.

The problem is, how many people, probably mostly young people, go to conferences? Am I wasting my time trying to offer these talks if no one going to the conferences wants them? Do people going to conferences, generally speaking, want the technical nitty-gritty or do they want the intro stuff? Yes, I know there is a spread but where is the real need? I suppose I thought of this recently when I did a talk on Index Organized Tables and almost everyone in the room already used them. Then, a few hours later, I did an intro to database design. Almost everyone in the room was a seasoned database designer… I doubt I said much of real value to either audience.

That leaves my last point about should the experts do intro talks? A problem with experts doing intro talks is the expert knows it all. It can be hard to remember what you really needed to know at the start (and also, my own problem, knowing what to drop out the talk as, really, it is “being smart as an ego-trip” that the new people can do without). But if you are not an expert, can you handle the What If questions? I have played with this issue with my Intro to Exadata talk. I wrote the draft when I had very little real experience and I have only modified it as I gained more experience. I’m glad I did as when I revisited the presentation recently I started putting loads of stuff in that only makes sense when you are used to it’s peculiarities. Thankfully, I realised this and stripped most of it out again. So well, in fact, that one person who wrote about the talk said “it was nice to see someone talk about it who was an Oracle expert who obviously knew little about the topic”. Well, something like that :-)

Enough. I would appreciate other people’s opinions and experiences on this.

The materialized view approach for implementing a table constraint

In yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.

The high-level cookbook for this approach is as follows:

  1. We create a materialized view that refreshes on commit,
  2. The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,
  3. And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,
  4. We devise a construct such that on-commit refresh of the materialized view *always* fails whenever (at least) one row is materialized in the view. This can be done in two manners:
    1) we add a check constraint on the underlying table of the materialized view that always fails, or
    2) we add a before-insert row-trigger on the underlying table of the materialized view that always fails.
Here's the slide on this from my 'harmful triggers' presentation:
So let's try this with our example constraint (managers require a clerk in same department). The assertion for this constraint was:
#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
With this assertion we can now mechanically generate a materialized view for our constraint, using the DUAL table. Note: we negate the assertion so that the materialized view ends up having characteristics 2 and 3 from our cookbook above. So the 'not exists' turns into an 'exists'.

#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from dual
where exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
/

Note: we explicitly want this materialized view to be "fast refreshable", meaning that Oracle will use intelligence to minimize the work required to refresh this view. In order for Oracle to be able to do so, we would also need to create a materialized view log on the table involved, which is EMP (and DUAL?) in this case. Creating the MV-log is left for the reader.

And finally we add a CHECK clause to the underlying table segment of this materialized view (whose name is the same as the materialized view). This CHECK clause is such that it always evaluates to FALSE.

#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">alter table managers_need_clerk add CHECK( 0 = 1 )
/

The way this now should work is that whenever a transaction introduces a department that has a manager, but no clerk, and tries to commit, this on-commit refresh materialized view will produce a single row to be inserted into the underlying table segment. This triggers validation of our CHECK clause, which will always fail. Which in turn causes the commit to fail, thereby preventing this transaction to successfully complete.

All seems very well, until you now hit the following error:

ORA-12052: cannot fast refresh materialized view [owner].[mat.view]

There are still various restrictions imposed upon materialized views for them to be fast refreshable. See the Oracle documentation for this. Sometimes you might be surprised though that a bit of rewriting a materialized view could end up such that the materialized view becomes fast refreshable. Rewriting them into straight joins is a strategy that might work here. For instance our materialized view above can be rewritten into this:

#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from (select c.job
from emp m
,emp c
where m.job = 'MANAGER'
and m.deptno = c.deptno (+)
and 'CLERK' = c.job (+))
where job is NULL
/

I haven't tested above alternative: with the appropriate materialized view logs, it could well be fast refreshable now...

On final comment on this approach for implementing table constraints: Oracle (must and) will serialize refreshes of the materialized view among simultaneously executing (or rather, committing) transactions. Rob van Wijk has a nice blogpost on this here.

When unused – isn’t.

So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a […]

And what about table constraints?

In a previous post we've introduced a classification scheme for constraints:

  • attribute constraints
  • tuple constraints
  • table constraints
  • database constraints
  • dynamic constraints
And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.
Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would make our lives real easy when it comes down to implementing table constraints. The example constraint "we cannot have a manager without a clerk in the same department" could be implemented as:

#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create assertion managers_need_clerk as
check(not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
)
/

Presto. Done.
It would then be up to the DBMS to maintain this constraint. Of course we require the DBMS to do that in an efficient manner. This will all become clear during the course of the next couple of posts.

By the way you may think, why not just do this with a CHECK constraint?

#eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">alter table emp add constraint managers_need_clerk as
check(not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
)
/

Well that's because CHECK constraints do not allow sub-queries. They don't allow that for the same reason as why we still do not have support for assertions. Both require the DBMS vendor to produce some seriously complex piece of software that can accept an arbitrary complex boolean SQL-expression and compute from that the most efficient way to maintain that boolean expression inside all concurrently running transactions. The research and development effort for this still needs to be done.

So, we have to resort to some other means when it comes to implementing table constraints. Here's a list of possible implementation strategies.

So we've discussed the first one already: it's highly preferred, but unfortunately only a very partial solution. The only table constraints that we can deal with declaratively are:

  • Keys (be them primary or unique), and
  • Foreign keys, in case the FK refers back to another column in the same table (in which case the foreign key is a table constraint, and not a database constraint).
The trigger approach is what the rest of this blog will be all about. Every table constraint can be implemented using triggers. Contrary to popular belief this is doable. But it *is* rather complex. The fact that this is a full solution for the table constraint class, is a big pro though.
Then there is the API-approach. This is the approach where you encapsulate all DML statements inside stored procedures, and disallow any direct DML access to your tables. The only way to modify your tables is through the stored procedure API-layer. And inside this API-layer, you deal with constraint validation, just before or right after you issue the DML statements. To me this is a fundamentally flawed solution, since it will always lead to constraint enforcing code duplication. And since it is not a "once and for all" solution. Every time you maintain your application and need to introduce new transactions, you'll have to take care of constraint enforcement again. Many people also tend to completely disregard the complexities involved. To a certain extent, they are the exact same complexities as are involved in the trigger approach: you'll have to take care of serialization and efficiency (all explained in future posts) in this approach too. And finally, in practice it is very difficult to maintain the enforcement of only allowing access to you tables via the API-layer. At some point people will find ways to go around the API-layer, and thereby be able to introduce corrupt data.
And there is the function based index trick. This is where we can use unique, function-based, indexes to implement keys across a subset of rows in a table. This too is a very partial solution for implementing table constraints. I'm assuming, since you read this blog, you are familiar with this trick, otherwise let me know in a comment and I'll provide you with an example.
Another approach is to employ materialized views in an ingenious manner. Like triggers, this is in theory a full solution, but in practice only a very partial one. It all boils down to the same research and development effort mentioned above when we discussed assertions, not having been done yet. We will discuss the materialized view approach in our next post.
Stay tuned.

MobaXterm 6.2…

I just noticed a new version of MobaXterm has been released. Not totally sure what has changed in 6.2. I can’t seem to find a changelog.

Fun, fun, fun…

Cheers

Tim…

Update: Fidel pointed out the obvious changelog. Shows how observant I am. :)


MobaXterm 6.2… was first posted on March 6, 2013 at 10:07 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

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!

Filed under: oracle