LAST UPDATE:
|
1 |
Dec 13, 2016 |
APPLIES TO:
|
1 2 |
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1] Information in this document applies to any platform. |
PURPOSE:
|
1 2 3 4 5 |
In recent quarters, during non-code closure bug review for Performance, it was found that more than 50% of duplicates (36,96) closed by Sustaining Engineering (SE) belonged to version 12.1. As a result this document was created to list most commonly reported and share this with Tier-1 engineers so that they could check this before logging any new bugs. Thus the Goal of this document is to provide a list of known bugs reported in 12C for database & SQL performance for support analysts to check before logging new bugs. In addition to the bugs listed in this article, there is also a useful list of Documented Database Bugs With High "Solved SR" Count where a high number of Service Requests means that there are more than 50 "Solved SR" entries for the bug - ie: all bugs in this doc have > 50 "Solved SR" entries.” : Document 2099231.1 Documented Database Bugs With High "Solved SR" Count |
DETAILS:
Query Optimizer / SQL Execution
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Bug 20271226 - QUERY INVOLVING VIRTUAL COLUMN AND PRIMARY KEY CONSTRAINT CRASHES Document 19046459.8 - Inconsistent behavior of OJPPD Document 22077191.8 - Create Table As Select of Insert Select Statement on 12.1 is Much Slower Than on 11.2 Bug 20597568 - PJE INCORRECTLY APPLIED TO A CONNECT BY QUERY Document 18456944.8 - Suboptimal plan for query fetching ROWID from nested VIEW with fix 10129357 Bug 20355502 - QUERY PARSE NEVER ENDS ( BURNING CPU ALL THE TIME) Document 20355502.8 Limit number of branches on non-cost based OR expansion Document 20636003.8 - Slow Parsing caused by Dynamic Sampling (DS_SRV) queries in 12.1.0.2 Bug 22513913 - QUERY FAILS WITH ORA-07445: [KKOSJT()+281] WHEN USING DATABASE LINK Bug 22020067 - UPGRADE 11.2.0.2 TO 11.2.0.4 SCALAR SUBQUERY UNNEST DISABLED Bug 21099502 - JPPD Not Happening In UNION ALL View Having Group-by and Aggregates Bug 22862828 - Regression with 22706363, JPPD Does Not Occur Despite the Fix Control 9380298 is ON Bug 19295003 - High CPU While Parsing Huge SQL [kkqtutlTravOptAndReplaceOJNNCols] Document 21091518.8 - Suboptimal plan for SQLs using UNION-ALL with bug fix 18304693 enabled Document 22113854.8 - Query Against ALL_SYNONYMS Runs Slow in 12C Document 21871902.8 - SELECT query fails with ORA-7445 [qerixRestoreFetchState2] - superseded by Document 22255113.8 - High parse time with high memory and CPU usage Document 22339954.8 - Bug 22339954 - High Parse Time for Query Using UNPIVOT Operator Document 19490852.8 - Excessive "library cache lock" waits for DML with PARALLEL hint when parallel DML is disabled Bug 20226806 - QUERY AGAINST ALL_CONSTRAINTS AND ALL_CONS_COLUMNS RUNS SLOWER IN 12.1.0.2 duplicate of Bug 20355502 - QUERY PARSE NEVER ENDS ( BURNING CPU ALL THE TIME) Document 20118383.8 - Long query parse time in 12.1 when many histograms are involved - superseded by Document 18795224.8 - Hard parse time increases with fix 12341619 enabled, fixed in 12.2 Document 19475484.8 - Cardinality of 0 for range predicate with fix for bug 6062266 present (default), fixed in 12.2 Document 2182951.1 Higher Elapsed Time after Updating from 11.2.0.4 to 12.1.0.2 Document 18498878.8 - medium size tables do not cached consistently causing unnecessary waits on direct path read, fixed in 12.2 Bug 23516956 - DYNAMIC SAMPLING QUERIES CONTAINS INCORRECT HINTS duplicate of Document 19631234.8 - Suboptimal execution plan for Dynamic Sampling Queries Bug 20503656 - Remote SQLs having group by with bind variables throw ORA-00979, fixed in 12.2 Bug 19047578 - Optimizer No Longer Uses Function Based Index When CURSOR_SHARING=FORCE Document 22734628.8 - Wrong results from UNION ALL using OJPPD with cost based transformation disabled, fixed in 12.2 Bug 21839477 - ORA-7445 [QESDCF_DFB_RESET] WITH FIX FOR BUG:20118383 ON, fixed in 12.2 Document 20774515.8 - Wrong results with partial join evaluation, fixed in 12.2 Bug 21303294 - Wrong Result Due to Lost OR Predicate in Bitmap Plan, fixed in 12.2 Document 22951825.8 - Wrong Results with JPPD, Concatenation and Projection Pushdown, fixed in 12.2 Bug 19847091 - HUGE INLIST SQL HANGS DURING PARSE superseded by Bug 20384335 - CPU REGRESSION DUE TO PLAN CHANGE IN ONE SELECT SQL WITH IN PREDICATE, fixed in 12.2 |
SQL Plan Management (SPM)
|
1 2 3 4 5 6 7 |
Document 20877664.8 - SQL Plan Management Slow with High Shared Pool Allocations Document 21075138.8 - SPM does not reproduce plan with SORT UNIQUE, affected from 11.2.0.4 and fixed in 12.2 only Document 20978266.8 - SPM: SQL not using plan in plan baselines and plans showing as not reproducible - superseded, fixed in 12.2 Document 19141838.8 - ORA-600 [qksanGetTextStr:1] from SQL Plan Management after Upgrade to 12.1 Document 18961555.8 - Static PL/SQL baseline reproduction broken by fix for bug 18020394, fixed in 12.2 Document 21463894.8 - Failure to reproduce plan with fix for bug 20978266, fixed in 12.2 Document 2039379.1 ORA-04024: Self-deadlock detected while trying to mutex pin cursor" on AUD_OBJECT_OPT$ With SQL Plan Management (SPM) Enabled |
Wrong Results
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Document 20214168.8 - Wrong Results using aggregations of CASE expression with fix of Bug 20003240 present Bug 21971099 - 12C WRONG CARDINALITY FROM SQL ANALYTIC WINDOWS FUNCTIONS Document 16191689.8 - Wrong resilts from projection pruning with XML and NESTED LOOPS Bug 18302923 - WRONG ESTIMATE CARDINALITY IN HASH GROUP BY OR HASH JOIN Bug 21220620 - WRONG RESULT ON 12C WHEN QUERYING PARTITIONED TABLE WITH DISTINCT CLAUSE Document 22173980.8 - Wrong results (number of rows) from HASH join when "_rowsets_enabled" = true in 12c (default) Bug 20871556 - WRONG RESULTS WITH OPTIMIZER_FEATURES_ENABLE SET TO 12.1.0.1 OR 12.1.0.2 Bug 21387771 - 12C WRONG RESULT WITH OR CONDITION EVEN AFTER PATCHING FOR BUG:20871556 Bug 22660003 - WRONG RESULTS WHEN UNNESTING SET SUBQUERY WITH CORRELATED SUBQUERY IN A BRANCH Bug 22373397 - WRONG RESULTS DUE TO MISSING PREDICATE DURING BITMAP OR EXPANSION Bug 22365117 - SQL QUERY COMBINING TABLE FUNCTION WITH JOIN YIELDS WRONG JOIN RESULTS Bug 20176675 - Wrong results for SQLs using ROWNUM<=N when Scalar Subquery is Unnested (VW_SSQ_1) Document 19072979.8 - Wrong results with HASH JOIN and parameter "_rowsets_enable" Document 22338374.8 - ORA-7445 [kkoiqb] or ORA-979 or Wrong Results when using scalar sub-queries Document 22365117.8 - Wrong Results / ORA-7445 from Query with Table Function Bug 19318508 - WRONG RESULT IN 12.1 WITH NULL ACCEPTING SEMIJOIN - fixed in 12.2 Bug 21962459 - WRONG RESULTS WITH FIXED CHAR COLUMN AFTER 12C MIGRATION WITH PARTIAL JOIN EVAL Bug 23019286 - CARDINALITY ESTIMATE WRONG WITH HISTOGRAM ON COLUMN GROUP ON CHAR/NCHAR TYPES, fixed in 12.2 Bug 23253821 - Wrong Results While Running Merge Statement in Parallel, fixed in 12.2 Document 18485835.8 - Wrong results from semi-join elimination if fix 18115594 enabled, fixed in 12.2 Document 18650065.8 - Wrong Results on Query with Subquery Using OR EXISTS or Null Accepting Semijoin, fixed in 12.2 Document 19567916.8 - Wrong results when GROUP BY uses nested queries in 12.1.0.2 - superseded by Document 20508819.8 - Wrong results/dump/ora-932 from GROUP BY query when "_optimizer_aggr_groupby_elim"=true - superseded by Document 21826068.8 - Wrong Results when _optimizer_aggr_groupby_elim=true Document 20634449.8 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2 Bug 20162495 - WRONG RESULTS FROM NULL ACCEPTING SEMI-JOIN, fixed in 12.2. |
Statistics
|
1 2 3 4 5 6 7 8 |
Bug 22081245 - COPY_TABLE_STATS DOES NOT WORK PROPERLY FOR TABLES WITH SUB PARTITIONS Bug 22276972 - DBMS_STATS.COPY_TABLE_STATS INCORRECTLY ADJUST MIN/MAX FOR PARTITION COLUMNS Document 19450139.8 Slow gather table stats with incremental stats enabled Bug 21258096 - UNNECESSARY INCREMENTAL STATISTICS GATHERED FOR UNCHANGED PARTITIONS DUE TO HISTOGRAMS Bug 23100700 - PERFORMANCE ISSUE WITH RECLAIM_SYNOPSIS_SPACE, fixed in 12.2 Document 21171382.8 - Enhancement: AUTO_STAT_EXTENSIONS preference on DBMS_STATS - Enhancement To turn off automatic creation of extended statistics in 12c Document 21498770.8 - automatic incremental statistics job taking more time with fix 16851194, fixed in 12.2 Document 22984335.8 - Unnecessary Incremental Partition Gathers/Histogram Regathers |
Errors
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Document 20804063.8 ORA-1499 as REGEXP_REPLACE is allowed to be used in Function-based indexes (FBI) Document 17609164.8 ORA-600 [kkqctmdcq: Query Block Could] from ANSI JOIN with no a join predicate Document 21482099.8 ORA-7445 [opitca] or ORA-932 errors from aggregate GROUP BY elimination Document 21756734.8 - ORA-600 [kkqcsnlopcbkint : 1] from cost based query transformation Bug 22566555 - ORA-00600 [KKQCSCPOPNWITHMAP: 0] FOR SUBQUERY FACTORING QUERY WITH DISTINCT Bug 21377051 - ORA-600 [QKAFFSINDEX1] FROM DELETE STATEMENT Document 21188532.8 - Unexpected ORA-979 with fix for bug 20177858 - superseded by Bug 23343584 - GATHER_TABLE_STATS FAILS WITH ORA-6502/ORA-6512 duplicate of Bug 22928015 - GATHER DATABASE STATS IS RUNNING VERY SLOWLY ON A RAC ENVIRONMENT Document 21038926.8 - ORA-7445 [qesdcf_dfb_reset] with fix for bug 20118383 present, fixed in 12.2 Document 18405192.8 - ORA-7445 under qervwFetch() or qervwRestoreViewBufPtrs() when deleting from view with an INSTEAD OF trigger Document 21968539.8 - ORA-600 [kkqcscpopnwithmap: 0] Document 18201352.8 - ORA-7445 [qertbStart] or similar errors from query with DISTINCT and partial join evaluation (PJE) transformation Document 19472320.8 - ORA-600 [kkqtSetOp.1] from join factorization on encrypted column Bug 22394273 - ORA-600 [QESMMCVALSTAT4] FROM SELECT STATEMENT Document 21529241.8 - DBMS_STATS ORA-6502: "PL/SQL: numeric or value error" Document 20505851.8 - ORA-600 / ORA-7445 qksqbApplyToQbcLoc from WITH query that has duplicate predicates Bug 19952510 - ORA 600 [QERSCALLOCATE: BUFFER SIZE LIMIT] AND ORA 600 [KEUUXS_1] Bug 21274291 - ORA-600 :[17147] AND [ORA-600 :[KGHALF] DURING AN INSERT Bug 21524270 - ORA-28115 ON ORACLE 12C WITH VPD IN MERGE STMT, BUT INSERT SUCCEEDS Bug 20660917 - INSTEAD OF TRIGGER IS FIRED TWICE INSTEAD OF ONCE WITH FIX TO 13812807, Causes ORA-07445 [kxtifir] in 12.1.0.2 - fixed in 12.2. |
Adaptive Query Optimization
|
1 2 3 4 5 6 7 8 9 10 |
Bug 18745156 - ADAPTIVE DYNAMIC SAMPLING CAUSES BAD CARDINALITY ESTIMATE Bug 16033838 - TST&PERF: ADAPTIVE JOINS LEADS TO WORSE PLAN FOR QUERY B4TKVSMPFSP85 Document 20465582.8 High parse time in 12c for multi-table join SQL with SQL plan directives enabled - superseded Document 20807398.8 ORA-600 [kgl-hash-collision] with fix to Bug 20465582 installed Document 18430870.8 Adaptive Plan and Left Join Give Wrong Result Bug 21912039 : GSIAS: PERF REGRESSION: ADAPTIVE PLAN SQL_ID DN50XQR69FJ0F GETS WORSE duplicate of Bug 20243268 : EM QUERY WITH SQL_ID 4RQ83FNXTF39U PERFORMS POORLY ON ORACLE 12C RELATIVE TO 11G Bug 19731829 - ISSUES WITH PACK AND UNPACK OF SQL PLAN DIRECTIVES Document 20370037.8 - KGLH0 growth leading to ORA-4031 by cardinality feedback, fixed in 12.2 Document 20413540.8 - Excessive executions of SQL frjd8zfy2jfdq |
Library cache / Rowcache / Shared Cursors / Dictionary / Buffer cache
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Document 19450314.8 Unnecessary invalidations in 12c Bug 21153142 - ROW CACHE LOCK SELF DEADLOCK ACCESSING SEED PDB Bug 22081947 - ORA-4023 MAY OCCUR IN ACTIVE DATA GUARD ENVIRONMENTS Bug 12387079 - THE CHILD CURSOR IS INCREASED WHEN THE CURSOR IS KEPT AND EXECUTED DDL superseded by Bug 19239846 - FIX FOR Bug 12387079 NEEDS TO BE RE-WORKED Bug 12320556 - HIGH VERSION COUNTS OCCUR DUE TO AUTH_CHECK_MISMATCH, INSUFF_PRIVS_REM=Y superseded by Bug 21515534 - QUERY USING 2 REMOTE DB NOT SHARED - AUTH_CHECK_MISMATCH ,INSUFF_PRIVS_REM Document 21515534.8 Query referencing multiple remote databases not shared with reason AUTH_CHECK_MISMATCH INSUFF_PRIVS_REM Document 20907061.8 High number of executions for recursive call on col$ Document 20476175.8 High VERSION_COUNT (in V$SQLAREA) for query with OPT_PARAM('_fix_control') hint Bug 21799609 - ORA-04024 DEADLOCK ON STARTUP ON SYS.ECOL$ ON LOAD HISTOGRAMS duplicate of Bug 19469538 - LOADING OF DATA IN ECOL$ TABLE IS NON-OPTIMAL FOR QUERIES Bug 22586498 - HUGE M000 TRACEFILE DUE TO OBSOLETE CURSOR DUMPS Document 20906941.8 - DBW0 spins with high CPU Bug 17700290 - TST&PERF: LIBRARY CACHE MUTEX WAIT TIME INCREASED HUGELY IN MAIN - affected 12.1.0.2, fixed in 12.2 only Bug 22733141 - GATHERING STATS ON X$KQLFBC HANGS Bug 23098370 - DBWR CAN ISSUE MORE THAN SSTMXIOB IO'S Bug 23103188 - Incorrect ORA-1031 or ORA-942 during Grant due to 22677790, fixed in 12.2 Bug 19340498 - CDB:NO ROWS RETURNED WHEN QUERYING V$SQL_SHARED_MEMORY INSIDE A PDB Bug 23514710 - CROSS-CONTAINER FIXED TABLE NOT OBSOLETED WHEN PARENT IS MARKED OBSOLETE in ADG Env Document 19392364.8 - Process spin in kkscsFindMatchingRange() with adaptive cursor sharing, fixed in 12.2 Document 2096561.1 - High Amount Of Shared Memory Allocated Into KGLH0 Heap In 12.1.0.2 Document 2119923.1 - Shared Pool from KGLH0 constantly growing causing ORA-04031 and Latch contention Document 23168642.8 - Sporadic ORA-600 [kglunpin-bad-pin] / ORA-14403 / high 'library cache: mutex x' using partitions and deferred seg creation / ORA-600 [qesmascTrimLRU_1] with fix 21759047, fixed in 12.2 Document 21759047.8 - High 'library cache: mutex x' Contention when Many Sessions are Executing Cursors Concurrently Against a Partitioned Table - superseded, fixed in 12.2 Document 14380605.8 - High "library cache lock", "cursor: pin S wait on X" and "library cache: mutex X" waits, fixed in 12.2 Document 19822816.8 - High parse time for SQL with PIVOT and binds (can block LGWR on "library cache lock"), fixed in 12.2 Document 13542050.8 - 'library cache: mutex X' waits : A mutex related hang with holder around 65534 (0xfffe), fixed in 12.2 Document 21834574.8 - Mutex contention and increased hard parse time due to ILM (segment-access tracking) checking with partitioned objects, fixed in 12.2 Document 19790972.8 - "library cache lock" waits due to DBMS_STATS gather of stats for a subpartition, fixed in 12.2 |
Server Manageability (SVRMAN)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Bug 21521882 - SQLT CAUSES ORA-00600: [KGHSTACK_UNDERFLOW_INTERNAL_1] duplicate of Bug 19593445 - SR12.2CDBCONC3 - TRC - KPDBSWITCH Document 18148383.8 AWR snapshots stopped , MMON hung with "EMON to process ntnfs" - affected 12.1.0.1, fixed in 12.1.0.2 Bug 20976392 - AWR REPORT: NEGATIVE OR WRONG VALUES IN %TOTAL OF UNOPTIMIZED READS Document 2016112.1 - Replaying a PRE-12C Capture On 12.1.0.2.0 Encounters Unexpected ORA-1000 Errors Bug 21117072 - DBREPLAY PATCH BUNDLE 1 FOR 12.1.0.2 , RAT Patch Bundle 1 for 12.1.0.2 is mandatory LGWR Bug 23501117 - WORKLOAD REPLAY SKIPS USERCALLS FOR WORKLOAD CAPTURED UNDER 11.2.0.4 Document 22345045.8 - ORA-600 [kewrspbr_2: wrong last partition] in AWR internal tables after upgrading to 12.1.0.2 Multiple LGWR Document 1968563.1 Hang Waiting on 'LGWR worker group ordering' with Deadlock Between LGWR (Log Writer) Slaves (LG0n) when Using Multiple LGWR Processes Document 1957710.1 ALERT: Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium - ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 |
Exadata/In-memory/Multitenant
|
1 2 3 4 5 6 7 |
Document 21553476.8 Wrong Results using aggregations of CASE expression with fix of Bug 20003240 present in Exadata Bug 19130972 - EXTREMELY LONG PARSE TIME FOR STAR QUERY (For In-memory DB, fixed in 12.1.0.2 DBBP 3) Bug 17439841 - IMC DYNAMIC SAMPLING CAUSE "CURSOR: PIN S WAIT ON X" ON PARALLEL QUERY - affected 12.1.0.2, fixed in 12.1.0.2 Bug 21445204 - PARSING OF A QUERY TAKES LONG TIME WITH IMC Document 21153142.8 - Row cache lock self deadlock accessing seed PDB, fixed in 12.2. Bug 20766944 - QUERIES INVOLVING INT$DBA_CONSTRAINTS TAKE A LOT OF TIME, fixed in 12.2. Document 17805926.8 - Parameter changes at PDB level affect other PDBs, fixed in 12.2 |
Popular Documents for Known issues/Bugs
|
1 2 3 4 5 |
Document 2034610.1 Things to Consider Before Upgrading to 12.1.0.2 to Avoid Poor Performance or Wrong Results Document 2035898.1 Patches to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management (SPM) Document 2107602.1 Things to Consider When Using Incremental Statistics Document 1683802.1 12.1.0.2 Patch Set - List of Bug Fixes by Problem Type Document 1924126.1 12.1.0.2 Patch Set Updates - List of Fixes in each PSU |
Port-Specific
|
1 |
Document 1970525.1 Things to Consider to Avoid RDBMS Performance Problems on SPARC Platforms Using Oracle 12.1.0.2 |
Reference documents covering 12c new features
|
1 2 3 4 5 6 7 8 9 10 11 |
The following list of documents cover many of the features that have been introduced or enhanced in 12c and as provided to aid re-discovery: Document 2031605.1 Adaptive Query Optimization Document 2002108.1 Dynamic Sampling Level Is Changed Automatically in 12C Document 2033658.1 Dictionary Queries Running Slow in 12C PDBs Document 2002089.1 High Latch Free Waits on 'Result Cache: RC Latch' In 12C when RESULT_CACHE_MODE = MANUAL Document 2004828.1 ORA-20002: Unable To Export Table Stats Using DBMS_STATS Package In 12c Database Document 2051004.1 ORA-12012 ORA-20000 During Automatic Statistics Collection Job in 12C With Concurrent Option Enabled Document 1955319.1 Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----" From 12.1.0.2 Document 2053877.1 Wrong Results (0 Rows) Returned for Query that Includes Subquery Containing AND ROWNUM when OPTIMIZER_ADAPTIVE_FEATURES = TRUE Document 2041541.1 Gather_Database_Stats_Job_Proc Taking More Time in 12.1.0.2 Than 11.2.0.4 |
Documentation
|
1 2 3 |
Database SQL Tuning Guide 12.1: http://docs.oracle.com/database/121/TGSQL/toc.htm Database Performance Tuning Guide: http://docs.oracle.com/database/121/TGDBA/toc.htm Database Testing Guide (RAT): http://docs.oracle.com/database/121/RATUG/toc.htm |
参考：
Commonly Reported Known Issues for Database and Query Performance Reported in 12C (Doc ID 2097793.1 INTERNAL)
Documented Database Bugs With High “Solved SR” Count (Doc ID 2099231.1 INTERNAL)