Быстродействие загрузки данных из DB2 в Oracle

#oracle #производительность #odbc #db2

Вопрос сложный и неоднозначный, но надеюсь на любые советы (а может кому и мой опыт
поможет):


Есть сервер с Oracle 11.2.0.3 x32 на Windows Server 2008R2 x64 (4ядра/8 потоков,
8гб оперативы, один HDD без рейда).
В источниках ODBCx32 настроено подключение к DB2 через CLI DRIVER с именем db2
Целевой сервер DB2 находится на мейнфрейме, настроек которого я не знаю, версия 9fix15
Поднят дополнительный listener:   

SID_LIST_LISTENERdb2 =
  (SID_LIST =
    (SID_DESC=
         (SID_NAME=db2)
         (ORACLE_HOME=C:\app\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc))
  )
LISTENERdb2 =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
    )
  )


В tnsnames.ora:   

db2 =
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523))
   (CONNECT_DATA=(SID=db2))
   (HS=OK)
) 

Создан public dblink: 

CREATE PUBLIC DATABASE LINK db2 CONNECT TO
"login" IDENTIFIED BY "pwd" using 'db2';`

Создана процедура:  

CREATE OR REPLACE PROCEDURE P_LOAD_DATA IS 
BEGIN 
insert into data_table select * from data_table@db2 d where d.date_op between TRUNC(SYSDATE
- 4/24, 'HH24') + 1/24/60 AND TRUNC(SYSDATE - 2/24, 'HH24'));
END;/

Создан job на запуск процедуры раз в 2 часа (или 12 джобов на каждый запуск).


Целевая таблица размером примерно 13млн записей по 50 столбцов, моя таблица собирает
архив из целевой и имеет размер примерно 150млн записей тех же 50 столбцов.

Перекачка ~600к записей (150-200мб) занимает в лучшем случае час, периодически переваливает
за 4-5 часов. Плюс хорошо бы держать индексы на таблицу архива, чтобы из нее можно
было потом что-то вытащить в приемлемый срок, но это опционально. Корректный ли выбран
способ наполнения архива или есть альтернативы лучше? Проблема в том, что периодически
джобы прерываются с причиной "Job slave process was terminated", а в логах 

ORA-00603: ORACLE server session terminated by fatal error
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003 {08003,NativeErr
= -99999}
ORA-02063: preceding 2 line


Это вероятно мейнфрейм закрывает подключение по таймауту.
Причина разрядности Оракла - ни один найденный вариант драйвера DB2 не работал на х64.
    

Ответы

Ответ 1

Ну, очевидно, стоит начать ковырять с анализа того, куда же уходит и час и 4 часа
- т.е. включаем трассировку на сессию джоба и анализируем полученный трейс. Дальше,
в порядке отработки версии что тормозит DB2 на мейнфрейме, я бы просто отселектил необходимый
диапазон, но без записи в таблицу - т.е., как говорят в unix-мире, в /dev/null. Но
при этом чтобы IBM отдал все записи и они прошли по сети и db-link'у - т.е. просто
SELECT, но с COUNT'ом или группировкой + поиграть с хинтом +driving_site - чтоб точно
все записи сначала пришли к нам в Оракл. Ну и потом уже - классические варианты записи
большого объема в Oracle - NOLOGGING, +APPEND, варианты с временной таблицей (партицией)
и DBMS_REDEFINITION / EXCHANGE PARTITION, удаление индексов до / пересоздание после,
BULK INSERT и тд и тп.

Главное начать и выяснить, что оптимизировать или где затык. А дальше уже дело техники.

Если есть такая физическая/юридическая возможность + необходимость в предлагаемом
- если Вы можете предоставить удаленный доступ - с удовольствием помогу решить (или
хотя бы "осмотреть наружно" / применить озвученные выше рекомендации) текущую задачку
"физически", бо интересный случай + имею предыдущий опыт в решении похожих ситуаций,
когда перекачка данных в гетерогенных средах ведет себя не так, как ожидается.

Это предложение, кстати - подключиться и помочь удаленно - относится и ко всем остальным,
кто рано или поздно попадет в этот тред/обсуждение (через поиск или еще как).

Вложенные транзакции SQL сервер

#sql #qt #sql_server #odbc #транзакции

Здравствуйте.

Ситуация следующая. Есть SQL Server 2008, в программе используется драйвер QODBC3.
Соединение установлено, данные пишутся/читаются.

Проблема, видимо, в отсутствии понимания принципа работы с вложенными транзакциями.
Судя по документации к восьмому серверу, каждый новый BEGIN TRANSACTION  увеличивает
счётчик TRANCOUNT на единицу. Каждый COMMIT уменьшает его на 1. Транзакция завершается
и фиксируются изменения, когда счётчик станет равен 0. У меня же выходит, что первый
же commit завершает транзакцию вне зависимости от того, сколько было BEGIN TRANSACTION.
Пусть имеется база и запрос к ней:  

db = QSqlDatabase::addDatabase("QODBC3");
...
query = new QSqlQuery(db);
...


Выполняем:

db.transaction(); 
      query->exec("INSERT INTO TestOnly (Value) VALUES('1')");
      db.transaction();
            query->exec("INSERT INTO TestOnly (Value) VALUES('2')");
      db.commit(); 
      query->exec("INSERT INTO TestOnly (Value) VALUES('3')");
db.rollback(); 


В результате в таблице оказываются все 3 значения ('1','2','3'). И в SQL SMS видно,
что после первого же коммита пропадает единственная активная транзакция. Соответвственно
последующий RollBack ничего не делает.

В чём здесь проблема? Я совсем не правильно понял идею со счётчиком открытых транзакций,
или просто драйвер/Qt не поддерживают такой функционал? Или я упустил что-то где-то
в настройках (сервера/драйвера)?



С проверкой возвращаемых значений операций открытия/закрытия транзакций пример выглядит
страшнее, но ничего не поделать:

    bool ok;

    ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //0

    query->exec("INSERT INTO TestOnly (Value) VALUES('1')");
    PrintTranCount(); //1

    ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //2

    query->exec("INSERT INTO TestOnly (Value) VALUES('2')");
    PrintTranCount(); //3

    ok = db.commit(); qDebug() << "Commit transaction" << " result = " << ok << ";
errorText: " << db.lastError().text();
    PrintTranCount(); //4

    query->exec("INSERT INTO TestOnly (Value) VALUES('3')");
    PrintTranCount(); //5

    ok = db.rollback();  qDebug() << "Rollback transaction" << " result = " << ok
<< "; errorText: " << db.lastError().text();
    PrintTranCount(); //6


Результат:

Start transaction  result =  true ; errorText:  " "  
Transaction Count   0: 0  
Transaction Count   1: 1   
Start transaction  result =  true ; errorText:  " "   
Transaction Count   2: 1   
Transaction Count   3: 1   
Commit transaction  result =  true ; errorText:  " "  
Transaction Count   4: 0  
Transaction Count   5: 0   
Rollback transaction  result =  true ; errorText:  " "  
Transaction Count   6: 0   


Иными словами, вторая db.transaction() не инкриминирует счётчик совсем и транзакция
полностью завершается после первого COMMIT'а. Буду благодарен за любые подсказки по
этому поводу.

Вывод значения счётчика транзакций:

void PrintTranCount()
{
    static int Num = 0;
    query->exec("SELECT @@TRANCOUNT");query->first();
    qDebug() << "Transaction Count  "<< Num << ": " <value(0).toInt();
    Num++;
}

    

Ответы

Ответ 1

Краткий ответ

Вызов метода QSqlDatabase::transaction() не начинает транзакцию в базе, а лишь включает
режим "ручной режим фиксации". Для того чтобы начать явную транзакцию, нужно отослать
базе непосредственно команду "begin tran".

Пояснения

Проблема заключалась в том, что я принимал желаемое поведение драйвера за действительное
и поленился залезть в исходники. Спасибо @teran за то, что "ткнул носом" куда надо.

Описание функции bool QSqlDatabase::transaction() гласит:


  Begins a transaction on the database if the driver supports
  transactions. Returns true if the operation succeeded. Otherwise it
  returns false.


Из этого описания я сделал ложный вывод о том, что драйвер отправляет базе команду
открытия транзакции, что, мягко говоря, не соотносится с действительностью.

Из исходников:

bool QSqlDatabase::transaction()
{
    if (!d->driver->hasFeature(QSqlDriver::Transactions))
        return false;
    return d->driver->beginTransaction();
}


Функция "спрашивает" драйвер о поддержке транзакций, вызывая hasFeature(QSqlDriver::Transactions).
Если получает положительный ответ - вызывает метод драйвера beginTransaction().

bool QODBCDriver::beginTransaction()
{
    Q_D(QODBCDriver);
    //---------------Проверяем открыта ли база-----------------
    if (!isOpen()) 
    { 
        qWarning("QODBCDriver::beginTransaction: Database not open");
        return false;
    }
    //------Инициализация переменной-параметра значением,-----
    //------отключающим автоматический commit операций--------
    SQLUINTEGER ac(SQL_AUTOCOMMIT_OFF);  
    //-------Устанавливаем этот параметр и проверяем на ошибки-----------------
    SQLRETURN r  = SQLSetConnectAttr(d->hDbc,
                                      SQL_ATTR_AUTOCOMMIT,
                                      (SQLPOINTER)size_t(ac),
                                      sizeof(ac));
    if (r != SQL_SUCCESS) {
        setLastError(qMakeError(tr("Unable to disable autocommit"),
                     QSqlError::TransactionError, d));
        return false;
    }
    return true;
}


В функции beginTransaction(), в свою очередь, единственное производимое действие
- отключение автоматического подтверждения изменений в базе. Никаких "begin tran".

В результате, с отключённым автокоммитом база данных при выполнении каждой инструкции
начинает неявную транзакцию. Это (а не сама функция transaction(), как я думал) и приводит
к установке счётчика транзакций @TRANCOUNT в единицу. Нет никакой вложенности транзакций
в данном случае, она одна и завершается сразу после первого же COMMIT'а.

Функция commit() (как и rollback()) содержит в себе вызов QODBCDriver::endTrans(),
который вновь отключает режим неявных транзакций. Это отключение объясняет, почему
в моём примере в результирующей таблице была и третья строка (та, что перед ROLLBACK'ом).
COMMIT перед отправкой команды вставки этой строки завершил неявную транзакцию и отключил
ручной режим фиксации, так что INSERT был автоматически зафиксирован в базе, а rollback()
отработал вхолостую.


Ответ 2

По поводу того, как работают вложенные транзакции на mssql:

use tempdb
go
set nocount on;
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
begin tran
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
begin tran
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
commit
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END
commit
SELECT [@@TRANCOUNT]=@@TRANCOUNT ,[XACT_STATE()]=XACT_STATE() ,[IMPLICIT_TRANSACTIONS]=CASE
WHEN @@OPTIONS & 2 = 2 THEN 1 ELSE 0 END

@@TRANCOUNT XACT_STATE() IMPLICIT_TRANSACTIONS
----------- ------------ ---------------------
0           0            0
1           1            0
2           1            0
1           1            0
0           0            0

Вложенные транзакции SQL сервер

Здравствуйте.
Ситуация следующая. Есть SQL Server 2008, в программе используется драйвер QODBC3. Соединение установлено, данные пишутся/читаются.
Проблема, видимо, в отсутствии понимания принципа работы с вложенными транзакциями. Судя по документации к восьмому серверу, каждый новый BEGIN TRANSACTION увеличивает счётчик TRANCOUNT на единицу. Каждый COMMIT уменьшает его на 1. Транзакция завершается и фиксируются изменения, когда счётчик станет равен 0. У меня же выходит, что первый же commit завершает транзакцию вне зависимости от того, сколько было BEGIN TRANSACTION. Пусть имеется база и запрос к ней:
db = QSqlDatabase::addDatabase("QODBC3"); ... query = new QSqlQuery(db); ...
Выполняем:
db.transaction(); query->exec("INSERT INTO TestOnly (Value) VALUES('1')"); db.transaction(); query->exec("INSERT INTO TestOnly (Value) VALUES('2')"); db.commit(); query->exec("INSERT INTO TestOnly (Value) VALUES('3')"); db.rollback();
В результате в таблице оказываются все 3 значения ('1','2','3'). И в SQL SMS видно, что после первого же коммита пропадает единственная активная транзакция. Соответвственно последующий RollBack ничего не делает.
В чём здесь проблема? Я совсем не правильно понял идею со счётчиком открытых транзакций, или просто драйвер/Qt не поддерживают такой функционал? Или я упустил что-то где-то в настройках (сервера/драйвера)?

С проверкой возвращаемых значений операций открытия/закрытия транзакций пример выглядит страшнее, но ничего не поделать:
bool ok;
ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //0
query->exec("INSERT INTO TestOnly (Value) VALUES('1')"); PrintTranCount(); //1
ok = db.transaction(); qDebug() << "Start transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //2
query->exec("INSERT INTO TestOnly (Value) VALUES('2')"); PrintTranCount(); //3
ok = db.commit(); qDebug() << "Commit transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //4
query->exec("INSERT INTO TestOnly (Value) VALUES('3')"); PrintTranCount(); //5
ok = db.rollback(); qDebug() << "Rollback transaction" << " result = " << ok << "; errorText: " << db.lastError().text(); PrintTranCount(); //6
Результат:
Start transaction result = true ; errorText: " " Transaction Count 0: 0 Transaction Count 1: 1 Start transaction result = true ; errorText: " " Transaction Count 2: 1 Transaction Count 3: 1 Commit transaction result = true ; errorText: " " Transaction Count 4: 0 Transaction Count 5: 0 Rollback transaction result = true ; errorText: " " Transaction Count 6: 0
Иными словами, вторая db.transaction() не инкриминирует счётчик совсем и транзакция полностью завершается после первого COMMIT'а. Буду благодарен за любые подсказки по этому поводу.
Вывод значения счётчика транзакций:
void PrintTranCount() { static int Num = 0; query->exec("SELECT @@TRANCOUNT");query->first(); qDebug() << "Transaction Count "<< Num << ": " <value(0).toInt(); Num++; }

Ответ

Краткий ответ
Вызов метода QSqlDatabase::transaction() не начинает транзакцию в базе, а лишь включает режим "ручной режим фиксации". Для того чтобы начать явную транзакцию, нужно отослать базе непосредственно команду "begin tran".
Пояснения
Проблема заключалась в том, что я принимал желаемое поведение драйвера за действительное и поленился залезть в исходники. Спасибо @teran за то, что "ткнул носом" куда надо.
Описание функции bool QSqlDatabase::transaction() гласит:
Begins a transaction on the database if the driver supports transactions. Returns true if the operation succeeded. Otherwise it returns false.
Из этого описания я сделал ложный вывод о том, что драйвер отправляет базе команду открытия транзакции, что, мягко говоря, не соотносится с действительностью.
Из исходников:
bool QSqlDatabase::transaction() { if (!d->driver->hasFeature(QSqlDriver::Transactions)) return false; return d->driver->beginTransaction(); }
Функция "спрашивает" драйвер о поддержке транзакций, вызывая hasFeature(QSqlDriver::Transactions). Если получает положительный ответ - вызывает метод драйвера beginTransaction().
bool QODBCDriver::beginTransaction() { Q_D(QODBCDriver); //---------------Проверяем открыта ли база----------------- if (!isOpen()) { qWarning("QODBCDriver::beginTransaction: Database not open"); return false; } //------Инициализация переменной-параметра значением,----- //------отключающим автоматический commit операций-------- SQLUINTEGER ac(SQL_AUTOCOMMIT_OFF); //-------Устанавливаем этот параметр и проверяем на ошибки----------------- SQLRETURN r = SQLSetConnectAttr(d->hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)size_t(ac), sizeof(ac)); if (r != SQL_SUCCESS) { setLastError(qMakeError(tr("Unable to disable autocommit"), QSqlError::TransactionError, d)); return false; } return true; }
В функции beginTransaction(), в свою очередь, единственное производимое действие - отключение автоматического подтверждения изменений в базе. Никаких "begin tran".
В результате, с отключённым автокоммитом база данных при выполнении каждой инструкции начинает неявную транзакцию. Это (а не сама функция transaction(), как я думал) и приводит к установке счётчика транзакций @TRANCOUNT в единицу. Нет никакой вложенности транзакций в данном случае, она одна и завершается сразу после первого же COMMIT'а.
Функция commit() (как и rollback()) содержит в себе вызов QODBCDriver::endTrans(), который вновь отключает режим неявных транзакций. Это отключение объясняет, почему в моём примере в результирующей таблице была и третья строка (та, что перед ROLLBACK'ом). COMMIT перед отправкой команды вставки этой строки завершил неявную транзакцию и отключил ручной режим фиксации, так что INSERT был автоматически зафиксирован в базе, а rollback() отработал вхолостую.