Oracle 11g высокая скорость ввода-вывода Подождите

Oracle Version: 11.1.0.7.0

У нас более высокое ожидание ввода-вывода в одном из наших экземпляров Oracle RAC.

Один SQL имеет большое время выполнения — 1452,57 с на выполнение. Это начало происходить внезапно в один прекрасный день. Раньше для запроса 20k (параметр: v4) записей требовалось максимум 3-4 минуты.

Записи о подписке: 59 миллионов (непараллельные)

Записи о заряде: 2k - 3k

SQL ниже

выберите o.msisdn, o.spid, o.serviceid, o.ChargeReferenceID, o.channelID, o.nextchargetime, o.failtimestamp, o.lastmonfeeday, o.networkId, o.retryEndDateTime, o.trialType, o.subFlag, o .faultCode из информации о подписке o, ставка r, где (o.monthbillid = :v1) и (((o.state = :"SYS_B_00") и (o.nextchargetime ‹ :v2) и ((o.IsAutoExtend ‹> :"SYS_B_01) ") или ((o.IsAutoExtend = :"SYS_B_02") и (o.extendflag ‹> :"SYS_B_03")))) или (o.subFlag = :"SYS_B_04" и o.state = :"SYS_B_05" и o .retryenddatetime > :v2)) и (o.ChargeClassForSub = r.chargeclassidx) и ((r.chargemode = :"SYS_B_06" и r.activetype = :"SYS_B_07" и o.nextchargetime != :"SYS_B_08") или ( r.chargemode = :"SYS_B_09" и r.activetype ‹> :"SYS_B_10") или (r.chargemode >= :"SYS_B_11" и r.chargemode ‹= :"SYS_B_12" и r.basecharge >= :"SYS_B_13" ) или (r.chargemode = :"SYS_B_14") или (r.chargemode = :"SYS_B_15") или (r.chargemode = :"SYS_B_16") ) и (o.failtimestamp ‹= :v3) и (rownum ‹= :v4)

Согласно отчету AWR, Top 5 Time Foreground Events

Чтение прямого пути [Среднее время ожидания: 22 с, %DB Time: 50,75%] Последовательное чтение файла БД [Среднее время ожидания: 15 с, %DB Time: 38,00]

Я не смогу опубликовать полный отчет AWR, потому что он ограничен. Поэтому, пожалуйста, спросите подробности, я опубликую

Пожалуйста, найдите план объяснения ниже:

ID Exec Ord Operation Go To More Peek Bind Capt Bind Cost2 Estim Card LAST Starts LAST Output Rows LAST Over/Under Estimate1 PStart PStop Рабочая область 0 7 SELECT STATEMENT
23335 1 2577 1 6 COUNT STOPKEY [+] [+]
[+] 23335 1 2577 2 5 . HASH JOIN [+] [+]
[+] 23335 20001 1 2577 8x over [+] 3 1 .. TABLE ACCESS FULL CHARGERATE [+] [+] 68 3035 1 3036 1x 4 4 .. СПИСОК РАЗДЕЛОВ SINGLE [ +] 23266 25223 1 2577 10x более KEY KEY 5 3 ... ДОСТУП К ТАБЛИЦАМ ПО ЛОКАЛЬНОМУ ИНДЕКСУ ROWID SUBSCRIBEINFO [+] [+] [+]
[+] 23266 25223 1 2577 10x более KEY KEY 6 2 ... .INDEX RANGE SCAN IDX_FAILTIMESTAMP_NEW [+] [+] [+] [+] 2435 1 2100765 KEY KEY

ИОСТАТ

Linux 2.6.16.46-0.12-smp (mdspdb01) 16.11.12

avg-cpu: %user %nice %system %iowait %steal %idle

       8.41    0.00    9.38   13.25    0.00   67.67

Устройство: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

сда 5,71 39,53 121,79 665679995 2051190222

сдб 85,75 178,15 171,12 3000316741 2881953582

сдк 111,05 161,69 43,96 2723201251 740429949

Мы создали индекс для полей monthbillid, nextchargetime и failtimestamp... Хотя он сильно улучшил кардинальность на 1/6, но увеличил стоимость в 4-5 раз. Но оракул по умолчанию берет новый индекс

создать индекс IDX_MONTHBILLQUERY для информации о подписке (monthbillid, nextchargetime, failtimestamp) локального табличного пространства IMUSE_INDEX;

dbms_stats.gather_index_stats('IMUSE01', 'IDX_MONTHBILLQUERY');

У нас в отчетах AWR хард синтаксический анализ = 0. А также мы изменили cursor_sharing = FORCE

Теперь ИО под контролем. Все еще чувствует, это не первопричина. Кроме того, мы выделили экземпляр для этого запроса, который выполняется более 10 раз в час, для извлечения 20 000 записей требуется примерно 100 секунд.

Кто-нибудь может подсказать, будет ли хорошим решением переходить в режим оптимизатора как first_rows или использовать подсказку first_rows(20000).

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


performance oracle11g sql-tuning iowait
person Rajeev    schedule 16.11.2012    source источник
comment
Вы проверили свое оборудование (например, iostat?)   -  person vladr    schedule 16.11.2012

Ответы (2)


arrow_upward
0
arrow_downward

Проблема в том, что оператор вызывает более 50000 операций чтения с диска. Вероятно, это вызвано использованием cursor_sharing. Этот параметр обычно используется, если приложение закодировано без использования переменных связывания (очень плохо. Не ходите, бегите, чтобы исправить это приложение). Вероятно, вы даже установили cursor_sharing на force, что может иметь нежелательные эффекты, подобные описанному, и просмотр курсора также не будет работать в большинстве случаев.

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

person steve    schedule 18.11.2012
comment
CURSOR_SHARING=FORCE, вероятно, всегда плохая идея. Но для запросов к хранилищу данных имеет смысл жестко закодировать значения вместо использования переменных связывания. Если выполнение запроса займет несколько минут, может оказаться целесообразным заставить оптимизатора потратить лишнюю десятую секунды на составление лучшего плана. - person Jon Heller; 19.11.2012
comment
У нас есть все переменные связывания и Cursor_sharing=similar. - person Rajeev; 21.11.2012

arrow_upward
0
arrow_downward

Проблема решена..... Совместное использование курсора сделано для принудительного... Это очень сильно сократило ввод-вывод. Теперь IO в норме во всех случаях. Затем мы создали два индекса для одного и того же запроса, рекомендованного советником sqltuning, и приняли профиль.

2- Поиск профиля SQL (см. Раздел объяснения планов ниже) -------------------------------------- ------------------ Для этого оператора найден потенциально лучший план выполнения.

Рекомендация (оценочная польза: 80,44%)

  • Попробуйте принять рекомендуемый профиль SQL. выполнить dbms_sqltune.accept_sql_profile(task_name => 'my_sqltune_task1', task_owner => 'IMUSE01', replace => TRUE);

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

                       Original Plan  With SQL Profile  % Improved
                   -------------  ----------------  ----------   Completion Status:             PARTIAL          COMPLETE   Elapsed

    Время (мс): 31479 8049 74,43% Время процессора (мс): 5172 1656 67,98%
    Время пользовательского ввода-вывода (мс): 16367 3422 79,09%
    Получение буфера: 265365 51818 80,47%
    Чтение диска : 3227 524 83,76%
    Прямая запись: 0 0 Обработано строк: 0 20000 Выборка:
    0 20000 Выполнение: 0
    1

3- Нахождение индекса (см. раздел «Объяснение планов» ниже) --------------------------------------- ----------- План выполнения этого оператора можно улучшить, создав один или несколько индексов
.

Рекомендация (оценочная польза: 81,1%)

  • Рассмотрите возможность запуска Советника по доступу для улучшения структуры физической схемы или создания рекомендуемого индекса. создать индекс IMUSE01.IDX$$_67E5B0001 на IMUSE01.SUBSCRIBEINFO("СОСТОЯНИЕ","SUBFLAG","MONTHBILLID","RETRYENDDATETIME");

  • Рассмотрите возможность запуска Советника по доступу для улучшения структуры физической схемы или создания рекомендуемого индекса. создать индекс IMUSE01.IDX$$_67E5B0002 на IMUSE01.SUBSCRIBEINFO("СОСТОЯНИЕ","MONTHBILLID","FAILTIMESTAMP");

    Обоснование --------- Создание рекомендуемых индексов значительно улучшает план выполнения этого оператора. Однако может быть предпочтительнее запускать «советник по доступу» с репрезентативной рабочей нагрузкой SQL, а не с одним оператором. Это позволит получить исчерпывающие рекомендации по индексам, которые учитывают накладные расходы на обслуживание индекса и дополнительное потребление пространства.

4- Реструктурировать поиск SQL (см. план 1 в разделе объяснения планов) ------------------------------------ ---------------------------- Предикат "O"."NEXTCHARGETIME"‹>:B1, используемый в строке с идентификатором 5 плана выполнения, условие неравенства в индексированном столбце "NEXTCHARGETIME". Это условие неравенства не позволяет оптимизатору эффективно использовать индексы в таблице "IMUSE01"."SUBSCRIBEINFO".

Рекомендация -------------- - Перепишите предикат в эквивалентную форму, чтобы использовать преимущества индексов.

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

person Rajeev    schedule 02.12.2012