Patchset 11.2.0.2 is out there for a long, looong time. But still Roy and me – and unfortunately our customers – sometimes experience some nice surprises after upgrade.
Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle 11.2.0.2 with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy’s entry below on the change with JOB_QUEUE_PROCESSES in 11.2.0.2).
Yesterday I’ve received a text message from Roy to have a closer look into a service request the customer did open due to massive performance problems after go-live. The customer has seen MUTEX S CONTENTION in the AWR and ADDM reports slowing down the whole cluster. And the support people from BDE did a good job to drill down and diagnose the issue. But the solution is an interesting surprise.
The April 2011 PSU 11.2.0.2.2 (Patch 11724916) did contain a fix:
Bug:10187168 Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold
And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it “the old ‘hidden bug fix’ trick…” the code is there but you’ll have to enable it. MOS Note 10187168.8 describes what you’ll have to do.
Actually you’ll have to set this hidden parameter:
_cursor_features_enabled=1026
and besides that this nice event in your init.ora/spfile:
events= “106001 trace name
context forever, level 1024”
Then bounce the instance … an voila … the patch is now enabled.
Scary, isn’t it …?
As I’ve spent 6 years in Oracle’s RDBMS Support as well I was first checking the patch readme to see whether this is mentioned somewhere – but the only remark pointing to this bug is:
Bug 10264680 – INCORRECT VERSION_NUMBER REPORTED
AFTER PATCH FOR 10187168 APPLIED
Anyway, what is this parameter for?
It’s an enhancement request to allow parent cursor to be obsoleted if the version count exceeds a defined threshold. This threshold gets set with the new underscore _cursor_obsolete_threshold. But due to the fact that other patches included in this PSU don’t know about this newly introduced underscore event 106001 has to be set instead. The value equals the threshold.
To make things more complicated and enable this fix now (see my comments above) the underscore _cursor_features_enabled has to be set as well. And its value is dependend on the patch level – for Oracle 11.2.0.2 it is 1026 whereas for Oracle 11.1.0.7 it would be 18.
And let me mention that these parameters will have to be set and adjusted just under Oracle’s Support guidance!
