SAP Basis Tech Supports

Symptom

Due to optimizer-specific patches, a new optimizer merge patch (8526100) was created and it fixes the following bugs:
8526100: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#13)
6642751: SELECT USING OPTIMIZER_MODE = FIRST_ROWS_10 FAILS WITH
ORA-7445 [_QKABXO+18]
7695742: DBMS_STATS.IMPORT_SCHEMA_STATS TAKES VERY LONG
8355120: PEFORMANCE PROBLEM BASED ON SUBQUERY UNNESTING
8366255: WRONG RESULT FROM DIFFERENT SESSIONS EVEN PLANS ARE SAME
8467800: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#12)
6471770: PARALLEL SERVERS DIE UNEXPECTEDLY RESULTING IN FAILURE
OF QUERIES
7716219: HASH GROUP BY INEFFICIENT AND GENERATING MUCH MORE TEMP
THAN SORT GBY
8284438: ADAPTIVE SHRUNK AGGREGATION CHECKS FOR SWITCH TO SHRUNK
MODE TOO OFTEN
8284633: SHRUNK AGGREATION NEEDS TO BE ENABLED WHEN ADAPTIVE FETCH
KICKS IN
5394888: WRONG CARDINALITY CALCULATION IN FIRST ROWS K MODE FOR HJ
SWAPPED ARGS
6399168: GATHER STATISTICS ON TABLE WITH BITMAP INDEXES RUNS FOREVER
7657126: _OPTIMIZER_BETTER_INLIST_COSTING IS CAUSING PERFORMANCE
PROBLEMS
7891471: QUERY SLOW WITH ORDER BY AND FIRST_ROWS_10 OPTIMIZATION
IN 10.2
8318020: INCORRECT CARDINALITY ESTIMATE FOR QUERY THAT SPANS
MUPTIPLE PARTITIONS
8348514: OPTIMIZER_INDEX_COST_ADJ IS APPLIED TWICE FOR
INDEX SKIP SCAN COST
8299095: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#11)
6741425: PROCESS DIED WITH ORA-00600 INTERNAL ERROR CODE
[KDISLE NROWS], [1]
7272039: INDEX SKIP SCAN IS NOT CONSIDERED
7441785: INCREMENTAL, APPROX_GLOBAL DOES NOT UPDATE
HISTOGRAM CORRECTLY
6526370: SELECTIVITY FOR PREDICATES OUTSIDE COLUMN MIN/MAX
VALUES IMPERFECT
7116357: APPROX_GLOBAL AND PARTITION DOES NOT SKIP EMPTY PARTITIONS
7463138: AGGREGATION OF HISTOGRAM ISSUES
7692248: HISTOGRAM INFORMATION IS NOT ALWAYS COLLECTED
WITH DBMS_STATS
8201796: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#10)
6319761: DBMS_STATS.GATHER_SCHEMA_STATS IS FAILING WITH ORA-01476
7509689: “_FIX_CONTROL” IS CAUSING TROUBLE DURING STARTUP
7654407: ORA-7445 [KKESTGETCOLGROUPNDV()+29] WHEN RUNNING SQL
7690331: WRONG RESULTS (DUPLICATES) WITH
UNIQUE SUBQUERY & STAR TRANS
7278117: MERGE LABEL REQUEST ON TOP OF 10.2.0.4
FOR BUGS 7155655 6917874
7664560: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#9)
5099019: DBMS_STATS DOESN’T COUNT LEAF_BLOCKS CORRECTLY
7592450: MERGE LABEL REQUEST ON TOP OF 10.2.0.4
FOR BUGS 7229351 6934892
7229351: DBMS_STATS.DELETE_DICTIONARY_STATS ERRORS AS
USER OPS$SAP_ALL\D000674
6934892: INCORRECT CARDINALITY WHEN USING HISTOGRAMS -
6 CHARS VS 7 CHARS
7592168:OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#8)
5251842: RULE HINT IN GV$DATAFILE CAUSED CUSTOMER QUERY USING
WRONG PLAN.
7007924: WRONG RESULT FROM BITMAP INDEX
7295298: QUERY AGAINST ALL_OBJECTS IS SLOW
7522608: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#7)
7430745: ORA-1422 DBMS_STATS.GATHER_TABLE_STATS ON X$KTFBUE
ON 10.2.0.4
7430474: FIRST_ROWS_K RECOST FOR ORDER BY ELIMINATION DOES NOT
FIND BEST PLAN
7429070: BIG PGA MEM ALLOC DURING PARSE TIME – KXS-HEAP-C
7257160: ORA-600[15160] WITH SELECT STATEMENT
7454131: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#6)
5648287: IN STAR TRANSFORMATION CARDINALITY OF JOIN BACK
IS NOT CORRECT
5709135: ORA-4030 – MEMORY LEAK
6430500: CBO DOES NOT CHOOSE UNIQUE INDEX
6833602: WRONG RESULTS – PREDICATE LOST WITH BITMAP OR
7211965: COST CALCULATION FOR BETWEEN HAS CHANGED WITH 10.2.0.4
7236148: BAD PLAN AFTER UPGRADE TO 10.2.0.3.0
7325597: WRONG INDEX CHOSEN AFTER APPLYING OPTIMIZER MERGE 7168928
7439957: ORA-00600 FOR LRGSRGU DIF TQSM920T102.DIF
7366749: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#5)
7280289: MERGE LABEL REQUEST ON TOP OF 10.2.0.4
FOR BUGS 7273832 6917874
6917874: QUERY ON DBA_TAB_PARTITIONS AND DBA_PART_TABLES
RETURNS NO RECORDS
7273832: MERGE LABEL REQUEST FOR BUGS 7155655 7138405 6845871
7155655: DIFFERENT QUERY RESULTS WITH PATCH 6917874 ON 10.2.0.4
7138405: PSRC; PREDICATE NOT PUSHED TO UNION ALL VIEW IN 10.2.0.4
6845871: JOIN AND ROWNUM USED IN 10.2.0.3 CAUSES BAD PLAN
7331569: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#4)
7300599: MERGE LABEL REQUEST FOR BUGS 6151380 6399597 7013768
6151380: ORA-600 [QERPX_RES_QCRANGE]
6399597: PERFORMANCE REGRESSION FROM 9.2 TO 10.2 ON
FILTER OPERATION
7013768: STAR TRANSFORMED SQL STATEMENT CAUSING
ORA-7445 [KKFDMRK()+492]
7228971: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#3)
7188932: SIMPLE QUERY LOOPS IN OPTIMIZER IN KKOMPR()
7149751: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#2)
6221403: INCORRECT SELECTIVITY WITH EXTENTED STATISTICS AND
OUT OF RANGE VALUES
6440977: REDUNDANT PREDICATES ARE NOT RECOGNIZED ACROSS
JOIN CONDITION
6972291: COLUMN GROUP SELECTIVITY IS NOT USED WHEN THERE IS A
HISTOGRAM ON ONE COLUMN DEF
6988848: OPTIMIZER MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR SAP (#1)
5714944: INDEX SKIP SCAN FOR A SPECIFIC QUERY INSTEAD OF
INDEX UNIQUE SCAN
6120483: CBO CHOOSES OBVIUSLY WRONG PLAN FOR A SIMPLE
SINGLE TABLE QUERY
6329318: SELECTIVITY OF BETWEEN IS LOWER THAN EQUALITY
6626018: COST OF FILTER OPERATION TO LOW CALCULATED
6670551: CREATING INDEX ON EMPTY TABLE DOES NOT GATHER STATISTICS
Other terms
Oracle, CBO, optimizer merge fix, 10.2.0.4, STAR TRANSFORMATION, wrong values, ORA-00600, ORA-07445, _fix_control, performance
Reason and Prerequisites
The corrections for the bugs described above are provided by the merge fix mentioned at the beginning of this note.
The attached interim patches are available on SAP Service Marketplace. Download the relevant patch for your operating system and install it in your system. Note that Oracle 10.2.0.4 must be installed before you do this.
Solution
The patches are contained in the following directories in the software center on SAP Service Marketplace (http://service.sap.com/swcenter-3pmain):

AIX5L (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/AIX5L (64-bit)/
p8526100_10204_AIX5L.zip
HP-UX PA-RISC (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/HP-UX PA-RISC (64-bit)/
p8526100_10204_HPUX-64.zip
HP-UX Itanium (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/HP-UX Itanium (64-bit)/
p8526100_10204_HPUX-IA64.zip
HP Tru64 (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/HP Tru64 (64-bit)/
p8526100_10204_Tru64.zip
Linux x86 (32-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Linux x86 (32-bit)/
p8526100_10204_Linux-x86.zip
Linux Itanium (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Linux Itanium (64-bit)/
p8526100_10204_Linux-IA64.zip
Linux x86-64 (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Linux x86-64 (64-bit)/
p8526100_10204_Linux-x86-64.zip
Linux on Power (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Linux on Power (64-bit)/
p8526100_10204_IBMPower.zip
Solaris SPARC (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Solaris SPARC (64-bit)/
p8526100_10204_Solaris-64.zip
Solaris x86-64 (64-bit)
/Oracle/Oracle 10.2.0.4/Database RDBMS/Solaris x86-64 (64-bit)/
p8526100_10204_Solaris86-64.zip
MS Windows (32-bit)
MS Windows Itanium (64-bit)
MS Windows x86-64 (64-bit)
The optimizer merge fix (8467800) is available as of Windows patch collection 2?
for Oracle 10.2.0.4
For information about availability, see Note 1137346.

This note will be updated when more patches are available.

General parameter changes that are made in the Oracle Cost Based Optimizer by the Oracle 10.2.0.4 patch set.
Previous Oracle releases (9.2x, lower than 10.2.0.4) always had performance problems or “wrong value” bugs that could be avoided by settings the following parameters:
_B_TREE_BITMAP_PLANS (FALSE)
_INDEX_JOIN_ENABLED (FALSE)
_TABLE_LOOKUP_PREFETCH_SIZE (0)
_BLOOM_FILTER_ENABLED (FALSE)
All of these errors are corrected by Oracle patch set 10.2.0.4. We will revert to the standard system settings as of Oracle 10.2.0.4. This means that these parameters no longer have to be set in the init<SID>.ora / spfile.

Important information about optimizer merge fix 8526100 (#13)
(Version 10.06.09)
No patch-specific parameters were added to the optimizer merge fix 8526100. Note 830576 contains the current and recommended database parameters.
For further information, read the special patch installation notes in the readme file after successfully installing the patch:
(Follow the sequence):
Post-install steps:
startup database
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/dbmsstat.sql
start $ORACLE_HOME/rdbms/admin/prvtstas.plb
start $ORACLE_HOME/rdbms/admin/prvtstat.plb
exit sqlplus
Caution:
Since reloading these PL/SQL scripts may invalidate existing packages, check the number of invalid packagesbeforeandafteryou install the patch:
select * from dba_objects where status <> ‘VALID’;
If the number of packages has increased after you install the patch, execute the SQL scriptutlrpto generate all the packages again.
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/utlrp.sql

Important information about optimizer merge fix 8467800 (#12)
(Version 08.05.09)
Optimizer merge fix 8467800 contains some patches that have to be activated manually. Note 830576 contains the current and recommended database parameters.
For further information, read the special patch installation notes in the readme file after successfully installing the patch:
(Follow the sequence):
Post-install steps:
startup database
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/dbmsstat.sql
start $ORACLE_HOME/rdbms/admin/prvtstas.plb
start $ORACLE_HOME/rdbms/admin/prvtstat.plb
exit sqlplus
Caution:
Since reloading these PL/SQL scripts may invalidate existing packages, check the number of invalid packagesbeforeandafteryou install the patch:
select * from dba_objects where status <> ‘VALID’;
If the number of packages has increased after you install the patch, execute the SQL scriptutlrpto generate all the packages again.
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/utlrp.sql
Patch conflict / Patch subsets:
1. Due to a patch conflict, the individual patch that was previously available:
7695742 – DBMS_STATS.IMPORT_SCHEMA_STATS TAKES VERY LONG
has to be removed from SAP Service Marketplace.
This patch was recognized as a conflict during the installation of 8467800 and was removed automatically from the inventory by OPatch.
The patch will be delivered again in the next CBO merge #13.2. The individual patch that was previously available (6471770) was removed from SAP Service Marketplace and is now part of the CBO merge #12.

Important information about optimizer merge fix 8299095 (#11)
(Version 10.03.09)
Optimizer merge fix 8299095 contains some patches that have to be activated manually. Note 830576 contains the current and recommended database parameters.
For further information, read the special patch installation notes in the readme file after successfully installing the patch:
(Follow the sequence):
Post-install steps:
startup database
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/dbmsstat.sql
start $ORACLE_HOME/rdbms/admin/prvtstas.plb
start $ORACLE_HOME/rdbms/admin/prvtstat.plb
exit sqlplus
Caution:
Since reloading these PL/SQL scripts may invalidate existing packages, check the number of invalid packagesbeforeandafteryou install the patch:
select * from dba_objects where status <> ‘VALID’;
If the number of packages has increased after you install the patch, execute the SQL scriptutlrpto generate all the packages again.
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/utlrp.sql

Important information about optimizer merge fix 8201796 (#10)
(Version 10.02.09)
No patch-specific parameters were added to the optimizer merge fix 8201796. Note 830576 contains the current and recommended database parameters.
Due to a patch conflict, the previous generic individual patch 6934892 had to be added to the optimizer merge fix. This means you have to reload certain *.plb files.
For information about this, read the special patch installation notes in the readme file after successfully installing the patch:
Post-install steps:
startup database
connect using sqlplus as SYSDBA
start $ORACLE_HOME/rdbms/admin/prvtstas.plb
start $ORACLE_HOME/rdbms/admin/prvtstat.plb
exit sqlplus
Caution:
On some platforms, the readme file (README.txt) contains the following “Pre-install steps”:
(a) Pre-install steps:
# shutdown database
# archive new object files into libserver10.a
# relink oracle
# put prvtstas.plb and prvtstat.plb in $ORACLE_HOME/rdbms/admin
Do not perform these steps – the OPatch performs them automatically.
The individual patch 6934892 that was previously available has been removed from SAP Service Marketplace.

Important information about optimizer merge fix 7592168 (#8)
(Version 10.12.08)
No patch-specific parameters were added to the optimizer merge fix 7592168. Note 830576 contains the current and recommended database parameters.

Important information about optimizer merge fix 7522608 (#7)
(Version 10.11.2008)
Optimizer merge fix 7522608 contains some patches that have to be activated manually. Note 830576 contains the current and recommended database parameters.
Read Note 1232043, which provides information about the patch conflict between optimizer merge fix 7522608 and the individual patch 5251842.
If you use MOPatch to apply patches, you must first manually uninstall the individual patch 5251842 (rollback) because MOPatch does not install the CBO merge #7 if there is a conflict.
If you use OPatch to apply patches, answer the question whether you want
to uninstall patch 5251842 with “Y”.

Important information about optimizer merge fix 7228971 (#3)
(Version 08.07.2008)
Optimizer merge fix 7228971 contains some patches that have to be activated manually. Note 830576 contains the current and recommended database parameters.

Important support information for optimizer merge fix 7228971 (#3)
Version 14.08.2008:
Under the following circumstances, the current optimizer merge fix 7228971 chooses an unsuitable index during the execution:
- An INDEX ONLY ACCESS is selected as the execution plan
(This is the case if the columns in the SELECT statement also occur in the INDEX and therefore, no additional table access is required.)

As a workaround, set the following parameter in init<SID>.ora / spfile:
“_fix_control”=’6120483:OFF’
The bug fix 6329318 increases the cardinality estimate by 20 times for BETWEEN (5% instead of 0.25%). This may have a negative effect on more complex joins. As a workaround, set the following parameter in init<SID>.ora / spfile:
“_fix_control”=’6329318:OFF’