We have a problem with our Oracle server. It was upgraded to 11g a couple of months ago and is running a third-party system. The system has been running for years (and has had several other issues), but this is new: Once or twice a day, CPU usage increases, and Cursor Mutex S is very visible, having had 30 million wait events since server startup (which was recently).
It seems that all of a sudden, a number of simple
INSERT has started giving problems. We have checked that statistics, indexes, etc. are as they should be - up-to-date, properly sized, space on disk, etc... No problems there.
We have isolated a single SQL-expression as being the main culprit. A number of similar statements give similar problems, but I'll focus on the one. The 'middleware' software executing this particular insert is running simultaneously on ~70 servers.
When we started noticing the problems, this statement had 10,000+ entries in
v@sql_shared_cursor. We have set up a cron job purging the cursors every five minutes, but this doesn't solve anything and only reduces the problem slightly.
Looking again at
v@sql_shared_cursor, it turns out that the reason for the many cursors being created is
INST_DRTLD_MISMATCH = Y. This is odd, since the middleware (over which we have little direct control) doesn't insert that many rows.
We turned to the vendor and asked how they did the inserts. They replied that they do a select from the table
WHERE 1 = 0 to get the column-structure into their internal ADODB object that they then fill with relevant data. They usually execute between 1 and 20 inserts pr. 'batch'.
I'm guessing that ADODB behind the scene makes it look like a bulk insert when you do a batch-update, which will be the only rational reason for Oracle to look at this as a bulk, but I have been unable to find any hard facts on this.
Can anybody offer insight into:
- Why this happens to begin with?
- If related to 11g, then why it happens now, two months after upgrade?
- Any other parameters I should look at in order to get to the bottom of this?
EDIT: It turns out this may very well be a bug with Oracle on Linux. We're currently testing a patch and I'll post an answer myself in a couple of days, if it turns out to be true.
EDIT2: The patch didn't fix it - and while we probably haven't found the cause, we may have alleviated the problem by increasing the number of redo-logs. I still hope to write an answer to this at some point.