CftClub.ru
Клуб специалистов ЦФТ-Банк

Оптимизация запросов

 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA
Предыдущая тема :: Следующая тема  
Автор Сообщение
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеЧт Авг 14, 2008 10:34   Оптимизация запросов Ответить с цитатой
Полезность: Нет оценки
Есть такой отчет в "Кассах и обменных пунктах" - Справка о суммах принятой и выданной денежной наличности. В настоящий момент он сильно тормозит. Возможно торможение стало следствием роста базы, а может и планы запросов в какой-то момент изменились. Но в любом случае мы имеем тормозной отчет.

После небольшого разбирательства стало понятно, что отчет строится на функциях библиотеки 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 изменения плана запроса на нужный?

ЗЫ К сожалению, изыскания пока к положительному результату не привели, т.к. там куча подобных запросов, каждый из которых надо разбирать отдельно.
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеЧт Авг 14, 2008 11:28    Ответить с цитатой
Полезность: Нет оценки
Суммарное время выполнения конкретно этого запроса по ходу создания отчета уменьшилось в 3.7 раза: с 67 секунд до 18.
tsktalk
Участник со стажем


Вступление в Клуб: 27.09.2007
СообщениеПт Авг 15, 2008 05:15   Re: Оптимизация запросов Ответить с цитатой
Полезность: Нет оценки
Цитата:

Теперь вопросы:
1. Должен ли разработчик ПО оценивать оптимальный план запроса и прописывать еще на этапе написания функций хинты в явном виде? Следует ли обращаться в поддержку?

формирование плана запроса зависит от множества парметров
как параметров инициализации БД, размеров базы, методов и частоты сбора статистики, так и параметров выставляемых на уровне сессии (смотреть в табличку профайл владельца и параметр алтер_сешен)

Цитата:

2. Можно ли добиться без изменения дистрибутивного кода с помощью средств Oracle 9 изменения плана запроса на нужный?

можно

первый и правильный - снять старый план на дистрибутивной операции, снять план на ваш взгляд правильный в данном случае
зарегить заявку и поддержка проанализирует и дасть отказ обоснованный или пофиксят в будущем патчике

второй - не совсем удобный - смотрите в сторону хранения и подмены планов запросов (есть хорошие статейки на эту тему)
в некоторых случаях (экстренная небходимость) - это оперативно помогает
но в этом механизме есть свои ньюансы и их надо учитывать
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПт Авг 15, 2008 07:54   Re: Оптимизация запросов Ответить с цитатой
Полезность: Нет оценки
tsktalk пишет:
формирование плана запроса зависит от множества парметров
Зависимость понятна. Но ведь разработчик в голове понимает, как оптимальным образом искать. И может заранее заложить эти знания в код. Причем в качественной системе это должно быть продумано заранее, должно тестироваться на огромных объемах данных

tsktalk пишет:
второй - не совсем удобный - смотрите в сторону хранения и подмены планов запросов (есть хорошие статейки на эту тему) в некоторых случаях (экстренная небходимость) - это оперативно помогает

Не подскажете, где можно почитать?
r00st
Эксперт


Вступление в Клуб: 14.09.2007
СообщениеПт Авг 15, 2008 08:08    Ответить с цитатой
Полезность: Нет оценки
Разработчик должен учитывать построение плана. Это однозначно. Однако надо понимать, что возможность тестирования ограничивается одной-двумя схемами разработки, а сопровождаемых банков десятки, и каждый со своими особенностями.

Обращаться в поддержку желательно, но если на схеме разработчика план построится нормально (а так бывает зачастую), то никаких изменений в дистрибутиве не последует.

Хинтование неудобно тем, что в процессе обновлений ФЯ использование жестко прописанного индекса может быть неактуальным ввиду его модернизации (или даже удаления) или наличия иного, более подходящего. Ввиду того, что список индексов и используемых ими полей находится не на виду, отследить их изменение намного сложнее, чем сравнивать тексты операций, поэтому предпочитаю избавляться от ненужных индексов изменениями в условиях "нежелательных" полей: "upper" повесить или "+0" добавить.
tsktalk
Участник со стажем


Вступление в Клуб: 27.09.2007
СообщениеПт Авг 15, 2008 08:58    Ответить с цитатой
Полезность: Нет оценки
полностью согласен
Smile
у каждого свои особенности и свой ограниченный набор функционала
пример
в одном банке эта операция при текущем наборе данных встает на правильный план
в другом при другом наборе данных или другом размере - на неправильный
Риторический вопрос:
На каком из вариантов надо тестировать этот запрос и какой вариант оптимизации оставлять в дистрибутиве?
Smile

тестирование на больших объемах данных (или правильнее сказать на реальных) тут вообще должна быть интересная история
покажите мне любого адекватного руководителя или службу безопасности банка, которые позволят на сторону передать всю базу (даже обезличив ее)
если я бы узнал, что банк в котором я обслуживаюсь передал свою финансовую информацию на сторону, то я бы постарался больше не пользоваться услугами этого банка.

и если моя мысль верна, то дистрибутивные операции должны тестироваться на неких усредненных данных и некотором среднем их количестве. а там планы запросов встают правильно.


Да и сам оптимизатор оракла не совершенен в некоторых случаях, простое изменение порядка табличек после слова from в запросе приводит в реальности просто к чудесам и ускорению на порядок
Smile
(или к замедлению - что тоже не редкость)
Smile
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПт Авг 15, 2008 10:40    Ответить с цитатой
Полезность: Нет оценки
Коллеги, спасибо за обсуждение.
Конкретика у кого-нибудь есть?
Мне надо срочно изменять планы запросов из дистрибутивной библиотеки. Изменение кода - единственный вариант?
tsktalk
Участник со стажем


Вступление в Клуб: 27.09.2007
СообщениеПт Авг 15, 2008 12:00    Ответить с цитатой
Полезность: 1
Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.
Творошенко Сергей, Oracle Certified Professional DBA,
Источник: http://sertss.narod.ru/articles/using_outlines.html
Oracle Magazine RE ,Январь/Февраль 2004
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПт Авг 15, 2008 15:35    Ответить с цитатой
Полезность: Нет оценки
tsktalk пишет:
Использование хранимых шаблонов (stored outlines) при настройке приложений с недоступным исходным кодом.
Творошенко Сергей, Oracle Certified Professional DBA,
Источник: http://sertss.narod.ru/articles/using_outlines.html
Oracle Magazine RE ,Январь/Февраль 2004
Попробуем применить. Спасибо.
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA Часовой пояс: GMT + 3
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru