Oracle / Мониторинг использования индексов в планах запросов в Oracle 10g

в 9:03, , рубрики: oracle 10g, метки:

Для мониторинга использования индексов Oracle предлагает простой способ — включить мониторинг индекса и выключитьпо завершению значимого для данного индекса периода. Описание на сайте 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 = 'LANDGUT'        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; 

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 ; 

Выводы: предложенный метод мониторинга использования индекса можно использовать для мониторинга любого объекта в планах запросов в таком разрезе как это нужно вам и так часто как это нужно вам )))

Автор: rolechka


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js