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

Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1)
Things to Consider to Avoid SQL Performance Problems on 19c (Doc ID 2773715.1)
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';

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

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 всех партиций немаленькой таблицы: