Долгое выполнение запроса
На страницу 1, 2 След.
|
| Предыдущая тема :: Следующая тема |
| Автор |
Сообщение |
smirnovan
Участник со стажем
Вступление в Клуб: 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
Участник со стажем
Вступление в Клуб: 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 |
|
Полезность: Нет оценки
|
Сильно выросло количество записей в этих таблицах?
Может пора статистику по ним обновить?
оптимизатор оракла самостоятельно принимает решение о том какие индексы и когда использовать и делает это как раз на основе статистики (это если очень грубо  ) |
|
|
Матвеев Евгений
Профи
Вступление в Клуб: 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
Участник со стажем
Вступление в Клуб: 10.12.2014
|
| Вт Окт 18, 2016 20:04 |
|
Полезность: Нет оценки
|
Тест успели восстановить видимо с этой бякой. Там тоже самое.
Вот еще нарыл что с хинтом RULE, выполняется быстро. Это типа указатель оптимизатору использовать старый метод оптимизации, не на статистике. Видимо все таки со статистикой что то. |
|
|
smirnovan
Участник со стажем
Вступление в Клуб: 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
Участник со стажем
Вступление в Клуб: 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
Участник со стажем
Вступление в Клуб: 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 "… позволяет установить инициализационные параметры [оптимизатора] на время выполнения запроса.. |
Данный вариант решения применим, к конкретному запросу...
В случае с общей настройкой не нужно каждый раз что то дополнительно указывать... |
|
|
|
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
|
|
О проекте
Поиск
Отзывы
Регистрация
Вход
