Рано или поздно, но база начинает разрастаться как в количестве таблиц и индексов, так и в их объёме. Что бы не возникла ситуация, когда база вдруг останавливается с сообщением о нехватке места, необходимо производить регулярный мониторинг роста базы с накоплением статистических данных по таблицам и индексам.
Это можно сделать стандартными средствами многих программ, но они, в большинстве случаев, при мониторинге делают analize всех таблиц и индексов, что при первом запуске таких программ может привести к очень серьёзным осложнениям в виде неконтролируемого изменения планов запросов. Кроме того, эти программы зачастую пытаются прописать свои таблицы, кстати, достаточно большие по размеру, в tablespace SYSTEM.
При меньших возможностях, возможен более экономный и безопасный вариант.
С необходимой переодичностью, вручную или через задания, запускается процедура сбора информации о числе строк, количестве экстентов и занятых блоков таблиц и индексов указанных рабочих схем, а также объёме свободного пространства. Это даёт всю необходимую статистическую информацию о росте БД .
1. Создаётся пользователь stat (у меня это apt).
2. У него создаётся таблица table_stat:
CREATE TABLE TABLE_STAT
(
NAME VARCHAR2(30) NOT NULL,
DAY DATE NOT NULL,
NUM_ROWS NUMBER,
EXTENTS NUMBER,
BLOCKS NUMBER
)
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 304K
NEXT 688K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
)
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX PK_TABLE_STAT ON TABLE_STAT
(NAME, DAY)
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 200K
NEXT 816K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
)
NOPARALLEL;
CREATE INDEX TABLE_STAT#DAY ON TABLE_STAT
(DAY)
TABLESPACE USERS
PCTFREE 1
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 200K
NEXT 496K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
)
NOPARALLEL;
CREATE PUBLIC SYNONYM TABLE_STAT FOR TABLE_STAT;
ALTER TABLE TABLE_STAT ADD (
CONSTRAINT PK_TABLE_STAT PRIMARY KEY (NAME, DAY)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 200K
NEXT 816K
MINEXTENTS 1
MAXEXTENTS 505
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
));
GRANT INSERT ON TABLE_STAT TO UBC;
3. От пользователя, владельца контролируемой схемы, запускается процедура сбора
статистики. Естественно нужно подставит свои имена схем :)
DECLARE
/* Процедура предназначена для сбора статистических параметров состояния таблиц
для анализа роста БД ( моя - полный вариант) */
CURSOR tbl_cur IS
SELECT table_name, owner FROM sys.DBA_TABLES
WHERE owner IN ('UBC','TOAD');
CURSOR ind_cur IS
SELECT object_name, owner
FROM sys.DBA_OBJECTS
WHERE owner IN ('UBC','TOAD')
AND object_type ='INDEX';
v_num_rows NUMBER;
v_extents NUMBER;
v_blok NUMBER;
sql_txt VARCHAR2(255);
v_cursorID INTEGER;
status INTEGER;
dat DATE:=SYSDATE;
a INTEGER;
-- variables for proc. sys.dbms_space.unused_space
total_blocks NUMBER;
total_bytes NUMBER;
unused_blocks NUMBER;
unused_bytes NUMBER;
last_used_extent_file_id NUMBER;
last_used_extent_block_id NUMBER;
last_used_block NUMBER;
BEGIN
FOR tbl_row IN tbl_cur LOOP
v_cursorID:=DBMS_SQL.OPEN_CURSOR;
sql_txt:='SELECT count(*) num_rows FROM '||tbl_row.owner||'.'||tbl_row.table_name;
DBMS_SQL.PARSE(v_cursorID, sql_txt, dbms_sql.v7);
DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, v_num_rows);
status:=DBMS_SQL.EXECUTE(v_cursorID);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursorID)=0 THEN EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_cursorID,1,v_num_rows);
BEGIN --в случае, если кол-во строк изменилось, то вычислить остальное и записать в табл. TABLE_STAT
SELECT 1 INTO a FROM apt.TABLE_STAT
WHERE NAME=tbl_row.table_name AND num_rows=v_num_rows
AND DAY=(SELECT MAX(DAY) FROM apt.TABLE_STAT WHERE NAME=tbl_row.table_name );
EXCEPTION WHEN NO_DATA_FOUND THEN a:=-1;
END;
IF a=-1 THEN
SELECT EXTENTS INTO v_extents
FROM sys.DBA_SEGMENTS WHERE segment_name=tbl_row.table_name
AND owner=tbl_row.owner;
sys.dbms_space.unused_space(tbl_row.owner, tbl_row.table_name,'TABLE',
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block);
v_blok:=total_blocks-unused_blocks;
INSERT INTO apt.TABLE_STAT(NAME, DAY, num_rows, EXTENTS, blocks)
VALUES (tbl_row.table_name, dat, v_num_rows, v_extents, v_blok);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursorID);
END LOOP; --по таблицам
-- цикл по индексам
FOR ind IN ind_cur LOOP
sys.dbms_space.unused_space(ind.owner, ind.object_name,'INDEX',
total_blocks,
total_bytes,
unused_blocks,
unused_bytes,
last_used_extent_file_id,
last_used_extent_block_id,
last_used_block);
v_blok:=total_blocks-unused_blocks;
BEGIN --в случае, если данные не повторяются, то записать в табл. TABLE_STAT
SELECT 1 INTO a FROM apt.TABLE_STAT
WHERE NAME=ind.object_name AND blocks=v_blok
AND DAY=(SELECT MAX(DAY) FROM apt.TABLE_STAT WHERE NAME=ind.object_name );
EXCEPTION WHEN NO_DATA_FOUND THEN a:=-1;
END;
IF a=-1 THEN
SELECT EXTENTS INTO v_extents
FROM sys.DBA_SEGMENTS WHERE segment_name=ind.object_name
AND owner=ind.owner;
INSERT INTO apt.TABLE_STAT(NAME, DAY, num_rows, EXTENTS, blocks)
VALUES(ind.object_name,dat,NULL,v_extents,v_blok);
END IF;
END LOOP; -- по индексам
INSERT INTO apt.TABLE_STAT(NAME, DAY, num_rows, EXTENTS, blocks)
SELECT 'FREE_SPACE', dat, SUM(bytes)/1048576, COUNT(*), NULL FROM sys.DBA_FREE_SPACE
WHERE tablespace_name='USERS';
END;
4. Запускаются от пользователя с правами DBA необходимые запросы по выводу
статистики.
От переодичности её запуска зависит точность статистики. Я запускаю 1 раз в
день, благо процесс длится около 1 мин (4 Gb данных).
Подборка SQL для анализа полученных данных.
-- показывает степень заполненности таблиц и индексов.
SELECT /*+ rule */ ts.NAME,ts.BLOCKS used_blocks, ts.EXTENTS,
ts.NUM_ROWS, dt.BLOCKS all_blocks,
TRUNC((dt.BLOCKS-ts.BLOCKS)*100/dt.BLOCKS) free_space
FROM apt.table_stat ts, sys.DBA_SEGMENTS dt
WHERE ts.NAME=dt.SEGMENT_NAME
AND DAY=(SELECT MAX(DAY) FROM table_stat WHERE NAME=ts.NAME)
ORDER BY 6
Маленький процент свободного места в таблице говорит о том, что она скоро разрастётся на очередной сегмент
(если это растущая таблица)
SELECT /*+ rule */ SUM(dt.NEXT_EXTENT)/(1024*1024) needed_space,
MAX(dt.NEXT_EXTENT)/(1024*1024) max_segment
FROM apt.table_stat ts, sys.DBA_SEGMENTS dt
WHERE ts.NAME=dt.SEGMENT_NAME
AND DAY=(SELECT MAX(DAY) FROM table_stat WHERE NAME=ts.NAME)
AND TRUNC((dt.BLOCKS-ts.BLOCKS)*100/dt.BLOCKS)<3
-- показывает последнее изменение таблиц (на сколько строк и блоков с дня -- предпоследнего изменения таблицы )
SELECT /*+ rule */ t.NAME, t1.NUM_ROWS, t1.NUM_ROWS-t.NUM_ROWS diff_rows, t1.blocks-t.blocks diff_blocks, t1.EXTENTS-t.EXTENTS diff_exts FROM table_stat t, table_stat t1 WHERE t.NAME=t1.NAME AND t1.DAY=(SELECT MAX(DAY) FROM table_stat WHERE NAME=t.NAME) AND t.DAY=(SELECT MAX(DAY) FROM table_stat WHERE DAY<(SELECT MAX(DAY) FROM table_stat)) AND t1.NUM_ROWS-t.NUM_ROWS>0 ORDER BY 1
-- показывает последние даты изменения числа экстентов у таблиц SELECT t.name, to_char(min(t.day),'dd.mm.yy') day_modif, max(extents) extents FROM table_stat t WHERE t.extents=(SELECT EXTENTS extents FROM table_stat WHERE name=t.name AND day=(SELECT max(day) FROM table_stat )) AND t.extents>1 GROUP BY name
--Статистика изменения свободного места в целом по базе
SELECT * FROM table_stat WHERE name='FREE_SPACE'
-- разница в статистике в базе, зафиксированной процедурой и реальной
SELECT /*+ rule */ table_name, dt.num_rows num_rows_stat, t.num_rows num_rows_real, ROUND(t.num_rows-NVL(dt.num_rows,0)) diff FROM DBA_TABLES dt, table_stat t WHERE dt.TABLE_NAME=t.NAME AND t.DAY=(SELECT MAX(DAY) FROM table_stat WHERE NAME=t.NAME )
-- то же самое, но убраны пустые и неанализированные в базе таблицы -- и отсортированы по уменьшению разницы
SELECT /*+ rule */ table_name,dt.num_rows num_rows_stat, t.num_rows num_rows_real, ROUND(t.num_rows-dt.num_rows) diff, ROUND((t.num_rows-dt.num_rows)*100/t.num_rows) proc FROM DBA_TABLES dt, table_stat t WHERE dt.TABLE_NAME=t.NAME AND dt.num_rows IS NOT NULL AND t.num_rows>0 AND t.DAY=(SELECT MAX(DAY) FROM table_stat WHERE NAME=t.NAME ) ORDER BY ABS(ROUND((t.num_rows-dt.num_rows)*100/t.num_rows)) DESC
-- вывод статистики о среднесуточном росте числа записей в таблицах
SELECT /*+ rule */ t2.NAME, ROUND(AVG(t2.num_rows-t1.num_rows)) AVG_DIFF FROM table_stat t1, table_stat t2 WHERE t2.NAME=t1.NAME AND t1.DAY=(SELECT MAX(DAY) FROM table_stat WHERE DAY<t2.DAY ) GROUP BY t2.NAME ORDER BY 2 DESC
-- Показывает прогнозируемый рост таблицSELECT /*+ rule */ ut.TABLE_NAME,ut.BLOCKS,ut.EMPTY_BLOCKS, ROUND((t.AVG*ut.BLOCKS/t.num_rows),3) bloks_to_day, ROUND(ut.EMPTY_BLOCKS/(t.AVG*ut.BLOCKS/t.num_rows)) days_to_next_extent, ROUND(ut.NEXT_EXTENT/1048576,2) next_ext_MB FROM DBA_TABLES ut, (SELECT t2.name, ROUND(AVG(t2.num_rows-t1.num_rows)) AVG, MAX(t2.num_rows) num_rows FROM table_stat t1, table_stat t2 WHERE t2.NAME=t1.NAME AND t1.DAY=(SELECT MAX(DAY) FROM table_stat WHERE DAY>SYSDATE-10 AND DAY<t2.DAY ) HAVING ROUND(AVG(t2.num_rows-t1.num_rows))>0 GROUP BY t2.name) t WHERE t.name=ut.TABLE_NAME AND t.AVG*(ut.BLOCKS-ut.EMPTY_BLOCKS)>0 ORDER BY 5Выделенный фрагмент применяется, если используется не вся статистика (это может быть достаточно долго, или, например, в сборе статистики был большой перерыв) , а только за последние дни ( в данном случае за 10 дней).
---------------------------------
-- удаление лишней двойной статистики за один день -- при запуске анализа несколько раз в день DELETE FROM apt.TABLE_STAT t WHERE EXISTS (SELECT 1 FROM apt.TABLE_STAT ts WHERE ts.name=t.name AND TRUNC(ts.DAY)=TRUNC(t.DAY) AND ts.ROWID>t.ROWID)Хостинг от uCoz