Oracle SQL: наиболее эффективный способ (с точки зрения сетевых обходов) выполнять случайные небольшие и разбросанные записи в столбец LOB

LOB-объекты Securefile имеют некоторые улучшения производительности по сравнению со старыми LOB-объектами Basicfile, главным образом кэш-память Write Gather Cache (WGC), которая буферизует записи и периодически очищает их, чтобы уменьшить количество операций ввода-вывода при записи на диск. Таким образом, это не так плохо, как запись одного фрагмента (кратного размера вашего блока) для каждой операции записи, хотя этот фрагмент остается базовой единицей ввода-вывода на диск для LOB-сегментов.

Я провел несколько экспериментов, сравнивая 10 000 случайных записей по 100 байт (всего 1 МБ) с BLOB-объектом размером 16 МБ и 10 000 случайных обновлений строк в столбце RAW(100), чтобы сравнить производительность между использованием BLOB и использованием дочерней таблицы RAW(100).

Настройка LOB:

 drop table lobtest;
 create table lobtest (myblob blob);

/*
** Initial population
*/
DECLARE
  var_myblob blob;
  var_mybuffer raw(100) := HEXTORAW('7785069247a730153313c7c8244db9228b0ce07a2aa8646cf1bc908bc7a275913c16a0081594f1b3979a515b7c37b61993e17caaccbe1401bd52ca34bf21d7a99b0c8220eae182a0f7ec71db249da8d96a585cf8fa81af6d09129d82afaafd7d60d77e4c');
  var_offset pls_integer;
  var_lob_size integer;
BEGIN
  INSERT INTO lobtest VALUES (EMPTY_BLOB()) RETURNING myblob INTO var_myblob;
  
  FOR i IN 1..100000
  LOOP
    var_offset := ((i-1)*100)+1;
    dbms_lob.write(var_myblob,100,var_offset,var_mybuffer);
  END LOOP;
END;
/
/*
** Show LOB segment size
*/
SELECT ROUND(s.bytes/(1024*1024)) blob_mb,l.*
  FROM user_lobs l,
       user_segments s
 WHERE l.column_name = 'MYBLOB'
   AND l.segment_name = s.segment_name;  
/     

Результат: 16 МБ

Тест обновления LOB

/*
** Capture session stats before
*/
drop table stat$before;
create table stat$before as 
SELECT sn.name,ss.value
  FROM v$mystat ss,
       v$statname sn
 WHERE ss.statistic# = sn.statistic#       
   AND (sn.name IN ('physical writes direct (lob)',
                   'physical write bytes',
                   'physical read bytes',
                   'physical write total bytes',
                   'redo size',
                   'undo change vector size')
        OR sn.name LIKE 'securefile%')                   
 ORDER BY ss.value DESC               
 /
/*
** Do 10,000 random writes to the BLOB created above in a single transaction
*/
DECLARE
  var_myblob blob;
  var_mybuffer raw(100) := HEXTORAW('7785069247a730153313c7c8244db9228b0ce07a2aa8646cf1bc908bc7a275913c16a0081594f1b3979a515b7c37b61993e17caaccbe1401bd52ca34bf21d7a99b0c8220eae182a0f7ec71db249da8d96a585cf8fa81af6d09129d82afaafd7d60d77e4c');
  var_offset pls_integer;
  var_lob_size integer;
  var_start_ts timestamp with time zone;
BEGIN
  var_start_ts := SYSTIMESTAMP;
  
  SELECT myblob INTO var_myblob FROM lobtest WHERE ROWNUM = 1 FOR UPDATE;
    
  FOR i IN 1..10000
  LOOP
    var_offset := LEAST(FLOOR(ABS(dbms_random.random())/10000)*100,16000000)+1;
    dbms_lob.write(var_myblob,100,var_offset,var_mybuffer);
  END LOOP;
  
  COMMIT;
  
  dbms_output.put_line(SYSTIMESTAMP - var_start_ts);
END;
/

/*
** Capture session stats after and calculate diffs
*/
SELECT sn.name,ss.value - NVL(b.value,0) diff
  FROM v$mystat ss,
       v$statname sn,
       stat$before b
 WHERE ss.statistic# = sn.statistic#       
   AND (sn.name IN ('physical writes direct (lob)',
                   'physical write bytes',
                   'physical read bytes',
                   'physical write total bytes',
                   'redo size',
                   'undo change vector size')
        OR sn.name LIKE 'securefile%')  
   AND sn.name = b.name(+)
 ORDER BY diff DESC
 /

Результат:

Это произошло после многократного запуска вышеуказанного теста обновления. Каждый раз, когда я запускал его, цифры падали, показывая эффект кэша записи и сбора данных. Эти цифры показывают, где они достигли дна и достигли максимальной эффективности. В ходе теста было обновлено 1 МБ (securefile bytes non-transformed = 1000000), используя 10 000 отдельных операций записи (securefile number of non-transformed flushes = 10000). Он прочитал 15 МБ (physical read bytes), сгенерировал 10 МБ повтора в журнал (redo size) и 4 МБ отмены (undo change vector size). Кэш Write Gather очищался 3565 раз (securefile number of flushes), или примерно 1 из каждых 3 операций записи. Счетчики для physical write bytes/physical write total bytes, похоже, не настроены должным образом — в моем случае 8192 — это ровно один блок/кусок, и он не может записать только 1 блок, когда выполнил 3565 сбросов. Похоже, что кэш Write Gather маскирует реальный объем записи на диск. Если предположить, что каждый сброс представляет собой фрагмент, то можно предположить, что общий объем операций ввода-вывода составляет 29 МБ (3565 * 8192). v$segstat дает цифру 894 физических операций записи (894*8192=7МБ). В любом случае, мы выполняем намного больше операций записи, чем 1 МБ, но кэширование не позволяет нам записывать 81 МБ, как мы (предположительно) делали бы это с базовым файлом (10000 * 8192). Общее время теста: 3,3 секунды.

Настройка RAW(100)

 drop table lobtest2;
 create table lobtest2 (offset integer, blob_data raw(100));
 
 
DECLARE
  var_mybuffer raw(100) := HEXTORAW('7785069247a730153313c7c8244db9228b0ce07a2aa8646cf1bc908bc7a275913c16a0081594f1b3979a515b7c37b61993e17caaccbe1401bd52ca34bf21d7a99b0c8220eae182a0f7ec71db249da8d96a585cf8fa81af6d09129d82afaafd7d60d77e4c');
  var_offset pls_integer;
BEGIN
  FOR i IN 1..100000
  LOOP
    var_offset := ((i-1)*100)+1;
    INSERT INTO lobtest2 VALUES (var_offset,var_mybuffer);
  END LOOP;
  COMMIT;
END;
/ 
create index i_lobtest2 on lobtest2(offset);
/

Тест обновления RAW(100)

drop table stat$before;
create table stat$before as 
SELECT sn.name,ss.value
  FROM v$mystat ss,
       v$statname sn
 WHERE ss.statistic# = sn.statistic#       
   AND (sn.name IN ('physical writes direct (lob)',
                   'physical write bytes',
                   'physical read bytes',
                   'physical write total bytes',
                   'redo size',
                   'undo change vector size'))                   
 ORDER BY ss.value DESC               
 /
  
DECLARE
  var_mybuffer raw(100) := HEXTORAW('7785069247a730153313c7c8244db9228b0ce07a2aa8646cf1bc908bc7a275913c16a0081594f1b3979a515b7c37b61993e17caaccbe1401bd52ca34bf21d7a99b0c8220eae182a0f7ec71db249da8d96a585cf8fa81af6d09129d82afaafd7d60d77e4c');
  var_offset pls_integer;
  var_start_ts timestamp with time zone;
BEGIN
  var_start_ts := SYSTIMESTAMP;
  
  FOR rec_offset IN (SELECT offset FROM lobtest2 ORDER BY REVERSE(TO_CHAR(offset)) FETCH FIRST 10000 ROWS ONLY)
  LOOP
    UPDATE lobtest2 SET blob_data = var_mybuffer WHERE offset = rec_offset.offset;
  END LOOP;
  
  COMMIT;
  
  dbms_output.put_line(SYSTIMESTAMP - var_start_ts);  
END;
/ 
SELECT sn.name,ss.value - NVL(b.value,0) diff
  FROM v$mystat ss,
       v$statname sn,
       stat$before b
 WHERE ss.statistic# = sn.statistic#       
   AND (sn.name IN ('physical writes direct (lob)',
                   'physical write bytes',
                   'physical read bytes',
                   'physical write total bytes',
                   'redo size',
                   'undo change vector size'))  
   AND sn.name = b.name(+)
 ORDER BY diff DESC
 /

Результат:

Мы по-прежнему выполнили 1 МБ обновлений, включающих 10 000 случайных 100-байтовых изменений, как и в предыдущем тесте. Метрики физического чтения/записи бессмысленны, поскольку это были буферизованные операции чтения/записи через кэш, как и обычные операции с таблицами. Процессу переднего плана не нужно выполнять какие-либо собственные записи (кроме журнала повторного выполнения), поскольку DBWR выполнит эту задачу позже, когда пожелает. Общий размер повтора составил 3 МБ, размер отмены — 1 МБ, общее время теста: 0,18 секунды.

Сравнивая два теста, мы видим, что запись в BLOB должна читаться в BLOB-объекте с диска (не кэшированном. Вы можете изменить это с помощью настройки CACHE, но BLOB-объект размером 1 ГБ слишком велик для кэширования), поэтому их много. физического прямого пути чтения, который не нужен в сценарии RAW(100), который вообще не выполняет чтения, когда все находится в буферном кеше. Метод BLOB сгенерировал в три раза больше повторов, в четыре раза больше отмен и выполнил неисчислимое количество операций записи (где-то в 7–20 раз больше фактического количества логических изменений). С точки зрения чистого времени метод RAW(100) работал в 17 раз быстрее, чем метод BLOB.

Таким образом, хотя LOB-объекты Securefile имеют некоторые хорошие улучшения производительности по сравнению с базовым файлом, они все равно не могут сравниться с использованием обычных типов данных в строке. Если вам нужна скорость, механизмы параллелизма и блокировки обычных операций со строками, а также все преимущества кэширования, вам лучше использовать подход, не связанный с LOB, например предложенный метод RAW(100). Вы можете написать функцию PL/SQL для повторной сборки строк в правильном порядке и возврата временного BLOB-объекта, если он нужен вашей программе-потребителю в этом формате.

Очевидно, что это не решает проблему задержки в сети, которую можно уменьшить только за счет буферизации на стороне клиента, чтобы в базу данных отправлялось меньше изменений. Но даже в этом случае требуется меньше сетевых обращений туда и обратно с UPDATE обычного типа данных в строке, чем с чем-либо, связанным с LOB, поскольку операции LOB требуют нескольких операций на строку (создание или выборка локатора, открытие объекта, запись, закрытие, и т. д..). Таким образом, отказ от BLOB также поможет вам справиться с задержкой в ​​сети.