Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: December 2010

Gather or Not? Rus.



Собирать или не собирать?

Речь конечно же идет не о картошке, а о системной статистике СУРБД Oracle. С картошкой все ясно созрела значит пора собирать, а что насчет системной статистики?

Что такое системная статистика (system statistics) и для чего она нужна? Системная статистика это набор системных показателей определяющих различные параметры системы, такие как скорость процессора, максимальное количество одновременно считываемых блоков и т. д. А также такие показатели как время считывания одного блока с диска (single block read time) и одновременного считывания множества блоков (multiblock read time).

Оптимизатор запросов Oracle использует данную статистику в своих вычислениях оптимального плана выполнения запросов, он использует значения данных параметров системы при вычислении стоимости операций ввода/вывода и процессорных вычислений, что влияет на конечный план выполнения запроса. Такие показатели как малая пропускная способность ввода/вывода дисковой системы, большое время считывания множества блоков за рас либо количество множества блоков которые можно считать одновременно приводят к увеличению стоимости планов использующих полное сканирование таблиц или к предпочтению вложенных циклов (nested loops) объединениям с использованием хеш функций (hash join). И наоборот высокие значения данных показателей приведут к более низким ценам полных сканирований и операций с использованием хеш функций.

А теперь о том как и когда собирать системную статистику и что если ее не собирать. Oracle не собирает системную статистику автоматически, но он имеет набор значений по умолчанию (default values), которые он использует при отсутствии собранной статистики. Но значения по умолчанию имеют не все показатели системы, а только основные. Собирать, изменять а также просматривать системную статистику можно используя системный пакет для управления статистическими данными DBMS_STATS в схеме SYS. Вам должна быть присвоена роль DBA или GATHER_SYSTEM_STATISTICS для работы с системной статистикой.

Как часто собирать статистику зависит от стабильности вашей системы. Перед тем как собрать статистику в первый раз, если вы никогда еще этого не делали либо уже забыли когда вы это делали в последний раз, то перед сбором системной статистики настоятельно рекомендую собрать информацию о текущих планах выполнения запросов, чтобы после сбора статистики можно было увидеть их изменения, если они произойдут.  Также необходимо сохранить текущие значения системных параметров, как это сделать обсудим ниже. Если версия вашей системы 10g и выше то вы можете воспользоваться AWR репозиторием по умолчанию хранящим в себе данные за последние 7 дней, в том числе и планы выполнения запросов. Изменение параметров системы а также типа и количества нагрузки на систему приводят к необходимости повторного сбора системной статистики, так как Oracle сам об этих изменения не узнает, ему надо об этом сказать. В число таких изменений могут входить такие как апгрейд процессоров на более производительные, перенос файлов БД на более скоростные носители данных, изменение способа хранения, допустим с файловой системы на ASM или любых других физических изменений системы которые по вашему мнению могут привести к изменению какого либо из параметров системы. А также такие факторы как увеличение нагрузки на систему что тоже приведет к уменьшению пропускной способности носителя данных, либо ее изменение с OLTP на DSS или наоборот. Одним словом сбор системной статистики это способ сообщения СУРБД Oracle об изменениях произошедших в системе, чтобы эти изменения адекватно учитывались при вычислениях оптимального способа доступа к данным.

Параметры системной статистики оптимизатора.

В первую очередь надо отметить что системная статистика может быть двух видов:

  • С учетом реальной нагрузки на систему, т.е. Workload Statistics
  • Без учета реальной нагрузи на систему, т.е. Noworkload Statistics

Каждый из видов имеет свой набор параметров.

Workload статистика, это статистика которая учитывает реальную нагрузку на систему в определенный интервал времени. Для сбора данного вида статистики используются методы сбора (gathering_mode) INTERVAL, START и STOP процедуры DBMS_STATS.GATHER_SYSTEM_STATS.

INTERVAL – при использовании данного метода, статистика будет собрана в течении определенного времени. Время в течении которого будет осуществляться сбор начинается с момента запуска процедуры и длится заданное параметром interval количество минут. К примеру, чтобы собрать статистику начиная с текущего времени и продолжительностью в 12 часов, надо выполнить следующую команду:

SQL> begin
  2  sys.dbms_stats.gather_system_stats(
  3                                     gathering_mode=>'INTERVAL',
  4                                     interval=>720
  5                                    );
  6  end;
  7  /

Параметр interval задается в минутах, по умолчанию он равен 60-ти минутам. После запуска команды контроль возвращается пользователю и вы можете закрыть сессию если вам это необходимо.

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

SQL> exec sys.dbms_stats.gather_system_stats('START');

-- After some time

SQL> exec sys.dbms_stats.gather_system_stats('STOP');

Эти команды не обязательно выполнять в рамках одной сессии, т.е. вы можете запустить START и закрыть сессию, через некоторое время открыть новую сессию и запустить STOP команду для завершения сбора. Кстати, команду STOP, также можно использовать для завершения текущего сбора статистики запущенного методом INTERVAL если вы считаете что прошло достаточное количество времени и вы не хотите ждать до завершения заданного интервала.

Параметрами системной статистики вычисляемыми при Workload сборе являются

  • mbrc
  • mreadtim
  • sreadtim
  • cpuspeed
  • maxthr
  • slavethr

mbrc (multi block read count) этот параметр определят среднее количество блоков которое система считывала за одну операцию ввода/вывода при полном сканировании таблиц. Оптимизатор использует именно этот параметр для вычисления стоимости полного сканирования таблиц. И, если этот параметр окажется достаточно большим то цена на полное сканирование может оказаться дешевле чем на доступ по индексу!

mreadtim (multi block read time) этот параметр отображает среднее время в миллисекундах ms необходимое системе на последовательное чтение множества блоков за рас. Если данное время окажется достаточно коротким, то стоимость полного сканирования может оказаться дешевле.

sreadtim (single block read time) этот параметр отображает среднее время на чтение в миллисекундах ms одного блока, к примеру при доступе к индексу по ключу

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

maxthr (maximum throughput) этот параметр отображает максимальную пропускную способность системы ввода/вывода, количество байтов в секунду

slavethr (slave throughput) а этот параметр указывает на скаредную пропускную способность подпроцесса параллельного выполнения, тоже в байтах в секунду

При Workload статистике в буферном кеше (buffer cache) создаются счетчики, которые инкрементируются после завершения каждой операции чтения. Так как эти счетчики находятся в буферном кеше то, при вычислении времени считывания блока(ов) т.е. sreadtim и mreadtim в это время также включается время ожидания защелки (buffer cache latch) а также время необходимое на внутрикешовые операции с блоком. Поэтому если при сборе данного типа статистики система была в состоянии перегруженности и сильной конкуренции за блоки в буферном кеше, то это отразится на значении этих параметров и как следствие на том что оптимизатор после окончания сбора статистики будет стараться выбирать планы использующие наименьшее количество операций ввода/вывода. Также надо учитывать то что, для сбора workload статистики, в частности параметров mbrc и mreadtim необходимо что бы в период сбора статистики имело место хотя бы одно полное последовательное сканирование таблици. Иначе эти параметры останутся без значений. Имейте ввиду что при параллельном полном сканировании таблици (table full scan in parallel) блоки минуют кеш буфер, они считываются в PGA параллельных подпроцессов. А так как счетчики считываний блоков находятся в кеш буфере то эти данные не учитываются. Для уверенности того что необходимая статистика будет собранна можно в период сбора статистики вручную запустить последовательное полное сканирование таблици.

SQL> exec sys.dbms_stats.gather_system_stats('START');

SQL> alter session disable parallel query;

SQL> select /*+ FULL(a) */ count(*) from bigtable;

SQL> exec sys.dbms_stats.gather_system_stats('STOP');

Еще надо отметить что процесс сбора статистики ни как не влияет на производительность системы!

Nowrokload статистика, данный тип статистики применяют в случае отсутствия нагрузки на систему. При запуске команды система симулирует нагрузку на систему проводя случайные (random) операции ввода/вывода по всем дисковым файлам базы данных.

Параметрами системной статистики вычисляемыми при Noworkload сборе являются

  • cpuspeedNW
  • ioseektim
  • iotfrspeed

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

ioseektim этот параметр указывает на скорость наведения головки диска на нужный сектор, его значением по умолчанию является 10 миллисекунд (ms)

iotfrspeed (io transfer speed) это скорость чтения с диска за одну операцию чтения, значение по умолчанию 4096 bytes/ms, это достаточно низкое значение, многие системы способны на большее.

Если собрана Workload статистика то Noworkload статистика автоматически игнорируется оптимизатором запросов. Собирается Noworkload статистика процедурой GATHER_SYSTEM_STATS без параметров:

SQL> exec sys.dbms_stats.gather_system_stats();

Использование Noworkload статистики не рекомендуется.

Полезные Советы

Во всех последующих примерах будем считать что наша система является базой смешанного типа, т.е. днем она функционирует как OLTP система а по ночам как DSS, для генерации отчетов и загрузки/выгрузки данных.

Для начала, давайте разберемся с тем, как можно посмотреть текущие значения параметров системной статистики. Для этого можно воспользоваться процедурой GET_SYSTEM_STATS пакета DBMS_STATS, но если вы не мазохист то эту информацию можно показать при помощи следующего запроса из системной таблици:

SQL> exec sys.dbms_stats.gather_system_stats('START');

PL/SQL procedure successfully completed.

SQL> set linesize 200
SQL> set pagesize 200
SQL> col pname format a15
SQL> col pval2 format a15
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     MANUALGATHERING
SYSSTATS_INFO                  DSTART                     12-28-2010 10:48
SYSSTATS_INFO                  DSTOP                      12-28-2010 10:48
SYSSTATS_INFO                  FLAGS                    1
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             7.699
SYSSTATS_MAIN                  MREADTIM             8.601
SYSSTATS_MAIN                  CPUSPEED              1050
SYSSTATS_MAIN                  MBRC                    13
SYSSTATS_MAIN                  MAXTHR               66560
SYSSTATS_MAIN                  SLAVETHR
SYSSTATS_TEMP                  SBLKRDS              38141
SYSSTATS_TEMP                  SBLKRDTIM           232620
SYSSTATS_TEMP                  MBLKRDS              51853
SYSSTATS_TEMP                  MBLKRDTIM           286600
SYSSTATS_TEMP                  CPUCYCLES          2454144
SYSSTATS_TEMP                  CPUTIM             2424414
SYSSTATS_TEMP                  JOB                      0
SYSSTATS_TEMP                  CACHE_JOB                5
SYSSTATS_TEMP                  MBRTOTAL            664098

22 rows selected.

Как видно из листинга мы запустили сбор Workload статистики, в ручном режиме, т.е. в режиме START/STOP. Давайте разберем содержимое таблици sys.aux_stats$. Столбец SNAME, делит содержимое таблици на 3 части, это SYSSTATS_INFO, SYSSTATS_MAIN и SYSSTATS_TEMP.

SYSSTATS_INFO отображает текущее состоянии системной статистики

STATUS

COMPLETED – сбор статистики успешно завершен

AUTOGATHERING – сбор статистики находится в процессе автоматического сбора (INTERVAL)

MANUALGATHERING – сбор статистики находится в процессе мануального сбора (START/STOP)

BADSTATS – статистика находится в некорректном состоянии, необходимо пересобрать статистику

DSTART – время начала сбора статистики

DSTOP – время когда сбор был завершен если STATUS имеет значение COMPLETED, либо время когда сбор будет завершен если STATUS имеет значение AUTOGATHERING. При STATUSe MANUALGATHERING это поле равно значению DSTART, так как система не знает когда вы остановите сбор статистики. Как только вы запустите gather_system_stats(‘STOP’) статус поменяется на COMPLETED и DSTOP установится на время завершения сбора.

FLAGS – этот параметр является внутренним и не представляет для нас никакого интереса

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

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

Итак, как видно из нашего примера выше, STATUS равен MANUALGATHERING так как наш мануальный сбор еще в процессе и DSTOP равен DSTART, также здесь присутствует временная статистика. Остановим сбор статистики:

SQL> exec sys.dbms_stats.gather_system_stats('STOP');

PL/SQL procedure successfully completed.

SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     COMPLETED
SYSSTATS_INFO                  DSTART                     12-28-2010 10:48
SYSSTATS_INFO                  DSTOP                      12-28-2010 11:54
SYSSTATS_INFO                  FLAGS                    0
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             9.562
SYSSTATS_MAIN                  MREADTIM            13.158
SYSSTATS_MAIN                  CPUSPEED              1048
SYSSTATS_MAIN                  MBRC                     8
SYSSTATS_MAIN                  MAXTHR               66560
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

Мы завершили сбор. Итак, что изменилось? STATUS теперь имеет значение COMPLETED а DSTOP указывает на время завершения сбора. Мы также видим что некоторые из системных параметров тоже изменились, например sreadtim и mreadtim изменились с 7.699 и 8.601 соответственно на 9.562 и 13.158, а mbrc с 13-ти блоков на 8. Это означает что в период сбора система находилась в более нагруженном состоянии. Поэтому системную статистику надо собирать в тот период работы вашей системы в котором нагрузка является обычной для нее. Например если вы соберете статистику с 7-ми утра по 9 часов утра она в нашем случае не будет отображать реальных возможностей системы, так как ночные процессы уже завершены а дневная нагрузка еще не началась. В нашем случае нам нужно собирать две статистики дневную и ночную, так как наша система смешанного типа, как это сделать обсудим позже.

Еще мы видим что параметр slavethr остался без значения, это потому что мы собирали статистику днем, когда наша система работала в режиме OLTP, и не использовала параллельные операции, если мы соберем статистику ночью где преобладают отчеты в параллельном режиме то этот параметр будет иметь соответствующее значение.

Если вы счастливый обладатель Oracle версии 10g и выше, то для сравнения планов выполнения запросов вы можете использовать AWR репозиторий, т.е. вьюшки DBA_HIST_%. Общая схема должна выглядеть так:

  1. Запускаем сбор статистики
  2. Проверяем состояние системы, на выявление изменений в планах выполнения

Итак, первый шаг:

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') "Current Time" from dual; 

Current Time
-------------------
28.12.2010 10:00:48

Запускаем сбор статистики, продолжительностью в 4 часа, т.е. 240 минут.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 240);

PL/SQL procedure successfully completed.

К двум часам дня сбор нашей статистики должен быть завершен, проверяем:

SQL> set linesize 200
SQL> set pagesize 200
SQL> col pname format a15
SQL> col pval2 format a15
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1 PVAL2
------------------------------ --------------- ---------- ------------------
SYSSTATS_INFO                  STATUS                     COMPLETED
SYSSTATS_INFO                  DSTART                     12-28-2010 10:01
SYSSTATS_INFO                  DSTOP                      12-28-2010 14:01
SYSSTATS_INFO                  FLAGS                    0
SYSSTATS_MAIN                  CPUSPEEDNW        1000.789
SYSSTATS_MAIN                  IOSEEKTIM            8.588
SYSSTATS_MAIN                  IOTFRSPEED       23337.879
SYSSTATS_MAIN                  SREADTIM             6.001
SYSSTATS_MAIN                  MREADTIM             9.788
SYSSTATS_MAIN                  CPUSPEED              1050
SYSSTATS_MAIN                  MBRC                    15
SYSSTATS_MAIN                  MAXTHR              176560
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

Сбор статистики завершен.

Второй шаг проверить систему на возможное изменение планов выполнения запросов. Чтобы сделать это нам нужно воспользоваться AWR репозиторием, который в свою очередь состоит из снимков системы. По умолчанию система делает снимки каждый час, судя по значению DSTOP наш сбор статистики завершен буквально через минуту после последнего снимка системы. А новые планы могут вступить в силу только после 14:01, так как именно в это время новая статистика вступила в силу. Процесс MMON сбросивший в 14:00 собранную за час информацию о системе на диск в виде снимка, теперь начал накапливать новую информацию, а за минуту особо не накопишь. Поэтому нам либо нужно подождать час, до следующего автоматического снимка либо, если мы торопимся то можно подождать хотя бы пол часика и с надеждой что достаточно информации об интересующих нас запросах собрано, воспользоваться либо динамическими представлениями для доступа к текущей информации напрямую из SGA  либо, снять снимок вручную и воспользоваться репозиторием. Для целостности примера я воспользуюсь вторым вариантом.

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

Итак прошло пол часа, снимаем снимок:

SQL> var snap_id number;
SQL> exec :snap_id:=dbms_workload_repository.create_snapshot();
SQL> print :snap_id

SNAP_ID
----------
       789

Теперь снимок 789 хранит в себе запросы и планы с учетом новой системной статистики, т.е. наша группа риска.

Теперь когда наш репозиторий содержит достаточное количество информации, нам необходимо определить те запросы которые нас интересуют. Тексты запросов, информация о которых содержится в репозитории, можно извлечь из представления DBA_HIST_SQLTEXT. Это можно сделать либо визуально просмотрев поле SQL_TEXT либо с помощью запроса. К примеру меня интересуют запросы к таблице CUSTOMERS которая является одной из самых критичных для моих приложений. Кстати, не используйте запросы обращающиеся к удаленным объектам по линкам, информация об их планах не сохраняется.

SQL> select sql_id, sql_text from dba_hist_sqltext where upper(sql_text) like '%CUSTOMERS%';

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
dcxm4kr1urg93 SELECT "CUSTID","NAME","ADDRESS" FROM "CUSTOMERS" WHERE "STATUS"=:1 AND ("PASSPO

Мы определили запрос, SQL_ID данного запроса “dcxm4kr1urg93”, теперь дело за малым, нам необходимо сравнить планы выполнения данного запроса в снимке 789 и в предыдущих снимках, возьмем к примеру снимок номер 786. Для этого мы воспользуемся представлением DBA_HIST_SQLSTAT, в нем есть поле PLAN_HASH_VALUE, это поле содержит результат хеш функции от плана данного запроса, т.е. план отображенный всего одной цифрой. Вместо того чтобы сравнивать громоздкие планы построчно на предмет определения изменений, можно сравнить всего две цифри и если они будут различаться то это будет означать изменение плана выполнения. И только в том случае если изменение присутствует, мы обратимся к самим планам. Итак:

select   snap_id, plan_hash_value
    from dba_hist_sqlstat
   where snap_id in (789, 786) and sql_id = 'dcxm4kr1urg93'
order by snap_id desc;

   SNAP_ID PLAN_HASH_VALUE
---------- ---------------
       789      2018760100
       786      1582437882

Как мы видим хеш значения наших планов различаются, значит план изменился. Теперь надо определить эти изменения в лучшую сторону или нет, если система настроена правильно то изменения, если они есть, должны быть в лучшую сторону. Для того чтобы просмотреть план выполнения данного запроса можно воспользоваться непосредственно самим представлением DBA_HIST_SQL_PLAN в котором отображены планы выполнения всех запросов присутствующих в репозитории. Однако есть более удобный способ, воспользоваться замечательной процедурой в составе пакета DBMS_XPLAN, которая отображает сам запрос и план его выполнения в читабельном виде, это DISPLAY_AWR. Эта процедура создана специально для форматирования и вывода на экран планов из репозитория. Надо будет просто запустить эту процедуру два раза, она принимает два параметра sql_id и plan_hash_value

select * from table(dbms_xplan.display_awr('dcxm4kr1urg93',2018760100));

select * from table(dbms_xplan.display_awr('dcxm4kr1urg93',1582437882));

Еще может быть полезен следующий запрос. Он поможет вам определить все запросы у которых изменился план между двумя снимками. На нашем примере выведем разницу между снимками 789 и 786:

variable snap_id_1 number;
variable snap_id_2 number;
exec :snap_id_1:=789;
exec :snap_id_2:=786;
select   a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
    from dba_hist_sqlstat a, dba_hist_sqlstat b
   where (a.snap_id = :snap_id_1 and b.snap_id = :snap_id_2)
     and (a.sql_id = b.sql_id)
     and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;

SQL_ID        SNAP_ID_1_PLAN SNAP_ID_2_PLAN
------------- -------------- --------------
dcxm4kr1urg93     2018760100     1582437882
dgyj6z7xacxjm     2654526844     4032830135
cf621qmts91wf     1366804877     1860467754
7ng34ruy5awxq     3691521353     3984801583
31a13pnjps7j3     1921616694     1196813614
0dwr2vd6vbqzs     3812733207     4046359532

Вот еще один полезный запрос который может вам пригодиться, он извлекает запросы чьи планы выполнения когда либо менялись, запрос охватывает все снимки из которых состоит AWR репозиторий:

select distinct sql_id, plan_hash_value, f snapshot,
                (select begin_interval_time
                   from dba_hist_snapshot
                  where snap_id = f) snapdate
           from (select sql_id, plan_hash_value,
                        first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
                   from (select   sql_id, plan_hash_value, snap_id,
                                  count (distinct plan_hash_value) over (partition by sql_id) a
                             from dba_hist_sqlstat
                            where plan_hash_value > 0
                         order by sql_id)
                  where a > 1)
       order by sql_id, f;

SQL_ID        PLAN_HASH_VALUE   SNAPSHOT SNAPDATE
------------- --------------- ---------- ---------------------------
0cjngzmtm4yv1      2832338232        826 30-DEC-10 01.00.18.618 AM
0cjngzmtm4yv1      3190648541        826 30-DEC-10 01.00.18.618 AM
0dwr2vd6vbqzs      4046359532        679 23-DEC-10 11.00.47.249 PM
0dwr2vd6vbqzs      3812733207        800 28-DEC-10 11.00.05.858 PM
0fr8zhn4ymu3v      3355330683        655 22-DEC-10 11.00.34.978 PM
0fr8zhn4ymu3v      2692826851        836 30-DEC-10 11.00.39.688 AM
0vwa9n600yvgm      3485788549        814 29-DEC-10 01.00.13.700 PM
0vwa9n600yvgm      3865505343        814 29-DEC-10 01.00.13.700 PM
130dvvr5s8bgn      2800640262        654 22-DEC-10 10.00.02.235 PM
130dvvr5s8bgn       464519793        726 25-DEC-10 10.00.47.927 PM
1b28hzmjun5t0      2418697239        677 23-DEC-10 09.00.15.885 PM
1b28hzmjun5t0      3693292387        709 25-DEC-10 05.00.15.195 AM
1gu8t96d0bdmu        17605035        654 22-DEC-10 10.00.02.235 PM
1gu8t96d0bdmu      2959582498        702 24-DEC-10 10.52.44.000 PM
2syvqzbxp4k9z      3223879427        701 24-DEC-10 09.00.05.673 PM
2syvqzbxp4k9z       105727130        822 29-DEC-10 09.00.51.332 PM
2xyb5d6xg9srh      1783251294        701 24-DEC-10 09.00.05.673 PM
2xyb5d6xg9srh        78148816        822 29-DEC-10 09.00.51.332 PM
.
.
.

По результату запроса видно что, к примеру, запрос “0dwr2vd6vbqzs” в снимке 679 имел план выполнения 4046359532 а в снимке 800 он изменился на 3812733207. Воспользуйтесь пакетом DBMS_XPLAN для просмотра планов выполнения этого запроса, пример приведен выше.

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

Как вернуть предыдущее состояние системы? Для начала давайте рассмотрим такие нужные процедуры пакета DBMS_STATS как:

  • get_stats_history_retention (function)
  • get_stats_history_availability (function)
  • restore_system_stats
  • alter_stats_history_retention
  • delete_system_stats
  • create_stat_table
  • export_system_stats
  • import_system_stats

Процедуры пакета DBMS_STATS отвечающие за сбор статистики, не обязательно системной но и объектов базы данных перед обновлением статистики сохраняют старую статистику в специальном репозитории который по аналогии с AWR репозиторием (SM/AWR) называется SM/OPTSTAT и также хранится в табличном пространстве SYSAUX, этот репозиторий также имеет срок хранения данных, по умолчанию равный 31-му дню. Это позволяет в любой момент времени восстановить статистику любого объекта базы данных либо системную статистику в прежнее состояние или состояние в любой момент времени.

get_stats_history_retention – функция возвращающая текущее значение срока хранения старой статистики оптимизатора в репозитории

get_stats_history_availability – функция возвращающая максимальную дату и время хранящейся в репозитории статистики

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
                         31

SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
26-NOV-10 10.23.55.988228000 PM +04:00

Как видно из листинга максимальный срок хранения резервной статистики равен 31-му дню и максимальная дата к которой можно восстановить статистику 26-ое Ноября.  С помощю запроса в V$SYSAUX_OCCUPANTS мы можем определить какой объем пространства занимает резервная статистика оптимизатора в табличном пространстве SYSAUX

SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where occupant_name='SM/OPTSTAT';

OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Optimizer Statistics History                          192576

190 Мегабайт занимает резервная история в табличном пространстве SYSAUX. Срок хранения статистики можно изменить с помощю процедуры alter_stats_history_retention, к примеру установим это значение на 40 дней:

SQL> exec dbms_stats.alter_stats_history_retention(40);

PL/SQL procedure successfully completed.

Процедура restore_system_stats позволяет восстановить системную статистику на состояние в указанном времени

procedure restore_system_stats(as_of_timestamp timestamp with time zone);

К примеру чтобы восстановить системную статистику на состояние которое было на 10-ое декабря 09:00 утра:

SQL> exec dbms_stats.restore_system_stats(to_date('10.12.2010 09:00:00','dd.mm.yyyy hh24:mi:ss'));

PL/SQL procedure successfully completed.

delete_system_stats удаляет системную статистику.

create_stat_table это процедура необходимая для создания таблицы для хранения статистики, вот спецификация:

procedure create_stat_table(
             ownname varchar2,
             stattab varchar2,
             tblspace varchar2 default null,
             global_temporary boolean default false);
  • ownname – имя владеющей схемы
  • stattab – имя создаваемой таблицы
  • tblspace – табличное пространство, если не указанно, будет использовано табличное пространство схемы
  • global_temporary – позволяет создать таблицу в виде глобальной временной таблицы, информация в ней будет автоматически очищаться при завершении сессии. Данная возможность позволяет не засорять табличное пространство. Так как резервная копия статистики и без того хранится в специальном репозитории то не имеет смысла самому хранить эту информацию. Если таблица будет использоваться для промежуточных результатов то лучше ее создать как временную

export_system_stats данная процедура позволяет экспортировать текущую статистику в пользовательскую таблицу, созданную с помощью процедуры create_stat_table

procedure export_system_stats (
                  stattab  varchar2,
                  statid   varchar2 default null,
                  statown  varchar2 default null);
  • stattab – имя таблицы в которую будет экспортирована статистика
  • statid – идентификатор статистики, с помощью данного параметра можно ее уникально идентифицировать
  • statown – имя владельца таблицы

import_system_stats – данная процедура позволяет импортировать статистику из пользовательской таблицы обратно в систему

procedure import_system_stats (
   stattab  varchar2,
   statid   varchar2 default null,
   statown  varchar2 default null);
  • stattab – имя таблицы в которую будет экспортирована статистика
  • statid – идентификатор статистики для импорта
  • statown – имя владельца таблицы

Итак, возвращаясь к тому как восстановить статистику при ситуации когда план запроса изменился в не лучшую сторону.

  • Если Вы впервые собирали системную статистику, т.е. до этого ее вообще не было, то используем процедуру delete_system_stats
  • Если статистика уже существовала то можно использовать либо restore_system_stats либо процедуры export_system_stats и import_system_stats

Примеры:

Удалим системную статистику

SQL> exec dbms_stats.delete_system_stats();

Пример восстановления статистики смотрите выше. Рассмотрим вариант экспорта/иморта существующей статистики. Для начала создадим таблицу для временного хранения статистики, назовем ее TEMPSYSSTAT в схеме SYS:

SQL> exec dbms_stats.create_stat_table('SYS','TEMPSYSSTAT',null,true);

Теперь, перед тем как начать сбор статистики экспортируем текущую статистику и уникально идентифицируем ее как BEFORE_GATHER

SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

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

SQL> exec dbms_stats.import_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

Если план выполнения генерируемый оптимизатором не верный, и вы знаете что есть вариант при котором производительность запроса лучше, то не спешите использовать хинты. Использование хинтов позволит обойти проблему связанную с конкретным запросом, а что если есть и другие запросы с некорректными планами выполнения, ведь если оптимизатор сгенерировал неверный план для данного запроса то он может сделать тоже самое и для любого другова запроса! Надо решать проблему. Есть множество факторов которые могут повлиять на выбор плана. Надо в первую очередь проверить свежесть статистики всех объектов в базе данных, проверить наличие гистограмм на столбцы участвующие в выборке, пересобрать статистику при необходимости. Также просмотрите состояние индексов, фактор кластеризации, соотношение количества уникальных ключей к общему количеству строк. Также проверьте такой параметр инициализации как optimizer_index_cost_adj, он очень сильно влияет на выбор способа доступа к данным. Опять же вы могли выбрать не удачное время для сбора системной статистики, пересоберите ее в другой промежуток времени.

Так как нагрузка на систему в нашей виртуальной базе различна в ночное и дневное время то нам целесообразно иметь два набора системной статистики оптимизатора. Один из них собранный в ночное время а другой в дневное. Днем будет активизирована дневная статистика а ночью ночная. Итак соберем два набора статистики назовем их DAYSTATS и NIGHTSTATS. Но для начала создадим таблицу для перманентного хранения наших статистик, ее назовем PERMSYSSTAT

SQL> exec dbms_stats.create_stat_table('SYS','PERMSYSSTAT');

Теперь нам надо собрать дневную статистику

-- Daytime statistics, gathered from 10am to 4pm
SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 360);

PL/SQL procedure successfully completed.

Теперь если все нормально, экспортируем данную статистику в перманентную таблицу

SQL> exec dbms_stats.export_system_stats('PERMSYSSTAT','DAYSTATS','SYS');

PL/SQL procedure successfully completed.

Повторим процесс для ночного времени

-- Night time statistics, gathered from 9pm to 4am
SQL> exec dbms_stats.export_system_stats('TEMPSYSSTAT','BEFORE_GATHER','SYS');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_stats.gather_system_stats('INTERVAL', 420);

PL/SQL procedure successfully completed.

Экспортируем данную статистику в перманентную таблицу

SQL> exec dbms_stats.export_system_stats('PERMSYSSTAT','NIGHTSTATS','SYS');

PL/SQL procedure successfully completed.

Теперь у нас есть два набора статистики, днем мы будем активировать дневную а ночью ночную

-- Activate from 8am
SQL> exec dbms_stats.import_system_stats('PERMSYSSTAT','DAYSTATS','SYS');

PL/SQL procedure successfully completed.

-- Activate from 8pm
SQL> exec dbms_stats.import_system_stats('PERMSYSSTAT','NIGHTSTATS','SYS');

PL/SQL procedure successfully completed.

Конечно будет лучше если вы автоматизируете этот процесс, либо с помощью пакета DBMS_SCHEDULER или другими средствами ОС, например crontab на UNIX-овых системах.

В некоторых случаях вы можете изменить какой либо из параметров системной статистики вручную. Например после сбора ночной статистики вы обнаружили что некоторые запросы работают по не оптимальному плану, например используют доступ по индексу в большие таблицы и вложенные циклы для их объединения. Однако полное сканирование и объединение при помощи хеш функции было бы более оптимально. Просмотрев параметры системной статистики вы заметили что параметр mbrc ниже чем вы ожидали, допустим 23. Чтобы не пересобирать всю статистику заново вы можете вручную изменить данный параметр с помощью процедуры set_system_stats, например

SQL> exec sys.dbms_stats.set_system_stats('mbrc', 64);

Еще вы можете увеличить значение параметра optimizer_index_cost_adj тем самым повысить цену на доступ по индексу, но к началу дневного цикла значение надо вернуть.

Если возникнут вопросы, оставляйте их в комментариях.


(c) Aychin Gasimov, 12/2010, Azerbaijan Republic