Oracle 19c bug: JPPD не работает в присутствии GROUP BY и TABLE expression

Некоторое время назад коллега-разработчик Рустам Кучукбаев обратил внимание на проблему, для которой мне удалось подготовить и выложить на Oracle Live SQL (для эффективного общения с поддержкой) до сих пор доступный тесткейс

Кратко:
, использование конструкции TABLE() (в любом месте запроса) в общем случае совместимо с использованием Join Predicate Push-Down (JPPD)
, Join Predicate Push-Down into Group by View использует механизм OJPPD = old-style (non-cost-based) JPPD
, и, хотя в обоих случаях результатом является похожая комбинация операций NESTED LOOPS OUTER + VIEW PUSHED PREDICATE, одновременное использование GROUP BY и TABLE expression не всегда совместимы и допускаются CBO

Соответствующий BUG 33490092: JPPD does not works if query contains GROUP BY и TABLE expression зарегистрирован, но проблема остаётся актуальной вплоть до версии 19.14 включительно (https://livesql.oracle.com), и, вероятно, далее

Под катом — технические детали теста

forum.ora600.ru

Отличная новость — уважаемые коллеги из компании ФОРС Дистрибуция подготовили и открыли общедоступный форум:

«…ФОРС Дистрибуция открывает форум под названием ORA-600 для общения специалистов по проектированию, программированию и эксплуатации систем управления базами данных, и, в первую очередь – СУБД Oracle

Цели нашего форума:
– поддержать контакты специалистов и их профессиональные компетенции;
– сохранить сообщество русскоязычных специалистов по базам данных;
– помочь друг другу в решении разнообразных технологических вопросов»

Необходимо и очень своевременно

CBQT как причина неприменения SQL Profile

DBA_HIST_SQLSTAT показывал неудачное применение SQL Profile ftd8uxp52atxc_171718292, который, как следует из названия был создан для фиксации PHV=171718292 при выполнении запроса sql_id=’ftd8uxp52atxc’:

19.10@SQL> @dba_hist_sqlstat "sql_id = 'ftd8uxp52atxc' and snap_id between 162866 and 162880 and (elapsed_time_delta > 0 and executions_delta is not null)"

INST BEGIN_SNAP_ID BEGIN_SNAP_TIME EXECS ROWS_PROCESSED SQL_ID              PLAN SQL_PROFILE                    COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US APWAITS_PER_EXEC CCWAITS_PER_EXEC
---- ------------- --------------- ----- -------------- ------------- ---------- ------------------------ ---------- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------------ --------------- ---------------------- ------------- ---------------- ---------------- ------------------- ---------------- ----------------
   2        162865 12.05 03:20         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  8934042471              1    305896696    282975170      36310083            35055208           136929         137049                  0               0                      0             0                0         21593172              346160              355             5478
   2        162866 12.05 03:30         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  8934042471              0    603983195    558916274      63044796            61722057           241105         243920                  0               0                      0             0                1         44074913              425026             1359                0
   1        162867 12.05 03:40         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  8934042471              0    606312767    562119171      58397260            57114056           223101         223110                  0               0                      0             0                0         37440448               70364             1393              705
   1        162868 12.05 03:50         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  8934042471              1    582448391    539183707      64643642            63933793           249739         249750                  0               0                      0             0                1         42908868                 812             2023               16
   1        162869 12.05 04:00         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  8934042471              0    612321974    572454428      61043090            59739314           233357         233550                  0               0                      0             0                0         38210402               20670             2758               92
   2        162870 12.05 04:10         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              1    572825789    514330322      56715937            55439999           216560         216740                  0               0                      0             0                0         58730257               44889             1567             9325
   2        162871 12.05 04:20         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              1    598220957    562919540      70838598            69543454           271650         271772                  0               0                      0             0                1         35998351                 337             1877                0
   2        162872 12.05 04:30         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              0    605741845    561779759      63464098            60955237           238105         238742                  0               0                      0             0                0         44223701               35896            26022                0
   1        162873 12.05 04:40         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  1075139854              1    507241980    452766912      48913039            47635018           186073         186087                  0               0                      0             0                0         54871213              215933              766            12890
   2        162873 12.05 04:40         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              0     44815083     42761030       4121686             4073878            15917          15924                  0               0                      0             0                1          1800624              195765              287                0
   1        162874 12.05 04:50         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  1075139854              0    401938344    362014495      37325547            37321002           145784         145789                  0               0                      0             0                1         38167755               85945             8828                0
   2        162874 12.05 04:50         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              1    188258303    171864837      23051209            23049807            90036          90039                  0               0                      0             0                0         16580865               25111              896                0
   2        162875 12.05 05:00         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292    10952492              0    614283130    547104399      62684542            61399568           239842         240088                  0               0                      0             0                0         67599156              266006             1720               42
   2        162876 12.05 05:10         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  7086032129              1    491644982    458997657      53480588            52203291           203917         203919                  0               0                      0             0                0         33113862               47768             2337             4853
   2        162877 12.05 05:20         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  7086032129              0    420380980    397677064      47607005            46313111           180911         181175                  0               0                      0             0                1         21555775              695084             2399                0
   1        162878 12.05 05:30         0              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  7086032129              0    604555876    567502963      62017218            60739254           237262         237414                  0               0                      0             0                0         35138863              264714           480952             1828
   1        162879 12.05 05:40         1              0 ftd8uxp52atxc 4038921846 ftd8uxp52atxc_171718292  7086032129              1    580320578    545988670      64752330            61845730           241584         241778                  0               0                      0             0                1         33763360              284977             6693              978

17 rows selected

Отличие целевого плана PHV=171718292 от данного в ощущениях PHV=4038921846 оказалось более чем значительным:

SQL> @sql_plan_diff_outl_v2 ftd8uxp52atxc 171718292 ftd8uxp52atxc 4038921846 %
--------------------------------
SQL Plan "Outline" sections diff
--------------------------------

PHV_171718292
----------------------------------------------------------------------------------------------------------------------------
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$693A5C0E" "MV_XXXX_AGENT_PAYMENT_DATA_T2"@"SEL$5")
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$9AACC4F0" "MV_XXXX_AGENT_PAYMENT_DATA_NG7"@"SEL$4")
INDEX_RS_ASC(@"SEL$693A5C0E" "MV_XXXX_AGENT_PAYMENT_DATA_T2"@"SEL$5" ("MV_XXXX_AGENT_PAYMENT_DATA_T2"."PAYMENT_BATCH_ID"))
INDEX_RS_ASC(@"SEL$9AACC4F0" "MV_XXXX_AGENT_PAYMENT_DATA_NG7"@"SEL$4" ("MV_XXXX_AGENT_PAYMENT_DATA_NG7"."PAYMENT_BATCH_ID"))
OLD_PUSH_PRED(@"SEL$EBB137E2" "AP"@"SEL$1" ("MV_XXXX_AGENT_PAYMENT_DATA_T2"."PAYMENT_BATCH_ID"))
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SET$1")
OUTLINE_LEAF(@"SEL$693A5C0E")
OUTLINE_LEAF(@"SEL$9AACC4F0")
OUTLINE_LEAF(@"SET$AD7CC163")
----------------------------------------------------------------------------------------------------
PHV_4038921846
----------------------------------------------------------------------------------------------------
FULL(@"SEL$4" "MV_XXXX_AGENT_PAYMENT_DATA_NG7"@"SEL$4")
FULL(@"SEL$5" "MV_XXXX_AGENT_PAYMENT_DATA_T2"@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SET$1")

19 rows selected

Интересно найти причину нестабильного поведения:

ORA-00600: internal error code, arguments: [6907] при выполнении alter table move partition COMPRESS

Коллега Сергей Щукин столкнулся:

19.10@SQL> alter table PART_TABLE move partition P24 tablespace PART_TS1 lob(P_ROWS) store as (tablespace PART_TS1) online compress basic parallel 8;

ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-00600: internal error code, arguments: [6907], [18446744073709521437], [0], [1], [], [], [], [], [], [], [], []

и идентифицировал опцию COMPRESS (BASIC|FOR OLTP|ROW STORE COMPRESS ADVANCED) как определяющую, опции ONLINE и PARALLEL не влияют на ошибку

Специалисты поддержки Oracle предложили проверить, как оказалось, действенный workaround:

SQL> alter session set "_widetab_comp_enabled" = false;

, однако положительный feedback донести не удалось по причине безвременно прерванных коммуникаций и, как следствие, описание ошибки на сайте Oracle пока не появилось(

Кстати, параметр _widetab_comp_enabled (wide table compression feature), появившись в 12c, кроме указанного, успешно генерирует баги в версиях 19g:

Bug 30527275 alter table hits ORA-7445 [kdblcgenblock_adjust] during non-destructive sql/dml/ddl workload
Bug 30651570 ORA-600: [kdsgrp1] After INSERT With APPEND Hint In Compressed Partitioned Table
…

Далее — детали трейсов

Фиксация планов импортированных запросов

Периодически возникает необходимость фиксировать планы выполнения «недоступных» запросов, например, в случае, если запрос всегда выполнялся на RO-standby, где непросто воспользоваться историей выполнения, и фиксировать план естественно на primary db, для чего потребуются значения 2-х полей V$SQL.SQL_FULLTEXT и V$SQL_PLAN.OTHER_XML, доступные, например, по dblink:

SQL> insert into system.temp_adg_sqltext
  2    (sql_id, sql_fulltext, plan_hash_value, other_xml)
  3    select s.sql_id, s.sql_fulltext, p.plan_hash_value, p.other_xml
  4      from gv$sqlarea@link2ro_adg  s, -- ANSI JOIN produces ORA-22992
  5           gv$sql_plan@link2ro_adg p
  6     where s.sql_id = p.sql_id
  7       and s.sql_id = '1yrunq6hc53wy'
  8       and p.plan_hash_value = 3726933773
  9       and p.other_xml is not null
 10       and rownum <= 1
 11  /

1 row inserted

SQL> commit;

Commit complete

Для создания SQL Profile этого достаточно:

19c upgrade: 4 документа поддержки и пара примеров

Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1)

List of some known bug fixes for Database Performance in 19c (Feb 4, 2022):

Bug	Description	Included in RU?	Comments
Document 30329209.8	High wait on row cache mutex after upgrading to 12.2.0.1 and above	Yes, from 19.8 onwards	Apply 19.8 or above
Document 31933451.8	High row cache mutex contention	Yes, from 19.13 onwards	Apply 19.13 or above
Document 29523216.8	Major performance bug for dc_users row cache	Yes, from 19.7 onwards	Apply 19.7 or above
Document 30712670.8	High row cache mutex contention for queries with dblink (dc_props / dc_cdbprops)	Yes, from 19.10 onwards	Apply 19.10 or above
Document 30431274.8	High row cache mutex contention (ktatminextsz) — regression of 22909260	Yes, from 19.7 onwards	Apply 19.7 or above
Document 29628647.8	High CPU for DESCRIBE command due to contention in dc_users rowcache	Yes, from 19.10 onwards	Apply 19.10 or above
Document 32043701.8	row cache lock for sequences in RAC due to S-optimization feature for dc_sequences	No	Get one-off backport
Document 30489582.8	Hanganalyze trace unable to identify the blocker of «row cache lock» in RAC	Yes, from 19.10 onwards	Apply 19.10 or above
Document 30327149.8	GEN0 process in RAC waiting on ktatminextsz while reading rowcache	Yes, from 19.7 onwards	Apply 19.7 or above
Document 30720844.8	CLMN process waits on ‘library cache: mutex X’ and/or might cause ORA-600 [kglrfcl_1]	Yes, from 19.8 onwards	Apply 19.8 or above
Document 30384121.8	LCK process in RAC holds mutex in kglHandleMessage causing database hang	Yes, from 19.7 onwards	Apply 19.7 or above
Document 32356628.8	Huge waits on ‘library cache: mutex X’ with audit enabled for ‘select any table’ privilege	Yes, from 19.12 onwards	Apply 19.12 or above
Document 28889389.8	High waits on ‘cursor:mutex X’ after upgrade	Yes, from 19.10 onwards	Apply 19.10 or above
Document 31211220.8	High version count (cursor leaks) due to BIND_EQUIV_FAILURE mismatch	No	Get one-off backport
Document 31471376.8	ORA-04024: SELF-DEADLOCK DETECTED WHILE TRYING TO MUTEX PIN CURSOR	No	Get one-off backport
Document 20319830.8	Latch Get and Free Functions Available for Shared Parent-Child Latches	Yes, from 19.11 onwards	Apply 19.11 or above
Document 31602782.8	ORA-12850/ORA-12872 or huge waits on ‘cursor: pin S wait on X’ with parallel execution	Yes, from 19.14 onwards	Apply 19.14 or above
Document 31753692.8	High waits on ‘cursor: pin S wait on X’ and ‘cursor: mutex X’ with PX_MISMATCH	Yes, from 19.10 onwards	Apply 19.10 or above
Document 30293345.8	Waits for latch: MGA Shared Context Latch After Migration to 18c or above	Yes, from 19.8 onwards	Apply 19.8 or above
Document 30614411.8	Huge delay in LGWR BOC (Broadcast-on-commit) processing in RAC	Yes, from 19.8 onwards	Apply 19.8 or above
Document 31827912.8	High waits for ‘log file sync’ & ‘remote write sync’ in RAC ADG with Fast-Start Failover (FSFO)	Yes, from 19.10 onwards	Apply 19.10 or above
Document 31176502.8	LGWR Hangs during log switch after Upgrade to 19c in RAC due to Cache Fusion Write Hang	Yes, from 19.11 onwards	Apply 19.11 or above
Document 31331038.8	ORA-600 error in ADG SYNC mode on Exadata with PMEMlog	No	Get one-off if Exadata
Document 32249371.8	High ‘log file parallel write’ waits on Exadata due to single HCA bottleneck	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32498752.8	ORA-600 [ksu_get_available_pso: numa mismatch] signaled when using parallel LGWR	No	Get one-off backport
Document 30978554.8	GC hang on read-mostly object or ORA-481 in RAC	Yes, from 19.9 onwards	Apply 19.9 or above
Document 32035536.8	Sessions Blocked by ‘gc current request’ in RAC	Yes, from 19.11 onwards	Apply 19.11 or above
Document 28697526.8	Session Hangs On ‘gc cr request’ And Other Sessions Wait On ‘cr request retry’	Yes, from 19.9 onwards	Apply 19.9 or above
Document 32227352.8	High CPU with set role command	Yes, from 19.11 onwards	Apply 19.11 or above
Document 31812824.8	Slow performance with set role command	Yes, from 19.12 onwards	Apply 19.12 or above
Document 28889730.8	«Insert As Select» or a «Create Table As Select» command consume huge space	Yes, from 19.4 onwards	Apply 19.4 or above
Document 32379140.8	LCK process in RAC crashes due to ORA-07445 [kjcvmsn()+128] [SIGSEGV]	Yes, from 19.12 onwards	Apply 19.12 or above
Document 30240930.8	Scheduler Jobs Time Classified as Background Instead of Foreground	Yes, from 19.9 onwards	Apply 19.9 or above
Document 29932310.8	AWR Report Generation Takes Long time in 19c	Yes, from 19.10 onwards	Apply 19.10 or above
Document 31489731.8	ORA-600/ORA-7445 While Shared Pool Memory is Being Freed	No	Get one-off backport
Document 31892767.8	Improvement to Temp Space Shrink	No	Get one-off backport
Document 32465193.8	ORA-4031 Due to high SQL Monitoring allocations in Shared Pool	Yes, from 19.13 onwards	Apply 19.13 or above
Document 31820859.8	ORA-4025 Due To 65535 Active Locks Limit Reached On Select NLS_CHARSET_ID	Yes, from 19.9 onwards	Apply 19.9 or above
Document 30887989.8	ORA-00001 While Generating AWR Snapshot in non-CDB with Resource Manager enabled	Yes, from 19.13 onwards	Apply 19.13 or above
Document 29423227.8	Drop Partition with global indexes hangs on library cache lock	Yes, from 19.11 onwards	Apply 19.11 or above
Document 32234161.8	Performance Slow due to High CPU post July 2020 DBRU (19.8) caused by Space Management slave processes (Wnnn)	Yes, from 19.10 onwards	Apply 19.10 or above
Document 29454450.8	High waits on «latch: cache buffers chains» in RAC	Yes, from 19.9 onwards	Apply 19.9 or above
Document 31563138.8	Securefile mutex waits when many inserts occurring on Securefile compressed LOB	Yes, from 19.11 onwards	Apply 19.11 or above
Document 32103628.8	High Latch Free Waits While Flushing Top Segment Statistics in AWR	No	Get one-off backport
Document 33123985.8	DBW0 Process Generate Huge Traces With Dumping DBWR Process State After DBRU 19.11	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32936537.8	Significant Contention on «library cache: mutex X» While accessing Interval or Auto-List Partitioned table Concurrently	No	Get one-off backport
Document 32148419.8	High Row Cache Lock Waits on Alter Table Exchange Partition in RAC Environment	Yes, from 19.12 onwards	Apply 19.12 or above
Document 30662963.8	High contention for «latch: MGA shared context root latch» When Many Sessions are Logging out	No	Get one-off backport
Document 32550751.8	ONLY FOR AIX: Performance issues due to MGA related operations in AIX	Yes, from 19.12 onwards	Apply 19.12 or above
Document 33352794.8	ONLY FOR AIX: High waits on ‘latch: MGA shared context root latch’ and ‘latch: MGA shared context latch’ even with Fix 32550751	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32117253.8	High «enq: RO — fast object reuse» waits & active checkpoint queue latch gets	Yes, from 19.12 onwards	Apply 19.12 or above
Document 30710917.8	Cursor: mutex S waits due to High Version Count For SQL Statements using Bind variables and DBLINK From 12.2	No	Get one-off backport
Document 33025005.8	Waits on ‘latch: cache buffers chains’ after Database Upgrade from 12.1.0.2 to 19c	No	Get one-off backport
Document 31387123.8	High Waits on ‘enq: IV — contention’ observed in RAC Standby environment	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32225742.8	Gathering Statistics in Primary DB Results in ORA-4061/ORA-4065 in Secondary DB	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32069508.8	High Latch: Cache Buffers Chains Contention in Standby Database	Yes, from 19.13 onwards	Apply 19.13 or above
Document 32119144.8	Large Buffer Cache on Exadata Disables Smart Scan for Parallel Queries	No	Get one-off backport
Document 33163187.8	High Version Count due To «Bind Mismatch» Even When Using Same Bind Values	No	Get one-off backport
Document 32755517.8	High Version count with USER_BIND_PEEK_MISMATCH for SQLs with bind peeking disabled	Yes, from 19.13 onwards	Apply 19.13 or above
Document 33121934.8	Library cache lock / load lock / mutex x during connection storm due to update user$	No	Get one-off backport

Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)

List of some known bug fixes for SQL Performance in 19c (Feb 18, 2022):

Bug	Description	Included in RU?	Comments
Document 31545400.8	Parallel Query Downgrades to Serial For In-memory Cursor Duration Tables	No	Get one-off backport
Document 32642587.8	Join Predicate Push Down(JPPD) not allowed for SQL with XMLTABLE and UNION-ALL	Yes, from 19.13 onwards	Apply 19.13 or above
Document 31957152.8	High Parse Time for Complex Query With Callstack kkqvmMarkNonDetermQbcCB	Yes, from 19.11 onwards	Apply 19.11 or above
Document 28414968.8	OR Expansion Not Happening Despite Cost Based or Expansion Being Enabled	Yes, from 19.11 onwards	Apply 19.11 or above
Document 30537403.8	Suboptimal Join Order for SQL Statements with Large Number of Tables	Yes, from 19.11 onwards	Apply 19.11 or above
Document 31057402.8	ORA-07445 [evaopn3]	Yes, from 19.11 onwards	Apply 19.11 or above
Document 31368027.8	Outline Hints not used for Parallel With clause SQLs	Yes, from 19.11 onwards	Apply 19.11 or above
Document 27261477.8	Parallel DOP downgrades Due to Plan Mismatch between QC and Slaves	Yes, from 19.9 onwards	Apply 19.9 or above
Document 30928343.8	QC hangs while waiting for a JOIN message reply from a Slave	Yes, from 19.10 onwards	Apply 19.10 or above
Document 32522472.8	ORA-12850 errors for SELECT queries involving CONTAINERS clause executed in CDB	No	Get one-off backport
Document 30486896.8	Parallel Queries with very high DOP greater than PARALLEL_DEGREE_LIMIT	Yes, from 19.10 onwards	Apply 19.10 or above
Document 28907129.8	Memory Corruption during Online Statistics Gathering	No	Get one-off backport
Document 30621255.8	Nested Adaptive Join Query Fails With ORA-07445 [QKACCO]	Yes, from 19.9 onwards	Apply 19.9 or above
Document 29213893.8	ORA-01422 on gather_table_stats on table sharing name with remote table	No	Get one-off backport
Document 31602782.8	ORA-12850 / ORA-12872 While Executing Queries in Parallel	Yes, from 19.14 onwards	Apply 19.14 or above
Document 29680700.8	ORA-1733 on Simple View Update	Yes, from 19.10 onwards	Apply 19.10 or above
Document 31031240.8	Insert into GTT (Global Temporary Tables) Runs Slow with increasing no.of records	Yes, from 19.11 onwards	Apply 19.11 or above
Document 29275347.8	Gather stats on fixed table X$KTFBUE automatically	No, not back-portable for 19c	Use workaround
Document 30233934.8	Query on Dictionary Views for example DBA_IND_STATISTICS Runs Slow on RAC	Yes, from 19.10 onwards	Apply 19.10 or above
Document 31143146.8	ORA-600: [qosdexpstatread: expcnt mismatch] even after applying 28681153	Yes, from 19.12 onwards	Apply 19.12 or above
Document 26749785.8	Enhancement to have more controls on Auto Statistics Advisor in 19c	No	Get one-off backport
Document 24759028.8	COPY_TABLE_STATS copies Partition level Histogram for Virtual Columns causing Incorrect Selectivity	No	Get one-off backport
Document 32345099.8	ORA-600 [19005] due to Corrupt Histogram Statistics for Table(s)	No	Get one-off backport
Document 30787757.8	Online Index Statistics happening for Global index upon Partition move or split operation	No	Get one-off backport
Document 29843277.8	ORA-12850 or ‘cursor: pin S wait on X’ waits for parallel query due to plan mismatch between QC and slaves	Yes, from 19.6 onwards	Apply 19.6 or above
Document 30222669.8	‘cursor: pin S wait on X’ waits for Parallel query with GTT (Global Temporary Tables) and high Version count with ‘Session Specific Cursor Session Mismatch(2)’	Yes, from 19.10 onwards	Apply 19.10 or above
Document 30458233.8	Diagnostic improvement for ORA-12850/ORA-12805 in Parallel execution	No	Get one-off backport
Document 29302565.8	Performance regression in all_tab_statistics	Yes, from 19.10 onwards	Apply 19.10 or above
Document 30764405.8	Queries against SYS.X$KSOLSFTS are slow in 19c	Yes, from 19.10 onwards	Apply 19.10 or above
Document 29252790.8	PX Deq: Table Q Normal and PX Deq: Execute Reply waits seen with a select statement after upgrade	Yes, from 19.12 onwards	Apply 19.12 or above
Document 32404638.8	ORA-12850 for Parallel query When Using WITH CLAUSE (Subquery Factoring)	Yes, from 19.12 onwards	Apply 19.12 or above
Document 23311885.8	Recursive Query (SQL_ID=1086bmxp9twd8) on SUM$ and SUMDETAIL$ Causes Performance Issue	Yes, from 19.12 onwards	Apply 19.12 or above
Document 31162457.8	OR Expansion Does not Happen in 19c Despite giving the OR_EXPAND Hint	No	Get one-off backport
Document 30018126.8	Skip scan suboptimally selected over range scan for filter subquery	No	Get one-off backport
Document 32851615.8	Old-Style OR Expansion Not Happening in 19c Despite Being Possible	No	Get one-off backport
Document 32754044.8	Join Predicate Push Down (JPPD) Does not Happen When Join Column in LOB	No	Get one-off backport
Document 31360214.8	Sub-optimal plan with Scalar Subquery Unnesting with table function (Collection Iterator)	Yes, from 19.13 onwards	Apply 19.13 or above
Document 33323903.8	High Parse time and PGA usage for SQL statements using Virtual Columns	No	Get one-off backport
Document 33325981.8	Subquery Unnesting Not happening from 19.9 DBRU due to some regression	Yes, from 19.13 onwards	Apply 19.13 or above
Document 33039277.8	Many SQL Statements with ANSI Joins Fail with ORA-4023 Post 19c Upgrade	No	Get one-off backport
Document 32230780.8	Cost Based Query Transformation is disabled in 19c for SQLs using ANSI Outer joins	No	Get one-off backport
Document 28513129.8	Large Queries with Many Outer Joins(Left/Right) Hangs on Parse in 19c	No	Get one-off backport
Document 32642994.8	Query Parsing Hangs on ‘row cache lock’ waits	No	Get one-off backport
Document 33145153.8	SQL Regression : Upgrade From 19.7 To 19.11 : No JPPD After Upgrade	Yes, from 19.14 onwards	Apply 19.14 or above
Document 33021036.8	After Upgrade To 19c GV$SQL_MONITOR Queries And SQLMON Generation Slow	Yes, from 19.14 onwards	Apply 19.14 or above
Document 32508585.8	Performance Impact with Hash Join when Adaptive Parallel Distribution Method is used	Yes, from 19.14 onwards	Apply 19.14 or above

Things to Consider to Avoid Prominent Wrong Result Problems on 19C Proactively (Doc ID 2606585.1)
Things to Consider to Avoid SQL Plan Management (SPM) Related Problems on 19c (Doc ID 2774029.1)

— относительно свежие, полагаю, недельной давности, обнаружены в процессе разбора пары багов, всплывших после обновления 19.5 -> 19.10 и, как оказалось, уже описанных в одном из этих документов, наряду с десятками прочих, зачастую не пофикшенных в выпущенных RU до 19.12 включительно (19c — Long Term Release — 3-й год с момента выпуска)))

Далее — детали пары наблюдаемых багов:

1. BUG 32117253 — enq: RO — Fast Object Reuse Events Since Upgrading To 19c (Doc ID 2783009.1), появился в 19.10, пока не пофикшен в RU

Oracle 19.10: CBO -> RBO Switchover

Наблюдали с коллегами красивое: при «мягком» (без рестарта инстансов) switchover с использованием dgmgrl большая часть планов выполнения, во-первых, изменились не в лучшую сторону по сравнению со стабильными и, во-вторых, планы стали бесценными:)

SQL Monitoring Report

SQL Text
------------------------------
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ ...

Global Information
------------------------------
 Status              :  EXECUTING                              
 Instance ID         :  2                                      
 Session             :  SYS (805:36665)                        
 SQL ID              :  2sy642rws6b1s                          
 SQL Execution ID    :  33554432                               
 Execution Started   :  08/20/2021 13:37:33                    
 First Refresh Time  :  08/20/2021 13:37:39                    
 Last Refresh Time   :  08/20/2021 15:40:24                    
 Duration            :  7372s                                  
 Module/Action       :  DBMS_SCHEDULER/SCOTT_MV_REFRESH_JOB
 Service             :  SYS$USERS                              
 Program             :  oracle@..net (J00A) 

Global Stats
============================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | Cluster  |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes |
============================================================================================
|    7373 |    7341 |     0.01 |        0.01 |     0.07 |       31 |     1G |   20 | 160KB |
============================================================================================

SQL Plan Monitoring Details (Plan Hash Value=1866327940)
============================================================================================================================================================================================
| Id   |          Operation           |         Name          |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Mem | Activity |    Activity Detail     | Progress |
|      |                              |                       | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |     |   (%)    |      (# samples)       |          |
============================================================================================================================================================================================
|    0 | INSERT STATEMENT             |                       |         |      |           |        |     1 |          |      |       |   . |          |                        |          |
|    1 |   LOAD TABLE CONVENTIONAL    | LU_EMPLOYEE           |         |      |           |        |     1 |          |      |       |   . |          |                        |          |
|    2 |    UNION-ALL                 |                       |         |      |           |        |     1 |          |      |       |   . |          |                        |          |
|    3 |     VIEW                     |                       |         |      |           |        |     1 |          |      |       |   . |          |                        |          |
|    4 |      WINDOW SORT PUSHED RANK |                       |         |      |      7356 |     +6 |     1 |        0 |      |       | 5MB |     0.01 | Cpu (1)                |          |
| -> 5 |       FILTER                 |                       |         |      |      7368 |     +6 |     1 |    46803 |      |       |   . |          |                        |          |
| -> 6 |        MAT_VIEW ACCESS FULL  | PER_ALL_PEOPLE_F      |         |      |      7368 |     +6 |     1 |    78028 |      |       |   . |          |                        |       7% |
| -> 7 |        MAT_VIEW ACCESS FULL  | PER_ALL_ASSIGNMENTS_F |         |      |      7373 |     +1 | 64573 |    34736 |   20 | 160KB |   . |    99.99 | Cpu (7359)             |          |
|      |                              |                       |         |      |           |        |       |          |      |       |     |          | resmgr:cpu quantum (1) |          |
|    8 |     FAST DUAL                |                       |         |      |           |        |       |          |      |       |   . |          |                        |          |
============================================================================================================================================================================================

19c upgrade: патч 31985738 для кластерных конфигураций

С квалифицированной помощью Алексея Спиричева по проблеме Instance restarts with ORA-07445 [kjcipctxfreereqlist] получили рекомендацию по установке Bug 31985738 — ORA-600:internal error code, arguments: [kjci_complete:invalid_req] (Doc ID 31985738.8)

На 3-й неделе тестирования создаётся впечатление, что патч кроме указанной решает набор проблем типа KJCI (Kernel lock management communication Cross Instance call):

ORA-07445 [kjci_initreq]
ORA-07445 [kjci_sndreqlocal]
ORA-07445 [kjci_sndreqfailure]
ORA-00600 [kjci_complete:invalid_req]

Особености event 10235

Продолжая про event, при разборе неприятной проблемы ORA-600 [532] leads to ORA-700 [ksepop:1 ksepop recursion ] аналитик поддержки с оговорками* рекомендовал в целях диагностики установить:

19.0.0.0.SCOTT@ SQL> alter system set event = '10235 trace name context forever, level 65538' container=all sid='*' scope=spfile;

System altered.

, что (после применения параметра/рестарта) приводит и интересному эффекту в части использования PL/SQL, %rowtype и EXECUTE IMMEDIATE с IN OUT параметрами (**):

19c upgrade: ORA-1555 is not reported in alert log

В начале месяца Алексей Фролов (Спортмастер) на семинаре RuOUG рассказал о непростом пути обновления на версии 19c

В дополнение к теме имеется любопытный опыт работы с Bug 31888148 — ORA-1555 is not reported in alert log after RU 19.4.0.0.0 (Doc ID 31888148.8), к счастью, уже поправленном в современных версиях:

The fix for 31888148 is first included in	
21.1.0.0.0
19.9.0.0.DBRU:201020 (OCT 2020) DB Release Update Revision(DB RU)
19.8.1.0.0 (Oct 2020) DB Release Update Revision(DB RUR)
19.7.2.0.0 (Oct 2020) DB Release Update Revision(DB RUR)

В качестве workaround-а проблемы ORA-01555 is Not Reported in Alert Log Since RU 19.4.0.0.0 (Doc ID 2656624.1) в документе указывается 2 пути: условно сложный (применение патча) и простой (изменение параметра)

SOLUTION
Please apply patch 31888148.

or

Please set the following events parameter.

SQL> alter system set events '10442 trace name context forever, level 10';

Простой путь открывает бездны не сразу