Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 dbms_redefinition долгий sync из-за SELECT 1 FROM TABLE  [new]
keldn
Member

Откуда:
Сообщений: 18
Собственно проблема буквально отображена в названии топика. Пишу в надежде может кто сталкивался, я лично облазил гугл и металинк не нашел никаких следов. Вариант SR здесь не рассматривается.

Все стабильно воспроизводится на EE 12.1 и 12.2 пропатченных PSU Oct 2018


Делаю dbms_redefinition
руками создаю interim table (удалены несколько столбцов, в start_redef_table используется col_mapping)
руками создаю timestamp based matview log:

CREATE MATERIALIZED VIEW LOG ON ORIGINAL_TABLE
PCTFREE 10 INITRANS 32 TABLESPACE TBS1 parallel 16
WITH PRIMARY KEY EXCLUDING NEW VALUES PURGE IMMEDIATE ASYNCHRONOUS;


Далее любой запуск dbms_redefinition.start_redef_table или dbms_redefinition.sync_interim_table
состоит из двух длинных фаз

1. сессия выполняет SELECT 1 FROM ORIGINAL_TABLE
с планом

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |       |   818K(100)|          |        |      |            |
|   1 |  PX COORDINATOR         |                    |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000           |    21G|   818K  (1)| 00:01:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR    |                    |    21G|   818K  (1)| 00:01:04 |  Q1,00 | PCWC |            |
|*  4 |     INDEX FAST FULL SCAN| IDX1               |    21G|   818K  (1)| 00:01:04 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access(:Z>=:Z AND :Z<=:Z)
       filter(TBL$OR$IDX$PART$NUM(<?>,0,8,0,"ORIGINAL_TABLE".ROWID)=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=8)
   - Degree of Parallelism is 16 because of session


2. Собственно вставка данных в interim table
с планом

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                      |       |       |    13M(100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                    |                      |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000             |    21G|    10T|    13M  (1)| 00:17:29 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (EQUI-PARTITION) | INTERIM_TABLE        |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |                      |    21G|    10T|    13M  (1)| 00:17:29 |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST ALL         |                      |    21G|    10T|    13M  (1)| 00:17:29 |     1 |     2 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | ORIGINAL_TABLE       |    21G|    10T|    13M  (1)| 00:17:29 |     1 |  1104 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - Degree of Parallelism is 16 because of session



Проблема в том что запрос SELECT 1 FROM ORIGINAL_TABLE на исходной таблице выполняется 1,5 часа даже через INDEX FAST FULL SCAN с параллелью 16

Что еще хуже, в момент выполнения DBMS_REDEFINITION.FINISH_REDEF_TABLE эта процедура из select и insert последовательно выполняется дважды и второй раз БЕЗ параллелизма и ни параметрами сессии ни параметрами объектов параллелизм не включается.
При этом происходит блокировка оригинальной таблицы и при размере индекса 500Гб это растягивается на часы.

На мой взгляд это противоречит самой идее dbms_redefinition, но стабильно воспроизводится на двух разных версиях БД.
4 апр 19, 17:26    [21853044]     Ответить | Цитировать Сообщить модератору
 Re: dbms_redefinition долгий sync из-за SELECT 1 FROM TABLE  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1610
keldn,

Были проблемы с commit scn based dbms_redefinition или когда поток изменений был такой, что синхронизация не справлялась.
В теме уже timestamp.
keldn
руками создаю interim table (удалены несколько столбцов, в start_redef_table используется col_mapping)

set unused online не хватит?
Учесть негативный эффект любого supplemental logging:
List of Nonblocking DDLs Added in 12.1 that Downgrade to Blocking During Supplemental Logging
SQL Language Reference
alter table set unused column online

Если supplemental logging нужен (GoldenGate/LogMiner/etc), то тоже самое можно с ddl_lock_timeout малым в цикле, пока alter table не пройдет.
keldn
Что еще хуже, в момент выполнения DBMS_REDEFINITION.FINISH_REDEF_TABLE эта процедура из select и insert последовательно выполняется дважды и второй раз БЕЗ параллелизма и ни параметрами сессии ни параметрами объектов параллелизм не включается.
При этом происходит блокировка оригинальной таблицы и при размере индекса 500Гб это растягивается на часы.

Формально, Oracle задокументировал, что будут блокировки и ввели dml_lock_timeout, чтобы минимизировать негативные последствия:
20.8.6.1 Performing Online Redefinition with Multiple Procedures in DBMS_REDEFINITION
Administrator guide
Execute the FINISH_REDEF_TABLE procedure to complete the redefinition of the table. During this procedure, the original table is locked in exclusive mode for a very short time, independent of the amount of data in the original table. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.

Можно предположить, что FINISH_REDEF_TABLE это:
первый sync_interim_table (select 1/insert), чтобы обработать то, что в mat view log (нет идей, зачем select 1).
TM lock, чтобы гарантировать отсутствие изменений по таблице
последний sync_interim_table перед подменой original и interim

Я бы попробовал SQL Patch или иные методы хинтования на parallel, хотя сомневаюсь, что поможет, раз degree объектов не помогает.
Можно с небольшим downtime то же сделать, но не выполнять FINISH_REDEF_TABLE:
1. выполнить abort_redef_table и руками сделать все DDL, приводящие к тому же конечному результату, что и FINISH_REDEF_TABLE
2. не использовать dbms_redefinition, создать mat view, синхронизировать, удалить mat view с сохранением container table, перенести все, что нужно и переименовать mat view в исходную таблицу

Сократить эти 1.5 часа нет ресурсов? Например, если читает direct path read, то в память или flash cache запихнуть? Я на Амазоне бы тип инстанса поменял, если бы нужны были экстра ресурсы.
5 апр 19, 21:38    [21854443]     Ответить | Цитировать Сообщить модератору
 Re: dbms_redefinition долгий sync из-за SELECT 1 FROM TABLE  [new]
keldn
Member

Откуда:
Сообщений: 18
SeaGate,

Спасибо за идеи.

Какое-то странное поведение, похоже что этот INDEX FAST FULL SCAN на самом деле не совсем FULL потому что его время варьируется в зависимости от количества данных для синхронизации при неизменном размерер индекса.

В итоге пока обошелся перенеся индекс на SSD RAID, блокировка сократилась до каких-то вменяемых значений.


хорошо еще что этот индекс есть - потому что без него fullscan идет по таблице ))
8 апр 19, 10:17    [21855510]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить