Динамічне перестворення глобальних тимчасових таблиць

Введення


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


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


Це може бути актуальне, коли обсяг даних в одному сеансі розрахунку дуже значний (сотні тисяч або мільйони рядків). Як правило, це розрахункові завдання OLAP (DSS), наприклад, розрахунок часу і швидкості продажів товарів по всій мережі магазинів, прогноз товарного запасу, розрахунок матеріального балансу. У результаті такого подання GTT, "висота" таблиці скорочується пропорційно кількості магазинів мережі (слабко змінюються ознаки), наприклад в 50 раз, з 30 млн. до 600 тис. рядків для кожного типу даних (залишки, продажу і т.д.).


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


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


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


Алгоритм дій



  1. Отримання списку блокуючих сеансів. Тип блокувань – "TO1 для GTT.
  2. Сповіщення за допомогою DBMS_ALERT всіх "думаючих" сеансів і повторний запит списку блокуючих сеансів.
  3. Примусове завершення роботи ("вбивання") блокуючих сеансів.
  4. Перевірка і "вбивання" завислих KILLED-сеансів для поточного ORACLE_SID. При цьому використовується технологія виконання команд ОС з PL / SQL: треба "вбити" процес або нитка завислого сеансу (варіанти для UNIX-і для Windows-платформи, притому безпосередньо з PL / SQL, без використання зовнішніх завдань і планувальників).
  5. Перестворення всіх потрібних тимчасових таблиць та індексів.
  6. Перекомпіляція всіх або обраних INVALID-об'єктів.

1. До речі, щось я не знайшов у документації опису цього типу блокування. Адже, як легко переконатися, саме такі блокування встановлюються, коли сеанс вставив якісь дані в глобальну тимчасову таблицю рівня сеансу … – Примітка В.К.


Повний текст пакету представлений в Додатку 2, Я ж зупинюся на необхідних деталях і доповненнях.


Тонкощі реалізації


1. Отримання списку блокуючих сеансів та блокування GTT


При першій вставці в глобальну тимчасову таблицю сервер Oracle встановлює на неї блокування "TO", Яка утримується за будь-яких подальших змінах, до настання однієї з двох умов:



Таким чином, щоб знайти блокуючий сеанс, необхідно шукати блокування будь пересоздаваемой глобальної тимчасової таблиці, яка бере участь у розрахунках (в нашому прикладі – 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.ENGINE=”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). Це можна зробити двома способами:



Я буду використовувати останній варіант і перекомпілювати об'єкти тільки однієї схеми, 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.ENGINE=”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. Публікується з дозволу автора. Всі питання за змістом статті направляйте йому. Якщо автор захоче прокоментувати ваші питання для всіх абонентів, ці коментарі будуть опубліковані в розсилці.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*