Динамическое пересоздание глобальных временных таблиц

Источник: incomua

Введение

Иногда приходиться решать задачи управления таблицами с отличной от классических канонов структурой. По ряду причин, такие таблицы растут как вниз (добавление строк данных), так и вправо (добавление столбцов). Причиной может быть исторически принятая, унаследованная структура данных (например, приложение, портированное в СУБД Oracle без перепроектирования структур), или проблемы производительности на этапе сложного расчета.

Например, базовые данные находятся в правильном, нормализованном представлении, а на начальном этапе многоступенчатого расчета производиться "динамическое распрямление вправо" по слабо изменяемому признаку и заполнение заранее созданных глобальных временных таблиц (Global Temporary Table - GTT). Результаты расчета могут быть затем агрегированы или "схлопнуты" по какому-либо признаку.

Это может быть актуально, когда объем данных в одном сеансе расчета очень значителен (сотни тысяч или миллионы строк). Как правило, это расчетные задачи OLAP (DSS), например, расчет времени и скорости продаж товаров по всей сети магазинов, прогноз товарного запаса, расчет материального баланса. В результате такого представления GTT, "высота" таблицы сокращается пропорционально количеству магазинов сети (слабо изменяемому признаку), например в 50 раз, с 30 млн. до 600 тыс. строк для каждого типа данных (остатки, продажи и т.д.).

Я не стану давать оценок таким структурам с точки зрения классического проектирования, скажу лишь, что они используются и, на этапе расчета, могут давать значительный выигрыш в производительности. Тем более, если динамика роста вправо приемлема (ограничение в Oracle 9i - 1000 столбцов). Реализация такого расчетного механизма возможна, например, с применением динамического SQL и наборов (collections).

Итак, каждый сеанс использует для расчетов некоторый набор PL/SQL-пакетов, хранит данные своего расчета в наборе временных таблиц (например, уровня сеанса, ON COMMIT PRESERVE ROWS).

Следовательно, мы сталкиваемся с проблемой пересоздания временных таблиц после добавления или удаления слабо изменяющегося признака (магазина). Пересоздание возможно как сразу (в оnline), так и отложенно (с помощью задания, выполняемого по определенному графику, например, ночью, при минимальной нагрузке на сервер).

Алгоритм действий

Получение списка блокирующих сеансов. Тип блокировок - 'TO'¹ для 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. Публикуется с разрешения автора. Все вопросы по содержанию статьи направляйте ему. Если автор захочет прокомментировать ваши вопросы для всех подписчиков, эти комментарии будут опубликованы в рассылке.

Ссылки по теме