Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2 (Doc ID 2034610.1)

SCOPE

This Document concerns itself with highly desirable patches and related fixes in 12.1.0.2 that are not included in PSU updates, either because they contain optimizer layer fixes or because the PSU that will contain them has not yet been released.

 

TIP: If a patch is not available for your specific version and OS, please open a Service Request with details on the patch needed. Please include a list of patches already applied (opatch lsinventory -detail) as well as any other patches you intend to apply.

 

DETAILS

While we try and include important fixes in the PSUs that are released on a quarterly basis, there are restrictions as to what can go into a PSU. One of the restrictions is fixes that may change execution plans, i.e. optimizer layer fixes. The reason for this is that it reduces the amount of testing required to go from one PSU to another.  Other fixes may not yet have been released in a PSU.

Please consider applying the following patches to avoid certain performance, ORA-600/ORA-7445, and wrong results issues.

NOTE: Interim patches for defects are produced on a case by case basis. Patches listed in this document may not exist for all platforms and furthermore may not be feasible to produce. Listing in this document alone neither justifies nor guarantees that a particular patch can or will be produced. As with any other interim patch, certain criteria for customer business and operational impact must be met before the production of an interim patch is accepted. Individual defects may have little to no chance of being encountered, dependent on the applications you are running and features you use. For example, a port specific IBM AIX defect is not applicable to other platforms and a Real Application Cluster defect may not be applicable unless you are running RAC. For more details regarding error correction policies, see following note:

Document 209768.1 Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy

Patches relevant for all platforms

For best results, install the latest 12.1.0.2 PSU as described in Document 756671.1, as it fixes many issues not listed in this note.  Install the patches listed here for the relevent PSU:

No PSU PSU 1   2   3   4   5 160119 160419 160719 161018 170117 Bugs Fixed
Patch 19855835 for 12.1.0.2.0 Document 19855835.8 Upgrade slow when reorganizing large stats history tables
NB: Only applicable for upgrades from 11.2.0.3 or below. Apply before running the 12.1.0.2 upgrade script. There is no benefit to applying it later on.
Patch 20879889 for 12.1.0.2.0 Included in PSU 160419 and above Document 20879889.8 Open cursor leak from DML on table with a materialized view log
Patch 22837323 for 12.1.0.2.0 Included in PSU 5 and above Document 20476175.8 High VERSION_COUNT (in V$SQLAREA) for query with OPT_PARAM(‘_fix_control’) hint
Patch 22860122 for 12.1.0.2.5 Patch 23043224 for 12.1.0.2.160419 Document 20807398.8 ORA-600 [kgl-hash-collision] with fix to bug 20465582 installed
Document 21826068.8 Wrong Results when _optimizer_aggr_groupby_elim=true
Patch 23665623 for 12.1.0.2.0 Document 23665623.8 ORA-7445 kkeics with fix for bug 21091518 (supersedes Document 21091518.8 Extend fix of bug 18304693 to Partition Views)
Patch 24739928 for 12.1.0.2.0 Request Patch 24739928 for 12.1.0.2.1 Patch 24739928 for 12.1.0.2.2 Patch 24739928 for 12.1.0.2.3 Patch 24739928 for 12.1.0.2.4 Patch 24739928 for 12.1.0.2.160419 Patch 24739928 for 12.1.0.2.160719 Included in PSU 170117 and above Document 24739928.8 ORA-7445 [kglMutexCleanupAll] with fix for Bug 13542050 (supersedes Document 13542050.8 A mutex related hang with holder around 65534 (0xfffe))
Patch 18430870 for 12.1.0.2.0 Document 18430870.8 Adaptive Plan and Left Join Give Wrong Result
Patch 18650065 for 12.1.0.2.0 Patch 18650065 for 12.1.0.2.160419 Document 18650065.8 Wrong Results on Query with Subquery Using OR EXISTS or Null Accepting Semijoin
Patch 19174639 for 12.1.0.2.0 Document 19174639.8 Plan regression in 11.2.0.4 – OJPPD not occurring when expected
Patch 21171382 for 12.1.0.2.0 Document 21171382.8 Enh: AUTO_STAT_EXTENSIONS preference on DBMS_STATS

 

Other fixes relevant for all platforms

Set the parameter “_optimizer_aggr_groupby_elim”=false and “_optimizer_reduce_groupby_key”=false system-wide to prevent several wrong results bugs, including “Wrong Results with filtering on an aggregation expression” (Document 23147905.8) and “Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2” (Document 20634449.8):

alter system set “_optimizer_aggr_groupby_elim”=false scope=both;
alter system set “_optimizer_reduce_groupby_key”=false scope=both;

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *