w_dev_o28plsql.html

Лучшие способы применения LIMIT и обработки %NOTFOUND

Я начал использовать BULK COLLECT везде, где необходимо извлечь большой объем данных. Однако из-за этого у меня возникли проблемы с администратором БД. Он хнычет, что хотя мои программы и могут работать намного быстрее, они также требуют намного больше памяти. Он не желает рекомендовать их использование в массовом порядке. Что может сделать программист?

Наиболее важно при изучении и в начале использования таких возможностей, как BULK COLLECT, то, что надо помнить, это отнюдь не бесплатно. Почти всегда в чём-нибудь приходится искать компромисс. Компромисс при BULK COLLECT похож на многие другие случаи применения возможностей для улучшения производительности, и заключается он в том: «что работает быстрее, то требует больше памяти».

Напомню, что именно, память под коллекции отводится в программной области (program global area, PGA), а не в системной (system global area, SGA). SGA доступна всем сессиям, соединенным с Oracle, а PGA выделяется для каждой сессии. Таким образом, если программа запрашивает 5MB памяти для заполнения коллекции в 100 одновременных соединениях, то ей необходимо выделить 500MB в PGA, в дополнение к памяти, выделенной в SGA.

К счастью, PL/SQL позволяет разработчикам управлять количеством памяти, используемой в BULK COLLECT с помощью выражения LIMIT.

Предположим, необходимо извлечь все строки из таблицы employees и затем выполнить некий анализ компенсации для каждой строки. Я могу использовать BULK COLLECT, как показано ниже:

PROCEDURE process_all_rows
IS
   TYPE employees_aat 
   IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   l_employees employees_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_employees
      FROM employees;
     
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP
       analyze_compensation 
      (l_employees(indx));
   END LOOP;
END process_all_rows;

Очень сжатый, элегантный и эффективный код. Однако, если таблица employees содержит десятки тысяч строк, каждая из которых состоит из сотен столбцов, эта программа может вызвать интенсивное расходование памяти PGA.

Поэтому следует избегать этого вида "неограниченного" использования BULK COLLECT. Вместо него поместите предложение SELECT во внешний курсор и затем используйте простой цикл для извлечения многих, но не всех строк из таблицы при каждом прохождении тела цикла, как показано на Листинге 1.

Листинг 1: Использование BULK COLLECT с предложением LIMIT 

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
    CURSOR employees_cur 
    IS 
        SELECT * FROM employees;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
BEGIN   
    OPEN employees_cur;
    LOOP
        FETCH employees_cur 
            BULK COLLECT INTO l_employees LIMIT limit_in;

        FOR indx IN 1 .. l_employees.COUNT 
        LOOP
            analyze_compensation (l_employees(indx));
        END LOOP;

        EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows;

Процедура process _ all _ rows листинга 1 запрашивает извлечение limit _ in строк за раз. PL/SQL будет повторно использовать те же самые limit_in элементов коллекции каждый раз, когда данные извлекаются и, таким образом, будет использоваться повторно та же самая память. Даже если таблица растет, использование PGA будет оставаться постоянным.

Как же выбрать число, которое будет использоваться в выражении LIMIT? Теоретически можно вычислить, сколько памяти может быть выделено в PGA, а затем установить лимит как можно ближе к этому числу.

Тесты, которые выполнил я (и мои коллеги), показывают совершенно одинаковую производительность, независимо от выбранного для лимита значения, как только он превысит 25. Скрипт test_diff_limits.sql , включающий демонстрационный код для этой статьи, доступен по ссылке otn.oracle.com/oramag/oracle/08-mar/o28plsql.zip . Он показывает это с помощью представления словаря базы данных ALL_SOURCE в Oracle Database 11 g . Вот результаты, которые видны (в сотнях секунд) после извлечения всех 470,000 строк:

Elapsed CPU time for limit of 1 = 1839
Elapsed CPU time for limit of 5 = 716
Elapsed CPU time for limit of 25 = 539
Elapsed CPU time for limit of 50 = 545
Elapsed CPU time for limit of 75 = 489
Elapsed CPU time for limit of 100 = 490
Elapsed CPU time for limit of 1000 = 501
Elapsed CPU time for limit of 10000 = 478
Elapsed CPU time for limit of 100000 = 527

Я был очень рад, узнав, что Oracle Database 10g автоматически оптимизирует выполнение цикла FOR с курсором до скорости, близкой к BULK COLLECT. К сожалению, моя компания все еще работает с Oracle9i Database, поэтому я начал конвертировать циклы FOR с курсором в BULK COLLECT-ы. И у меня появилась проблема: я использую LIMIT=100, мой запрос извлекает в общей сумме 227 строк, но программа обрабатывает только 200 из них. [Запрос показан на Листинге 2.] Что я делаю неправильно?

Листинг 2: BULK COLLECT, %NOTFOUND и пропущенные строки
PROCEDURE process_all_rows
IS
   CURSOR table_with_227_rows_cur 
   IS 
      SELECT * FROM table_with_227_rows;

   TYPE table_with_227_rows_aat IS 
      TABLE OF table_with_227_rows_cur%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_table_with_227_rows table_with_227_rows_aat;
BEGIN   
   OPEN table_with_227_rows_cur;
   LOOP
      FETCH table_with_227_rows_cur 
         BULK COLLECT INTO l_table_with_227_rows LIMIT 100;

         EXIT WHEN table_with_227_rows_cur%NOTFOUND;     /* cause of missing rows */

      FOR indx IN 1 .. l_table_with_227_rows.COUNT 
      LOOP
         analyze_compensation (l_table_with_227_rows(indx));
      END LOOP;
   END LOOP;

   CLOSE table_with_227_rows_cur;
END process_all_rows;

Мы подошли так близко к абсолютно корректно конвертированному циклу FOR с курсором в BULK COLLECT! Мы ошиблись только в том, что по привычке использовали %NOTFOUND атрибута курсора в выражении EXIT WHEN.

корректно, когда данные извлекаются по одной строке за раз. Однако, с BULK COLLECT эта строка кода может недообработать данные, как это и было описано.

Проверим, что получается, когда вы запускаете программу, и почему последние 27 строк были пропущены. После открытия курсора и входа в цикл, происходит вот что:

Когда используется BULK COLLECT и коллекции для извлечения данных из курсора, никогда не следует обращаться к атрибутам курсора для принятия решения о том, следует ли прекратить цикл и обработку данных.

Поэтому, чтобы убедиться, что все 227 строк обработаны, замените следующее предложение:

EXIT WHEN
table_with_227_rows_cur%NOTFOUND;
на
EXIT WHEN
l_table_with_227_rows_cur.COUNT = 0;

В общем, при работе с BULK COLLECT следует иметь в виду следующее:

Steven Feuerstein ( steven.feuerstein@quest.com ) is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein's self-appointed mission in life these days is to improve the quality and quantity of PL/SQL code testing.