Есть такой отчет в "Кассах и обменных пунктах" - Справка о суммах принятой и выданной денежной наличности. В настоящий момент он сильно тормозит. Возможно торможение стало следствием роста базы, а может и планы запросов в какой-то момент изменились. Но в любом случае мы имеем тормозной отчет.
После небольшого разбирательства стало понятно, что отчет строится на функциях библиотеки NOTRADE.LIB_RPT, где можно увидеть достаточно много сложных запросов с большим количеством условий. Вот, к примеру, один из них:
Код:
SELECT SUM(B2.C_AMOUNT)
FROM Z#SAFE B3, Z#CERT_RICHES B2, Z#DOC_RICHES B1, Z#DOC_OPER A4, Z#OPER_RICH A3, Z#SAFE A2, Z#RICHES_BANK A1
WHERE A1.C_SAFE_IN=A2.ID AND A1.C_OPER=A3.COLLECTION_ID AND A3.C_DOCS=A4.COLLECTION_ID
AND B1.C_CERT_RICHES=B2.ID(+) AND B1.C_SAFE_PLACE=B3.ID
AND (A2.C_KASSA = :B2 AND ((:B1 IS NULL AND A1.C_SAFE_IN <> A1.C_SAFE_OUT) OR A1.C_USER_IN = :B1 )
AND UPPER(A1.C_R_OPER) = 6892586 AND A1.C_DATE_INC IS NULL
AND B1.C_DATE_EXEC >= :B5
AND B1.C_DATE_EXEC < to_date(:B5 +1)
AND A4.C_DOCUM = B1.ID AND (B2.C_CASH = :B4 OR B2.C_RICHES = :B3 ) AND B3.C_KASSA = :B2
AND (:B1 IS NULL OR B1.C_USER_AUTH = :B1 )
AND (B1.C_PRIX = '1') AND B1.STATE_ID = 'PROV' AND B1.C_DOC IS NULL)
А вот план выполнения:
Код:
SELECT STATEMENT, GOAL =
SORT AGGREGATE
FILTER
NESTED LOOPS
FILTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#SAFE
INDEX RANGE SCAN Object owner=IBS Object name=Z#IX_Z#SAFE_REF8
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#RICHES_BANK
INDEX RANGE SCAN Object owner=IBS Object name=Z#IX_Z#RICHES_BANK_REF5
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#OPER_RICH
INDEX RANGE SCAN Object owner=IBS Object name=Z#IX_Z#OPER_RICH_COLL
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#DOC_OPER
INDEX RANGE SCAN Object owner=IBS Object name=IDX_Z#DOC_OPER_COLL_ID
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#DOC_RICHES
INDEX UNIQUE SCAN Object owner=IBS Object name=PK_Z#DOC_RICHES_ID
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#CERT_RICHES
INDEX UNIQUE SCAN Object owner=IBS Object name=PK_Z#CERT_RICHES_ID
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#SAFE
INDEX UNIQUE SCAN Object owner=IBS Object name=PK_Z#SAFE_ID
Если вдуматься в суть выборки, становится понятно, что наиболее оптимально выбирать сначала записи в таблице B1 по B1.C_DATE_EXEC. На это поле создан индекс, но он почему-то не подхватывается. Пробовал писать различные "общие" хинты типа RULE, FIRST_ROWS - план чуть-чуть меняется, но индекс по дате не используется. Пробовал собрать статистику по всем таблица запроса - ничего не поменялось.
Естественно есть вариант явно указать в хинте использование этого индекса, или отключить индекс Z#IX_Z#RICHES_BANK_REF5. Тогда план запроса принимает вид:
Код:
SELECT STATEMENT, GOAL =
SORT AGGREGATE
FILTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
MERGE JOIN CARTESIAN
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#SAFE
INDEX RANGE SCAN Object owner=IBS Object name=Z#IX_Z#SAFE_REF8
BUFFER SORT
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#DOC_RICHES
INDEX RANGE SCAN Object owner=IBS Object name=IDX_Z#DOC_RICHES_EXEC
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#SAFE
INDEX UNIQUE SCAN Object owner=IBS Object name=PK_Z#SAFE_ID
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#DOC_OPER
INDEX RANGE SCAN Object owner=IBS Object name=Z#IX_Z#DOC_OPER_REF3
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#OPER_RICH
INDEX UNIQUE SCAN Object owner=IBS Object name=Z#IX_Z#OPER_RICH_COL4
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#RICHES_BANK
INDEX UNIQUE SCAN Object owner=IBS Object name=Z#IX_Z#RICHES_BANK_COL7
TABLE ACCESS BY INDEX ROWID Object owner=IBS Object name=Z#CERT_RICHES
INDEX UNIQUE SCAN Object owner=IBS Object name=PK_Z#CERT_RICHES_ID
Теперь вопросы:
1. Должен ли разработчик ПО оценивать оптимальный план запроса и прописывать еще на этапе написания функций хинты в явном виде? Следует ли обращаться в поддержку?
2. Можно ли добиться без изменения дистрибутивного кода с помощью средств Oracle 9 изменения плана запроса на нужный?
ЗЫ К сожалению, изыскания пока к положительному результату не привели, т.к. там куча подобных запросов, каждый из которых надо разбирать отдельно.
Теперь вопросы:
1. Должен ли разработчик ПО оценивать оптимальный план запроса и прописывать еще на этапе написания функций хинты в явном виде? Следует ли обращаться в поддержку?
формирование плана запроса зависит от множества парметров
как параметров инициализации БД, размеров базы, методов и частоты сбора статистики, так и параметров выставляемых на уровне сессии (смотреть в табличку профайл владельца и параметр алтер_сешен)
Цитата:
2. Можно ли добиться без изменения дистрибутивного кода с помощью средств Oracle 9 изменения плана запроса на нужный?
можно
первый и правильный - снять старый план на дистрибутивной операции, снять план на ваш взгляд правильный в данном случае
зарегить заявку и поддержка проанализирует и дасть отказ обоснованный или пофиксят в будущем патчике
второй - не совсем удобный - смотрите в сторону хранения и подмены планов запросов (есть хорошие статейки на эту тему)
в некоторых случаях (экстренная небходимость) - это оперативно помогает
но в этом механизме есть свои ньюансы и их надо учитывать
формирование плана запроса зависит от множества парметров
Зависимость понятна. Но ведь разработчик в голове понимает, как оптимальным образом искать. И может заранее заложить эти знания в код. Причем в качественной системе это должно быть продумано заранее, должно тестироваться на огромных объемах данных
tsktalk пишет:
второй - не совсем удобный - смотрите в сторону хранения и подмены планов запросов (есть хорошие статейки на эту тему) в некоторых случаях (экстренная небходимость) - это оперативно помогает
Разработчик должен учитывать построение плана. Это однозначно. Однако надо понимать, что возможность тестирования ограничивается одной-двумя схемами разработки, а сопровождаемых банков десятки, и каждый со своими особенностями.
Обращаться в поддержку желательно, но если на схеме разработчика план построится нормально (а так бывает зачастую), то никаких изменений в дистрибутиве не последует.
Хинтование неудобно тем, что в процессе обновлений ФЯ использование жестко прописанного индекса может быть неактуальным ввиду его модернизации (или даже удаления) или наличия иного, более подходящего. Ввиду того, что список индексов и используемых ими полей находится не на виду, отследить их изменение намного сложнее, чем сравнивать тексты операций, поэтому предпочитаю избавляться от ненужных индексов изменениями в условиях "нежелательных" полей: "upper" повесить или "+0" добавить.
полностью согласен
у каждого свои особенности и свой ограниченный набор функционала
пример
в одном банке эта операция при текущем наборе данных встает на правильный план
в другом при другом наборе данных или другом размере - на неправильный
Риторический вопрос:
На каком из вариантов надо тестировать этот запрос и какой вариант оптимизации оставлять в дистрибутиве?
тестирование на больших объемах данных (или правильнее сказать на реальных) тут вообще должна быть интересная история
покажите мне любого адекватного руководителя или службу безопасности банка, которые позволят на сторону передать всю базу (даже обезличив ее)
если я бы узнал, что банк в котором я обслуживаюсь передал свою финансовую информацию на сторону, то я бы постарался больше не пользоваться услугами этого банка.
и если моя мысль верна, то дистрибутивные операции должны тестироваться на неких усредненных данных и некотором среднем их количестве. а там планы запросов встают правильно.
Да и сам оптимизатор оракла не совершенен в некоторых случаях, простое изменение порядка табличек после слова from в запросе приводит в реальности просто к чудесам и ускорению на порядок
(или к замедлению - что тоже не редкость)
Коллеги, спасибо за обсуждение.
Конкретика у кого-нибудь есть?
Мне надо срочно изменять планы запросов из дистрибутивной библиотеки. Изменение кода - единственный вариант?
Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.
Творошенко Сергей, Oracle Certified Professional DBA,
Источник:
Oracle Magazine RE ,Январь/Февраль 2004
Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.
Творошенко Сергей, Oracle Certified Professional DBA,
Источник:
Oracle Magazine RE ,Январь/Февраль 2004
Вы не можете начинать темы Вы не можете отвечать на сообщения Вы не можете редактировать свои сообщения Вы не можете удалять свои сообщения Вы не можете голосовать в опросах
Домен cftclub.ru не связан с ЗАО "Центр Финансовых Технологий" и ни в коей мере не нарушает авторских и иных прав
Владелец может не разделять мнения Участников и не несет ответственности за их публикации
Powered by phpBB
О проекте
Поиск
Отзывы
Регистрация
Вход
