Добрый день, всем любителям Oracle и пирогов из картофельных очистков*!
I. Введение.
Одна из самых часто встречающихся проблем при использовании баз данных - это поиск узких мест: нахождение долго выполняющихся запросов и их исправление.
Как часто вы сталкивались с ситуацией, когда запрос мгновенно отрабатывает в тестовом окружении, но начинает «тормозить» на боевом сервере? Или замедление запросов проявляется не сразу, а по мере роста данных?
В одной организации, где я раньше работал, для поиска проблемных запросов использовалось логирование. Не хватает информации - нужно добавить логирования. И ввести дежурство по разбору логов. Ну как тут не вспомнить Томаса Кайта? «Не изобретайте колесо. Я видел не одну команду разработчиков, которые сталкивались с проблемами - не только на техническом уровне - из-за неосведомленности о том, что свободно предлагается Oracle. Это происходит, когда кто-то обнаруживает, что средство, на реализацию которого потрачено пару месяцев, уже есть в ядре базы данных»**. Зачем изобретать логирование, если в Oracle уже есть такие прекрасные средства, как ASH (Active Session History) и AWR (Automatic Workload Repository) - встроенные инструменты мониторинга и диагностики производительности?
Ручной анализ плана выполнения SQL-запросов, который можно получить из ASH или AWR, занимает время и требует экспертных знаний о работе конкретной базы данных Oracle, информации о схеме данных, наличии индексов, о разбиении таблиц на партиции и т.д. Но все необходимые для оптимизации данные уже есть в самой базе - это приводит к логическому выводу: процесс оптимизации можно автоматизировать.
Программа по оптимизации запросов в Oracle должна выполнять следующие действия:
Получать Top-20 (настраивается) самых тяжелых запросов из ASH или AWR;
Получать планы SQL-запросов через DBMS_XPLAN, из кэша курсоров в оперативной памяти или из исторической статистики DBA_HIST_SQL_PLAN;
Собирать информацию об объектах, используемых в SQL-запросах;
Получать DML-команды объектов, используемых в запросах.
Объединив данные, мы можем, используя экспертные системы на основе LLM моделей, получить рекомендации по оптимизации запросов:
Создание индексов;
Модификация запроса (если это возможно);
Изменение способа хранения в таблицах, которые могут привести к оптимизации операций ввода-вывода.
II. Инструмент.
Хочу поделиться опытом, представить вам свою программу: Optimus Oracle.
Программа позволяет подключать разнообразные LLM-модели:
Локальные, используя сервер Ollama;
Облачные - используя универсальную библиотеку OpenAI в Python для подключения к множеству облачных LLM сервисов или библиотеку Dashscope, для подключения к API-платформе от Alibaba Cloud
Программа выполняет только
SELECT-запросы к системным представлениям и словарям Oracle, не используя DML INSERT/UPDATE/DELETE/MERGE или DDL;Код программы выложен в свободном доступе на GitHub https://github.com/aleksanderFox/optimusOracle - под MIT лицензией, вы свободно можете ознакомиться с кодом и модифицировать программу под свои требования;
Не ругайте слишком сильно - программа написана на Python, а не на asm с xor AX, AX.
III. Быстрый старт.
Python 3.10+
Доступ к экземпляру Oracle с включёнными ASH/AWR
API-ключ для LLM (OpenAI, DashScope) или локальный экземпляр Ollama
git clone https://github.com/aleksanderFox/optimusOracle
pip install -r requirements.txt
Настроить config - указать строку подключения и LLM-провайдера
python oracle_query_optimizer.py
Полное описание на https://github.com/aleksanderFox/optimusOracle/blob/main/README.md
IV. Примеры использования.
Приведу несколько примеров того, как использование программы существенно ускорило запросы.
Запрос:
SELECT VAL FROM LIST_CONST WHERE UPPER(NAME) = UPPER(:B4) AND :B3 BETWEEN FD AND TD AND (NVL(PARAM,0) = NVL(:B2 ,0) AND NVL(PARAM2,0) = NVL(:B1 ,0) OR NVL(PARAM,0) = NVL(:B2 ,0) AND NVL(PARAM2,0) = 0 OR NVL(PARAM,0) = 0 AND NVL(PARAM2,0) = NVL(:B1 ,0) OR NVL(PARAM,0) = 0 AND NVL(PARAM2,0) = 0 ) ORDER BY CASE WHEN NVL(PARAM,0) = NVL(:B2 ,0) AND NVL(PARAM2,0) = NVL(:B1 ,0) THEN 1 WHEN NVL(PARAM,0) = NVL(:B2 ,0) AND NVL(PARAM2,0) = 0 THEN 2 WHEN NVL(PARAM,0) = 0 AND NVL(PARAM2,0) = NVL(:B1 ,0) THEN 3 WHEN NVL(PARAM,0) = 0 AND NVL(PARAM2,0) = 0 THEN 4 END
План (план получен с GATHER_PLAN_STATISTICS после сброса buffer cache - см. Примечание):
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers | Reads | |-----|---------------------|------------|--------|--------|--------------|---------|-------| | 0 | SELECT STATEMENT | | | 1 | 00:00:00.14 | 247 | 245 | | 1 | SORT ORDER BY | | 1 | 1 | 00:00:00.14 | 247 | 245 | |* 2 | TABLE ACCESS FULL | LIST_CONST | 1 | 1 | 00:00:00.14 | 247 | 245 |
Рекомендации qwen3.8-max*** (далее текст написан ИИ)
Узкие места:
1. Полное сканирование таблицы LIST_CONST
Таблица небольшая: 7751 строка, 382 блока, примерно 3 МБ.
Для редких выполнений TABLE ACCESS FULL здесь не является катастрофой.
Но если запрос выполняется часто, например в OLTP-режиме, полное сканирование каждый раз читает/проверяет все блоки таблицы, хотя по предикатам можно отсечь очень малую часть данных.
2. Фильтры не полностью поддерживаются существующими индексами
В запросе используются:
UPPER(NAME) = UPPER(:B4)
:B3 BETWEEN FD AND TD
NVL(PARAM,0) = NVL(:B2,0)
NVL(PARAM2,0) = NVL(:B1,0)
Существующий индекс: LIST_CONST_NAME_FD_TD_PAR (UPPER(NAME), FD, TD, PARAM) может помочь только частично с UPPER(NAME) и FD, но:
он содержит PARAM, а не NVL(PARAM,0);
он не содержит PARAM2;
он не покрывает предикаты по NVL(PARAM2,0).
Существующий уникальный индекс не подходит для этого SQL
Индекс:
LIST_CONST_NAME_TD_PARAM (LOWER(NAME), TD, NVL(PARAM,0), NVL(PARAM2,0))
не может быть напрямую использован, так как запрос фильтрует по UPPER(NAME), а не по LOWER(NAME), и дополнительно использует FD.
Сортировка не является проблемой: SORT ORDER BY обрабатывает оценочно 41 строку (реально одну - см. примечание). Даже если сортировка останется, её стоимость мала.
Избежать сортировки индексом без изменения логики запроса практически невозможно, так как ORDER BY CASE зависит от значений bind-переменных.
Рекомендация: создать функциональный составной индекс под предикаты запроса
Тип оптимизации: индекс.
Создать функциональный индекс, который соответствует наиболее селективным условиям запроса:
CREATE INDEX LIST_CONST_NAME_FD_TD_PAR2 ON LIST_CONST ( UPPER(NAME), FD, TD, NVL(PARAM, 0), NVL(PARAM2, 0), VAL ) TABLESPACE INDX ONLINE;
Конец блока, сформированного ИИ
После создания индекса план изменился:
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers | Reads | |-----|--------------------|-----------------------------|--------|--------|--------------|---------|-------| | 0 | SELECT STATEMENT | | | 1 | 00:00:00.01 | 2 | 2 | | 1 | SORT ORDER BY | | 1 | 1 | 00:00:00.01 | 2 | 2 | |* 2 | INDEX RANGE SCAN | LIST_CONST_NAME_FD_TD_PAR2 | 1 | 1 | 00:00:00.01 | 2 | 2 |
Увеличение быстродействия: в 14 раз, количество Reads уменьшилось в 122 раза.
Небольшое примечание:
Нужно признаться честно, при эксплуатации боевого сервера все сессии запускаются без STATISTICS_LEVEL = ALL (или использовании хита GATHER_PLAN_STATISTICS в SQL-запросах). Как следствие план выполнения запроса не содержит столбцов A-Rows/A-Time/etc. Поэтому текст, сформированный LLM, основан на основании столбцов E-Rows (Estimated Rows), Cost, Time (E-Time). Для анализа результативности изменений, предложенного LLM, была собрана полная статистика с использованием хита GATHER_PLAN_STATISTICS. Для чистоты эксперимента перед выполнением команд выполнялась команда очистки данных из кэша буферов.
ALTER SYSTEM FLUSH BUFFER_CACHE;
Тестирование проводилось в Oracle 11g Enterprise Edition Release 11.2.0.4.0.
Но это простой запрос. Рассмотрим более сложный запрос:
SELECT ROWNUM AS RN, ( SELECT STRAGG(VALUE(T)) FROM TABLE(KK_ASB_ST_DOC.CLOB_TO_TABLE(KK_CONVERT.BLOB_TO_CLOB(V.VAL_BLOB))) T WHERE VALUE(T) LIKE '%' || :B1 || '%' ) AS SN, V.FD, U.NAME AS PERSON_NAME, CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN INDOC.CONTRAGENT_N WHEN V.CLIENT_PROP_ITEMS_N = 325 THEN P.CONTRAGENT_N WHEN V.CLIENT_PROP_ITEMS_N = 511 THEN J.CONTRAGENT_N END AS CONTRAGENT_N, N.CODE AS NOM_CODE, CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN 'Приход' WHEN V.CLIENT_PROP_ITEMS_N = 325 THEN 'Упаковка' WHEN V.CLIENT_PROP_ITEMS_N = 511 THEN 'Подборка' END AS OPER_NAME, CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN INDOC.N WHEN V.CLIENT_PROP_ITEMS_N = 325 THEN P.N WHEN V.CLIENT_PROP_ITEMS_N = 511 THEN O.N END AS DOC_N, CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN INDOC.NUM_INVOICE WHEN V.CLIENT_PROP_ITEMS_N = 325 THEN P.DOC_NUM WHEN V.CLIENT_PROP_ITEMS_N = 511 THEN O.DOC END AS DOC FROM CLIENT_PROP_VALUES V, ACTIONS A, PRM_USERS U, ST_DOC_IN_DET INDET, ST_DOC_IN INDOC, NOMENKLATURA N, ST_DOC_PACK_DET PD, ST_DOC_PACK P, ST_DOC_OUT O, ST_BAR_JOBS J WHERE LENGTH(:B1 ) > 7 AND V.CLIENT_PROP_ITEMS_N IN (324, 325, 511) AND V.TD = DATE '3000-01-01' AND DBMS_LOB.INSTR(KK_CONVERT.BLOB_TO_CLOB(V.VAL_BLOB), :B1 ) > 0 AND A.R_OBJ_N(+) = V.N AND A.TABLENAME(+) = 'CLIENT_PROP_VALUES' AND U.N(+) = A.PRM_USERS_N AND U.TD(+) = DATE '3000-01-01' AND INDET.N(+) = V.CLIENT_N AND INDET.TD(+) = DATE '3000-01-01' AND INDOC.N(+) = INDET.SQ_ST_DOC_IN_N AND INDOC.TD(+) = DATE '3000-01-01' AND N.N = CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN INDET.NOMENKLATURA_N WHEN V.CLIENT_PROP_ITEMS_N = 325 THEN PD.NOMENKLATURA_N WHEN V.CLIENT_PROP_ITEMS_N = 511 THEN J.NOMENKLATURA_N END AND N.TD = DATE '3000-01-01' AND PD.N(+) = V.CLIENT_N AND PD.TD(+) = DATE '3000-01-01' AND P.N(+) = PD.ST_DOC_PACK_N AND P.TD(+) = DATE '3000-01-01' AND J.N(+) = V.CLIENT_N AND O.N(+) = J.BASE_DOC_N AND O.TD(+) = DATE '3000-01-01' ORDER BY FD
План:
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers | Reads | |-----|--------------------------------------|-----------------------|--------|--------|--------------|---------|-------| | 0 | SELECT STATEMENT | | | 1 | 00:01:00.41 | 12M | 327K | | 1 | SORT AGGREGATE | | 1 | 1 | 00:00:00.01 | 810 | 0 | |* 2 | COLLECTION ITERATOR PICKLER FETCH | CLOB_TO_TABLE | 408 | 1 | 00:00:00.01 | 810 | 0 | | 3 | SORT ORDER BY | | 33169 | 1 | 00:01:00.41 | 12M | 327K | | 4 | COUNT | | | 1 | 00:01:00.41 | 12M | 327K | |* 5 | HASH JOIN | | 33169 | 1 | 00:01:00.41 | 12M | 327K | |* 6 | HASH JOIN RIGHT OUTER | | 33234 | 1 | 00:00:58.91 | 12M | 327K | |* 7 | TABLE ACCESS FULL | PRM_USERS | 511 | 477 | 00:00:00.01 | 15 | 0 | |* 8 | HASH JOIN OUTER | | 33234 | 1 | 00:00:58.91 | 12M | 327K | |* 9 | HASH JOIN OUTER | | 33234 | 1 | 00:00:58.34 | 12M | 327K | | 10 | NESTED LOOPS OUTER | | 33074 | 1 | 00:00:39.97 | 12M | 158K | |* 11 | HASH JOIN RIGHT OUTER | | 33074 | 1 | 00:00:39.97 | 12M | 158K | |* 12 | TABLE ACCESS FULL | ST_DOC_IN | 64184 | 64586 | 00:00:00.03 | 2013 | 0 | |* 13 | HASH JOIN OUTER | | 33074 | 1 | 00:00:39.89 | 12M | 158K | |* 14 | HASH JOIN OUTER | | 33074 | 1 | 00:00:39.42 | 12M | 158K | |* 15 | HASH JOIN OUTER | | 33074 | 1 | 00:00:35.78 | 12M | 158K | | 16 | PARTITION LIST ALL | | 33023 | 1 | 00:00:25.90 | 12M | 122K | |* 17 | TABLE ACCESS FULL | CLIENT_PROP_VALUES | 33023 | 1 | 00:00:25.90 | 12M | 122K | |* 18 | TABLE ACCESS FULL | ST_DOC_IN_DET | 1804K | 1797K | 00:00:08.35 | 36243 | 36240 | |* 19 | TABLE ACCESS FULL | ST_DOC_PACK_DET | 3201K | 3211K | 00:00:01.05 | 36003 | 0 | |* 20 | TABLE ACCESS FULL | ST_DOC_PACK | 416K | 421K | 00:00:00.14 | 3652 | 0 | | 21 | PARTITION HASH ITERATOR | | 1 | 0 | 00:00:00.01 | 4 | 0 | | 22 | TABLE ACCESS BY LOCAL INDEX ROWID | ACTIONS | 1 | 0 | 00:00:00.01 | 4 | 0 | |* 23 | INDEX RANGE SCAN | ACTIONS_TBL_OBJ | 1 | 0 | 00:00:00.01 | 4 | 0 | | 24 | PARTITION LIST ALL | | 10M | 10M | 00:00:09.50 | 168K | 168K | | 25 | TABLE ACCESS FULL | ST_BAR_JOBS | 10M | 10M | 00:00:04.34 | 168K | 168K | |* 26 | TABLE ACCESS FULL | ST_DOC_OUT | 441K | 442K | 00:00:00.22 | 17374 | 0 | |* 27 | TABLE ACCESS FULL | NOMENKLATURA | 1215K | 1220K | 00:00:00.49 | 30513 | 0 |
Рекомендации qwen3.8-max*** (далее текст написан ИИ)
Запрос не оптимизирован: стоимость плана высокая, время оценивается в минуты, в плане много полно сканирующих операций и дорогих операций над LOB.
Основные узкие места:
1. Полный скан CLIENT_PROP_VALUES
В плане: PARTITION LIST ALL + TABLE ACCESS FULL CLIENT_PROP_VALUES.
Таблица большая: ~3.5 млн строк, 885 МБ.
Фильтры есть:
CLIENT_PROP_ITEMS_N IN (324, 325, 511)
TD = DATE ‘3000-01-01’
Существует индекс CLIENT_PROP_VALUES_I9 (CLIENT_PROP_ITEMS_N, TD, FD), но он не используется.
Причина, вероятно:
нужен доступ к VAL_BLOB;
функция KK_CONVERT.BLOB_TO_CLOB(V.VAL_BLOB) в WHERE;
оптимизатор считает полный скан дешевле;
2. Очень дорогая работа с BLOB/CLOB
В WHERE выполняется:
DBMS_LOB.INSTR(KK_CONVERT.BLOB_TO_CLOB(V.VAL_BLOB), :B1) > 0
В скалярном подзапросе снова выполняется:
KK_ASB_ST_DOC.CLOB_TO_TABLE(KK_CONVERT.BLOB_TO_CLOB(V.VAL_BLOB))
Фактически конвертация и разбор LOB выполняются многократно.
Поиск по содержимому LOB не может использовать обычный B-tree-индекс.
3. Полные сканы больших таблиц
ST_BAR_JOBS: ~10.5 млн строк, полный скан всех партиций.
ST_DOC_PACK_DET: ~3.2 млн строк.
ST_DOC_IN_DET: ~2 млн строк.
NOMENKLATURA: ~1.2 млн строк.
ST_DOC_OUT: ~450 тыс. строк.
Для большинства из них есть подходящие индексы по N, TD, но план использует HASH JOIN и полные сканы.
4. Сложные CASE в условиях соединения
Предикат:
N.N = CASE WHEN V.CLIENT_PROP_ITEMS_N = 324 THEN INDET.NOMENKLATURA_N …
Такие условия плохо оптимизируются, мешают выбору нормального порядка соединений и использованию индексов.
5. Все таблицы соединяются сразу для всех трёх типов
Для CLIENT_PROP_ITEMS_N = 324 реально нужны INDET, INDOC.
Для 325 нужны PD, P.
Для 511 нужны J, O.
Но в запросе все таблицы присутствуют одновременно.
Это может приводить к лишним соединениям и даже к нежелательному умножению строк, если по PD, J есть несколько совпадений.
Рекомендации:
Рекомендация 1: Переписывание запроса - разделение на три логические ветки через UNION ALL
Рекомендация 2: Индекс для CLIENT_PROP_VALUES под фильтры запроса
Тип оптимизации: индекс.
Действие:
Создать локальный индекс, который покрывает основные предикаты:
CLIENT_PROP_ITEMS_N TD FD CLIENT_N
Существующий индекс:
CLIENT_PROP_VALUES_I9 (CLIENT_PROP_ITEMS_N, TD, FD)
уже близок к нужному, но можно рассмотреть расширенный индекс, чтобы уменьшить обращения к таблице и улучшить доступ к CLIENT_N.
После модификации, предложенной qwen3.8-max, запрос стал выглядеть так:
with v as ( select n, fd, client_n, client_prop_items_n, val_clob from ( select n, fd, client_n, client_prop_items_n, kk_convert.blob_to_clob(val_blob) AS val_clob from client_prop_values where length(:B1) > 7 and client_prop_items_n IN (324, 325, 511) and td = date '3000-01-01' ) where dbms_lob.instr(val_clob, :B1) > 0 ), base as ( -- 324: Приход select v.fd, u.name as person_name, indoc.contragent_n, n.code as nom_code, 'Приход' as oper_name, indoc.n as doc_n, indoc.num_invoice as doc, v.val_clob from v left join actions a on a.r_obj_n = v.n and a.tablename = 'CLIENT_PROP_VALUES' left join prm_users u on u.n = a.prm_users_n and u.td = date '3000-01-01' join st_doc_in_det indet on indet.n = v.client_n and indet.td = date '3000-01-01' join st_doc_in indoc on indoc.n = indet.sq_st_doc_in_n and indoc.td = date '3000-01-01' join nomenklatura n on n.n = indet.nomenklatura_n and n.td = date '3000-01-01' where v.client_prop_items_n = 324 union all -- 325: упаковка select v.fd, u.name as person_name, p.contragent_n, n.code as nom_code, 'Упаковка' as oper_name, p.n as doc_n, p.doc_num as doc, v.val_clob from v left join actions a on a.r_obj_n = v.n and a.tablename = 'CLIENT_PROP_VALUES' left join prm_users u on u.n = a.prm_users_n and u.td = date '3000-01-01' join st_doc_pack_det pd on pd.n = v.client_n and pd.td = date '3000-01-01' join st_doc_pack p on p.n = pd.st_doc_pack_n and p.td = date '3000-01-01' join nomenklatura n on n.n = pd.nomenklatura_n and n.td = date '3000-01-01' where v.client_prop_items_n = 325 union all -- 511: подборка select v.fd, u.name as person_name, j.contragent_n, n.code as nom_code, 'Подборка' as oper_name, o.n as doc_n, o.doc as doc, v.val_clob from v left join actions a on a.r_obj_n = v.n and a.tablename = 'CLIENT_PROP_VALUES' left join prm_users u on u.n = a.prm_users_n and u.td = date '3000-01-01' join st_bar_jobs j on j.n = v.client_n join st_doc_out o on o.n = j.base_doc_n and o.td = date '3000-01-01' join nomenklatura n on n.n = j.nomenklatura_n and n.td = date '3000-01-01' where v.client_prop_items_n = 511 ) select ROWNUM AS RN, ( select STRAGG(VALUE(t)) from TABLE(KK_ASB_ST_DOC.CLOB_TO_TABLE(b.val_clob)) t WHERE dbms_lob.instr(VALUE(t), :B1) > 0 ) AS sn, b.fd, b.person_name, b.contragent_n, b.nom_code, b.oper_name, b.doc_n, b.doc from base b order by b.fd;
Конец блока, сформированного ИИ
План:
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers | Reads | |-----|--------------------------------------|----------------------------|--------|--------|--------------|---------|-------| | 0 | SELECT STATEMENT | | | 1 | 00:00:29.88 | 12M | 122K | | 1 | SORT AGGREGATE | | 1 | 1 | 00:00:00.01 | 899 | 0 | |* 2 | COLLECTION ITERATOR PICKLER FETCH | CLOB_TO_TABLE | 408 | 1 | 00:00:00.01 | 899 | 0 | | 3 | SORT ORDER BY | | 25010 | 1 | 00:00:29.88 | 12M | 122K | | 4 | COUNT | | | 1 | 00:00:29.88 | 12M | 122K | | 5 | VIEW | | 25010 | 1 | 00:00:29.88 | 12M | 122K | | 6 | UNION-ALL | | | 1 | 00:00:29.88 | 12M | 122K | |* 7 | HASH JOIN RIGHT OUTER | | 3475 | 0 | 00:00:01.18 | 526K | 14932 | |* 8 | TABLE ACCESS FULL | PRM_USERS | 511 | 477 | 00:00:00.01 | 15 | 0 | | 9 | NESTED LOOPS OUTER | | 3475 | 0 | 00:00:01.18 | 526K | 14932 | |* 10 | HASH JOIN | | 3240 | 0 | 00:00:01.18 | 526K | 14932 | |* 11 | HASH JOIN | | 3240 | 0 | 00:00:01.18 | 526K | 14932 | |* 12 | HASH JOIN | | 3693 | 0 | 00:00:01.18 | 526K | 14932 | |* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | CLIENT_PROP_VALUES | 3687 | 0 | 00:00:01.17 | 526K | 14932 | |* 14 | INDEX RANGE SCAN | CLIENT_PROP_VALUES_I3 | 74634 | 26656 | 00:00:00.02 | 111 | 0 | |* 15 | TABLE ACCESS FULL | ST_DOC_IN_DET | 1804K | 0 | 00:00:00.01 | 0 | 0 | |* 16 | INDEX FAST FULL SCAN | ST_DOC_IN_N_TD_COVER | 64184 | 0 | 00:00:00.01 | 0 | 0 | |* 17 | INDEX FAST FULL SCAN | NOMENKLATURA_N_TD_CODE | 1215K | 0 | 00:00:00.01 | 0 | 0 | | 18 | PARTITION HASH ITERATOR | | 1 | 0 | 00:00:00.01 | 0 | 0 | |* 19 | INDEX RANGE SCAN | ACTIONS_TBL_OBJ_PRMUSR | 1 | 0 | 00:00:00.01 | 0 | 0 | |* 20 | HASH JOIN RIGHT OUTER | | 19636 | 1 | 00:00:27.41 | 11M | 107K | |* 21 | TABLE ACCESS FULL | PRM_USERS | 511 | 477 | 00:00:00.01 | 15 | 0 | | 22 | NESTED LOOPS OUTER | | 19636 | 1 | 00:00:27.41 | 11M | 107K | |* 23 | HASH JOIN | | 18310 | 1 | 00:00:27.41 | 11M | 107K | |* 24 | HASH JOIN | | 18310 | 1 | 00:00:26.95 | 11M | 107K | |* 25 | HASH JOIN | | 18310 | 1 | 00:00:25.67 | 11M | 107K | | 26 | PARTITION LIST ALL | | 18310 | 1 | 00:00:22.26 | 11M | 107K | |* 27 | TABLE ACCESS FULL | CLIENT_PROP_VALUES | 18310 | 1 | 00:00:22.26 | 11M | 107K | |* 28 | INDEX FAST FULL SCAN | ST_DOC_PACK_DET_N_TD_COVER | 3201K | 3211K | 00:00:00.99 | 15953 | 433 | |* 29 | INDEX FAST FULL SCAN | NOMENKLATURA_N_TD_CODE | 1215K | 1220K | 00:00:00.37 | 5798 | 0 | |* 30 | TABLE ACCESS FULL | ST_DOC_PACK | 416K | 421K | 00:00:00.14 | 3652 | 0 | | 31 | PARTITION HASH ITERATOR | | 1 | 0 | 00:00:00.01 | 4 | 0 | |* 32 | INDEX RANGE SCAN | ACTIONS_TBL_OBJ_PRMUSR | 1 | 0 | 00:00:00.01 | 4 | 0 | |* 33 | HASH JOIN RIGHT OUTER | | 1899 | 0 | 00:00:01.29 | 611K | 21 | |* 34 | TABLE ACCESS FULL | PRM_USERS | 511 | 477 | 00:00:00.01 | 15 | 0 | | 35 | NESTED LOOPS OUTER | | 1899 | 0 | 00:00:01.29 | 610K | 21 | |* 36 | HASH JOIN | | 1771 | 0 | 00:00:01.29 | 610K | 21 | |* 37 | HASH JOIN | | 1822 | 0 | 00:00:01.29 | 610K | 21 | | 38 | NESTED LOOPS | | 3985 | 0 | 00:00:01.29 | 610K | 21 | | 39 | NESTED LOOPS | | 3985 | 0 | 00:00:01.29 | 610K | 21 | |* 40 | TABLE ACCESS BY GLOBAL INDEX ROWID | CLIENT_PROP_VALUES | 3966 | 0 | 00:00:01.29 | 610K | 21 | |* 41 | INDEX RANGE SCAN | CLIENT_PROP_VALUES_I3 | 80277 | 37974 | 00:00:00.03 | 160 | 1 | |* 42 | INDEX RANGE SCAN | ST_BAR_JOBS_N_TD_FD | 1 | 0 | 00:00:00.01 | 0 | 0 | | 43 | TABLE ACCESS BY GLOBAL INDEX ROWID | ST_BAR_JOBS | 1 | 0 | 00:00:00.01 | 0 | 0 | |* 44 | TABLE ACCESS FULL | ST_DOC_OUT | 441K | 0 | 00:00:00.01 | 0 | 0 | |* 45 | INDEX FAST FULL SCAN | NOMENKLATURA_N_TD_CODE | 1215K | 0 | 00:00:00.01 | 0 | 0 | | 46 | PARTITION HASH ITERATOR | | 1 | 0 | 00:00:00.01 | 0 | 0 | |* 47 | INDEX RANGE SCAN | ACTIONS_TBL_OBJ_PRMUSR | 1 | 0 | 00:00:00.01 | 0 | 0 |
Увеличение быстродействия: в 2 раза, количество Reads уменьшилось почти в 3 раза.
Ушли полные сканирования (TABLE ACCESS FULL) таблиц ST_DOC_PACK_DET, ST_BAR_JOBS, хотя остались для ST_DOC_IN_DET, ST_DOC_PACK и CLIENT_PROP_VALUES.
Присутствует тройное чтение CLIENT_PROP_VALUES: CTE V используется трижды внутри UNION ALL, но оптимизатор не материализует его автоматически (можно добавить хинт /*+ MATERIALIZE */). В плане это выражается тремя независимыми доступами к CLIENT_PROP_VALUES. Очевидно, что данный запрос можно продолжить оптимизировать, но уже без использования LLM.
В любом случае решение об изменении запросов или добавлении индексов принимает разработчик. LLM лишь обрабатывает большой объем данных и на их основании делает предложения об оптимизации.
V. Опыт эксплуатации.
В ходе опытной эксплуатации было замечено, что локальные модели, которые я смог установить на свой ноутбук с 16 ГБ памяти: qwen3:14b и gemma4:26b**** - справляются только с простыми задачами.
К примеру:
============================================================ Объект: BI_SHIPMENTS_RESERVE (TABLE) ============================================================ Размер: 3054 МБ, TS: USERS Столбцы: ДАТАЗАГРУЗКИ DATE NULL NDV=611 ДАТА DATE NULL NDV=625 СЧЁТФАКТУРА CHAR NULL NDV=135872 НОМЕРАКТА CHAR NULL NDV=75816 КОДКОНТРАГЕНТА CHAR NULL NDV=92 КОДНОМЕНКЛАТУРЫ CHAR NULL NDV=42016 СКЛАД CHAR NULL NDV=14 ПЛАН FLOAT NULL NDV=1951 ЗАРЕЗЕРВИРОВАНО FLOAT NULL NDV=2008 ПОДОБРАНО FLOAT NULL NDV=1994 ОТГРУЖЕНО FLOAT NULL NDV=1994
Рекомендации gemma4:26b (далее текст написан ИИ)
Анализ текущего состояния
Истинная причина низкой производительности (20 минут на 1.4 млн строк):
Катастрофическая ошибка в проектировании схемы данных - использование типа CHAR вместо VARCHAR2 для строковых полей.
Тип CHAR всегда дополняет значение пробелами до заявленной длины.
СКЛАД CHAR(1000)занимает 1000 байт на каждую строку, даже если название склада - 5 символов.КОДНОМЕНКЛАТУРЫ CHAR(200),СЧЁТФАКТУРА CHAR(100)и т.д.
В результате средняя длина строки (avg_row_len) раздута до 1483 байт. Таблица весом 3 ГБ на диске содержит по факту лишь несколько сотен мегабайт полезных данных. Оптимизатор вынужден читать 390 041 блок (3 ГБ) с диска в память, что создаёт колоссальную нагрузку на подсистему ввода-вывода (I/O) и потребляет огромный объем PGA/SGA.
Конец блока, сформированного ИИ
Для более сложных запросов локальные модели не справлялись и галлюцинировали - предлагали использование таблиц, которых нет в базе данных.
Облачные модели Alibaba Cloud - qwen3, хорошо анализировали сложные SQL-запросы и предлагали полезные советы. Ценно и то, что модели предлагали проанализировать использование индексов, близких к созданным, и по результату оставить или удалить их.
Мой совет, если вам важна приватность, и вы не хотите передавать статистику и структуру данных, используйте только локальные модели с высокой разрядностью квантования, чтобы не терять в качестве ответов.
Доступа к другим облачным моделям у меня не было - поэтому, если вам будет интересна эта программа и вы подключите другие облачные LLM - будет замечательно, если поделитесь опытом в комментариях.
VI. Безопасность и приватность.
Тема, которую хочется обойти стороной, но нельзя. Инструмент, который читает планы, структуру таблиц, статистику и тексты SQL, а потом отправляет всё это в LLM - это по определению риск утечки. Ниже - честно о том, с чем вы можете столкнутся, если будете использовать Optimus Oracle (или любой похожий инструмент) на реальной базе.
Что именно покидает базу данных
Даже если программа работает только с SELECT-запросами к системным представлениям, объём предоставляемой информации впечатляет:
Тексты SQL-запросов. Со всеми именами таблиц, колонок, схем, константами в предикатах. Иногда - с литералами, которые в проде не заменены на bind-переменные (например, номер контракта, ИНН, e-mail).
Планы выполнения. Из них видно структуру схемы: какие таблицы связаны, какие индексы есть, как партиционированы, сколько примерно строк.
Статистика. NUM_DISTINCT, AVG_ROW_LEN, гистограммы - по ним можно восстановить распределение значений в колонках.
Метаданные объектов. DDL-подобное описание таблиц, индексов, ограничений.
Фрагменты данных. В скалярных подзапросах и CASE-выражениях иногда встречаются литералы (‘Приход’, ‘Упаковка’, ‘Подборка’ как в нашем примере) - это уже бизнес-логика.
Если вы подключаете облачную LLM - всё это уходит на серверы провайдера (Google Cloud, Alibaba Cloud и т.д.), где хранится в логах в соответствии с их политикой. Даже если провайдер обещает «не использовать данные для обучения», сам факт передачи по сети - это уже канал утечки информации, который нужно осознанно закрывать.
Облако vs локальные модели
Простой критерий выбора:
Критерий | Локальные (Ollama) | Облачные (OpenAI, Alibaba) |
|---|---|---|
Данные покидают периметр | Нет | Да |
Качество ответов на сложных запросах | Среднее | Высокое |
Стоимость | Железо + электричество | Оплата за токены |
Скорость | Зависит от GPU | Обычно выше |
Юридические риски | Минимальные | Зависит от политики |
Из моего опыта локальные модели на домашних компьютерах справляются с простыми случаями и галлюцинируют на сложных. Облачные - наоборот. Идеальный компромисс для продакшена: отдельный изолированный контур с локальной LLM для чувствительных баз и облако - для песочниц и обезличенных данных.
Галлюцинации - это не баг, а свойство
LLM может уверенно предложить:
индекс на таблицу, которой нет в базе;
переписать запрос с использованием колонки, которой не существует.
Правило: любая рекомендация LLM - это черновик для разработчика, а не готовое решение. В самом инструменте это заложено: он ничего не выполняет сам, только предлагает. Финальное решение - всегда за человеком.
VII. Заключение:
Кто-нибудь спросит: «Зачем ты всё это написал? Опять статья про ИИ».
- Что вы всё пишете?
- Описываю предметы, ощущения. Людей. Я теперь каждый день пишу, надеясь спасти их от забвения.
- Мир Божий слишком велик, чтобы рассчитывать здесь на успех.
- Знаете, если каждый опишет свою, пусть небольшую, частицу этого мира… Хотя почему, собственно, небольшую? Всегда ведь найдётся тот, чей обзор достаточно широк*****.
Примечания:
* М. Э. Шеффер и Э. Бэрроуз «Клуб любителей книг и пирогов из картофельных очистков».
** Томас Кайт, Дарл Кун «Oracle для профессионалов» 3-е издание.
*** https://docs.modelstudio.console.alibabacloud.com/en/model-studio/qwen3-8-max?ref=bobzhu.tech
**** https://ollama.com/library/gemma4:26b
***** Евгений Водолазкин «Авиатор»
