Hello Community,
in this post i would like to examine 2 bugs we encountered of which one is now fixed in the fresh released SAP Bundle Patch SBP_112022_201105 for Oracle. (May, 18th SAP Note 1503709)
There has been one problem with DBMS_REDEFINITON which we encountered and it resulted in Oracle Bug 11775474. From the SBP-Readme it says:
DBMS_REDEFINITION DOES NOT PRODUCE DEFERRED SEGMENTS SINCE 11.2.0.2
This features normally reduces the used space amount for empty tables (first segment is not created if the table has 0 rows).
With Oracle 11.2.0.2 this feature was disabled because of the mentioned bug. Brtools uses DBMS_REDEFINITION during reorganizations so it created segments for every empty table which should not be the case..
In Oracle 11.2.0.1 the feature seems to work properly which i can´t check because we started directly on 11.2.0.2
My personal workaround is to drop the deffered segments manually "after" the reorganization:
DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS
Results vary from 6-8 GB in the tablespace with the most empty tables (normaly PSAPSR3)
The other problem we encountered resulted in Oracle Bug 11834459
This one is not fixed with the SBP so if you are going to reorganize your SAP Tables and Indexes take a look.
This bug results in a deadlock during reorganizations (ITL-Contention with "enq: TX - allocate ITL entry".
This happens when parallel threads reorganize (brtools option -p > 2)
Workaround here is to activate event 10995 before reorganization:
alter system set events '10995 trace name context forever, level 2';
and deactivate it after:
alter system set events '10995 trace name context off';
You can find an explanation and this workaround in SAP-Note 1565421
If you are not sure if you encounter the ITL-Contention take a look at your AWR Reports during reorganizations, you might find the event there. The time for reoganizations is significant when suffering this bug...
So i would suggest if you plan to use reorganize your databases with 11.2.0.2 you should activate the event mentioned in SAP-Note 1565421 and implement the fix for Oracle Bug 11775474 (SBP May).
Both bugs affected us at first in some way but workarounds are at hand so in my opinion it is no matter.
CU soon on SDN
