Введение
Иногда приходиться решать задачи управления таблицами с отличной от классических канонов структурой. По ряду причин, такие таблицы растут как вниз (добавление строк данных), так и вправо (добавление столбцов). Причиной может быть исторически принятая, унаследованная структура данных (например, приложение, портированное в СУБД Oracle без перепроектирования структур), или проблемы производительности на этапе сложного расчета.
Например, базовые данные находятся в правильном, нормализованном представлении, а на начальном этапе многоступенчатого расчета производиться "динамическое распрямление вправо" по слабо изменяемому признаку и заполнение заранее созданных глобальных временных таблиц (Global Temporary Table - GTT). Результаты расчета могут быть затем агрегированы или "схлопнуты" по какому-либо признаку.
Это может быть актуально, когда объем данных в одном сеансе расчета очень значителен (сотни тысяч или миллионы строк). Как правило, это расчетные задачи OLAP (DSS), например, расчет времени и скорости продаж товаров по всей сети магазинов, прогноз товарного запаса, расчет материального баланса. В результате такого представления GTT, "высота" таблицы сокращается пропорционально количеству магазинов сети (слабо изменяемому признаку), например в 50 раз, с 30 млн. до 600 тыс. строк для каждого типа данных (остатки, продажи и т.д.).
Я не стану давать оценок таким структурам с точки зрения классического проектирования, скажу лишь, что они используются и, на этапе расчета, могут давать значительный выигрыш в производительности. Тем более, если динамика роста вправо приемлема (ограничение в Oracle 9i - 1000 столбцов). Реализация такого расчетного механизма возможна, например, с применением динамического SQL и наборов (collections).
Итак, каждый сеанс использует для расчетов некоторый набор PL/SQL-пакетов, хранит данные своего расчета в наборе временных таблиц (например, уровня сеанса, ON COMMIT PRESERVE ROWS).
Следовательно, мы сталкиваемся с проблемой пересоздания временных таблиц после добавления или удаления слабо изменяющегося признака (магазина). Пересоздание возможно как сразу (в оnline), так и отложенно (с помощью задания, выполняемого по определенному графику, например, ночью, при минимальной нагрузке на сервер).
Алгоритм действий
- Получение списка блокирующих сеансов. Тип блокировок - 'TO'1 для GTT.
- Оповещение с помощью DBMS_ALERT всех "думающих" сеансов и повторный запрос списка блокирующих сеансов.
- Принудительное завершение работы ("убивание") блокирующих сеансов.
- Проверка и "убивание" зависших KILLED-сеансов для текущего ORACLE_SID. При этом используется технология выполнения комманд ОС из PL/SQL: надо "убить" процесс или нить зависшего сеанса (варианты для UNIX- и для Windows-платформы, притом непосредственно из PL/SQL, без использования внешних заданий и планировщиков).
- Пересоздание всех нужных временных таблиц и индексов.
- Перекомпиляция всех или выбранных INVALID-объектов.
1. Кстати, что-то я не обнаружил в документации описания этого типа блокировки. А ведь, как легко убедиться, именно такие блокировки устанавливаются, когда сеанс вставил какие-то данные в глобальную временную таблицу уровня сеанса... - Примечание В.К.
Полный текст пакета представлен в Приложении 2, я же остановлюсь на необходимых деталях и дополнениях.
Тонкости реализации
1. Получение списка блокирующих сеансов и блокировки GTT
При первой вставке в глобальную временную таблицу сервер Oracle устанавливает на нее блокировку 'TO', которая удерживается при любых дальнейших изменениях, до наступления одного из двух условий:
- завершение сеанса или фиксация транзакции (в зависимости от типа GTT, указанного при создании: ON COMMIT PRESERVE ROWS или ON COMMIT DELETE ROWS);
- выполнение оператора TRUNCATE TABLE.
Таким образом, чтобы найти блокирующий сеанс, необходимо искать блокировки любой пересоздаваемой глобальной временной таблицы, участвующей в расчетах (в нашем примере - ZZZ_TEST).
Установки статуса INVALID для создаваемого пакета TM_UTIL (и дальнейшую перекомпиляцию самого себя) можно избежать, используя динамический SQL в теле пакета.
В результате, имеем два фильтра для поиска блокирующего сеанса (тип блокировки и имя GTT). В качестве параметров дальнейшей команды ALTER SYSTEM KILL SESSION подаем полученные идентификаторы сеанса, SID и SERIAL#.
-- Курсор наличия блокирующих сеансов
CURSOR SESS_bl_cur
IS
SELECT distinct VS.SID, VS.SERIAL#
FROM V$SESSION VS, V$LOCK VL, DBA_OBJECTS OBJ
WHERE OBJ.OBJECT_NAME = 'ZZZ_TEST' AND
VL.TYPE='TO' AND
VL.ID1=OBJ.OBJECT_ID AND
VL.SID=VS.SID;
-- Запись для выборки из курсора
sess_bl_rec SESS_bl_cur%ROWTYPE;
Чтобы пакет смог успешно обратиться к соответствующим представлениям словаря данных, необходимо выдать пользователю-создателю пакета следующие привилегии:
GRANT SELECT ON SYS."V_$SESSION" TO "SHOPS_MOD" GRANT SELECT ON SYS."V_$LOCK" TO "SHOPS_MOD" GRANT SELECT ON SYS."DBA_OBJECTS" TO "SHOPS_MOD"
Потребуются также (см. далее) привилегии:
GRANT SELECT ON SYS."V_$PROCESS" TO "SHOPS_MOD" GRANT SELECT ON SYS."V_$INSTANCE" TO "SHOPS_MOD"
Впрочем, если безопасность и принцип минимальности привилегий вас не беспокоят, достаточно будет пары операторов:
GRANT SELECT ANY TABLE TO "SHOPS_MOD"; GRANT SELECT ANY DICTIONARY TO "SHOPS_MOD";
2. Оповещение с помощью DBMS_ALERT "думающих" сеансов
Сигнал (Alert) создается и контролируется на считающем клиенте, а имя сигнала либо жестко "зашивается" в пакете, либо может ему передаваться во входных параметрах. Отправка сигнала реализуется с помощью автономной транзакции, дабы преждевременно не фиксировать основную транзакцию.
PROCEDURE Alert_msg
(a_alert_name in VARCHAR2, a_alert_msg IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
-- Пошлем сообщение всем, кто зарегистрировался для получения нашего сигнала
DBMS_ALERT.signal(a_alert_name, a_alert_msg);
COMMIT;
end Alert_msg;
3. Прекращение работы блокирующих сеансов
Реализуем стандартным методом:
ALTER SYSTEM KILL SESSION 'int1, int2'
где параметры - полученные ранее SID и SERIAL#.
Более мягкий вариант:
ALTER SYSTEM DISCONNECT SESSION 'int1 , int2' POST_TRANSACTION IMMEDIATE
4. Проверка и "убивание" зависших KILLED-сеансов для текущего ORACLE_SID
Часто происходит так, что после завершения сеанса командой ALTER SYSTEM, сервер Oraсle не завершает сеанс, а присваивает ему статус 'KILLED'. Такие "зависшие" сеансы не освобождают ресурсы сервера (блокировки, защелки и т.д.), что не позволяет, в частности, пересоздавать использовавшиеся ими временные таблицы.
Справедливости ради стоит заменить, что в версии 9i ситуация кардинально улучшилась.
Для решения проблемы нужно "убить" серверный процесс (нить) зависшего KILLED-сеанса. В зависимости от платформы, существуют следующие варианты:
1.(NT): Использую Oracle-утилиту orakill. Добавляю instance_name, полученный из v$instance:
SELECT 'orakill ' // i.instance_name // ' ' // p.spid as Kill_cmd
FROM v$process p, v$session s, v$instance i
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
2.(SUSE Linux): Использую стандартную утилиту kill:
SELECT '/bin/kill -s KILL ' // p.spid as Kill_cmd
FROM v$process p, v$session s
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
(В представленном далее коде пакета я рассматриваю вариант реализации на SUSE Linux).
Но это ещё не всё, т.к. хотелось бы выполнять эту процедуру прямо из тела нашего пакета, а не через внешние задания или планировщики.
Фактически, речь идет о технологии выполнения команд ОС из PL/SQL. Это можно сделать с применением Java в Oracle. Создается java-класс OSUtil и функция-обертка, RUN_CMD. Плюс, для удобства представления результатов через DBMS_OUTPUT, можно создать процедуру RC.
------------------------------------------------------------------
-- 4.1. Технология выполнения команд ОС из PL/SQL
-------------------------------------------------------------------
create or replace and compile java source named "OSUtil" as
import java.io.*;
import java.lang.*;
public class OSUtil extends Object
{
public static int RunThis(String args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/
--------------------------------------------------------------
create or replace function RUN_CMD( p_cmd in varchar2) return number
AUTHID CURRENT_USER as language java
name 'OSUtil.RunThis(java.lang.String) return integer';
/
--------------------------------------------------------------
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
DBMS_OUTPUT.PUT_LINE('run_cmd returned : '//rpad(x, 3, ' ')//' for '//p_cmd);
end;
/
-------------------------------------------------------------------------------------
-- 4.2. Дать привилегии (от имени SYS), на выполнение команды (утилиты)
-------------------------------------------------------------------------------------
begin
dbms_java.grant_permission (
'SHOPS_MOD',
'SYS:java.io.FilePermission',
'/bin/kill',
'execute'
);
dbms_java.grant_permission (
'SHOPS_MOD',
'SYS:java.lang.RuntimePermission',
'*',
'writeFileDescriptor'
);
end;
/
-------------------------------------------------------------------------------------
-- 4.3. Сымитируем пакетное выполнение:
-------------------------------------------------------------------------------------
-- В первом сеансе, можно проверить свой SID:
select SID, SERIAL# from v$session where audsid=userenv('SESSIONID');
-- Из другого сеанса можно завершить первый (используя SID как параметр команды kill)
set serveroutput on size 1000000;
exec dbms_java.set_output(1000000);
exec shops_mod.rc('/bin/kill -s KILL 11630');
5. Пересоздание всех необходимых временных таблиц и индексов
Выполняются все необходимые пересоздания структур, которым теперь не мешают никакие блокировки. Предполагается, что новые сеансы, работающие с временными таблицами, не появятся, потому что это действие выполняется в период минимальной загруженности сервера.
6. Перекомпиляция всех или выбранных INVALID-объектов
Понятно, что после пересоздания временных таблиц все зависимые от них объекты станут недействительными (INVALID), поэтому наша задача их перекомпилировать.
Необходимо предотвратить перекомпиляцию пакетом самого себя (TM_UTIL). Это можно сделать двумя способами:
- добавить явный фильтр на имя пакета 'TM_UTIL' в курсор RECOMPILE_cur.
- избежать установки статуса INVALID для пакета 'TM_UTIL', используя динамический SQL в теле пакета.
Я буду использовать последний вариант и перекомпилировать объекты только одной схемы, SHOPS_MOD.
Если понадобится перекомпилировать зависимые объекты во всех схемах (уже от имени SYSTEM), нужно сделать UNION c 'alter session set current_schema owner'. Таким образом, запрос меняется:
SELECT DISTINCT 'alter session set current_schema=' //
owner //
';' cmd,
owner,
1 order_col,
NULL object_name
FROM dba_objects
WHERE status = 'INVALID'
AND object_type IN ('PACKAGE',
'PACKAGE BODY',
'VIEW',
'PROCEDURE',
'FUNCTION',
'TRIGGER')
UNION
SELECT 'ALTER ' //
DECODE (
object_type,
'PACKAGE BODY', 'PACKAGE',
object_type
) //
' ' //
owner //
'.' //
object_name //
' COMPILE' //
DECODE (
object_type,
'PACKAGE BODY', ' BODY',
''
) //
';' cmd,
owner,
2 order_col,
object_name
FROM dba_objects outer
WHERE status = 'INVALID'
AND object_type IN ('PACKAGE',
'PACKAGE BODY',
'VIEW',
'PROCEDURE',
'FUNCTION',
'TRIGGER')
AND ( object_type <>
'PACKAGE BODY'
OR NOT EXISTS ( SELECT NULL
FROM dba_objects
WHERE owner =
outer.owner
AND object_name =
outer.object_name
AND object_type =
'PACKAGE'
AND status =
'INVALID')
)
ORDER BY 2, 3, 4
Приложение 1: Тестовые таблицы
-- "Распрямленная" глобальная временная таблица для этапа расчета
CREATE GLOBAL TEMPORARY TABLE ZZZ_TEST
(DATE_ DATE,
ART VARCHAR2(30),
"ВВЦ_п" NUMBER,
"Л21_п" NUMBER,
"Мин_п" NUMBER,
"Икеа_п" NUMBER
-- ...
-- Магазинов может быть очень много
)
ON COMMIT PRESERVE ROWS;
CREATE INDEX I_ZZZ_TEST ON ZZZ_TEST (ART ASC);
-- Справочник объектов хранения (магазинов)
CREATE TABLE N_OBJ_STORAGE
(ID_OBJ NUMBER(7,0) NOT NULL,
ID_PROJECT NUMBER(7,0),
ID_LOC NUMBER(7,0),
TYPE_ VARCHAR2(20),
NAME_ VARCHAR2(200),
SHORTNAME VARCHAR2(20),
ACCOUNTTD VARCHAR2(20),
ADDRESS VARCHAR2(200),
TELEPHONE VARCHAR2(100),
DATEOPEN DATE,
DATECLOSE DATE,
INUSE NUMBER(1,0),
STATUS VARCHAR2(1) -- Флаг изменения записи [I,U,D]
);
Приложение 2: Исходный текст пакета TM_UTIL
-------------------------------------------------------
PACKAGE TM_UTIL
IS
TYPE TStringTab IS TABLE OF VARCHAR2(255)
INDEX BY BINARY_INTEGER;
PROCEDURE Alert_msg
(a_alert_name IN VARCHAR2,
a_alert_msg IN VARCHAR2);
FUNCTION KP_Recreate_TEST
RETURN NUMBER;
-- Возврат: 0 - Ошибка; 1 - Было пересоздание; 2 - Не было пересоздания
END;
-------------------------------------------------------
PACKAGE BODY TM_UTIL
IS
-- Вызов:
-- Alert_msg(alert_name, alert_msg);
PROCEDURE Alert_msg
(a_alert_name in VARCHAR2, a_alert_msg IN VARCHAR2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
-- Пошлем сообщение всем, кто зарегистрировался...
DBMS_ALERT.signal(a_alert_name,a_alert_msg);
COMMIT;
end Alert_msg;
/*==========================================================================*/
/* Ввод/модификация нового Канала Реализации (К.Р.):
/* Возврат: 0 - Ошибка; 1 - Было пересоздание; 2 - Не было пересоздания
/*==========================================================================*/
FUNCTION KP_Recreate_TEST
RETURN NUMBER
AS
-- Курсор факта изменения Обьекта хранения (поле STATUS: [I,U,D]).
-- Т.е. при удалении К.Р. запись не удаляется, а STATUS := 'D'
CURSOR STATUS_cur
IS
select ID_OBJ, ID_LOC, TYPE_, SHORTNAME
from N_OBJ_STORAGE WHERE STATUS IS NOT NULL;
st_rec STATUS_cur%ROWTYPE;
-- Курсор наличия блокирующих сеансов.
-- Можно использовать параметризованный курсор (для OBJECT_NAME и TYPE)
CURSOR SESS_bl_cur
IS
SELECT distinct VS.SID, VS.SERIAL#
FROM V$SESSION VS, V$LOCK VL, DBA_OBJECTS OBJ
WHERE OBJ.OBJECT_NAME = 'ZZZ_TEST' AND
VL.TYPE='TO' AND
VL.ID1=OBJ.OBJECT_ID AND
VL.SID=VS.SID;
sess_bl_rec SESS_bl_cur%ROWTYPE;
-- Курсор "убивания" подвисших KILLED-сеансов
CURSOR ORAKILL_cur
IS
SELECT '/bin/kill -s KILL ' // p.spid as Kill_cmd
FROM v$process p, v$session s
WHERE p.addr = s.paddr AND
s.status = 'KILLED';
orakill_rec ORAKILL_cur%ROWTYPE;
-- Перекомпилируем в цикле все INVALID-обьекты схемы SHOPS_MOD
CURSOR RECOMPILE_cur
IS
SELECT 'ALTER '
// DECODE (object_type,
'PACKAGE BODY', 'PACKAGE',
object_type
)
// ' '
// owner
// '.'
// object_name
// ' COMPILE'
// DECODE (object_type, 'PACKAGE BODY', ' BODY', '') cmd,
owner, object_name
FROM dba_objects OUTER
WHERE status = 'INVALID'
AND owner = 'SHOPS_MOD'
AND object_type IN
('PACKAGE',
'PACKAGE BODY',
'VIEW',
'PROCEDURE',
'FUNCTION',
'TRIGGER'
)
AND ( object_type <> 'PACKAGE BODY'
OR NOT EXISTS (
SELECT NULL
FROM dba_objects
WHERE owner = OUTER.owner
AND object_name = OUTER.object_name
AND object_type = 'PACKAGE'
AND status = 'INVALID')
);
recompile_rec RECOMPILE_cur%ROWTYPE;
l_alert_name Varchar2(30) := 'TM_RECREATE'; -- Имя сигнала
l_alert_msg Varchar2(500) :=
'ВНИМАНИЕ: Для введения в строй нового Канала реализации, ' // Chr(13)
// 'необходимо сохранить данные и выйти из текущей формы расчета. ' // Chr(13)
// 'Через 3 минуты сеанс будет завершен!';
la_smag TStringTab;
l_strSQL VARCHAR2(16000);
l_strSQL2 VARCHAR2(16000);
l_strSQL3 VARCHAR2(16000);
f_exit BOOLEAN := false; -- Выход: по умолчанию - НЕТ
l_ret NUMBER := 2; -- Возврат: по умолчанию - Не было пересоздания
BEGIN
-- Были ли измененные К.Р.?
-- Используется как факт начала пересоздания (%NOTFOUND=FALSE)
OPEN STATUS_cur;
FETCH STATUS_cur INTO st_rec;
IF STATUS_cur%NOTFOUND THEN
f_exit:=true;
ELSE
f_exit:=false;
END IF;
CLOSE STATUS_cur;
-- Выход, если нечего перестраивать (2).
IF f_exit=true THEN RETURN l_ret; END IF;
-- Да: были измененные К.Р. => Проверить, есть ли блокирующие сеансы.
-- Получаем список "нехороших" сеансов.
-- Если есть нехорошие сеансы - разослать им сообщение о выходе,
-- ждать 3 минуты, переоткрыть.
-- Если после перечитывания еще живут - KILL SESSION,
-- а потом - прекращение процессов (нитей)
OPEN SESS_bl_cur;
FETCH SESS_bl_cur INTO sess_bl_rec;
IF SESS_bl_cur%FOUND THEN
-- Пошлем сообщение всем "считающим" сеансам
TM_UTIL.Alert_msg(l_alert_name, l_alert_msg);
CLOSE SESS_bl_cur;
-- Подождем 3 минуты...
DBMS_LOCK.SLEEP(180);
-- Перечитаем заново... кто же нас не послушался ;)
OPEN SESS_bl_cur;
LOOP
FETCH SESS_bl_cur INTO sess_bl_rec;
EXIT WHEN SESS_bl_cur%NOTFOUND;
-- 2.2. Убиваем "нехорошие" сеансы после перезапроса
l_strSQL :='ALTER SYSTEM KILL SESSION ''' // to_char(sess_bl_rec.SID) // ','
// to_char(sess_bl_rec.SERIAL#) // '''';
EXECUTE IMMEDIATE l_strSQL;
END LOOP;
-- Убиваем KILLED-сеансы (если такие есть) для текущего ORACLE_SID
OPEN ORAKILL_cur;
LOOP
FETCH ORAKILL_cur INTO orakill_rec;
EXIT WHEN ORAKILL_cur%NOTFOUND;
-- Убить процесс (нить) зависшего KILLED-сеанса
shops_mod.rc(orakill_rec.Kill_cmd);
END LOOP;
CLOSE ORAKILL_cur;
END IF; -- По факту нехороших сеансов
CLOSE SESS_bl_cur; -- Для обоих случаев выхода
-- Да, были измененные К.Р. и, возможно, убивали сеансы.
-- Пересоздать нужные временные таблицы и их индексы
-- Удаляем временные таблицы (индексы удаляются автоматически)
l_ret:= 0; -- Как бы прогнозируем ошибку
EXECUTE IMMEDIATE 'drop table ZZZ_TEST';
-- Создаем новые таблицы
-- Создадим набор новых "доступных" магазинов
SELECT SHORTNAME
BULK COLLECT INTO LA_SMAG
FROM N_OBJ_STORAGE
WHERE TYPE_='КР' AND INUSE=1;
-- Пересоздание ZZZ_TEST
l_strSQL := 'CREATE GLOBAL TEMPORARY TABLE ZZZ_TEST (DATE_ DATE, ART VARCHAR2(30), ';
FOR j IN 1 .. la_smag.COUNT LOOP
l_strSQL := l_strSQL // '"' // la_smag(j) // '_п" NUMBER, ';
END LOOP;
l_strSQL := l_strSQL // '"Прод_маг" NUMBER) ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE l_strSQL;
-- Пересоздание индексов
EXECUTE IMMEDIATE 'CREATE INDEX SHOPS_MOD.I_ZZZ_TEST ON SHOPS_MOD.ZZZ_TEST (ART)';
-- Очищаем статус Каналов Реализации
EXECUTE IMMEDIATE 'UPDATE N_OBJ_STORAGE SET STATUS=NULL';
-- Перекомпилируем в цикле все INVALID-обьекты схемы SHOPS_MOD
-- в т.ч. наиболее нужные для нас считающие пакеты
OPEN RECOMPILE_cur;
LOOP
FETCH RECOMPILE_cur INTO recompile_rec;
EXIT WHEN RECOMPILE_cur%NOTFOUND;
-- Выполнить сформированный ALTER XXX SHOPS_MOD.XXX COMPILE
EXECUTE IMMEDIATE recompile_rec.cmd;
END LOOP;
CLOSE RECOMPILE_cur;
-- 6. Итоговый Commit и возврат результата
COMMIT;
l_ret:=1; -- Было успешное пересоздание
RETURN l_ret;
END KP_Recreate_TEST;
/*==========================================================================*/
END;
Эту статью написал и предложил для публикации в рассылке Alex Volny. Публикуется с разрешения автора. Все вопросы по содержанию статьи направляйте ему. Если автор захочет прокомментировать ваши вопросы для всех подписчиков, эти комментарии будут опубликованы в рассылке.