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

Введення


Іноді доводиться вирішувати завдання управління таблицями з відмінною від класичних канонів структурою. По ряду причин, такі таблиці ростуть як вниз (додавання рядків даних), так і праворуч (додавання стовпців). Причиною може бути історично прийнята, успадкована структура даних (наприклад, додаток, перенести в СУБД 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>

*

*