w_dev_o38plsql.html

Все коды примеров из этой статьи находятся в наборе sample code download

Лучшие практические приемы, чтобы узнать о размере PGA и об индексировании коллекций.

Мой DBA хочет, чтобы я уменьшил объем PGA (программная область памяти - program global area ), которая используется в моих программах с коллекциями. Разве не DBA должен управлять памятью, и если нет, то как я могу узнать, сколько PGA у меня используется?

Конечно же, именно администратор базы данных ( DBA - database administrator – АБД) в конечном итоге несет ответственность за анализ использования памяти, а также и за выполнение необходимой настройки конфигурации базы данных, и за рекомендации разработчикам, когда следует изменить их код.

Я хочу сказать, что настоятельно рекомендую всем разработчикам понимать и насколько возможно вникнуть во все аспекты разработки кода, чтобы результат их творчества стал наилучшим для пользователей.

Конечно же можно, например, измерить объем PGA , используемой программой. Для этого, правда, потребуется обратиться к АБД и попросить его дать вашей схеме привилегию SELECT на следующие представления :

Коль скоро можно выполнять запросы к этим представлениям, скомпилируйте в своей схеме процедуру show _ pga _ memory , показанную на Листинге 1, которая приводится в файле show _ pga _ memory . sp в наборе sample code download примеров для этой статьи). Затем после запуска приложения можно вызвать show _ pga _ memory , чтобы увидеть, насколько увеличилась PGA .

Листинг1: процедура show_pga_memory 
CREATE OR REPLACE PROCEDURE show_pga_memory (
   context_in   IN   VARCHAR2 DEFAULT NULL
)
IS
   l_memory   NUMBER;
BEGIN
    SELECT st.VALUE
       INTO l_memory
      FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
    WHERE se.audsid = USERENV ('SESSIONID')
        AND st.statistic# = nm.statistic#
        AND se.SID = st.SID
        AND nm.NAME = 'session pga memory';

   DBMS_OUTPUT.put_line (   CASE
                                            WHEN context_in IS NULL
                                                  THEN NULL
                                            ELSE context_in || ' - '
                                        END
                                    || 'PGA memory used in session = '
                                    || TO_CHAR (l_memory)
                                   );
END show_pga_memory;

При использовании этой процедуры во время тестирования BULK COLLECT с различными значениями выражения LIMIT , я обнаружил, что PGA используется в диапазоне от 4,613,072 до 46,883,792 байтов.

Я хочу использовать ассоциативные массивы для быстрого поиска названия офисного продукта по заданному номеру продукта и номер продукта по заданному названию. Названия продуктов уникальны , а номера продуктов являются числами . Я знаю, как можно использовать номера продуктов в качестве значения индекса в моей коллекции названий, но мне также нужен обратный процесс и поиск номера продукта по заданному названию продукта. Можно ли создать другой индекс по содержимому коллекции ?

Коллекция имеет только один индекс, который определен явно для типа ассоциативного массива в выражении INDEX BY (как число или строка) и неявно для типов вложенной таблицы и переменного массива (только число).

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

Я продемонстрирую обе эти техники, основываясь на данных о канцелярских товарах, которые инициализируются в теле пакета. Буду искать их, пока не найду. Сначала я создам пакет, спецификация которого включает коллекцию названий продуктов, как показано на Листинге 2.

Предположим, у меня есть название продукта, и требуется найти значение индекса, по которому можно удалить элемент. Листинг 3 содержит функцию product _ lookup 1, которая просматривает коллекцию до тех пор, пока не будет найдено соответствующее название.

Листинг 3: Функция для поиска названия продукта 
CREATE OR REPLACE FUNCTION product_lookup1 (NAME_IN IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   c_count    PLS_INTEGER := office_products.list_of_names.COUNT;
   l_index     PLS_INTEGER := office_products.list_of_names.FIRST;
   l_return    PLS_INTEGER;
BEGIN
   WHILE (l_index IS NOT NULL)
   LOOP
      IF office_products.list_of_names (l_index) = NAME_IN
      THEN
          l_return := l_index;
          l_index := NULL;
      ELSE
          l_index := office_products.list_of_names.NEXT (l_index);
      END IF;
   END LOOP;

   RETURN l_return;
END product_lookup1;

Это очень простая программа, легкая для понимания и поддержки. Однако она может выполняться неэффективно для больших коллекций — особенно, если попробовать найти значение индекса по названию, которого нет в списке. Для этого мне потребовалось бы просмотреть все содержимое коллекции. Ясно же, что мне не хочется просматривать коллекцию от начала и до конца.

Способ получше - эмуляция индекса по столбцу таблицы. Как бы следуем примеру Oracle Database. Если нужно быстро найти строку таблицы по заданному значению столбца, создается индекс по этому столбцу. В этом случае при работе с реляционной таблицей, я бы создал индекс по названию продукта.

Я могу сделать что-то подобное и для коллекции, однако для этого надо будет описать вторую коллекцию для эмуляции индекса по столбцу таблицы. В этой статье будет использоваться термин «индексная коллекция» в ссылках на коллекцию, которая будет создана для эмуляции индекса по столбцу таблицы.

Коллекция может иметь только один индекс, а коллекция list_of_names уже индексирована по номеру продукта. Поэтому я создам вторую коллекцию с индексом по названию продукта. Листинг 4 показывает модифицированную спецификацию пакета office_products.

Листинг 4: Модифицированная спецификация пакета office _ products

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

Этот метод совершенно необязательный, но настоятельно рекомендуемый. Другими словами, можно объявить типы коллекций и так:

Оба метода будут выполнять одну и ту же роль. Почему же подтипы лучше? Потому что результирующий код становится намного более прозрачным, делая его легче для понимания, отладки и поддержки.

Так как я использую подтипы, спецификация пакета говорит мне — очень явно и без необходимости комментариев — что у меня есть две коллекции для работы с:

Конечно, все еще нужно заполнить эти коллекции, поэтому давайте посмотрим на тело пакета, показанное на Листинге 5.

Сначала я изменил структуру тела пакета, переместив строки кода для заполнения коллекции с именами в свою собственную процедуру инициализации. Затем эта процедура вызывается в секции инициализации пакета . Я думаю, что поддержка кода в их собственных подпрограммах легче, чем длинного сложного блока кода в конце пакета. Кроме того, я теперь могу, если потребуется, объявить эту подпрограмму в спецификации, что позволит выполнить переинициализацию, как только она потребуется во время сессии.

Сама процедура initialize содержит локальную процедуру, add _ product , которая берет название и выполняет две вещи:

Затем add _ product вызывается для каждой строки таблицы продуктов.

При такой эмуляции индекса моя функция поиска становится очень простой, как показано на Листинге 6. Эта функция возвращает значение индекса для заданного названия, если строка с таким названием существует. Иначе функция возвращает NULL. Так как NULL недопустим в качестве индексного значения, он является хорошим индикатором для " Извините, продукта с таким названием нет ".

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

Сравнение производительности программ поиска. Я хочу сравнить производительность для следующих сценариев:

Сначала я изменю программу initialize таким образом, чтобы загрузить реальный объем данных в коллекции (коллекции больше не буду заполняться в секции инициализации), как показано на Листинге 7.

Листинг 7: Модифицированная процедура initialize 
CREATE OR REPLACE PACKAGE BODY office_products
IS
   PROCEDURE initialize (count_in IN PLS_INTEGER)
   IS
      PROCEDURE add_product (index_in IN index_t, NAME_IN IN name_t)
      IS
      BEGIN
          list_of_names (index_in) := NAME_IN;
          index_by_name (NAME_IN) := index_in;
      END add_product;
   BEGIN
      list_of_names.DELETE;
      index_by_name.DELETE;

      add_product (1567, 'Stapler');
      add_product (75009, 'Mousepad');
      add_product (101, 'Pencil sharpener');

      FOR indx IN 1 .. count_in
      LOOP
          add_product (indx * 1000, 'Product ' || indx);
      END LOOP;
   END initialize;
END office_products;

Затем создам процедуру compare_lookups, которая вычисляет DBMS_UTILITY.GET_CPU_TIME, 
  чтобы помочь мне определить затраченное время работы процессора (CPU) в сотых долях
  секунды. Листинг 8 включает код для замера производительности при реализации просмотра коллекции. 
Листинг 8: процедура compare_lookups 
CREATE OR REPLACE PROCEDURE compare_lookups (
   coll_count_in   IN   PLS_INTEGER
 , iterations_in   IN   PLS_INTEGER
)
IS
   l_time_before   PLS_INTEGER;
   l_index       PLS_INTEGER;
BEGIN
   office_products.initialize (coll_count_in);
  .
  .
  .
   l_time_before := DBMS_UTILITY.get_cpu_time;

   FOR indx IN 1 .. iterations_in
   LOOP
      l_index := product_lookup1 (
         'Product ' || TO_CHAR (coll_count_in / 2)); 
   END LOOP;

   DBMS_OUTPUT.put_line (   
      '   Scan Find - name exists = '
    || TO_CHAR (DBMS_UTILITY.get_cpu_time - l_time_before));
     .
     .
     .
END compare_lookups;

Затем я вызову процедуру compare_lookups с различным количеством строк в коллекции и количеством вызовов программ поиска: 
BEGIN
   compare_lookups (100,      1000);
   compare_lookups (1000,    1000);
   compare_lookups (100000, 1000);
   compare_lookups (100000, 10000);
END;

Листинг 9 показывает полученные результаты (в сотых долях секунды), в Oracle Database 10g Release 2 на ноутбуке с 4GB памяти. 
Листинг 9: Результаты процедуры compare_lookups
COUNT in collections = 100
Number of times program lookup called = 1000

   Scan Find - name exists = 0
   Scan Find - name does not exist = 2
   Index Find - name exists = 0
   Index Find - name does not exist = 0

COUNT in collections = 1000
Number of times program lookup called = 1000

   Scan Find - name exists = 12
   Scan Find - name does not exist = 11
   Index Find - name exists = 2
   Index Find - name does not exist = 0

COUNT in collections = 100000
Number of times program lookup called = 1000

   Scan Find - name exists = 681
   Scan Find - name does not exist = 1391
   Index Find - name exists = 0
   Index Find - name does not exist = 0

COUNT in collections = 100000
Number of times program lookup called = 10000

   Scan Find - name exists = 6578
   Scan Find - name does not exist = 14043
   Index Find - name exists = 1
   Index Find - name does not exist = 0

На основании этих данных можно сделать следующие выводы:

Наконец, для любого анализа производительности и заключений, следует выполнить скрипт compare _ lookups . sql (доступный в демонстрационном коде этой статьи) самостоятельно на своей версии Oracle Database и подтвердить эти результаты. Не забудьте . . . . Коллекции предоставляют очень эффективный механизм поиска данных . Извлечение данных из коллекции, поддерживаемой в программной области памяти ( program global area ), намного быстрее, чем выполнение этого же действия из хранимой таблицы, данные из которой поддерживаются в системной области памяти ( system global area ).

Однако при использовании коллекций уровня пакета необходимо всегда помнить о следующих моментах:

Если вы разрабатываете программное обеспечение для Oracle Database 11 g , рекомендуется использовать преимущества кэширования результата функции, о котором я писал в " On the PL / SQL Function Result Cache " в выпуске Oracle Magazine за Сентябрь/Октябрь 2007г. Эта возможность показывает производительность, похожую на пакетный кэш, но он разделяется всеми сессиями базы данных и автоматически обновляется при изменениях в связанных таблицах.

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

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.