25 November 2016
Two after-12c-upgrade Oracle bugs
Note
After upgrading to 12.1.0.2 on RAC cluster, we had a very slow SQL below that was reading column group statistics:
select count(*) from sys.col_group_usage$ where obj# = :1 and cols = :2
and trunc(sysdate) = trunc(timestamp) and bitand(flags, :3) = :3" (Doc ID 2013851.1 ).
According to Oracle support site, this was a known bug and you could work around this by deleting extended statistics, or by setting cursor_sharing to FORCE. After experimenting with both options, we switched the cursor_sharing to 'FORCE'. That caused another bug. A SQL below started running in 2.5 seconds per execution, and was waiting on "GV$: slave acquisition retry wait time".
select inst_id, instance_name, host_name from gv$instance
where instance_mode in (:"SYS_B_0",:"SYS_B_1") order by inst_id
Solution
Oracle had a suggestion on how to work around this, but that didn't apply to us, as we already had parallel_execution_message_size=16K (Doc ID 1589592.1)
Oracle released a new merged patch for us:
Bug 24434288 - MERGE REQUEST ON TOP OF DATABASE PSU 12.1.0.2.160719 FOR BUGS 19543384 20907061.
After upgrading to 12.1.0.2 on RAC cluster, we had a very slow SQL below that was reading column group statistics:
select count(*) from sys.col_group_usage$ where obj# = :1 and cols = :2
and trunc(sysdate) = trunc(timestamp) and bitand(flags, :3) = :3" (Doc ID 2013851.1 ).
According to Oracle support site, this was a known bug and you could work around this by deleting extended statistics, or by setting cursor_sharing to FORCE. After experimenting with both options, we switched the cursor_sharing to 'FORCE'. That caused another bug. A SQL below started running in 2.5 seconds per execution, and was waiting on "GV$: slave acquisition retry wait time".
select inst_id, instance_name, host_name from gv$instance
where instance_mode in (:"SYS_B_0",:"SYS_B_1") order by inst_id
Solution
Oracle had a suggestion on how to work around this, but that didn't apply to us, as we already had parallel_execution_message_size=16K (Doc ID 1589592.1)
Oracle released a new merged patch for us:
Bug 24434288 - MERGE REQUEST ON TOP OF DATABASE PSU 12.1.0.2.160719 FOR BUGS 19543384 20907061.
