В статье описывается конкретный опыт администраторов одной крупной компании в работе над принципом и способом сбора статистической информации по большой неоднородной изменяющейся БД.
Условия.
Допустим что, имеются следующая БД:
· СУБД Oracle 9.2.0;
· Объем данных >100 Гб;
· Большое число таблиц >500 шт;
· Число пользователей >500 шт;
· Число одновременно работающих пользователей >200;
· Сильных разброс размеров таблиц (512 Кб до 25 Гб);
· Сильный разброс характера работы с таблицами (изменяющие до 20% каждый день и почти неизменяющиеся больше месяца);
· Неоднородность характера работы с таблицами в течение месяца;
Задача.
Собрать статистику по всей БД так, чтобы ее актуальность не повлияла на работу, какой бы то ни было задачи в БД.
История.
Изначально при проектировании БД был выбран принцип ежедневного сбора статистических данных. Однако к моменту, когда БД достигла размеров в 800 таблиц объемом 100 Гб то сбор статистики по всем имеющимся таблицам при проценте - 33% стал занимать более 5 часов при том, что проводился ночью, когда практически отсутствовала нагрузка на сервер. Сбор статистки ежедневно ночью по 5 часов был признан не возможным в связи с тем, что ночью все же проводится значительная по объемам технологическая обработка данных, и к тому же, на конец и начало месяца приходится еще более большая нагрузка.
Зная теорию, практику и влияние правильного сбора статистики администраторы данной БД принимают решение собирать статистику не ежедневно, а раз в неделю по выходным ночью. При этом все таблицы были разделены по принципу объема (большие и остальные), и в ночь с СБ на ВС собиралась статистка по одному набору таблиц, а в ночь с ВС на ПН собиралась по другому набору. В результате такой схемы клиентская система и БД без замечаний существовали более 2 лет. За это период БД выросла до числа в 1000 таблиц и объемом более 200 Гб. Суммарное время сбора статистки за выходные уже составляло более 8 часов.
В ходе анализа очередного ежемесячного замедления работы определенной категории пользователей в начале месяца, выявилась сильная зависимость работы данной задачи от актуальной статистической информации по нескольким оперативным таблицам. Зависимость оказалась на первый взгляд очень странно сильной, ведь имеющаяся статистика по таблице недельной давности давала возможность ей прекрасно работать в предыдущий день, а на следующий день появились сильнейшие «тормоза». Сбор статистки на лету по данной таблице моментально заставил задачу с большой скоростью обновлять блоки (по таблице в первые дни месяца проводится UPDATE).
В результате анализа этой ситуации ничего сверхъестественного выявлено не было. Ничего другого нельзя было ожидать от таблицы, которая имела следующие характеристики:
· Оперативная;
· Не большого размера < 100Мб;
· Число индексов к числу полей = 0.6;
· Объем обновляемых данных в начале месяца 50% за несколько дней.
Ясно, что уже после первого дня обновления данных оперативная таблица с довольно большого объема претерпела изменений более чем на 25%. При чем, ситуация была сильно отягощена тем, что по таблице построено существенно более положенного число индексов, по которым и производилась хинтами выборка и обновление. А если учесть, что даже при локально-управляемых табличных пространствах индексы подвержены гораздо больше фрагментации чем данные, то картина ставится полностью понятной.
В связи с этим перед администраторами бала поставлена задача создать систему сбора статистики, учитывающую особенность сильно обновляемых малых таблиц, а так же заодно и остальных «заморочек» имеющихся во всей системе.
Предложенное решение.
Раз недельная статистика для некоторых таблиц не актуальна, то следует собирать анализ ежедневно, но как сказано выше, это потребует времени более 8 часов. Как совместить эти условия?
Решение лежит в учете первого и второго фактора, а также и в возможности продумать учет и других факторов не меняя систему сбора статистки в будущем. В результате поиска удалось разработать следующий принцип, а затем и систему сбора статистики:
· Все таблицы разделяются на три группы: малые, средние и большие;
· Каждой группе таблиц определен свой интервал сбора статистики (по умолчанию):
o Малые - 1 день;
o Средние - 3 дня;
o Большие - 7 дней.
· Для каждой таблицы индивидуально можно назначить следующие управляющие пункты:
o Флаг факта сбора/не сбора статистики;
o Последняя дата удачного сбора статистики;
o Период до следующего анализа;
o Процент анализа (по умолчанию - 33%).
Все характеристики сбора статистики для таблиц размещены в специальной таблице, из которой процедура, запускаемая JOBом, делает выборку таблиц и считывает их показатели для проведения анализа. При этом важно так настроить даты для сбора статистики больших и средних таблиц, чтобы они были примерно равномерно распределены между таблицами в рамках самого максимального интервала сбора анализа. Также рекомендуется использовать не один, а как минимум три JOBа, в соответствии с числом категорий таблиц. Это дает возможность предотвратить ситуацию, когда снятое по какой либо причине задание остановится в начале общего списка таблиц и остальные таблицы в текущие сутки уже не попадут на анализ.
Результаты. Анализ работы новой системы сбора статистики показал высокую эффективность. Все упомянутые выше замечания более не проявлялись. В среднем, по скользящему разно-периодичному графику время сбора составляет каждую ночь от 0,5 до 2 часов для всех групп таблиц. При этом малые таблицы, обрабатываемые еженочно, по времени занимают сервер всего на 20 - 25 минут.
Ниже приведены все скрипы для создания описанной системы:
Приложение.
--таблица консоль управления сбором статистики.
create table ANALYZED_OBJECT
(
ID_STAT NUMBER(10) PRIMARY KEY , --ID
TABLE_TYPE VARCHAR2(10) NULL, -- Тип таблицы
TABLE_NAME VARCHAR2(30) NULL, -- имя таблицы
TABLE_SIZE NUMBER(10,2) DEFAULT 0 NOT NULL, -- размер таблицы
STATUS NUMBER(1) DEFAULT 0 NOT NULL, -- флаг сбора стат.
DATE_ANALYZE DATE NULL, -- дата анализа
PERCENT NUMBER(2) DEFAULT 0 NOT NULL, -- процент анализа
PERIOD NUMBER(2) DEFAULT 0 NOT NULL -- число дней до след.
)
tablespace USER_DATA;
--таблица проверки факта сбора статистики.
create table ANALYZED_RESULT
(
DATE_ANALYZE DATE NULL, -- дата проведения
MSG VARCHAR2(100) NULL -- текст пояснения
)
tablespace USER_DATA;
Автор статьи: Федотов Василий Александрович
/* заполнение таблицы консоли управления характеристиками таблиц (Большие таблицы)*/
insert into ANALYZED_OBJECT
(id_stat, table_type, table_name, table_size, date_analyze, percent, period)
SELECT ROWNUM, 'BIG' , TABLE_NAME, MB, sysdate, 33, 7
from (select segment_name TABLE_NAME, sum(bytes)/1024/1024 MB
from dba_extents e, all_objects a
where a.object_type='TABLE'
and a.owner = 'OWNER' -- Ввести Пользователя-хозяина схемы таблиц
and a.temporary='N'
and a.object_name=e.segment_name
group by segment_name
having (sum(bytes)/1024/1024>=1000));
commit;
/* заполнение таблицы консоли управления характеристиками таблиц (средние таблицы). Где - № BIG номер последнего ROWNUM из первого набора.*/
insert into ANALYZED_OBJECT
(id_stat, table_type, table_name, table_size, date_analyze, percent, period)
SELECT ROWNUM + №BIG , 'MID' , TABLE_NAME, MB, sysdate, 33, 3
from (select segment_name TABLE_NAME, sum(bytes)/1024/1024 MB
from dba_extents e, all_objects a
where a.object_type='TABLE'
and a.owner = 'OWNER' -- Ввести Пользователя-хозяина схемы таблиц
and a.temporary='N'
and a.object_name=e.segment_name
group by segment_name
having (sum(bytes)/1024/1024>200 and sum(bytes)/1024/1024<1000));
commit;
/* заполнение таблицы консоли управления характеристиками таблиц (малые таблицы). Где - № MID номер последнего (ROWNUM + № BIG ) из предыдущего набора.*/
insert into ANALYZED_OBJECT
(id_stat, table_type, table_name, table_size, date_analyze, percent, period)
SELECT ROWNUM + №MID , 'SMALL' , TABLE_NAME, MB, sysdate, 33, 1
from (select segment_name TABLE_NAME, sum(bytes)/1024/1024 MB
from dba_extents e, all_objects a
where a.object_type='TABLE'
and a.owner = 'OWNER' -- Ввести Пользователя-хозяина схемы таблиц
and a.temporary='N'
and a.object_name=e.segment_name
group by segment_name
having (sum(bytes)/1024/1024<=200));
commit;
/* Создание процедуры сбора статистики*/
CREATE OR REPLACE PROCEDURE SET_AUDIT
(
typetable IN VARCHAR2
)
IS
NameErr VARCHAR2(100);
NumCount NUMBER:= 0;
BEGIN
INSERT INTO ANALYZED_RESULT
(date_analyze,msg)
VALUES
(SYSDATE,'Начало анализа - ' // typetable);
BEGIN
FOR NextRows IN
(
SELECT a.table_name NAME , b.percent PRC
FROM sys.ALL_TABLES a, analyzed_object b
WHERE a.table_name = b.table_name
AND iot_type IS NULL
AND a.owner='OWNER' -- ВвестиПользователя-хозяинасхемытаблиц
AND b.table_type=typetable
AND b.status=0
AND trunc(b.date_analyze)+b.period<=trunc(sysdate)
AND a.TEMPORARY = 'N'
) LOOP
dbms_ddl.analyze_object('TABLE', 'OWNER', NextRows.NAME,'ESTIMATE',NULL, NextRows.PRC);
UPDATE analyzed_object
SET date_analyze = SYSDATE
WHERE table_name = NextRows.NAME;
COMMIT;
NumCount := NumCount + 1;
END LOOP;
INSERT INTO ANALYZED_RESULT
(date_analyze,msg)
VALUES
(SYSDATE, TO_CHAR(NumCount)// typetable // ' таблиц проанализовано');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NameErr := SUBSTR(SQLERRM, 1, 100);
INSERT INTO ANALYZED_RESULT
(date_analyze,msg)
VALUES
(SYSDATE, NameErr);
COMMIT;
END;
END SET_AUDIT;
/* Создание задания для каждого из трех видов таблиц*/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '
BEGIN
SET_AUDIT(''BIG'');
END;
'
,next_date => to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+0.04'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' // to_char(x));
END;
/
-------------------------------------------------------------------------
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '
BEGIN
SET_AUDIT(''MID'');
END;
'
,next_date => to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+0.1'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' // to_char(x));
END;
/
-------------------------------------------------------------------------
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '
BEGIN
SET_AUDIT(''SMALL'');
END;
'
,next_date => to_date(sysdate,'dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+0.15'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' // to_char(x));
END;
/