Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
Добрый день. Стоит задача перенести данные из одной таблицы в другую. В таблице(в которую помещаем данные) примерно 4,5 млн записей. Таблица напичкана индексами и констрейнтами. 1. При загрузке данных с использованием вложенных таблиц. Сама вставка в коллекцию длится три часа.
declare
	type REC_Z001_TYPE is table of T_Z001_02%rowtype;
	REC_Z001 REC_Z001_TYPE;
	L_START number;
begin
	L_START := DBMS_UTILITY.GET_TIME;
	select * bulk collect
	  into REC_Z001
	  from T_Z001_02 T;
     

	DBMS_OUTPUT.PUT_LINE('Inserting BULK COLLECT  : ' ||
								(DBMS_UTILITY.GET_TIME - L_START));
                        
	L_START := DBMS_UTILITY.GET_TIME;
   
	forall I in REC_Z001.FIRST .. REC_Z001.LAST
		insert /*+ APPEND_VALUES */
		into T_Z001_02
		values REC_Z001(I);
      
	DBMS_OUTPUT.PUT_LINE('Bulk Inserts /*+ APPEND_VALUES */ : ' ||
								(DBMS_UTILITY.GET_TIME - L_START));
end;


2. Прямая вставка с /*+ append */ в режиме nologging, тиак же выполняется долго. Дает выигрыш отключение уникальных индексов, вторичных индексов зависимых таблиц, констрейнтов. Но при их обратном подключении после вставки приходится делать REBUILD индексов. Самое долгое это REBUILD PK и UK, выполняются полтора часа. Хотелось бы как то ускорить процесс вставки. Подскажите куда копать? За ответы заранее благодарен.
11 дек 13, 10:27    [15275342]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
eev
Member

Откуда: -
Сообщений: 1156
ВЕЗУНЧИК,
а так ?
insert...
select...
11 дек 13, 10:32    [15275384]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
Alexander Ryndin
Member

Откуда:
Сообщений: 4917
Блог
В сторону INSERT AS SELECT и параллелизма.
11 дек 13, 10:33    [15275391]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
eev,

2 Пункт и посвящен прямой вставке.
 execute immediate 'alter table T_Z001_01 nologging';
 pni_dis_enb_idnx('T_Z001_01',0); -- Отключение индексов, констрейнтов
 itme:=dbms_utility.get_time;
   insert /*+ append */  into
          T_Z001_01
                (rn,
                 god,
                 ms,
                 nd,
                 code,
                 tab_numb,
                 pref,
                 numb,
                 code_grf,
                 code_sost,
                 nrdgr,
                 krdgr,
                 nrdprox,
                 krdprox,
                 STATE,
                 DDATE,
                 nclnpfmtype,
                 company,
                 clnpspfm)
              select ngen_id,
                     t.GOD,
                     t.MS,
                     t.ND,
                     t.CODE,
                     t.TAB_NUMB,
                     t.PREF,
                     t.NUMB,
                     t.CODE_GRF,
                     t.CODE_SOST,
                     t.NRDGR,
                     t.KRDGR,
                     t.NRDPROX,
                     t.KRDPROX,
                     t.STATE,
                     t.DDATE,
                     t.NCLNPFMTYPE, /*связь*/
                     t.CLNPSPFM, /*связь*/
                     t.COMPANY /*связь*/              
                from imp_Z001_01 t;             
 nCOUNTROW:=sql%rowcount;
 pni_dis_enb_idnx('T_Z001_01',1);  -- Подключение индексов, констрейнтов, сбор статистики
  execute immediate 'alter table T_Z001_01 logging';
 SHODZAGR:='-- Пункт b) Вставка данных в реальную таблицу T_Z001_01 из временной imp_Z001_01. ';
 SHODZAGR:=SHODZAGR||'Кол-во загр. зап: '||to_char(nCOUNTROW);
 SHODZAGR:=SHODZAGR||' Время выполнения '||to_char(round((dbms_utility.get_time-itme)/100/60,2),'999999.99')||' минут';                
 dbms_output.put_line(SHODZAGR);
11 дек 13, 10:42    [15275439]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
Alexander Ryndin,
А можно поподробне про параллелизм, какой выигрыш можно получить с помощью его использования. Ну и плиз хоть маленький примерчик в тему.
11 дек 13, 10:44    [15275454]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
Alexander Ryndin
Member

Откуда:
Сообщений: 4917
Блог
ВЕЗУНЧИК
Alexander Ryndin,
А можно поподробне про параллелизм, какой выигрыш можно получить с помощью его использования. Ну и плиз хоть маленький примерчик в тему.
При использовании параллелизма можно получить прирост производительности кратный количеству ядер. А можно и не получить. Все зависит от того, куда вы упираетесь в производительность одного ядра или производительность системы ввода-вывода.
Подробно с примерами почитать можно здесь.

P.S. я, конечно, не знаю какая у вас структура данных, но вставка 4,5 млн. записей это минуты, но никак не часы
11 дек 13, 10:51    [15275484]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 346
Что показывает

select * from DBA_INDEXES t where t.TABLE_NAME = 'T_Z001_01'

?
11 дек 13, 11:02    [15275537]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 346
Cristiano_Rivaldo,

Сори - не увидел что отключено
11 дек 13, 11:02    [15275542]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
Cristiano_Rivaldo,

Без отключения индексов с применением parallel, выйгрыша в производительности нет, хотя идет 8 параллельных потоков. Думаю индексы тормозят вставку :(
 execute immediate 'alter session force parallel dml parallel 8';
  execute immediate 'alter session force parallel query parallel 8';
   
   insert /*+ append NOLOGGING*/  into
          T_Z001_01
                (rn,
                 god,
                 ms,
                 nd,
                 code,
                 tab_numb,
                 pref,
                 numb,
                 code_grf,
                 code_sost,
                 nrdgr,
                 krdgr,
                 nrdprox,
                 krdprox,
                 STATE,
                 DDATE,
                 nclnpfmtype,
                 company,
                 clnpspfm)
              select ngen_id,
                     t.GOD,
                     t.MS,
                     t.ND,
                     t.CODE,
                     t.TAB_NUMB,
                     t.PREF,
                     t.NUMB,
                     t.CODE_GRF,
                     t.CODE_SOST,
                     t.NRDGR,
                     t.KRDGR,
                     t.NRDPROX,
                     t.KRDPROX,
                     t.STATE,
                     t.DDATE,
                     t.NCLNPFMTYPE, /*связь*/
                     t.CLNPSPFM, /*связь*/
                     t.COMPANY /*связь*/              
                from imp_Z001_01 t;             
11 дек 13, 12:47    [15276360]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ВЕЗУНЧИК,

возможно, проблема вне этой таблицы. перед перестроением pk,uk отключите ссылки на них.
а включайте без валидации. валидацию проводите поштучно (но параллельно, в смысле sql).
11 дек 13, 13:00    [15276439]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
orawish,
Возможно я что то путаю. Я делаю ребилд индексов, до того как подключены ссылки на связные таблицы (эти таблицы кстати в момент загрузки пусты). привожу пример процедуры отключения и перестроения индексов.
procedure pni_dis_enb_idnx(stable_name in varchar2, nstatus in number) as
itime number;
begin
  
itime:=dbms_utility.get_time;  
if nstatus=0 then

    for cur in (select T.table_name, T.constraint_name, t.CONSTRAINT_TYPE, t.status 
                  from DBA_CONSTRAINTS T where R_CONSTRAINT_NAME in
                       (select CONSTRAINT_NAME  from DBA_CONSTRAINTS
                         where TABLE_NAME = upper(stable_name))
                   and T.owner='PARUS' and t.status='ENABLED')
      loop --Отключаем зависимые констрейнты
         execute immediate 'alter table '||cur.table_name||' disable constraint '||cur.constraint_name;
      end loop; 

    for cur in (select  t.TABLE_NAME, t.CONSTRAINT_NAME, t.CONSTRAINT_TYPE, t.status 
                from all_constraints t where t.TABLE_NAME=upper(stable_name) and t.status='ENABLED' /*and t.CONSTRAINT_TYPE!='P'*/
               )
      loop --Отключаем констрейнты своей таблицы
         execute immediate 'alter table '||cur.table_name||' disable constraint '||cur.constraint_name;
      end loop;
     
  for cur in (select t.TABLE_NAME, t.INDEX_NAME, t.index_type, t.status, t.UNIQUENESS 
                from all_indexes t where t.TABLE_NAME=upper(stable_name))
    loop --Отключаем индексы
         execute immediate 'ALTER INDEX '||cur.index_name||' UNUSABLE';
    end loop;
   dbms_output.put_line('-------------------Отключение индексов и констрейнтов: '||'Время: '||to_char(round((dbms_utility.get_time-itime)/100/60,2),'999999.99')||' минут');  
else -------------------------------- Подключение и пересоздание индексов и констрейнтов
   for cur in (select t.TABLE_NAME, t.INDEX_NAME, t.index_type, t.status, t.UNIQUENESS 
                 from all_indexes t where t.TABLE_NAME=upper(stable_name) and t.status='UNUSABLE')
    loop --Включаем и перестраиваем индексы
         execute immediate 'ALTER INDEX '||cur.index_name||' REBUILD';
    end loop;
    
   for cur in (select  t.TABLE_NAME, t.CONSTRAINT_NAME, t.CONSTRAINT_TYPE, t.status 
                 from all_constraints t where t.TABLE_NAME=upper(stable_name) and t.status='DISABLED')
      loop  --Включаем свои констрейнты
            execute immediate 'alter table '||cur.table_name||' enable constraint '||cur.constraint_name;
             if cur.CONSTRAINT_TYPE in ('U','P') then
                execute immediate 'ALTER INDEX '||cur.constraint_name||' REBUILD';
             end if;
      end loop;
      
  for cur in (select T.table_name, T.constraint_name, t.CONSTRAINT_TYPE, t.status 
                  from DBA_CONSTRAINTS T where R_CONSTRAINT_NAME in
                       (select CONSTRAINT_NAME  from DBA_CONSTRAINTS
                         where TABLE_NAME = upper(stable_name))
                   and T.owner='PARUS' and t.status='DISABLED')
      loop --Включаем зависимые констрейнты
         execute immediate 'alter table '||cur.table_name||' enable constraint '||cur.constraint_name;
      end loop;   
     dbms_output.put_line('-------------------Включение и rebuild индексов: '||'Время: '||to_char(round((dbms_utility.get_time-itime)/100/60,2),'999999.99')||' минут');   
     itime:=dbms_utility.get_time;  
     DBMS_STATS.GATHER_TABLE_STATS('PARUS', stable_name); --Сбор статистики 
     dbms_output.put_line('-------------------Сбор статистики: '||'Время: '||to_char(round((dbms_utility.get_time-itime)/100/60,2),'999999.99')||' минут');    
end if;
end;
11 дек 13, 13:19    [15276555]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
oracle1
Guest
Если дело в индексах, может ребилд в параллели?
11 дек 13, 14:21    [15276954]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
happytoday
Member

Откуда: Днепр
Сообщений: 239
ВЕЗУНЧИК,

Как вариант причиной может быть:
сannot allocate new log, sequence ...
Checkpoint not complete


но о других вариантах уже написали:
orawish
возможно, проблема вне этой таблицы...

Alexander Ryndin
Все зависит от того, куда вы упираетесь в производительность одного ядра или производительность системы ввода-вывода.
11 дек 13, 18:26    [15278862]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
JoeD
Member

Откуда: Москва
Сообщений: 266
Вроде как неоднократными обсуждениями и тестами пришли к общему мнению, что подобное проще всего делается через CTAS + PARTITION EXCHANGE? (искать выделенное в этом форуме или в любимом поисковике)
11 дек 13, 19:08    [15279035]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday,

А можно чуть поподробнее про
"сannot allocate new log, sequence ...
Checkpoint not complete"
12 дек 13, 10:34    [15280899]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
oracle1,

Браво oracle1,
execute immediate 'ALTER INDEX '||cur.index_name||' REBUILD parallel (degree 16 ) nologging';

скорость загрузки увеличилась в 4,5 раза. Спасибо за наводку.
12 дек 13, 11:19    [15281196]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
happytoday
Member

Откуда: Днепр
Сообщений: 239
ВЕЗУНЧИК,
Если по простому,то ситуацию вижу так:
1. БД работала и никого не беспокоила.
2. Было у неё 3-5 оперативных журнала по 100 Мб.
3. Под нагрузкой переключались оперативные журналы раз 30-40 минут и архивировались не спеша.
4. Решили люди перелить данных 4,5 млн записей.
5. БД никогда такого не делала и не была готова выполнить такую работу.
6. Так как БД была умная и трудолюбивая она взялась за эту сложную работу.
7. И пошла работа.... оперативные журналы переключаются и архивируются, но иногда случается так - оперативные журналы все ACTIVE и хочется переключиться но не можем .... сидим ждем ...

Предлагаю почитать:
Online redo logs или Событие контрольной точки в Oracle
Сообщение в alert.log: Checkpoint not complete
12 дек 13, 11:24    [15281240]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
happytoday
Member

Откуда: Днепр
Сообщений: 239
ВЕЗУНЧИК
Браво oracle1,
execute immediate 'ALTER INDEX '||cur.index_name||' REBUILD parallel (degree 16 ) nologging';
скорость загрузки увеличилась в 4,5 раза. Спасибо за наводку.


А что на parallel (degree 16 ) говорит DBA и самое главное пользователи в момент выполнения?
12 дек 13, 11:33    [15281321]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday
ВЕЗУНЧИК,
Если по простому,то ситуацию вижу так:
1. БД работала и никого не беспокоила.
2. Было у неё 3-5 оперативных журнала по 100 Мб.
3. Под нагрузкой переключались оперативные журналы раз 30-40 минут и архивировались не спеша.
4. Решили люди перелить данных 4,5 млн записей.
5. БД никогда такого не делала и не была готова выполнить такую работу.
6. Так как БД была умная и трудолюбивая она взялась за эту сложную работу.
7. И пошла работа.... оперативные журналы переключаются и архивируются, но иногда случается так - оперативные журналы все ACTIVE и хочется переключиться но не можем .... сидим ждем ...

Предлагаю почитать:
Online redo logs или Событие контрольной точки в Oracle
Сообщение в alert.log: Checkpoint not complete


Да полностью так с Тобой согласен, так и было. Администратор только успевала увеличивать файлы журналов. Приходилось ждать. Сейчас все вроде нормуль, я пытаюсь следить за ситуацией по V_$LOG.
12 дек 13, 11:43    [15281413]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday,

У нас происходит перенос части данных, эта таблица не единственная. На время этого переноса процесс работы пользователей в базе будет преостановлен. Это разовая работа. Вот и нужно чтоб была закончена в короткие сроки.
12 дек 13, 11:45    [15281429]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday,

Спасибо за информацию и полезные ссылки, буду рад поизучать.
12 дек 13, 11:47    [15281444]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
happytoday
Member

Откуда: Днепр
Сообщений: 239
ВЕЗУНЧИК
На время этого переноса процесс работы пользователей в базе будет преостановлен. Это разовая работа. Вот и нужно чтоб была закончена в короткие сроки.

Пару советов для администратора:
1. Перед началом работы оставить одну рабочую БД на узле (при необходимости и возможности)
2. Согласовать с DBA parallel (иногда значение 2 может быть лучше, чем 16)
3. Размер датафайлов увеличить до расчетных.

ВЕЗУНЧИК
... короткие сроки.

Обычно сроки ограничены временными рамками, может для одноразовой работы эти рамки можно и раздвинуть)))
12 дек 13, 12:13    [15281641]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday
ВЕЗУНЧИК
На время этого переноса процесс работы пользователей в базе будет преостановлен. Это разовая работа. Вот и нужно чтоб была закончена в короткие сроки.

Пару советов для администратора:
1. Перед началом работы оставить одну рабочую БД на узле (при необходимости и возможности)
2. Согласовать с DBA parallel (иногда значение 2 может быть лучше, чем 16)
3. Размер датафайлов увеличить до расчетных.

ВЕЗУНЧИК
... короткие сроки.

Обычно сроки ограничены временными рамками, может для одноразовой работы эти рамки можно и раздвинуть)))

Хорошо. Еще раз благодарю за помощь. А как можно определить оптимальное значение parallel (только тестовым путем или есть средства)? Или лучше выставить в Auto?
12 дек 13, 12:19    [15281703]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
happytoday
Member

Откуда: Днепр
Сообщений: 239
ВЕЗУНЧИК
А как можно определить оптимальное значение parallel (только тестовым путем или есть средства)? Или лучше выставить в Auto?

1. Auto я бы не ставил.
2. Мое мнение, что значение parallel ограниченно только здравым смыслом и осознанным выбором.
12 дек 13, 12:38    [15281847]     Ответить | Цитировать Сообщить модератору
 Re: Вставка большого объема данных  [new]
ВЕЗУНЧИК
Member

Откуда:
Сообщений: 48
happytoday,

Хорошо, количество parallel попытаюсь выяснить тестовым метом.
12 дек 13, 12:50    [15281949]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить