- BrainTools - https://www.braintools.ru -
Добрый день, всем любителям Oracle и пирогов из картофельных очистков*!
Одна из самых часто встречающихся проблем при использовании баз данных – это поиск узких мест: нахождение долго выполняющихся запросов и их исправление.
Как часто вы сталкивались с ситуацией, когда запрос мгновенно отрабатывает в тестовом окружении, но начинает «тормозить» на боевом сервере? Или замедление запросов проявляется не сразу, а по мере роста данных?
В одной организации, где я раньше работал, для поиска проблемных запросов использовалось логирование. Не хватает информации – нужно добавить логирования. И ввести дежурство по разбору логов. Ну как тут не вспомнить Томаса Кайта? «Не изобретайте колесо. Я видел не одну команду разработчиков, которые сталкивались с проблемами – не только на техническом уровне – из-за неосведомленности о том, что свободно предлагается Oracle. Это происходит, когда кто-то обнаруживает, что средство, на реализацию которого потрачено пару месяцев, уже есть в ядре базы данных»**. Зачем изобретать логирование, если в Oracle уже есть такие прекрасные средства, как ASH (Active Session History) и AWR (Automatic Workload Repository) – встроенные инструменты мониторинга и диагностики производительности?
Ручной анализ плана выполнения SQL-запросов, который можно получить из ASH или AWR, занимает время и требует экспертных знаний о работе конкретной базы данных Oracle, информации о схеме данных, наличии индексов, о разбиении таблиц на партиции и т.д. Но все необходимые для оптимизации данные уже есть в самой базе – это приводит к логическому выводу: процесс оптимизации можно автоматизировать.
Программа по оптимизации запросов в Oracle должна выполнять следующие действия:
Получать Top-20 (настраивается) самых тяжелых запросов из ASH или AWR;
Получать планы SQL-запросов через DBMS_XPLAN, из кэша курсоров в оперативной памяти [1] или из исторической статистики DBA_HIST_SQL_PLAN;
Собирать информацию об объектах, используемых в SQL-запросах;
Получать DML-команды объектов, используемых в запросах.
Объединив данные, мы можем, используя экспертные системы на основе LLM моделей, получить рекомендации по оптимизации запросов:
Создание индексов;
Модификация запроса (если это возможно);
Изменение способа хранения в таблицах, которые могут привести к оптимизации операций ввода-вывода.
Хочу поделиться опытом [2], представить вам свою программу: 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 [3] – под MIT лицензией, вы свободно можете ознакомиться с кодом и модифицировать программу под свои требования;
Не ругайте слишком сильно – программа написана на Python, а не на asm с xor AX, AX.
Python 3.10+
Доступ к экземпляру Oracle с включёнными ASH/AWR
API-ключ для LLM (OpenAI, DashScope) или локальный экземпляр Ollama
git clone https://github.com/aleksanderFox/optimusOracle [3]
pip install -r requirements.txt
Настроить config – указать строку подключения и LLM-провайдера
python oracle_query_optimizer.py
Полное описание на https://github.com/aleksanderFox/optimusOracle/blob/main/README.md [4]
Приведу несколько примеров того, как использование программы существенно ускорило запросы.
Запрос:
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 лишь обрабатывает большой объем данных и на их основании делает предложения об оптимизации.
В ходе опытной эксплуатации было замечено, что локальные модели, которые я смог установить на свой ноутбук с 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 млн строк):
Катастрофическая ошибка [5] в проектировании схемы данных – использование типа 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 – будет замечательно, если поделитесь опытом в комментариях.
Тема, которую хочется обойти стороной, но нельзя. Инструмент, который читает планы, структуру таблиц, статистику и тексты SQL, а потом отправляет всё это в LLM – это по определению риск утечки. Ниже – честно о том, с чем вы можете столкнутся, если будете использовать Optimus Oracle (или любой похожий инструмент) на реальной базе.
Даже если программа работает только с SELECT-запросами к системным представлениям, объём предоставляемой информации впечатляет:
Тексты SQL-запросов. Со всеми именами таблиц, колонок, схем, константами в предикатах. Иногда – с литералами, которые в проде не заменены на bind-переменные (например, номер контракта, ИНН, e-mail).
Планы выполнения. Из них видно структуру схемы: какие таблицы связаны, какие индексы есть, как партиционированы, сколько примерно строк.
Статистика. NUM_DISTINCT, AVG_ROW_LEN, гистограммы – по ним можно восстановить распределение значений в колонках.
Метаданные объектов. DDL-подобное описание таблиц, индексов, ограничений.
Фрагменты данных. В скалярных подзапросах и CASE-выражениях иногда встречаются литералы (‘Приход’, ‘Упаковка’, ‘Подборка’ как в нашем примере) – это уже бизнес-логика.
Если вы подключаете облачную LLM – всё это уходит на серверы провайдера (Google Cloud, Alibaba Cloud и т.д.), где хранится в логах в соответствии с их политикой. Даже если провайдер обещает «не использовать данные для обучения», сам факт передачи по сети – это уже канал утечки информации, который нужно осознанно закрывать.
Простой критерий выбора:
|
Критерий |
Локальные (Ollama) |
Облачные (OpenAI, Alibaba) |
|---|---|---|
|
Данные покидают периметр |
Нет |
Да |
|
Качество ответов на сложных запросах |
Среднее |
Высокое |
|
Стоимость |
Железо + электричество |
Оплата за токены |
|
Скорость |
Зависит от GPU |
Обычно выше |
|
Юридические риски |
Минимальные |
Зависит от политики |
Из моего опыта локальные модели на домашних компьютерах справляются с простыми случаями и галлюцинируют на сложных. Облачные – наоборот. Идеальный компромисс для продакшена: отдельный изолированный контур с локальной LLM для чувствительных баз и облако – для песочниц и обезличенных данных.
LLM может уверенно предложить:
индекс на таблицу, которой нет в базе;
переписать запрос с использованием колонки, которой не существует.
Правило: любая рекомендация LLM – это черновик для разработчика, а не готовое решение. В самом инструменте это заложено: он ничего не выполняет сам, только предлагает. Финальное решение – всегда за человеком.
Кто-нибудь спросит: «Зачем ты всё это написал? Опять статья про ИИ».
– Что вы всё пишете?
– Описываю предметы, ощущения. Людей. Я теперь каждый день пишу, надеясь спасти их от забвения.
– Мир Божий слишком велик, чтобы рассчитывать здесь на успех.
– Знаете, если каждый опишет свою, пусть небольшую, частицу этого мира… Хотя почему, собственно, небольшую? Всегда ведь найдётся тот, чей обзор достаточно широк*****.
Примечания:
* М. Э. Шеффер и Э. Бэрроуз «Клуб любителей книг и пирогов из картофельных очистков».
** Томас Кайт, Дарл Кун «Oracle для профессионалов» 3-е издание.
*** https://docs.modelstudio.console.alibabacloud.com/en/model-studio/qwen3-8-max?ref=bobzhu.tech [6]
**** https://ollama.com/library/gemma4:26b [7]
***** Евгений Водолазкин «Авиатор»
Автор: alisichkin
Источник [8]
Сайт-источник BrainTools: https://www.braintools.ru
Путь до страницы источника: https://www.braintools.ru/article/37200
URLs in this post:
[1] памяти: http://www.braintools.ru/article/4140
[2] опытом: http://www.braintools.ru/article/6952
[3] https://github.com/aleksanderFox/optimusOracle: https://github.com/aleksanderFox/optimusOracle
[4] https://github.com/aleksanderFox/optimusOracle/blob/main/README.md: https://github.com/aleksanderFox/optimusOracle/blob/main/README.md
[5] ошибка: http://www.braintools.ru/article/4192
[6] https://docs.modelstudio.console.alibabacloud.com/en/model-studio/qwen3-8-max?ref=bobzhu.tech: https://docs.modelstudio.console.alibabacloud.com/en/model-studio/qwen3-8-max?ref=bobzhu.tech
[7] https://ollama.com/library/gemma4:26b: https://ollama.com/library/gemma4:26b
[8] Источник: https://habr.com/ru/articles/1095954/?utm_source=habrahabr&utm_medium=rss&utm_campaign=1095954
Нажмите здесь для печати.