Oracle mechanics

26.05.2013

ASH-трейс и параметры статистики

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 01:13
Tags: ,

Время выполнения SQL запроса в веб-форме OEBS превысило допустимые пределы (таймаут веб-сервера = 10 минут) — как обычно, «вдруг» стало выполняться медленно

Выполнив скрипт «трассировки» ASH_SQLMON.SQL можно заметить, что основное время тратится на доступ к блокам некой штатной таблицы RA_CUSTOMER_TRX_ALL с использованием странного для OEBS метода доступа INDEX SKIP SCAN:

11.2.0.3.OEBS1@ SQL> @ash_sqlmon 5fbmjj0n32rfw "" ""

 ID PLAN_OPERATION                                   OBJECT_OWNER  OBJECT_NAME           COST CARDINALITY      BYTES TEMP_SPACE SESSION_STATE   WAIT_COUNT
--- ------------------------------------------------ ------------- --------------------- ---- ----------- ---------- ---------- --------------- ----------
...
157               NESTED LOOPS OUTER                                                      621           3        189            ON CPU          21
158                 NESTED LOOPS                                                          356         264      13464            ON CPU          1
159                   TABLE ACCESS BY INDEX ROWID    AR            RA_CUST_TRX_TYPES_ALL   10           1         13
160                     INDEX SKIP SCAN              AR            RA_CUST_TRX_TYPES_U1     1          19
161                   TABLE ACCESS BY INDEX ROWID    AR            RA_CUSTOMER_TRX_ALL    346         292      11096            ON CPU          2659 -- основное время (по кол-ву попаданий в ASH)
162                     INDEX SKIP SCAN              AR            RA_CUSTOMER_TRX_N17     40        1437                       ON CPU          1157 -- и тут тоже
163                 TABLE ACCESS BY INDEX ROWID      AR            RA_CUSTOMER_TRX_ALL      2        9048     108576            ON CPU          16
164                   INDEX RANGE SCAN               AR            RA_CUSTOMER_TRX_N6       1           1                       ON CPU          52
...

— странного по той причине, что обычно таблицы OEBS густо индексируются для обеспечения быстрого INDEX RANGE SCAN доступа к данным для большинства штатных запросов

Из мониторинга можно видеть, что таблица достаточно активно модифицировалать вплоть до 20.05.2013:

SQL> select * from DBA_TAB_MODIFICATIONS
  2  where table_name in ('RA_CUSTOMER_TRX_ALL')
  3  /

TABLE_OWNER TABLE_NAME             INSERTS    UPDATES    DELETES TIMESTAMP   TRUNCATED DROP_SEGMENTS
----------- ------------------- ---------- ---------- ---------- ----------- --------- -------------
AR          RA_CUSTOMER_TRX_ALL    1154878    1709648       1970 20.05.2013  NO                    0

, однако статистика собиралась почти месяц назад:

SQL> select num_rows, last_analyzed from dba_tables where table_name in ('RA_CUSTOMER_TRX_ALL')
  2  /

  NUM_ROWS LAST_ANALYZED
---------- -------------
  36908070 17.04.2013

Более того, таблица RA_CUSTOMER_TRX_ALL даже не рассматривается Oracle в качестве кандидата на сбор статистики:

SQL> declare
  2    mystaleobjs dbms_stats.objecttab;
  3  begin
  4    dbms_stats.gather_schema_stats(ownname => 'AR',
  5                                   options => 'LIST STALE',
  6                                   objlist => mystaleobjs);
  7    for i in 1 .. mystaleobjs.count loop
  8      dbms_output.put_line(mystaleobjs(i).objname);
  9      end loop;
 10    end;
 11  /

AR_TRX_BAL_SUMMARY

PL/SQL procedure successfully completed

поскольку изменения, накопившиеся за месяц, пока не превысили параметра STALE_PERCENT:

SQL> select DBMS_STATS.GET_PREFS('STALE_PERCENT', 'AR', 'RA_CUSTOMER_TRX_ALL') from dual
  2  /

DBMS_STATS.GET_PREFS
--------------------
10

Для исправления ситуации достаточно уменьшить STALE_PERCENT до

( ( DBA_TAB_MODIFICATIONS.INSERTS + UPDATES + DELETES ) / NUM_ROWS ) * 100 = 7%

, или 1%, или 0% — в последнем случае статистика будет автоматически собираться после любых изменений данных:

SQL> exec DBMS_STATS.SET_TABLE_PREFS('AR', 'RA_CUSTOMER_TRX_ALL', 'STALE_PERCENT', 1)

PL/SQL procedure successfully completed

SQL> select DBMS_STATS.GET_PREFS('STALE_PERCENT', 'AR', 'RA_CUSTOMER_TRX_ALL') from dual;

DBMS_STATS.GET_PREFS
--------------------
1

После чего таблица чудесным естественным образом попадает в список кандидатов на автоматический сбор статистики в течение ближайшего временного «окошка» (maintenance windows):

SQL> declare
  2    mystaleobjs dbms_stats.objecttab;
  3  begin
  4    dbms_stats.gather_schema_stats(ownname => 'AR',
  5                                   options => 'LIST STALE',
  6                                   objlist => mystaleobjs);
  7    for i in 1 .. mystaleobjs.count loop
  8      dbms_output.put_line(mystaleobjs(i).objname);
  9      end loop;
 10    end;
 11  /

AR_TRX_BAL_SUMMARY
RA_CUSTOMER_TRX_ALL

PL/SQL procedure successfully completed

В случае спешки, можно пересобрать статистику вручную:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('AR', 'RA_CUSTOMER_TRX_ALL');

PL/SQL procedure successfully completed

Результат: из плана исчезают непрофильные для OEBS операции INDEX SKIP SCAN, запрос выполняется за секунды

4 комментария »

  1. В качестве занудства можно добавить, что для аппсовой базы не рекомендуется использоваться dbms_stats и GATHER_STATS_JOB при сборе статистики.
    :)

    комментарий от Dmitry Stepanov — 27.05.2013 @ 10:23 | Ответить

    • Спасибо, Дмитрий, за уточнение — тут не рассматривается штатный метод управления статистикой бд OEBS с использованием FND_STATS, статистика собирается автоматически стандартными процедурами Oracle

      комментарий от Igor Usoltsev — 27.05.2013 @ 12:07 | Ответить

  2. А чем штатный метод для приложения плох? Морально готовлюсь, Oracle BI прикручиваем к известной тебе БД…

    комментарий от Sergey Golikov — 03.06.2013 @ 14:34 | Ответить

    • Штатный STALE_PERCENT=10% ничем не плох, он рассчитан из компромиса — чтобы не пропустить слишком большие изменения данных и не слишком часто запускать сбор статистики по таблице
      Т.е. если, например, у тебя в таблице фактов уже 1,000,000 записей при стандартном параметре статистика будет собираться автоматически только после того, как добавится 100,000 записей, а до этого момента момента USER_TAB_COL_STATISTICS.LOW_VALUE и HIGH_VALUE будут показывать значения на момент последнего запуска DBMS_STATS
      Что может быть критично для плана

      комментарий от Igor Usoltsev — 03.06.2013 @ 15:39 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: