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';

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

Oracle 19c: TO_NUMBER, aggregate functions и проблемы параллельного выполнения

Если в версии 12.1 проблем с TO_NUMBER, агрегатными функциями и параллельным выполнением запроса не наблюдалось:

12.1.0.2.@ SQL> create table scott.t2number tablespace users as
  2  select
  3     to_char(level,'99999.99') a
  4  from dual
  5  connect by level < 1e5
  6  /

Table created.

SQL> SELECT --+ parallel(8) monitor
  2   sum(TO_NUMBER(a, '99999.99'))
  3    FROM scott.t2number
  4  /

SUM(TO_NUMBER(A,'99999.99'))
----------------------------
                  4999950000

Oracle 19c: Bug 27175987 — disable partition pruning with non-deterministic functions

По следам недавнего успешного «краудсорсингового» решения проблемы в телеграм-группе @RuOUG

Собственно, моей была только формулировка проблемы:

а не сталкивался ли кто с нерабочим partition pruning в 19.5 при использовании в качестве предикатов значений функций?
в 12.1 всё работало(

Антон подтвердил, а Саян Малакшинов — тут же предложил рабочее решение:

[xtender]
Ага, что-то смутно знакомое, кажется я тогда делал x in (select func(…) from dual)

[Anton Spitsyn]
Действительно, в Oracle 18.9 когда в предикате значение функции, partition pruning работает, а в 19.4 уже не работает. Если функцию обернуть в select func(…) from dual, то начинает работать и в 19.4

Тест:

Подсказки TABLE_STATS/INDEX_STATS/COLUMN_STATS в стандартных SQL Profile

Обратил внимание, что в стандартном автоматически созданном (Sql Tuning Advisor) SQL Profile на фоне многочисленных корректирующих OPT_ESTIMATE лишь иногда (нечасто) проскакивают хинты типа TABLE_STATS / INDEX_STATS / COLUMN_STATS:

12.1.0.2. SQL> @sql_profile_hints SYS_SQLPROF_0271cb2a05080003

HINT
---------------------------------------------------------------------------------------------------------
OPT_ESTIMATE(...
...
OPT_ESTIMATE(...
COLUMN_STATS("YO"."MANAGER", "NAME", scale, length=70 distinct=13031)
COLUMN_STATS("YO"."MANAGER", "MANAGER_CODE", scale, length=4 distinct=13272 nulls=0 min=1 max=44787)
TABLE_STATS("YO"."MANAGER", scale, blocks=964 rows=21966)

21 rows selected

, фиксирующие текущую статистику таблиц, столбцов и индексов:

Quiz: изменение механизма JPPD при обновлении 12.1.0.2->19c

in English

Столкнулись с проблемой, подготовил простой тесткейс на данных схемы HR
В SR отправил, но, возможно, я упустил что-то очевидное или кто-то уже сталкивался, или есть идеи и/или время/желание поэкспериментировать
Запрос:

select /***/ * from hr.employees e1
left join
(select location_id, department_name, max(manager_id) max_manager_id
 from hr.departments where department_name = 'IT' group by location_id, department_name)
    e2 on e2.location_id = e1.department_id
AND    (NOT EXISTS      (SELECT 1
                         FROM   hr.employees e3
                         WHERE e3.department_id = e2.max_manager_id
                           AND e3.job_id = e1.job_id))
where e1.job_id = 'FI_MGR';

в версии 12.1.0.2 выдаёт простой и быстрый план:

Fake Table Expansion на примере In Database Row Archiving

С версии 12.1 несколько раз наблюдал фэйковые Table Expansion Transformation (TE) — случаи, когда в присутствии на таблице частично Usable/Unusable Partitioned Index, оптимизатор задействует TE для операций, никак не использующих/не касающихся этого партицированного индекса

Описываемый пример (12.1.0.2) для таблицы с включеным In Database Row Archiving:

SQL> select hidden_column from dba_tab_cols where table_name = 'SLA_DISTRIBUTION_LINKS' and column_name = 'ORA_ARCHIVE_STATE';

HIDDEN_COLUMN
-------------
YES

SQL> select status, count(*)
  2    from dba_ind_partitions
  3   where index_name = 'SLA_DISTRIBUTION_LINKS_XX1'
  4     and index_name in
  5         (select index_name from dba_indexes where table_name = 'SLA_DISTRIBUTION_LINKS')
  6   group by status;

STATUS     COUNT(*)
-------- ----------
USABLE            1
UNUSABLE         18

и частично USABLE индексом (экономили место!) отчётливо проявляет проблему

Простой запрос одной/первой строки затягивается на время = Full Scan всех партиций немаленькой таблицы:

Oracle 19c: ORA-07445: [kkopmDumpPlanInfo()+3242] при попытке dbms_sqldiag.dump_trace в присутствии Baseline

В процессе тестирования Oracle 19c попытка сгенерировать CBO trace неожиданно неудачна:

19.5.0.0.0@ SQL> begin
  2  dbms_sqldiag.dump_trace(
  3     p_sql_id=>'fppuw3hpvww2d',
  4     p_child_number => 0,
  5     p_component=>'Optimizer',
  6     p_file_id=>'My_CBO_Trace_fppuw3hpvww2d');
  7  end;
  8  /

ORA-03113: end-of-file on communication channel
Process ID: 207304
Session ID: 4046 Serial number: 47139

, файл трейса обрывается на этапе SPM plan reproduce:

SPM: planId in plan baseline = 825902678, planId of reproduced plan = 3531133102
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
  parse_schema name        : APPS
  plan_baseline signature  : 10360884871991321708
  plan_baseline plan_id    : 825902678
  plan_baseline hintset    :
    hint num  1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
    hint num  2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.2.0.4') -- SQL Patch ADM-3026 -- (*)
    hint num  3 len 22 text: DB_VERSION('12.1.0.2')
...
    hint num 1213 len 57 text: USE_NL(@"SEL$E330BDE4" "AR_RECEIVABLES_TRX_ALL"@"SEL$10")
    hint num 1214 len 63 text: NLJ_BATCHING(@"SEL$E330BDE4" "AR_RECEIVABLES_TRX_ALL"@"SEL$10")

*** 2020-03-20T18:20:26.903037+03:00 (OEBS(3))
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0x9507FDA, kkopmDumpPlanInfo()+3242] [flags: 0x0, count: 1]
2020-03-20T18:20:26.989775+03:00
Incident 915457 created, dump file: /opt/oracle/base/diag/rdbms/...
ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+3242] [SIGSEGV] [ADDR:0x10] [PC:0x9507FDA] [Address not mapped to object] []

— относительно свежей (неопределяемой на сайте поддержки, по крайней мере, для моего аккаунта) ошибкой ORA-07445: exception encountered: core dump [kkopmDumpPlanInfo()+…

Проблемы online index (re)build и избыточное преобразование Table Expansion

После формально успешного создания нестандартного индекса по 5 полям — многовато, но так советовал SQL Tuning Advisor!) — для стандартной таблицы EBS:

12.1.0.2@ SQL> create index XLA.IDX$$_A39FF0001 on XLA.XLA_DISTRIBUTION_LINKS("EVENT_ID","SOURCE_DISTRIBUTION_ID_NUM_1",...) online
  2  /

Index created.

с коллегой Максимом Филатовым наблюдали необычно абсолютное игнорирование этого индекса в трейсе CBO:

******************************************
----- Current SQL Statement for this session (sql_id=5a8dk72hdtuam) -----
explain plan for
SELECT --+ index(@"SEL$1" "XDL"@"SEL$1" "IDX$$_A39FF0001")
 XDL.*
  FROM apps.XLA_DISTRIBUTION_LINKS XDL
 WHERE (XDL.SOURCE_DISTRIBUTION_ID_NUM_1, XDL.EVENT_ID) in ((1, 1))
*******************************************
...
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: XLA_DISTRIBUTION_LINKS  Alias: XDL  (Using composite stats)
  #Rows: 5426304124  SSZ: 0  LGR: 0  #Blks:  207306547  AvgRowLen:  276.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0  CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000
Index Stats::
  Index: XLA_DISTRIBUTION_LINKS_N1  Col#: 5 11 86
    USING COMPOSITE STATS
  LVLS: 4  #LB: 45196718  #DK: 1627394718  LB/K: 1.00  DB/K: 2.00  CLUF: 3255994960.00  NRW: 5503051110.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_N3  Col#: 3 4
    USING COMPOSITE STATS
  LVLS: 3  #LB: 26862145  #DK: 2359951360  LB/K: 1.00  DB/K: 1.00  CLUF: 2114999539.00  NRW: 5502258838.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_U1  Col#: 1 20 25 3
    USING COMPOSITE STATS
  LVLS: 3  #LB: 37410241  #DK: 5613018740  LB/K: 1.00  DB/K: 1.00  CLUF: 1359701404.00  NRW: 5613018740.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    ALL PARTITIONS USABLE
  Index: XLA_DISTRIBUTION_LINKS_XX1  Col#: 45 3 4
    USING COMPOSITE STATS
  LVLS: 3  #LB: 6191214  #DK: 659324928  LB/K: 1.00  DB/K: 1.00  CLUF: 358318531.00  NRW: 973461787.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00 GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1
    UNUSABLE -- означает, что индекс имеет наряду с USABLE и UNUSABLE partitions, т.е. ALL PARTITIONS USABLE != true -- *
=======================================

т.е. по таблице XLA_DISTRIBUTION_LINKS и всем её индексам статистика приводится/рассматривается, кроме нового, с виду вполне VALID-ного: