Patchset 126.96.36.199 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 188.8.131.52 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 184.108.40.206).
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 220.127.116.11.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:
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 18.104.22.168 it is 1026 whereas for Oracle 22.214.171.124 it would be 18.
And let me mention that these parameters will have to be set and adjusted just under Oracle's Support guidance!