Правильно ли осуществляется мониторинг табличных пространств TEMP?
Я разработал этот монитор табличных пространств TEMP через DBLINKS в ORACLE, он отправляет электронное письмо, если какое-либо временное табличное пространство достигает какого-либо процента, вызванного v_used:
CREATE OR REPLACE PROCEDURE SP_TEMP_MON(
v_used NUMBER
) AS
v_sql VARCHAR2(4000);
v_html CLOB := EMPTY_CLOB();
v_execution_date DATE := SYSDATE;
v_db_link_name VARCHAR2(128);
v_link_open BOOLEAN := FALSE;
v_actions_found BOOLEAN := FALSE;
v_total_mb NUMBER;
v_query_mb_used NUMBER;
v_tablespace_mb_used NUMBER;
v_query_percentage_used NUMBER;
v_tablespace_percentage_used NUMBER;
TYPE temp_details_type IS RECORD (
tablespace VARCHAR2(128),
os_username VARCHAR2(128),
sql_text VARCHAR2(4000),
query_mb_used NUMBER,
tablespace_mb_used NUMBER,
total_mb NUMBER
);
TYPE temp_details_table IS TABLE OF temp_details_type;
temp_details_list temp_details_table := temp_details_table();
CURSOR c_dblinks IS
SELECT NOMBREDBLINK || '.DBLINK.DOMAIN.COM' AS NOMBREDBLINK
FROM BDHIST.CATALOGO_DBLINKS
WHERE CONECTA = 'SI' AND NODO IS NULL;
BEGIN
-- We iterate through all the user DBLINKs defined in the database to audit.
FOR rec_link IN c_dblinks LOOP
v_db_link_name := rec_link.NOMBREDBLINK;
v_link_open := FALSE;
BEGIN
-- Building the dynamic query to obtain tablespace usage information
v_sql := 'WITH sort_usage AS (
SELECT
T.tablespace,
SUM(T.blocks * TBS.block_size) / 1024 / 1024 AS mb_used,
S.osuser,
Q.sql_text
FROM
v$sort_usage@'||v_db_link_name||' T
JOIN
v$session@'||v_db_link_name||' S ON T.session_addr = S.saddr
LEFT JOIN
v$sqlarea@'||v_db_link_name||' Q ON T.sqladdr = Q.address
JOIN
dba_tablespaces@'||v_db_link_name||' TBS ON T.tablespace = TBS.tablespace_name
GROUP BY
T.tablespace, S.osuser, Q.sql_text
),
tablespace_summary AS (
SELECT
A.tablespace_name AS tablespace,
SUM(A.used_blocks * D.block_size) / 1024 / 1024 AS mb_used,
SUM(D.mb_total) AS total_mb
FROM
v$sort_segment@'||v_db_link_name||' A
JOIN
(SELECT
B.name,
C.block_size,
SUM(C.bytes) / 1024 / 1024 AS mb_total
FROM
v$tablespace@'||v_db_link_name||' B
JOIN
v$tempfile@'||v_db_link_name||' C ON B.ts# = C.ts#
GROUP BY
B.name,
C.block_size) D ON A.tablespace_name = D.name
GROUP BY
A.tablespace_name
)
SELECT
ts.tablespace,
su.osuser,
su.sql_text,
su.mb_used AS query_mb_used,
ts.mb_used AS tablespace_mb_used,
ts.total_mb
FROM
tablespace_summary ts
JOIN
sort_usage su ON ts.tablespace = su.tablespace
ORDER BY
query_mb_used DESC';
EXECUTE IMMEDIATE v_sql BULK COLLECT INTO temp_details_list;
v_link_open := TRUE;
-- Print headers if data found
IF temp_details_list.COUNT > 0 THEN
-- Check the usage percentage and whether it meets the mail sending condition
v_actions_found := FALSE;
FOR i IN 1..temp_details_list.COUNT LOOP
v_total_mb := temp_details_list(i).total_mb;
v_tablespace_mb_used := temp_details_list(i).tablespace_mb_used;
v_tablespace_percentage_used := (v_tablespace_mb_used / v_total_mb) * 100;
IF v_tablespace_percentage_used >= v_used OR (v_total_mb - v_tablespace_mb_used) / v_total_mb * 100 <= 100 - v_used THEN
v_actions_found := TRUE;
EXIT;
END IF;
END LOOP;
-- HTML headers
v_html :=
v_html
|| '<h2>DBLINK: ' || v_db_link_name || '</h2>'
|| '<table border = "1" cellpadding = "5" cellspacing = "0">'
|| '<tr>'
|| '<th>TABLESPACE</th>'
|| '<th>OS_USERNAME</th>'
|| '<th>SQL_TEXT</th>'
|| '<th>QUERY_MB_USED</th>'
|| '<th>TOTAL_MB</th>'
|| '<th>QUERY_PERCENTAGE_USED</th>'
|| '<th>TABLESPACE_PERCENTAGE_USED</th>'
|| '</tr>';
FOR i IN 1..temp_details_list.COUNT LOOP
v_query_mb_used := temp_details_list(i).query_mb_used;
v_query_percentage_used := (v_query_mb_used / v_total_mb) * 100;
v_html :=
v_html
|| '<tr>'
|| '<td>' || temp_details_list(i).tablespace || '</td>'
|| '<td>' || temp_details_list(i).os_username || '</td>'
|| '<td>' || temp_details_list(i).sql_text || '</td>'
|| '<td>' || TO_CHAR(temp_details_list(i).query_mb_used) || '</td>'
|| '<td>' || TO_CHAR(temp_details_list(i).total_mb) || '</td>'
|| '<td>' || TO_CHAR(v_query_percentage_used, 'FM9999990.000') || '%</td>'
|| '<td>' || TO_CHAR(v_tablespace_percentage_used, 'FM9999990.000') || '%</td>'
|| '</tr>';
END LOOP;
v_html := v_html || '</table>';
ELSE
v_html :=
v_html
|| '<h2>Detalles de DBLINK: ' || v_db_link_name || '</h2>'
|| '<p>TEMP not in use.</p>';
END IF;
COMMIT;
IF v_link_open THEN
EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('''||v_db_link_name||'''); END;';
v_link_open := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF v_link_open THEN
EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.CLOSE_DATABASE_LINK('''||v_db_link_name||'''); END;';
END IF;
DBMS_OUTPUT.PUT_LINE('Error processing DBLINK ' || v_db_link_name || ': ' || SQLERRM);
END;
END LOOP;
IF v_actions_found THEN
MONITORING_SCHEMA.PG_ENVIO_MAIL.entrega(
vg_from => '[email protected]',
vg_to => '[email protected]',
vg_asunto => 'TEMP Monitoring',
vg_cuerpo => v_html,
vg_firma => 'sender',
vg_cc => '[email protected]'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;
/
SHOW ERRORS;
Я поставил задание выполнять процедуру каждые 5 минут, отправляя почту, когда v_used = 85 процентов памяти заполнено любым DBLINK, но мне сказали, что она не отправляет электронное письмо, когда оно заполнено, или, по крайней мере, не отправляет перехватить все табличное пространство, когда это необходимо. Мой вопрос: правильный ли основной запрос?
WITH sort_usage AS (
SELECT
T.tablespace,
SUM(T.blocks * TBS.block_size) / 1024 / 1024 AS mb_used,
S.osuser,
Q.sql_text
FROM
v$sort_usage@'||v_db_link_name||' T
JOIN
v$session@'||v_db_link_name||' S ON T.session_addr = S.saddr
LEFT JOIN
v$sqlarea@'||v_db_link_name||' Q ON T.sqladdr = Q.address
JOIN
dba_tablespaces@'||v_db_link_name||' TBS ON T.tablespace = TBS.tablespace_name
GROUP BY
T.tablespace, S.osuser, Q.sql_text
),
tablespace_summary AS (
SELECT
A.tablespace_name AS tablespace,
SUM(A.used_blocks * D.block_size) / 1024 / 1024 AS mb_used,
SUM(D.mb_total) AS total_mb
FROM
v$sort_segment@'||v_db_link_name||' A
JOIN
(SELECT
B.name,
C.block_size,
SUM(C.bytes) / 1024 / 1024 AS mb_total
FROM
v$tablespace@'||v_db_link_name||' B
JOIN
v$tempfile@'||v_db_link_name||' C ON B.ts# = C.ts#
GROUP BY
B.name,
C.block_size) D ON A.tablespace_name = D.name
GROUP BY
A.tablespace_name
)
SELECT
ts.tablespace,
su.osuser,
su.sql_text,
su.mb_used AS query_mb_used,
ts.mb_used AS tablespace_mb_used,
ts.total_mb
FROM
tablespace_summary ts
JOIN
sort_usage su ON ts.tablespace = su.tablespace
ORDER BY
query_mb_used DESC'
или в моем подходе чего-то не хватает?
Помимо баллов Алекса, какую проблему вы пытаетесь решить с помощью этой работы? Табличное пространство TEMP полно временных данных. Если у вас нет системы, которая постоянно исчерпывает ваше табличное пространство TEMP, выполнение запроса каждые 5 минут с целью определения загрузки на 85% вряд ли позволит обнаружить это условие. Неправильный запрос может чертовски быстро заполнить табличное пространство TEMP, вызвать ошибку, а затем освободить место. Шансы на то, что вы обнаружите проблему, отправите электронное письмо, попросите человека получить электронное письмо и сделаете что-нибудь, чтобы предотвратить исчерпание табличного пространства до того, как будут использованы последние 15%, невелики.
Ответы 1
Если у вас есть база данных RAC (несколько хостов, монтирующих БД), вам нужно будет использовать gv$sort_segment (или, альтернативно, gv$sort_usage) вместо одноузловой версии v$, которая не будет фиксировать то, что используется сеансами в других экземплярах, к которым вы в настоящее время не подключены. Это приведет к занижению использованной суммы.
Во-вторых, как только вы это исправите, вам также нужно будет изменить эту строку, чтобы использовать MAX вместо SUM:
SUM(D.mb_total) AS total_mb
Поскольку это находится на уровне сегмента сортировки, а каждый экземпляр может иметь сегмент в каждом временном пространстве, ваш внешний запрос имеет меньшую степень детализации, чем внутренний, поэтому SUM умножит и без того правильный размер табличного пространства намного больше, чем он есть. должно быть. Измените это SUM на MAX, чтобы это исправить.
Одна из потенциальных проблем заключается в том, что gv$sort_usage будет отображать только текущие рабочие области SQL — если их нет, вы ничего не получите. Это исключит любой результат вашего запроса, поскольку вы присоединяетесь к нему в конце. Имейте в виду, что области сортировки/хеширования SQL — не единственные объекты, которые могут использовать temp. Например, глобальные временные таблицы тоже работают, но они не отображаются в этом представлении.
Вы также можете использовать gv$temp_extent_pool, который учитывает все виды временных экстентов.
Чтобы быть эффективным, вам действительно нужно выполнять это каждую минуту. 5 минут — это слишком далеко друг от друга — за 5 минут можно употребить много температуры. Также было бы лучше, если бы он запускался локально, а не через ссылку на базу данных.
Какую отладку вы делали? Что вы видите, когда запускаете запрос или процедуру вручную? Вы получаете сообщение об ошибке при выполнении запланированного задания? А что, если вы удалите уловку
when others(которая обычно сама по себе является ошибкой)? Выполняется ли задание с правами просмотра таблицы поиска ссылок, использования ссылок и выполнения процедуры отправки электронной почты? И т. д.