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

Долгое выполнение запроса
На страницу 1, 2  След.
 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA
Предыдущая тема :: Следующая тема  
Автор Сообщение
smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеПт Окт 14, 2016 16:49   Долгое выполнение запроса Ответить с цитатой
Полезность: Нет оценки
Коллеги, привет.

Нужна помощь. Вдруг внезапно стали долго выполнятся некоторые запросы. Не совсем понятно почему. Точнее совсем непонятно.
Вот например запрос, выполняется 3-4 минуты:
Код:
SELECT DISTINCT a1.ID DOL,
                b2.C_NUM_DOG NUM_DOG,
                b1.ID ZALOG,
                b1.C_PART_TO_LOAN PART,
                b1.C_SUMMA SUMMA
  FROM Z#GUARANTEES b4,
       Z#COM_STATUS_PRD b3,
       Z#PRODUCT b2,
       Z#ZALOG b1,
       Z#PART_TO_LOAN a1
 WHERE     b1.COLLECTION_ID IS NULL
       AND b1.id = b2.id
       AND b2.C_COM_STATUS = b3.id
       AND b1.C_VID_GUARANTEE = b4.id
       AND a1.C_PRODUCT = 60003825
       AND b1.C_PART_TO_LOAN = a1.COLLECTION_ID
       AND b3.C_CODE = 'WORK'
       AND b4.C_NAME LIKE '%Залог%'


а вот если так
Код:
SELECT /*+ INDEX(b1 Z#IX_Z#ZALOG_COL14) ALL_ROWS */
       DISTINCT a1.ID DOL,
                b2.C_NUM_DOG NUM_DOG,
                b1.ID ZALOG,
                b1.C_PART_TO_LOAN PART,
                b1.C_SUMMA SUMMA
  FROM Z#GUARANTEES b4,
       Z#COM_STATUS_PRD b3,
       Z#PRODUCT b2,
       Z#ZALOG b1,
       Z#PART_TO_LOAN a1
 WHERE     b1.COLLECTION_ID IS NULL
       AND b1.id = b2.id
       AND b2.C_COM_STATUS = b3.id
       AND b1.C_VID_GUARANTEE = b4.id
       AND a1.C_PRODUCT = 60003825
       AND b1.C_PART_TO_LOAN = a1.COLLECTION_ID
       AND b3.C_CODE = 'WORK'
       AND b4.C_NAME LIKE '%Залог%'
то меньше секунды.
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеПт Окт 14, 2016 18:30    Ответить с цитатой
Полезность: Нет оценки
В первом случае у тебя план запроса определяется средствами "оптимизатора" Oracle
Во втором, ты указываешь явно...какой индекс использовать

Другой вопрос, до того как у тебя "Вдруг внезапно стали долго выполнятся некоторые запросы", эти же запросы сколько по времени выполнялись?

По планам и стоимости запроса есть отдельные доки, много чего можно "соптимизировать" ...

Практически в любом SQL клиенте есть инструментарий, для расчета стоимости и вывода плана

Цитата:

Index Hint
Индексы играют крайне важную роль в настройке SQL. Индексы создают индексные структуры, организуя данные, что в свою очередь ускоряет поиск по этим данным. Само по себе создание индекса не ускоряет выполнение вопроса. Вы должны убедиться, что план исполнения запроса использует индекс, который вы указали в хинте. В следующем примере хинт заставляет оптимизатор использовать определенный индекс для поиска по полю last_name:

SELECT /*+ index(cust_table_last_name_indx) */
distinct author_names FROM devx_author_names WHERE
author_last_name ='DON%'

Если вы посмотрите на план исполнения данного запроса, то увидите, что оптимизатор задействовал индекс cust_table_last_name_indx. А еще Вы можете позволить оптимизатору самому выбрать любой индекс из предложенных вами: /*+ index( indx1, indx2) */.

Замечание: Само создание индекса не ускоряет выполнение запроса, для этого индекс должен быть проанализирован.

Синтаксис анализа индекса таков:

analyze index <index_name> compute statistics;

smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеСб Окт 15, 2016 15:12    Ответить с цитатой
Полезность: Нет оценки
Раньше он и выполнялся меньше секунды, сейчас вдруг стал больше 3 минут. С указанием хинта скорость вернулась на место. Хотелось бы понять что вдруг случилось. Оптимизатор стал как то неверно определять нужный индекс?
Код:
SELECT STATEMENT, GOAL = ALL_ROWS         100   6   876   1   71946849999   99
 HASH UNIQUE         100   6   876   1   71946849999   99
  NESTED LOOPS SEMI         99   6   876   1   11858503316   99
   HASH JOIN         98   12   1320   1   11858495212   98
    NESTED LOOPS         98   12   1320   1   11858495212   98
     STATISTICS COLLECTOR                        
      NESTED LOOPS         96   30   2580   1   11858473985   96
       MERGE JOIN CARTESIAN         31   12821   756439   1   7779669   31
        TABLE ACCESS FULL   IBS   Z#COM_STATUS_PRD   4   1   19   1   294000   4
        BUFFER SORT         27   12821   512840   1   7485669   27
         TABLE ACCESS FULL   IBS   Z#ZALOG   27   12821   512840   1   7485669   27
       TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PRODUCT   96   1   27   1   11858473985   96
        BITMAP CONVERSION TO ROWIDS                        
         BITMAP AND                        
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   PK_Z#PRODUCT_ID   1   1      1   84   1
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   Z#IX_Z#PRODUCT_REF12   1   1      1   922524   1
     TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
      INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_COLL   1   2      1   100   1
    TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
     INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_REF3   1   2      1   100   1
   TABLE ACCESS BY INDEX ROWID   IBS   Z#GUARANTEES   1   14   504   1   675   1
    INDEX UNIQUE SCAN   IBS   PK_Z#GUARANTEES_ID   1   1      1   84   1


Можете у себя на схеме IBS попробовать? Какой план будет и сколько будет выполняться?
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеСб Окт 15, 2016 15:23    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
Раньше он и выполнялся меньше секунды, сейчас вдруг стал больше 3 минут. С указанием хинта скорость вернулась на место. Хотелось бы понять что вдруг случилось. Оптимизатор стал как то неверно определять нужный индекс?
Код:
SELECT STATEMENT, GOAL = ALL_ROWS         100   6   876   1   71946849999   99
 HASH UNIQUE         100   6   876   1   71946849999   99
  NESTED LOOPS SEMI         99   6   876   1   11858503316   99
   HASH JOIN         98   12   1320   1   11858495212   98
    NESTED LOOPS         98   12   1320   1   11858495212   98
     STATISTICS COLLECTOR                        
      NESTED LOOPS         96   30   2580   1   11858473985   96
       MERGE JOIN CARTESIAN         31   12821   756439   1   7779669   31
        TABLE ACCESS FULL   IBS   Z#COM_STATUS_PRD   4   1   19   1   294000   4
        BUFFER SORT         27   12821   512840   1   7485669   27
         TABLE ACCESS FULL   IBS   Z#ZALOG   27   12821   512840   1   7485669   27
       TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PRODUCT   96   1   27   1   11858473985   96
        BITMAP CONVERSION TO ROWIDS                        
         BITMAP AND                        
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   PK_Z#PRODUCT_ID   1   1      1   84   1
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   Z#IX_Z#PRODUCT_REF12   1   1      1   922524   1
     TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
      INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_COLL   1   2      1   100   1
    TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
     INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_REF3   1   2      1   100   1
   TABLE ACCESS BY INDEX ROWID   IBS   Z#GUARANTEES   1   14   504   1   675   1
    INDEX UNIQUE SCAN   IBS   PK_Z#GUARANTEES_ID   1   1      1   84   1


Можете у себя на схеме IBS попробовать? Какой план будет и сколько будет выполняться?


Приветствую. В выходные, смотрю, решил поработать...
Вопрос n 1:
Посмотри на старых тестовых оба эти запроса за какое время выполняются?
Если как ранее, быстро... то
Вопрос n 2:
Смотри 4 плана, 2 на старой схеме, 2 на новой...
Результаты со стоимостью сюда...

Интернет не пашет(, только в понедельник теперь
Alkov
Профи
Неподтвержденный


Вступление в Клуб: 23.09.2010
СообщениеПн Окт 17, 2016 04:48    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
Раньше он и выполнялся меньше секунды, сейчас вдруг стал больше 3 минут. С указанием хинта скорость вернулась на место. Хотелось бы понять что вдруг случилось. Оптимизатор стал как то неверно определять нужный индекс?


Может просто индекс деградировал, давно перестраивали индекс ?
OlegFB
Участник - экстремал
Неподтвержденный


Вступление в Клуб: 11.07.2007
СообщениеПн Окт 17, 2016 07:54    Ответить с цитатой
Полезность: Нет оценки
Сильно выросло количество записей в этих таблицах?
Может пора статистику по ним обновить?
оптимизатор оракла самостоятельно принимает решение о том какие индексы и когда использовать и делает это как раз на основе статистики (это если очень грубо Smile )
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеПн Окт 17, 2016 13:29    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
Раньше он и выполнялся меньше секунды, сейчас вдруг стал больше 3 минут. С указанием хинта скорость вернулась на место. Хотелось бы понять что вдруг случилось. Оптимизатор стал как то неверно определять нужный индекс?
Код:
SELECT STATEMENT, GOAL = ALL_ROWS         100   6   876   1   71946849999   99
 HASH UNIQUE         100   6   876   1   71946849999   99
  NESTED LOOPS SEMI         99   6   876   1   11858503316   99
   HASH JOIN         98   12   1320   1   11858495212   98
    NESTED LOOPS         98   12   1320   1   11858495212   98
     STATISTICS COLLECTOR                        
      NESTED LOOPS         96   30   2580   1   11858473985   96
       MERGE JOIN CARTESIAN         31   12821   756439   1   7779669   31
        TABLE ACCESS FULL   IBS   Z#COM_STATUS_PRD   4   1   19   1   294000   4
        BUFFER SORT         27   12821   512840   1   7485669   27
         TABLE ACCESS FULL   IBS   Z#ZALOG   27   12821   512840   1   7485669   27
       TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PRODUCT   96   1   27   1   11858473985   96
        BITMAP CONVERSION TO ROWIDS                        
         BITMAP AND                        
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   PK_Z#PRODUCT_ID   1   1      1   84   1
          BITMAP CONVERSION FROM ROWIDS                        
           INDEX RANGE SCAN   IBS   Z#IX_Z#PRODUCT_REF12   1   1      1   922524   1
     TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
      INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_COLL   1   2      1   100   1
    TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   1   1   24   1   708   1
     INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_REF3   1   2      1   100   1
   TABLE ACCESS BY INDEX ROWID   IBS   Z#GUARANTEES   1   14   504   1   675   1
    INDEX UNIQUE SCAN   IBS   PK_Z#GUARANTEES_ID   1   1      1   84   1


Можете у себя на схеме IBS попробовать? Какой план будет и сколько будет выполняться?



Под себя запилил id

Код:

SELECT DISTINCT a1.ID DOL,
                b2.C_NUM_DOG NUM_DOG,
                b1.ID ZALOG,
                b1.C_PART_TO_LOAN PART,
                b1.C_SUMMA SUMMA
  FROM Z#GUARANTEES b4,
       Z#COM_STATUS_PRD b3,
       Z#PRODUCT b2,
       Z#ZALOG b1,
       Z#PART_TO_LOAN a1
 WHERE     b1.COLLECTION_ID IS NULL
       AND b1.id = b2.id
       AND b2.C_COM_STATUS = b3.id
       AND b1.C_VID_GUARANTEE = b4.id
       AND a1.C_PRODUCT = 3825914586
       AND b1.C_PART_TO_LOAN = a1.COLLECTION_ID
       AND b3.C_CODE = 'WORK'
       AND b4.C_NAME LIKE '%Авто%'


Первый запуск 11 сек
Последующие менее секунды
Результат 1 строка с данными

План

Код:

SELECT STATEMENT, GOAL = ALL_ROWS         111   9   1269
 HASH UNIQUE         111   9   1269
  NESTED LOOPS SEMI         110   9   1269
   NESTED LOOPS         99   77   9394
    NESTED LOOPS SEMI         88   77   7469
     NESTED LOOPS         49   292   17812
      TABLE ACCESS BY INDEX ROWID BATCHED   IBS   Z#PART_TO_LOAN   8   292   6132
       INDEX RANGE SCAN   IBS   Z#IX_Z#PART_TO_LOAN_REF3   1   68   
      TABLE ACCESS BY INDEX ROWID   IBS   Z#ZALOG   1   1   40
       INDEX UNIQUE SCAN   IBS   Z#IX_Z#ZALOG_COL15   1   1   
     TABLE ACCESS BY INDEX ROWID   IBS   Z#GUARANTEES   1   1   36
      INDEX UNIQUE SCAN   IBS   PK_Z#GUARANTEES_ID   1   1   
    TABLE ACCESS BY INDEX ROWID   IBS   Z#PRODUCT   1   1   25
     INDEX UNIQUE SCAN   IBS   PK_Z#PRODUCT_ID   1   1   
   TABLE ACCESS BY INDEX ROWID   IBS   Z#COM_STATUS_PRD   1   1   19
    INDEX UNIQUE SCAN   IBS   PK_Z#COM_STATUS_PRD_ID   1   1   




у тебя кстати стоимость меньше чем у меня...
Ты на тестовой попробовал? Быстрее работает?
У нас кстати планы отличаются

У меня вот такой бяки нет (погуглить можно что за битмап конвершион)
Код:

        BITMAP CONVERSION TO ROWIDS                         
         BITMAP AND                         
          BITMAP CONVERSION FROM ROWIDS                         
           INDEX RANGE SCAN   IBS   PK_Z#PRODUCT_ID   1   1      1   84   1
          BITMAP CONVERSION FROM ROWIDS   




Как советуют коллеги, попробуй перестроить индексы(те что в плане указаны + твой, который через HINT подставляешь) и собрать статистику...
smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеВт Окт 18, 2016 20:04    Ответить с цитатой
Полезность: Нет оценки
Тест успели восстановить видимо с этой бякой. Там тоже самое.
Вот еще нарыл что с хинтом RULE, выполняется быстро. Это типа указатель оптимизатору использовать старый метод оптимизации, не на статистике. Видимо все таки со статистикой что то.
smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеВт Окт 18, 2016 20:37    Ответить с цитатой
Полезность: Нет оценки
Нашел, что лечятся эти BITMAP CONVERSION выключением параметра
Код:
alter session set "_b_tree_bitmap_plans"=false

У вас этот параметр как задан?
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеВт Окт 18, 2016 20:48    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
Нашел, что лечятся эти BITMAP CONVERSION выключением параметра
Код:
alter session set "_b_tree_bitmap_plans"=false

У вас этот параметр как задан?


Приветствую

Код:

select * from v$obsolete_parameter where NAME like '%bitmap%' order by NAME;

      NAME   ISSPECIFIED   CON_ID
1   b_tree_bitmap_plans   FALSE   0



В файле параметров он не указан
то есть по дефолту. Дефолт зависит от версии Oracle
Сейчас попробую раскопать

У тебя под sysdba что показывает команда?

Код:

show parameter _b_tree_bitmap_plans


У меня под ibs ничего не показал, тут либо он действительно не задан, либо запускать под sysdba нужно, sysdba доступа нет

По некоторым версиям Oracle везде false стоит
http://www.orafaq.com/parms/parm118.htm

Попробуй поставить false и посмотреть влияние на скорость
smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеВт Окт 18, 2016 22:12    Ответить с цитатой
Полезность: Нет оценки
У меня тоже его не было. Попробовал включить
Код:
alter session set "_b_tree_bitmap_plans"=false

План изменился и скорость тоже увеличилась. Странно как то
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеВт Окт 18, 2016 22:26    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
У меня тоже его не было. Попробовал включить
Код:
alter session set "_b_tree_bitmap_plans"=false

План изменился и скорость тоже увеличилась. Странно как то


У администратора oracle попробуйте узнать, кто мог поменять параметр.
Сам он менять его едва ли будет на проде.
На Вашем месте, я бы зарегистрировал в цфт консультацию, получил бы у них ок на изменение, потом на тест, для общего тестирования, если все ок, то на прод.
Как то так...
smirnovan
Участник со стажем
<Банки (менее 3 участников)>


Вступление в Клуб: 10.12.2014
СообщениеЧт Ноя 17, 2016 14:50    Ответить с цитатой
Полезность: Нет оценки
Отчитаюсь. Так и сделал. Написал в ЦФТ, они подтвердили подозрение на этот параметр. Он скрытый и по умолчанию включен true, я его выключил, ситуация нормализовалась.
prankster
Профи
Открытие


Вступление в Клуб: 22.08.2014
СообщениеЧт Ноя 17, 2016 15:28    Ответить с цитатой
Полезность: Нет оценки
smirnovan пишет:
Отчитаюсь. Так и сделал. Написал в ЦФТ, они подтвердили подозрение на этот параметр. Он скрытый и по умолчанию включен true, я его выключил, ситуация нормализовалась.


А почему бы просто не использовать хинт?
opt_param('b_tree_bitmap_plans' 'false')
Матвеев Евгений
Профи
Неподтвержденный


Вступление в Клуб: 31.01.2012
СообщениеЧт Ноя 17, 2016 15:36    Ответить с цитатой
Полезность: Нет оценки
prankster пишет:
smirnovan пишет:
Отчитаюсь. Так и сделал. Написал в ЦФТ, они подтвердили подозрение на этот параметр. Он скрытый и по умолчанию включен true, я его выключил, ситуация нормализовалась.


А почему бы просто не использовать хинт?
opt_param('b_tree_bitmap_plans' 'false')


Цитата:
добавлен начиная с 10g R2, согласно документации 11g R2 «… позволяет установить инициализационные параметры [оптимизатора] на время выполнения запроса..


Данный вариант решения применим, к конкретному запросу...
В случае с общей настройкой не нужно каждый раз что то дополнительно указывать...
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Oracle DBA Часовой пояс: GMT + 3
На страницу 1, 2  След.
Страница 1 из 2

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