Ускорение в 200 раз — не предел

Всем привет, меня зовут Сергей Татарцев. Я эксперт-разработчик розничной АБС в банке Уралсиб. В финтехе уже много лет, в Уралсибе несколько месяцев и моя ключевая задача здесь – оптимизация в СУБД Oracle. Мне нравится эта тема, она дает развитие инженерному творчеству ^[1] и очень похожа на спорт, где от подхода к подходу видишь, что взял бОльший вес штанги или планку выше предыдущей.

Мое погружение в работу проходило постепенно, не было задач из серии «бросаемся на амбразуру». Процесс онбординга шёл плавно, в том числе и на тестовых задачах.
В этой статье я хочу поделиться одним из таких тестовых заданий. Где мне удалось ускорить один простой запрос в 250 раз, а подход к решению задачи взят к применению на похожих кейсах. Сразу скажу: сработала комбинация оракловые фишки+математические алгоритмы. Если у вас есть долгоиграющие запросы – мой кейс поможет вам посмотреть на них под другим углом. А может быть в комментариях вы научите меня чему-то новому.  

В чем суть задачи? Есть стейдж-таблица, назовем её TTABLE_CON_MIXER на 74000 записей. В ней все поля заполнены, кроме поля C_NAME – оно пустое.
Есть большая таблица-донор CLIENT на 14 миллионов записей.
В стейдж-таблице требуется для ~74000 записей сгенерить вымышленные фио на основании таблицы донора и заполнить пустую колонку C_NAME. Затем данные из стэйдж-таблицы TTABLE_CON_MIXER использовались многократно.
Чем не тестовое задание для собеса, да? 😉

Старый алгоритм для этого делал так:

1. Брались 3 случайные записи из большой таблицы.

2. От первой записи бралось имя, от второй отчество, от третьей – фамилия.

3. Так рождалось новое ФИО, которое и вставлялось в TTABLE_CON_MIXER.

Подытожим:

• Объем стейдж-таблицы: TTABLE_CON_MIXER 74 000 строк для обезличивания. Таблица подготовлена заранее.

• Состав таблицы: ID, ID клиента, Имя – ПУСТОЕ (его предстоит сгенерить), Пол.

• Объем таблицы-донора (ФИО) CLIENT где живут запчасти новых вымышленных имен: 14 млн реальных записей.

  Жесткие критерии качества:

• 🎯 100% Уникальность — ровно 0 дубликатов ФИО на выходе (Старый алгоритм на популяции 74К порождал до 1500 дубликатов)

• ⚧ Гендерное соответствие — мужские фамилии к мужским именам, женские — к женским

Как было “До” – Анатомия катастрофы

Старая реализация. Построчный UPDATE + вызов функции RANDOM_NAME для каждого клиента:

update Z#TTABLE_CON_MIXER a1 
set a1.C_NAME = RANDOM_NAME(SEX => a1.C_SEX)
/

Время работы 13 мин для 74 000 записей!!!

Построчный UPDATE (Row-by-Row Context Switching):
Конструкция update … set C_NAME = RANDOM_NAME(C_SEX) заставляла Oracle для каждого из 74 000 клиентов переключать контекст между SQL-движком
и PL/SQL-движком функции RANDOM_NAME. Но это полбеды.
Давайте заглянем под капот функции RANDOM_NAME, вот там начинается самая интересная часть нашего расследования!

Алгоритм функции крайне прост:
ЕСЛИ ПОЛ ЗАДАН
  ЗАПРОС ДЛЯ НОВОГО ИМЕНИ с учётом пола

ИНАЧЕ (а такое, как оказалось – бывает 😊)

 ЗАПРОС ДЛЯ НОВОГО ИМЕНИ без учёта пола

ЕСЛИ ничего не нашли (а такое, как оказалось – бывает)
  возврат default значения «Иванов Иван Иванович»
ИНАЧЕ
  возврат того, что нашли!

Поскольку из 74000 записей лишь у троих не был задан пол, мы будем рассматривать анатомию запроса ДЛЯ НОВОГО ИМЕНИ с учётом пола. Запрос без учета пола делал то же самое, но просто не было проверки EXISTS.

Поиск ФИО с «тройным сальто назад».

Вот запрос:

select  substr(trim(F || ' ' || I || ' ' || O), 1, 250) x
 from( select     rownum idx
                 ,c_name name
       from    z#client cl
       where cl.class_id = 'CL_PRIV'
             and   rownum <= 3
             and   mod(cl.id, floor(utils.Random(1000))) = 0
             and   upper(cl.c_name) not like 'НЕ ИСПОЛЬЗОВАТЬ%'
             and   exists( select    /*+ FIRST_ROWS */ null
                           from    z#cl_priv clp
                           where clp.id = cl.id
                                 and clp.C_SEX + 0 = :B1))
pivot(max(regexp_substr(name, 'S+', 1, idx)) 
for idx in(1 "F", 2 "I", 3 "O"));

Итак, что мы тут видим? Давайте разбираться.
Дословно тут сказано:

1. Заходи в таблицу z#client (в которой 14 миллионов записей) и читай запись.

2. Возьми айди этой записи и попробуй разделить её без остатка на случайное число от 1 до 1000. Заодно убедись, что найденное имя не соответствует «стоп-слову» НЕ ИСПОЛЬЗОВАТЬ.

3. Если это сделать удалось, проверь в другой таблице пол клиента. Если этот клиент не подходит по полу, то переходи к п.1. А если пол подошел – забирай запись в итоговую выборку.

4. Остановись, когда найдешь три записи.

5. Из трёх записей оператором PIVOTсделай одну запись, взяв через regexp_substrот первой найденной записи Фамилию, от второй – имя, от третьей – отчество.

И знаете, этот запрос отрабатывал не то, чтобы долго, а скажем так – вполне себе быстро! 4 сотых секунды!!!

04 сотых, вроде не долго, НО!

Давайте прежде всего посмотрим на статистику таблицы CLIENT.
А что с таблицей Z#CLIENT?

NUM_ROWS   BLOCKS  AVG_ROW_LEN 	ROWS_PER_BLOCK 	ROWS_PER_MULTIBLOCK
---------	------- ----------- --------------	-------------------
14017480   705394         481             	17          	     272

Как видим из статистики, в одном блоке данных, в среднем помещается 17 строк.
Но СЕРВЕР НАСТРОЕН НА МУЛЬТИБЛОЧНОЕ ЧТЕНИЕ по 16 блоков:
db_file_multiblock_read_count = 16

ROWNUM <=3 а трое ли их?
А вот теперь давайте посмотрим на реальный план выполнения этого запроса:

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | A-Rows |   A-Time   | Buffers | Reads  |
|                                                                                           ПАМЯТЬ    ДИСК   |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |      1 |      1 |00:00:00.04 |     287 |    299 |
|   1 |  VIEW                            |                 |      1 |      1 |00:00:00.04 |     287 |    299 |
|   2 |   SORT AGGREGATE                 |                 |      1 |      1 |00:00:00.04 |     287 |    299 |
|   3 |    VIEW                          |                 |      1 |      3 |00:00:00.04 |     287 |    299 |
|*  4 |     COUNT STOPKEY                |                 |      1 |      3 |00:00:00.04 |     287 |    299 |
|   5 |      NESTED LOOPS SEMI           |                 |      1 |      3 |00:00:00.04 |     287 |    299 |
|*  6 |       TABLE ACCESS FULL          | Z#CLIENT        |      1 |      8 |00:00:00.02 |     259 |    279 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| Z#CL_PRIV       |      8 |      3 |00:00:00.02 |      28 |     20 |
|*  8 |        INDEX UNIQUE SCAN         | PK_Z#CL_PRIV_ID |      8 |      8 |00:00:00.01 |      18 |     10 |
--------------------------------------------------------------------------------------------------------------

1. На шаге 6 плана, с которого начинается выполнение данного запроса,  Oracle совершает 18 физических подходов к диску и одномоментно поднимает в память ^[2] 4 743 строки из таблицы Z#CLIENT.  279 считанных блоков / 16 блоков за раз (db_file_multiblock_read_count = 16) = 17.4 –> 18 чтений.

2. Из этих 4 743 строк локальные фильтры в памяти (MOD и LIKE) пропускают наверх всего 8 записей (столбец A-Rows на шаге 6 равен 8). Эффективность отбора на этом этапе — всего 0.16%!

3. Из этих 8 выживших строк оператор соединения NESTED LOOPS SEMI (это эксистс – шаг 5) проверяет пол по индексу во второй таблице (Шаги 8 и 7) и оставляет только 3 строки с найденым полом, после чего COUNT STOPKEY наконец-то тушит этот запрос ( ШАГ 4).

   Вернее, лишь на 8я запись, которая влетела с шага 6 на шаги 8->7 оказалась третьей искомой. Я гонял запрос многократно и это число на связке шагов 6 ->8->7 постоянно было разным: 5,7,8,11,15 даже… Ну не попадались три женщины сразу подряд. Шерше ла фам! 😊

То есть, чтобы просто найти 3 случайные строки, база данных при каждом чихе была вынуждена физически выкачивать с диска и перебирать процессором почти 5 000 строк! И так 74 000 раз подряд.

А что же в цикле?

Для того, чтобы показать масштаб бедствия, я завернул этот запрос в цикл и заставил его исполниться 74000 раза. А сам снял SQLMonitorReport

Посмотрите на эти цифры: 13 минут выполнения, 16 миллионов логических чтений в памяти и ОДИН МИЛЛИОН физических запросов к дискам! Скрипт практически парализовал дисковую подсистему стенда. Стоит правда отметить, что в цикле единичный запрос выполнялся уже не за 0.04 сек, как на плане выше, а за: 785 сек / 74000 итераций = ~0.01 сек.
Вероятно, сказался «нагретый» кэш. Записи же поднимались одни и те же на каждой итерации – ORACLE не умеет при TABLE ACCESS FULL заходить в таблицу не с начала.
Но тем не менее 13 минут на 74 000 записей – это очень долго.

Первый подход: оптимизируем сам запрос.

select 
      substr(trim( 
          max(decode( rownum, 1, clp.C_FAMILY_CL)) 
|| ' ' || max(decode( rownum, 2, clp.C_NAME_CL)) 
|| ' ' || max(decode( rownum, 3, clp.C_SNAME_CL)))
      , 1, 250)  x1
from z#cl_priv SAMPLE BLOCK (0.1) clp
where rownum <= 3  
         and mod( clp.id, floor(utils.Random(10))) = 0
         and upper(clp.c_name_cl) not like 'ИСПОЛЬЗОВАТЬ%'
         and clp.c_sex = :B1;

• Вводим сэмпл блок

• Уменьшаем величину рандомизации ( в выборку будут попадать только 10 случайных процентов от считанного семпла

• Убираем связку из двух таблиц, ведь ФИО и пол есть в одной

• Заменяем оператор PIVOT в пользу простого MAX(DECODE(…

Расшифруем.

1. Сама по себе конструкция SAMPLE BLOCK заставит ORACLE заходить в таблицу каждый раз не с начала, как было до этого, а в разные её части, вычитывая сэмпл, который равен 0.1%  от общего числа блоков. При этом подкапотный ораклиный рандом заставит эти блоки выбираться случайным образом.
   То есть мы будем получать случайные небольшие порции данных, всегда разные при повторных обращениях.

2. Из вычитанных данных мы будем оставлять лишь 10% (mod( clp.id ^[3], floor(utils.Random(10))) = 0) . Без этого можно было тоже. Но у нас задача минимизировать дубликаты. А при многократном циклическом вызове, вероятность  нарваться на одни и те же блоки данных сильно возрастает. Я экспериментировал с этой цифрой и пришел экспериментальным путём к такому балансу между повторами ФИО и производительностью.

3. Оказалось, что у дочерней таблицы CL_PRIV, которая связана с CLIENT 1 к 1, и через которую раньше мы проверяли пол, есть и имя и фамилия и отчество, так почему же не читать сразу её, исключая связку двух многомиллионных таблиц?

04 сотых превращаются в 01! Или нечто меньшее?
Давайте теперь посмотрим на статистику таблицы CL_PLIV

NUM_ROWS 	BLOCKS 	AVG_ROW_LEN ROWS_PER_BLOCK ROWS_PER_MULTIBLOCK 
---------- ---------- ----------- -------------- ------------------- 
12991980 	 344021 	     193 		42 				672

И сразу план:

---------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |      1 |00:00:00.01 |       4 |     23 |
|   1 |  SORT AGGREGATE       |           |      1 |      1 |00:00:00.01 |       4 |     23 |
|*  2 |   COUNT STOPKEY       |           |      1 |      3 |00:00:00.01 |       4 |     23 |
|*  3 |    TABLE ACCESS SAMPLE| Z#CL_PRIV |      1 |      3 |00:00:00.01 |       4 |     23 |
---------------------------------------------------------------------------------------------

ORACLE для получения 3 записей на шаге 3 (он выполнялся первым) считал за 2 подхода к диску 23 блока в которых было 966 записей. (42*23 = 966)
Это в пять раз меньше, чем в старом плане на первом шаге!
Этих кандидатов хватило, чтобы тут же отфильтровать троих в памяти по фильтрам mod и like на этом же шаге и «потушить» запрос стопкеем (шаг 2).

Execution plan снятый display_cursor не показывает значения точнее одной СОТОЙ секунды.
Нужны иные инструменты, например трассировка. Поэтому цифру 00:00:00.01 в колонке A-Time следует расценивать, не буквально, а как «ничтожно малое».

Давайте теперь посмотрим, а что же в цикле, сняв SQL Monitor Report.

200 секунд делим на 74000, получаем 0.0027… на одно выполнение вот такая математика ^[4].
Время выполнения сократилось многократно c 0.04 до 0.0027!
Но 200 секунд смотрелось всё равно как-то не очень. И тут меня осенило.

«Экономный» подход. Включаем голову!

Три человека – это три имени, три фамилии и три отчества.
Почему из трёх найденных записей мы получаем одного Иванова Петра Сидоровича? А как же Петров Сидор Иванович? А чем хуже Сидоров Иван Петрович?
Ведь за ними не нужно ходить в базу – они уже считаны!!! Мы из 9 считанных запчастей оставляем 3, а 6 летят в корзину. Добавляем ещё 2 колонки в оптимизированный запрос, выстраивая матрицу по ROWNUM

В итоге число обращений в базу становится ВТРОЕ МЕНЬШЕ!!!
Я быстро переписал запрос, и функция стала отдавать по три ФИО, вместо одного.

Снял SQL Monitor для вызова запроса в цикле из 24666 итераций (74000/3 = 24666).

Как видим, время стало вместо 785 сек –> 71.
Выигрыш в 11 раз.
Что я сделал дальше?
Я ушел от переключения контекста, заменяя UPDATE на FORALL.
План такой

1. Считываем из TTABLE_CON_MIXER данные, упорядочивая их по ПОЛУ.

2. В цикле идём, вызывая на каждой третьей записи функцию RANDOM_NAME, которая отдаёт по три имени.

3. Складываем считанные имена в структуру для FORALL.

4. Как только пол сменился, выполняем порционный АПДЕЙТ через FORALL и COMMIT. Всего 3 апдейта: мужчины, женщины, пол не указан.

Время выполнения стало около 2 мин.
И можно было бы остановиться, если бы не одно «НО»!!!

Проблема ДУБЛИКАТОВ.

На популяции 10 000 человек алгоритм генерил около 50 дубликатов.
Гоняя на разных по объему выборках новый алгоритм, я выяснил, что процент дупликации крутится около 0,5%.
На 74 К дубликатов было от 480 до 650.
Это уже было связано с достижением предела ораклового движка рандомизации (даже с SAMPLE BLOCK) на частых цикличных вызовах, когда буферный кэш разогрет.
Кроме того, никто не отменял реальных «ТЁЗОК», хотя тут сценарий менее вероятен.

К слову сказать, старый алгоритм порождал дубликаты в ГОРАЗДО БОЛЬШЕМ ОБЪЁМЕ (ведь там не было SAMPLE BLOCK).
Так на популяции 74 К дупликация достигала отметки около 1500 чел.

Я стал думать, а можно ли совсем без дубликатов? И тут меня осенило второй раз.

Инженерный инсайт: Включаем голову-2

Ответьте себе на вопрос: сколько нужно неповторяющихся фамилий, неповторяющихся имен и неповторяющихся отчеств, чтобы получить гарантированно допустим 50 уникальных ФИО?

… Время пошло…

Подумали?

ЧЕТЫРЕ!!! Всего 4. Если мы возьмем по 4 элемента в каждой группе, то получим:
4 х 4 х 4 = 64 уникальных ФИО
Этого количества (64 варианта) с запасом хватает, чтобы выдать 50 абсолютно неповторяющихся полных имен.
Закономерность вырисовывалась кубически простая. Всего одна математическая формула (а именно кубический корень) дала мне взглянуть на проблему под иным углом.

Новый алгоритм (Сверхбыстрый ОЗУ-конвейер)

• На входе мы всегда можем считать в коллекцию всех мужчин из таблицы TTABLE_CON_MIXER в одну коллекцию.

• Таблица 74К считывается мгновенно не зависимо от наличия индексов.

• Далее извлекаем кубический корень из числа элементов этой коллекции и увеличиваем его до ближайшего целого! Это размерность нашего «куба». В данный момент для мужчин она равна 31.

• Далее вычитываем тремя запросами 31 уникальное имя, 31 уникальное отчество и 31 уникальную фамилию.

• Далее в тройном вложенном цикле собираем из этих 93-х запчастей новые ФИО, пока не достигнем числа «КОЛИЧЕСТВА ВСЕХ МУЖЧИН».

• Далее повторяем ^[5] то же самое для женщин. Размерность их куба сейчас составляет 34.

Дополню, что поскольку размерность куба растёт крайне медленно, такого подхода хватит на много лет вперёд.  До ~74000 записей обрабатываемый этим алгоритмом объём нарастал несколько десятилетий.

А что же под капотом?

Давайте посмотрим насколько тяжело сделать 6 запросов к таблице, в которой почти 13 миллионов записей?

select distinct c_name_cl
bulk collect into names_mf.c_name
                      from z#cl_priv sample block (0.02)
                      where c_sex = :B1 
			and c_name_cl is not null 
                        and c_name_cl not like 'ИСПОЛЬЗОВАТЬ%'
      fetch first 31 rows only;

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |     31 |00:00:00.10 |      19 |     38 |       |       |          |
|*  1 |  VIEW                   |           |      1 |     31 |00:00:00.10 |      19 |     38 |       |       |          |
|*  2 |   WINDOW NOSORT STOPKEY |           |      1 |     31 |00:00:00.10 |      19 |     38 | 86016 | 86016 |          |
|   3 |    VIEW                 |           |      1 |     31 |00:00:00.10 |      19 |     38 |       |       |          |
|   4 |     HASH UNIQUE         |           |      1 |     31 |00:00:00.10 |      19 |     38 |  1646K|  1646K| 1390K (0)|
|*  5 |      TABLE ACCESS SAMPLE| Z#CL_PRIV |      1 |    329 |00:00:00.10 |      19 |     38 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=31)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=31)
   5 - filter(("C_SEX"=2047936 AND "C_NAME_CL" IS NOT NULL AND "C_NAME_CL" NOT LIKE 'ИСПОЛЬЗОВАТЬ%'))

Как видим, благодаря маленькому сэмпл блоку удаётся отсканировать часть таблицы ЦЕЛИКОМ за 0.1.
Теперь я детально расскажу, что происходило при выполнении этого запроса. Пойдём по шагам, в том порядка, как Oracle выполнял их.

Шаг 5. TABLE ACCESS SAMPLE (Чтение диска и первичный отсев).
Что происходит: Oracle инициирует поблочное вероятностное сканирование таблицы Z#CL_PRIV. Процент сэмпла равен 0.02%. Из общего объема таблицы в 344 021 блок математический генератор выбрал массив случайных блоков (математическое ожидание около 68 блоков).
Физика I/O: Oracle делает Direct Path Read порциямипо 16 блоков (db_file_multiblock_read_count = 16). На этом конкретном запуске он успел прочитать всего несколько кластеров (суммарно 38 блоков с диска, что составило примерно 38*42 = 1595 строк).
Фильтрация строк: Из прочитанных 1596 строк СУБД распаковала сырые строки и прямо «на лету» применила к ним условия WHERE (пол, исключение маски ‘ИСПОЛЬЗОВАТЬ%’ и проверку на NOT NULL).
Результат (A-Rows = 329): Успешно прошли сито первичного отбора ровно 329 строк. Именно эти строки поднимаются наверх, на Шаг 4.
Шаг 4. HASH UNIQUE (Удаление дубликатов в памяти).
Что происходит: СУБД начинает процесс уникализации (DISTINCT) поступающих с шага 5 329 строк. Физика процесса: Oracle выделяет в оперативной памяти PGA хэш-таблицу. На пике под нее ушло 1390 КБ (Used-Mem). Каждое поступающее имя пропускается через хэш-функцию и укладывается в соответствующую хэш-корзину. Дубликаты отбрасываются.
Результат (A-Rows = 31): Как только из потока строк сформировалось ровно 31 уникальное имя, этот шаг прекращает принимать новые данные. Почему? Потому что им управляет вышестоящий Шаг 2, и оптимизатор уже на входе знает, что ему нужно будет только 31 строка. Какой смысл хэшировать дальше?
Шаг 3. VIEW (Служебное представление).
 Что происходит: Oracle берет результат шага 4 (уникальные имена) и «оборачивает» его во внутреннее представление (inline view). Это необходимо, чтобы поверх уникального набора данных можно было безопасно запустить аналитическую функцию ROW_NUMBER() OVER(). Какой row_number()? В запросе же нет никаких окон! Но об этому чуть позже. Сейчас главное то, что без этой VIEW Oracle не смог бы гарантировать, что нумерация строк начнется строго после того, как отработает DISTINCT.
Шаг 2. WINDOW NOSORT STOPKEY (Главный диспетчер и «Серебряная пуля»).
Что происходит: Это ключевой оператор, который появился благодаря синтаксису FETCH FIRST 31 ROWS ONLY.
Физика процесса: Он работает как конвейерный счетчик с обратной связью. Он стоит над хэш-таблицей и считает уникальные имена, которые из нее выходят. Видите «звёздочку» около цифры 2. Это информация о предикатах. На этом шаге стоит предикат ROW_NUMBER() OVER ( ORDERBY  NULL )<=31 – это и есть та самая нумерация, про которую я говорил на шаге 3. На этом шаге (WINDOW NOSORT STOPKEY) рассчитывается колонка для каждой строки при помощи аналитической функции ROWNUMER.
Для этого шага выделяются микроскопические 84 КБ памяти (86016 байт) под внутренние структуры окна. Как только счетчик дошел до 31, оператор STOPKEY посылает сигнал «СТОП» по всему конвейеру вниз (на Шаги 4 и 5).
В этот момент магия сработала: Oracle прекратил сканировать оставшиеся блоки сэмпла. Из 329 строк, которые успел поднять Шаг 5, хэш-таблица обработала лишь малую часть — ровно столько, сколько хватило для получения 31 уникального имени. Остальные строки и блоки были просто «выброшены» СУБД за ненадобностью.
Шаг 1. VIEW(Cлужебное представление  с предикатом).
Конструкция FETCH FIRST появилась в Oracle 12c. Но сам движок БД до сих пор трансформирует её в классический подзапрос с аналитической функцией. Наш исходный запрос Oracle негласно под капотом переписывает примерно в такой вид:

SELECT C_NAME_CL 
FROM (
    SELECT C_NAME_CL, 
           ROW_NUMBER() OVER (ORDER BY NULL) AS rowlimit_$$_rownumber -- (Шаг 2)
    FROM (
        SELECT DISTINCT C_NAME_CL FROM Z#CL_PRIV SAMPLE BLOCK... -- (Шаги 4 и 5)
    ) from$_subquery$_002 –- Шаг 3 (помещение во VIEW для нумерации после дистинкта)
) from$_subquery$_003  -- <- Вот эта виртуальная VIEW из Шага 1!
WHERE rowlimit_$$_rownumber <= 31; -- <- А это предикат фильтрации!

Системные (алиасные) имена, столбцов и представлений Oracle придумал самостоятельно и присвоил их в цепочке преобразований.
Поэтому в Predicat information на шаге 1 странная нотация “from$_subquery$_003”.”rowlimit_$$_rownumber”<=31, но нас это не должно волновать, это его подкапотные дела. Самое главное, что этот (будто бы «лишний») шаг нужен, чтобы отсечь 31 строку через логику ^[6] предиката фильтрации. Странно, но Oracle этим шагом гарантирует, что в выборку попадут 31 из 31 записи! 😊
Теперь пару слов о размере Сэмпл блока. Я рассчитал его размер так, чтобы был ~десятикратный запас по именам после применения уникальности.
Играя в размером sample block, на шаге 5 В СТОЛБЦЕ  A-ROWS  я хотел видеть цифру, которая будет примерно в 10 раз больше нужной мне 31.
Почему так? Почему не сделать сэмпл блок меньше?

Дело в том, что уменьшая сэмпл блок, я увеличивал скорость, но неизбежно наступал момент, когда на каком-то этапе мне не возвращалась 31 искомая строка. Их приходило меньше, вплоть до «ниодной». 
Я прогонял в циклах по 10 000 раз эти запросы с вытаскиванием по 31 имени и фиксировал, если возвращалось менее 31 строки.
Наконец на цифре 0.02% я добился, чтобы не было зафиксировано нехватки уникальных имен.

Вы мне можете сказать, какая-то неоптимальная оптимизация выходит. И вроде как этот запрос «тяжелее» того, который был на первой и на второй итерации оптимизации, и ты тащишь больше 1000 строк ради 31!
Да!
Но старый запрос выполнялся десятки тысяч раз, а этот шесть!!! По 0.1 сотых секунды.
То есть около 0,5 секунды, чтоб собрать все запчасти для генерации всех 74 тысяч имен с гарантией уникальности.
Мужчины – Имена, мужчины – фамилии, мужчины – отчества.
Женщины – Имена, женщины – фамилии, женщины – отчества.

Шесть, Карл!

Цикл с катапультированием.

Простейший тройной вложенный цикл вместо «дисковой рулетки», с чтением десятков тысяч блоков из многострадальной таблицы стал ключом к решению гарантированного отсутствия дубликатов.

begin
  <<main_loop>>
  for i in 1..v_cube_count loop 
    for j in 1..v_cube_count loop
      for k in 1..v_cube_count loop
        v_count := v_count + 1;
        names( v_count) := 
	names_mf.c_lastname(i)||' '||names_mf.c_name(j)||' '||names_mf.c_surname(k);
        exit main_loop when v_count = v_gender_count; 
      end loop;
    end loop;  
  end loop;    
end;

Финальный штрих — Управляемый хаос.
Но возникла эстетическая проблема.

Вложенный цикл выдает имена системно: сначала идут 34 человека с одной фамилией, потом со следующей. В итоговой таблице это смотрелось бы неестественно.

Перемешивать готовую колоду ФИО в памяти — это лишние операции.

Поэтому я пошел от обратного. При первоначальном считывании клиентов в коллекцию для последующего forall, я перемешал список ID клиентов стейдж-таблицы через ORDER BY dbms_random.value.

select id
bulk collect into ids
from Z#TTABLE_CON_MIXER
where c_sex = p_sex
order by dbms_random.value;

Стейдж маленький (74К), для Oracle это мгновенная задача.
В итоге наши последовательные, строго уникальные имена легли на случайные ID клиентов “веером”. Таблица стала выглядеть абсолютно натурально, как живая.

Итоги
Время выполнения полного UPDATE Z#TTABLE_CON_MIXER : ~3,22 – 4,51 СЕК.
Дубликатов не выявлено.
В данном кейсе я смог чисто на «Oracle» довести время выполнения с 13 минут до 2-х. А далее, уже математика и изменение алгоритма помогли мне 13 минут превратить в 3 секунды. Итеративность и творчество в процессе оптимизации – в действии.

Спасибо что дочитали!
😊