Для мониторинга использования индексов Oracle предлагает простой способ - включить мониторинг индекса и выключитьпо завершению значимого для данного индекса периода. В результате в представлении V$OBJECT_USAGE вы можете увидеть ответ "Yes" или "No".
Но что делать если:
- Вы уже знаете что индекс используется,
- популяция запросов уже настолько велика что проанализировать их на предмет использования запросами не представляется возможным
- Вам нужны доп. сведения о выполнении запросов
Ответ вполне очевиден - нужно проводить мониторинг текущей работы сервера за тот период который для вас является вполне приемлемым для оценки (календарный месяц например, когда все основные операции осуществляются).
Для этого можно использовать данные которые собирает AWR, пример такого использования описан в статье "ORACLE INDEX USAGE TRACKING".
Но и тут не все так хорошо - вы зависите как часто снимаются снимки базы и какой период обновления снимков (т.е. когда есть последний снимок). Вполне вероятно что Вы захотите проанализировать работу системы по какому-то объекту за неделю или за несколько, а данные AWR сохраняются только на несколько последних дней.
Для мониторинга можно использовать такой алгоритм:
1. Создать таблицы в которые собирать интересную информацию.
2. Создать таймерную задачу с некоторым периодом, в которой мониторить все разобранные планы запросов на предмет использоваия в них анализируемолго елемента ( внашем случае - индекса)
3. На протяжении и по окончанию периода выключить таймерную задачу и проанализировать полученные результаты.
4. По завершению мониторинга удалить все обьекты мониторинга или как минимум выключить JOB.
Ниже пример реализации описанного алгоритма:
1.1. Подготовим все нужные права. Под sys нужно дать права на V$SQL, V$SQL_PLAN, V$SQL_BIND_CAPTURE (обратите внимание, что права на имена V$SQL, V$SQL_PLAN дать нельзя т.к. они синонимы):
grant select on V_$SQL to schema_name;
grant select on V_$SQL_PLAN to schema_name;
grant select on V$SQL_BIND_CAPTURE to schema_name;
где schema_name- название схемы на которой нужно провести мониторинг.
1.2. Создадим таблицы для хранения полезной информации для анализа:
-- таблица для хранение информации из V$SQL по интересным для нас обьектам
CREATE TABLE monitoring_index_usage_table as
SELECT *
FROM v$sql s
WHERE s.hash_value IN
(SELECT v.hash_value FROM v$sql_plan v WHERE v.object_name = 'XXX');
-- Add/modify columns
-- Add/modify columns
ALTER TABLE monitoring_index_usage_table ADD what_mon VARCHAR2(100);
ALTER TABLE monitoring_index_usage_table add dt_mon date;
-- Add comments to the columns
COMMENT ON COLUMN monitoring_index_usage_table.what_mon
is 'Что мониторится';
COMMENT ON COLUMN monitoring_index_usage_table.dt_mon
is 'Когда сделана запись';
-- Create/Recreate indexes
CREATE INDEX idx_MONITORING_INDEX_USAGE_TABLE on MONITORING_INDEX_USAGE_TABLE (sql_id);
-- таблица для хранения инфо из v$sql_plan - планов запросов
CREATE TABLE monitoring_index_plans AS SELECT * FROM v$sql_plan WHERE ROWNUM = 0;
-- таблица для хранение информации о параметрах с которыми был разобран впервые или переразобран данный план (тут не храятся последние переменные)
CREATE TABLE monitoring_sql_bind_capture as
SELECT sql_id,
name,
position,
datatype_string,
was_captured,
last_captured,
value_string
FROM v$sql_bind_capture
WHERE sql_id = '-----';
где XXX - анализируемый елемент БД, в моем случае это был индекс, имя которого дальше будет object_name
2.1. Создадим процедуру для заполнения таблиц:
CREATE OR REPLACE PROCEDURE monitoring_sql_plans IS
BEGIN
-- запись запросов, которые используют в плане интересуный для нас индекс
-- object_name из тех что не были записаны ранее
-- мониторинг использования индекса
-- monitoring object_name on schema_name
INSERT INTO monitoring_index_usage_table
SELECT s.*, 'object_name usage', SYSDATE
FROM v$sql s
WHERE s.last_active_time > '14.02.2012 19:20'
AND s.parsing_schema_name = 'schema_name'
AND (s.address, s.hash_value) IN
(SELECT v.address, v.hash_value
FROM v$sql_plan v
WHERE v.object_name IN ('object_name')
AND v.object_owner = 'schema_name')
AND (address, hash_value) NOT IN
(SELECT address, hash_value FROM monitoring_index_usage_table);
FOR v_i IN (SELECT DISTINCT address, hash_value
FROM v$sql_plan
WHERE object_name IN ('object_name')
AND (address, hash_value) NOT IN
(SELECT address, hash_value FROM monitoring_index_plans)) LOOP
INSERT INTO monitoring_index_plans
SELECT *
FROM v$sql_plan v
WHERE v.hash_value = v_i.hash_value
AND v.address = v_i.address;
END LOOP;
---------------------------------------------------------------------------------
-- Обновить bind переменные если появились новые запросы или старые переразобрались с новыми планами
FOR v_i IN (SELECT sql_id,
NAME,
position,
datatype_string,
was_captured,
last_captured,
value_string
FROM v$sql_bind_capture
WHERE sql_id IN
(SELECT DISTINCT sql_id
FROM monitoring_index_usage_table)
AND (sql_id, last_captured) NOT IN
(SELECT DISTINCT sql_id, last_captured
FROM monitoring_sql_bind_capture)) LOOP
INSERT INTO monitoring_sql_bind_capture
VALUES
(v_i.sql_id,
v_i.name,
v_i.position,
v_i.datatype_string,
v_i.was_captured,
v_i.last_captured,
v_i.value_string);
END LOOP;
END monitoring_sql_plans;
где schema_name - имя вашей схемы БД
2.2. Создадим JOB для выполнения раз в пол часа (задайте удобное вам время):
BEGIN
sys.dbms_job.submit(job => :job,
what => 'begin monitoring_sql_plans; end;',
next_date => SYSDATE + 1 / 24 / 60 / 60,
INTERVAL => 'SYSDATE+1/48');
COMMIT;
END;
/
3. Дальше время от времени или по завершению значимого периода анализируем результат. Для этого привожу несколько полезных разных срезов:
SELECT round(t.cpu_time / 1000000, 2) AS time_seq,
t.loads,
t.executions,
decode(nvl(t.executions, 0), 0, 0,
round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load,
t.*
FROM monitoring_index_usage_table t
WHERE what_mon = 'UK_OBJ_DOC_OBJ_PROD_PART_BIRT usage'
ORDER BY time_per_load DESC;
-- BIND VARIABLE для найдовшого запиту
SELECT *
FROM monitoring_sql_bind_capture
WHERE sql_id -- = '6pdbd2w2nd9w9'
IN (SELECT sql_id
FROM (SELECT decode(nvl(t.executions, 0), 0, 0,
round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load,
t.*
FROM monitoring_index_usage_table t -- 22 -- 71 вечер 12 -- 116 день 13го
WHERE what_mon = 'object_name usage'
ORDER BY time_per_load DESC)
WHERE rownum = 1);
Описание V$SQL см. тут.
Описание V$SQL_PLAN см. тут.
Описание V$SQL_BIND_CAPTURE см. тут.
Для моих нужд такого кода оказалось достаточно и к тому же базу он не нагрузил даже в период активной работы сотни пользователей с системой.
Надеюсь кому-то данный пример поможет.
Также на скорую руку сделана процедурка которая делает distinct clob-ов которые хранят SQL_FULLTEXT:
CREATE OR REPLACE PROCEDURE mon_index_usage_get_only_sql IS
n NUMBER;
BEGIN
-- for getting distinct sqls to table mon_index_usage_sqls
-- from monitoring_index_usage_table
DELETE FROM mon_index_usage_sqls;
FOR v_i IN (SELECT * FROM monitoring_index_usage_table t) LOOP
SELECT COUNT(*)
INTO n
FROM mon_index_usage_sqls s
WHERE dbms_lob.compare(s.sql_fulltext, v_i.sql_fulltext) = 0;
IF (n = 0) THEN
INSERT INTO mon_index_usage_sqls
(sql_text, sql_fulltext)
VALUES
(v_i.sql_text, v_i.sql_fulltext);
END IF;
END LOOP;
END;
И ее использование:
BEGIN
-- Call the procedure
mon_index_usage_get_only_sql;
END;
/
SELECT * FROM mon_index_usage_sqls;
4. После того как мониторинг закончился базу лучше почистить от ненужных таблиц и данных:
EXECUTE DBMS_JOB.REMOVE(:jobno);
DROP TABLE monitoring_index_usage_table ;
DROP TABLE monitoring_index_plans ;
DROP TABLE monitoring_sql_bind_capture ;
DROP PROCEDURE monitoring_sql_plans ;
DROP PROCEDURE mon_index_usage_get_only_sql ;
Выводы: предложенный метод мониторинга использования индекса можно использовать для мониторинга любого объекта в планах запросов в таком разрезе как это нужно вам и так часто как это нужно вам )))