Содержание


Улучшение анализа использования индексов при работе с DB2 10.1

Применение списков использования

Comments

Зачем нужны индексы?

Индексы служат двум основным целям. Они обеспечивают логическое упорядочивание строк в таблице и ускоряют доступ к данным, предоставляя быстрый и эффективный метод определения местоположения конкретных строк данных. Кроме того, правильно заданные индексы способны гарантировать уникальность записей, хранящихся в таблице. Однако вся эта функциональность достается небесплатно, поскольку требует дополнительных издержек на сопровождение индексов при выполнении таких операций, как INSERT, UPDATE и DELETE. В некоторых средах, таких как среды оперативной обработки транзакций (OLTP), эти дополнительные издержки могут приводить к недопустимому снижению производительности. Таким образом, имеет место хрупкий баланс между использованием индексов для ускорения доступа к данным и снижением производительности, обуславливаемым наличием индексов. В результате крайне важно — особенно в OLTP-средах — гарантировать эффективное использование существующих индексов.

Мониторинг использования индекса с помощью инструмента db2pd

DB2 Problem Determination tool (db2pd) — это удобный диагностический инструмент, который можно применять для получения определенной информации о среде DB2. В отличие от других инструментов мониторинга, инструмент db2pd для получения нужной ему информации подключается к наборам совместно используемой памяти DB2. Поскольку db2pd не задействует "механизм" DB2, ему не приходится конкурировать за ресурсы базы данных, что делает его очень нетребовательным к ресурсам и эффективным. Кроме того, поскольку инструмент db2pd работает непосредственно с памятью, он способен извлекать данные быстро и без какого-либо их повреждения.

Для управления типом информации, которую собирает инструмент db2pd, имеется более 50 команд и опций; с помощью одной из этих опций (-tcbstats) можно дать инструменту db2pd указание собирать только информацию об использовании индексов. Например, для получения с помощью инструмента db2pd информации об использовании индексов базы данных под названием SAMPLE нужно выполнить команду, показанную в листинге 1.

Листинг 1. Использование команды db2p
db2pd -db sample -tcbstats index

Анализ выходной информации этой команды осложняется тем, что обращение к тому или иному индексу осуществляется по его идентификатору индекса (IID), а не по имени. Формат выходной информации также затрудняет работу с ней. Имеется метод для разбора выходной информации -tcbstats с целью извлечения данных об использовании индекса, однако этот процесс является достаточно сложным и может легко быть нарушен в случае изменения выходного формата db2pd.

Этот метод предусматривает синтаксический разбор результатов применения опции -tcbstats с целью извлечения подмножества информации об использовании индекса и загрузку извлеченной информации в таблицу с именем INDEX_USAGE. Затем для нескольких SQL-запросов генерируется Explain-информация, после чего к таблице INDEX_USAGE присоединяются представления системного каталога SYSCAT.TABLES и SYSCAT.INDEXES, а также Explain-таблицы, позволяющие определить, какие SQL-операторы используют индексы, и какие именно индексы. К сожалению, в таком подходе легко упустить из виду важные SQL-операторы.

Еще одна проблема состоит в следующем: поскольку полученные с помощью инструмента db2pd данные хранятся в различных наборах областей памяти DB2 (memory set), эти данные бесследно исчезают при деактивации исследуемой базы данных. Таким образом, информация об использовании индекса, на сбор которой было затрачено несколько дней или даже недель, может исчезнуть за несколько секунд. Состав информации об использовании индекса, возвращаемой инструментом db2pd, изменяется от версии к версии; в версии DB2 10.1 информация об использовании индекса собирается только для DML-операций.

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

В версии DB2 9.7 были усовершенствованы способы, позволяющие получать информацию об использовании индекса. Например, к представлению системного каталога SYSCAT.INDEXES был добавлен столбец LASTUSED. Поскольку эта информация хранится в таблице, то — в отличие от наборов областей памяти DB2 — она сохраняется и после того, как база данных деактивируется. Что еще более важно, сопровождение этой информации осуществляет в фоновом режиме процесс-демон с именем db2lused, который обновляет ее каждые пятнадцать минут. Кроме того, была добавлена табличная функция MON_GET_INDEX(), которая значительно упрощает извлечение информации об использовании индекса.

Эта функция возвращает информацию об индексах, которые были использованы с момента последней активации базы данных. Фактически ту же самую информацию, которую возвращает команда db2pd -tcbstats index, можно получить с помощью табличной функции MON_GET_INDEX(). В листинге 2 показан запрос, который задействует эту функцию для получения информации об использовании индексов.

Листинг 2. Запрос для получения информации об использовании индексов
SELECT SUBSTR(S.INDSCHEMA, 1, 10) AS indschema,
    SUBSTR(S.TABNAME, 1, 30) AS table_name,
    SUBSTR(S.INDNAME, 1, 30) AS index_name,
    T.INDEX_SCANS,
    T.INDEX_ONLY_SCANS
FROM TABLE(MON_GET_INDEX('','', -2)) AS t, SYSCAT.INDEXES AS s
WHERE T.TABSCHEMA = S.TABSCHEMA
    AND T.TABNAME = S.TABNAME
    AND T.IID = S.IID
    AND S.INDSCHEMA NOT LIKE 'SYS%'
ORDER BY S.TABNAME, S.INDNAME DESC

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

Мониторинг использования индекса с помощью объектов типа usage list

В версии DB2 10.1 был представлен новый объект базы данных под названием usage list, призванный обеспечить мониторинг использования таблиц и индексов. Как следует из его названия (usage list — список использования), этот объект базы данных используется для записи информации об DML-операторах, которые ссылаются на определенную таблицу или на определенный индекс. Каждая запись в таком списке использования содержит информацию о том, сколько раз выполнялась определенная DML-операция, а также совокупные статистические данные, которые указывают, каким образом каждый оператор повлиял на таблицу или на индекс в процессе исполнения. Списки использования создаются посредством выполнения команды CREATE USAGE LIST; основной базовый синтаксис этой команды показан в листинге 3.

Листинг 3. Базовый синтаксис команды CREATE USAGE LIST
CREATE USAGE LIST [ULName]
FOR [TABLE | INDEX] [ObjectName]
<LIST SIZE [100 | NumEntries]>
<WHEN FULL [WRAP | DEACTIVATE]>
<[INACTIVE | ACTIVE] ON START DATABASE>

Здесь:

  • ULName обозначает имя, которое должно быть присвоено создаваемому списку использования.
  • ObjectName задает по имени существующую таблицу (или индекс), для которой (которого) должен быть создан список использования.
  • NumEntries задает максимальное количество записей, которые могут храниться в создаваемом списке использования.

Примечание. Параметры или варианты, показанные в квадратных скобках ([]) являются обязательными; параметры/опции, показанные в угловых скобках (<>>), не являются таковыми.

После того как список использования был создан и активирован, для получения и форматирования собранной информации об использовании можно использовать две табличные функции мониторинга, также представленные в версии DB2 10.1 —MON_GET_TABLE_USAGE_LIST() и MON_GET_INDEX_USAGE_LIST().

Пример применения списка использования для мониторинга использования индекса

Чтобы продемонстрировать, как можно применить списки использования для мониторинга использования индекса, рассмотрим несколько "учебных" сценариев, разработанных с использованием таблицы PRODUCT, которая входит в состав базы данных SAMPLE, поставляемой вместе с продуктом DB2 for Linux, UNIX, and Windows. Эту базу данных можно создать командой db2sampl.

Если вы внимательно исследуете физический дизайн базы данных SAMPLE, то обнаружите, что индекс с именем PK_PRODUCT является единственным индексом типа REGULAR, заданным для таблицы PRODUCT. Для определения того, какие столбцы были использованы для описания индекса PK_PRODUCT, можно воспользоваться любыми из DB2-команд/SQL-операторов, показанных в листинге 4.

Листинг 4. Пример DB2-команд/SQL-операторов
DESCRIBE INDEXES FOR TABLE product SHOW DETAIL
                
SELECT SUBSTR(INDNAME, 1, 20) AS index_name, COLNAMES 
    FROM SYSCAT.INDEXES
    WHERE TABNAME = 'PRODUCT'
                
db2look -d SAMPLE -e -tw PRODUCT -o PRODUCT.DDL

Для создания списка использования с именем PK_PRODUCT_USE для индекса PK_PRODUCT достаточно выполнить SQL-оператор, показанный в листинге 5.

Листинг 5. Пример SQL-оператор для создания списка использования индекса
CREATE USAGE LIST pk_product_use FOR INDEX PK_PRODUCT
    LIST SIZE 500
    WHEN FULL WRAP

Этот конкретный оператор задает максимальное количество записей (500), которые разрешается иметь в создаваемом списке использования, и говорит СУБД DB2, что при заполнении списка использования она должна вернуться в начало и перезаписать более ранние записи, которые хранились в этом списке.

Прежде чем в список использования можно записывать информацию о DML-операторах, которые ссылаются на таблицу или на индекс, его сначала необходимо активировать. Если для оператора CREATE USAGE LIST, с помощью которого был создан соответствующий список использования, было специфицировано выражение ACTIVE ON START DATABASE, то этот список будет активирован автоматически при активации базы данных, в которой хранится этот список, или при первом установлении соединения с этой базой данных. Также список использования можно активировать оператором SET USAGE LIST. Оператор, показанный в листинге 6, активирует ранее созданный список использования PK_PRODUCT_USE.

Листинг 6. Активация списка PK_PRODUCT_USE
SET USAGE LIST pk_product_use STATE ACTIVE

Начиная с версии DB2 10.1, SQL-оператор ALTER позволяет вносить изменения в определение ранее созданного списка использования. Например, оператор, показанный в листинге 7, изменит количество записей, разрешенных в указанном списке использования, на 1000.

Листинг 7. Применение SQL-оператора ALTER
ALTER USAGE LIST pk_product_use LIST SIZE 1000

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

Листинг 8. Оператор, изменяющий поведение списка использования
ALTER USAGE LIST pk_product_use WHEN FULL DEACTIVATE

Чтобы получить перечень всех списков использования, которые были заданы для конкретной базы данных (включая те списки, которым было присвоено состояние Released, указывающее на высвобождение памяти, ассоциированной с соответствующим списком), запросите таблицу системного каталога SYSCAT.USAGELISTS с помощью оператора SELECT, показанного в листинге 9.

Листинг 9. Применение оператора SELECT
SELECT * from SYSCAT.USAGELISTS

Чтобы определить состояние одного нескольких списков использования, которые не находятся в состоянии Released, создайте запрос с вызовом функции мониторинга MON_GET_USAGE_LIST_STATUS() (см. листинг 10).

Листинг 10. Функция мониторинга
SELECT * FROM TABLE(MON_GET_USAGE_LIST_STATUS(NULL, NULL, -2))

Когда список использования для какого-либо индекса активируется, он начнет собирать информацию об DML-операторах, обращающихся к индексу, для которого этот список был определен. Как указывалось выше, информацию, хранящуюся в списке использования для индекса, можно получить посредством запроса, задействующего табличную функцию MON_GET_INDEX_USAGE_LIST(). Например, с помощью запроса, показанного в листинге 11, можно получить информацию, хранящуюся в созданном ранее списке использовании PK_PRODUCT_USE.

Листинг 11. Получение информации из списка использования PK_PRODUCT_USE
SELECT NUM_REFERENCES, EXECUTABLE_ID
FROM TABLE(MON_GET_INDEX_USAGE_LIST(NULL, 'PK_PRODUCT_USE', -2))

Второй параметр, переданный в табличную функцию MON_GET_INDEX_USAGE_LIST(), является именем конкретного списка использования; если вы присвоите этому параметру значение NULL, то функция возвратит информацию для всех ранее заданных списков использования, находящихся в активном состоянии. Таким образом, если вы задали несколько списков использования и некоторые из этих списков активны, а теперь хотите одновременно получить информацию обо всех этих списках, это можно сделать с помощью запроса, показанного в листинге 12.

Листинг 12. Одновременное получение информации
SELECT 
      SUBSTR(USAGELISTNAME,1,20) AS usagelistname,
      SUBSTR(INDNAME,1,20) AS indname, NUM_REFERENCES, EXECUTABLE_ID
    FROM TABLE(MON_GET_INDEX_USAGE_LIST(NULL, NULL, -2))

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

Обратите внимание, что в обоих показанных выше примерах извлекаются значения идентификатора EXECUTABLE_ID. Каждой записи в кэше пакетов (Package Cache) присваивается уникальный идентификатор EXECUTABLE_ID. Соответственно исследование идентификатора EXECUTABLE_ID позволяет определить, какие SQL-операторы связаны с использованием определенного индекса. Объединение результатов табличных функций MON_GET_INDEX_USAGE_LIST() и MON_GET_PKG_CACHE_STMT(), примененных к столбцу EXECUTABLE_ID — это эффективный способ выявления SQL-операторов, которые являются причиной использования определенного индекса компонентом DB2 Optimizer (оптимизатор базы данных).

Вернемся к таблице PRODUCT в базе данных SAMPLE. Если SQL-операторы, показанные в листинге 13, выполняются, то DB2 Optimizer должен выбрать использование индекса PK_PRODUCT для достижения оптимальной производительности запроса.

Листинг 13. Использование индекса PK_PRODUCT
SELECT pid FROM PRODUCT WHERE pid = '100-201-01';
SELECT pid FROM PRODUCT WHERE pid = '100-201-01';
SELECT pid FROM PRODUCT WHERE pid = '100-103-01';

Будем исходить из предположения, что для индекса PK_PRODUCT был создан и активирован список использования. Тогда показанный ниже запрос, который объединяет табличные функции MON_GET_PKG_CACHE_STMT() и MON_GET_INDEX_USAGE_LIST(), примененные к столбцу EXECUTABLE_ID, представит отчет, подтверждающий, что недавно выполненные SQL-операторы действительно используют индекс PK_PRODUCT (см. листинг 14).

Листинг 14. Запрос, объединяющий табличные функции
SELECT
      SUBSTR(i.INDNAME,1,14) AS index_name,
      SUBSTR(t.STMT_TEXT,1,50) AS sql_statment, t.NUM_EXECUTIONS
    FROM TABLE (MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2 )) AS t,
         TABLE(MON_GET_INDEX_USAGE_LIST(NULL, 'PK_PRODUCT', -2)) AS i
    WHERE t.EXECUTABLE_ID  = i.EXECUTABLE_ID
    ORDER BY NUM_EXECUTIONS DESC;

Если это так, то выходная информация этого запроса должна выглядеть, как в листинге 15.

Листинг 15. Пример выходной информации
INDEX_NAME     SQL_STATEMENT                                      NUM_EXECUTIONS
-------------- -------------------------------------------------- --------------
PK_PRODUCT     SELECT pid FROM PRODUCT WHERE pid = '100-201-01'                2
PK_PRODUCT     SELECT pid FROM PRODUCT WHERE pid = '100-103-01'                1

В этом примере результаты отсортированы так, что сначала идут операторы, используемые чаще других. Чтобы ограничить результаты первыми 20 операторами, добавьте к используемому запросу условие FETCH FIRST 20 ROWS ONLY.

При таком использовании кэша Package Cache необходимо иметь в виду два момента. Во-первых, чтобы сохранить все SQL-операторы, которые вы собираетесь протестировать, необходимо проследить за тем, чтобы конфигурационному параметру базы данных pckcachesz было присвоено достаточно большое значение. В этом примере конфигурационному параметру pckcachesz присвоено значение 10000, поскольку производилась оценка лишь нескольких простых SQL-операторов. При работе в другой среде необходимое значение этого параметра может быть гораздо больше.

Во-вторых, во время тестирования удостоверьтесь, что операция динамической очистки кэша пакетов НЕ выполняется. В противном случае вы рискуете потерять текстовые данные SQL-оператора, которые вы надеялись получить.

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

Предположим, что для таблицы PRODUCT в базе данных SAMPLE было создано три дополнительных индекса с помощью следующих SQL-операторов (см. листинг 16).

Листинг 16. Добавление трех индексов
CREATE INDEX product_gi1 ON PRODUCT (name ASC);
                
CREATE INDEX product_gi2 ON PRODUCT (promostart ASC, promoend ASC);
                
CREATE INDEX product_gi3 ON PRODUCT (price ASC);

Системный каталог для базы данных SAMPLE помогает генерировать операторы CREATE USAGE LIST и ACTIVATE USAGE LIST, которые необходимы при создании и активации списков использования для каждого из этих новых индексов. Например, необходимые DDL-операторы можно создать с помощью запросов, показанных в листинге 17.

Листинг 17. Запросы для создания DDL-операторов
SELECT
    'CREATE USAGE LIST ' 
    CONCAT INDNAME 
    CONCAT ' FOR INDEX '
    CONCAT RTRIM(INDSCHEMA) CONCAT '.' 
    CONCAT INDNAME 
    CONCAT ' LIST SIZE 500 WHEN FULL WRAP ;'
 FROM SYSCAT.INDEXES i
 WHERE TABNAME = 'PRODUCT'
    AND INDSCHEMA NOT LIKE 'SYS%' i
    AND INDEXTYPE = 'REG'
 ORDER BY INDNAME;
                
SELECT
    'SET USAGE LIST ' 
    CONCAT INDNAME 
    CONCAT ' STATE ACTIVE;'
  FROM SYSCAT.INDEXES i
  WHERE TABNAME = 'PRODUCT'
    AND INDSCHEMA NOT LIKE 'SYS%'
    AND INDEXTYPE = 'REG'
   ORDER BY INDNAME;

При исполнении этих двух запросов они генерируют DDL-выражения, с помощью которых можно создать и активировать четыре списка использования – по одному для каждого индекса, заданного для таблицы PRODUCT. Аналогичные запросы можно применять для удаления списков использования.

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

Листинг 18. Пример использования SQL-операторов для оценки новых индексов
SELECT pid FROM product 
    WHERE name = 'SNOW SHOVEL, BASIC 22 INCH';
                
SELECT pid FROM product 
    WHERE name = 'SNOW SHOVEL, SUPER DELUXE 26 INCH';
                
SELECT pid, name FROM product WHERE price = 19.99;
SELECT pid, name FROM product WHERE price = 49.99;
                
SELECT pid, name, price FROM product WHERE promostart = '11/19/2004';
                
SELECT pid, name, price FROM product 
    WHERE promostart = '12/22/2005' AND promoend = '02/22/2006';

После того как эти SQL-операторы будут выполнены, следующий запрос (см. листинг 19) можно применить для определения того, какие из новых индексов были выбраны компонентом DB2 Optimizer (если это имело место).

Листинг 19. Запрос для определения того, какие новые индексы были выбраны компонентом DB2 Optimizer
SELECT
        SUBSTR(i.INDNAME,1,12) AS index_name,
        SUBSTR(t.STMT_TEXT,1,59) AS sql_statment, t.NUM_EXECUTIONS
    FROM TABLE (MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2 )) AS t,
        TABLE(MON_GET_INDEX_USAGE_LIST(NULL, NULL, -2)) AS i
    WHERE t.EXECUTABLE_ID = i.EXECUTABLE_ID
    ORDER BY NUM_EXECUTIONS DESC

Результаты этого запроса должны выглядеть примерно так, как показано в листинге 20.

Листинг 20. Выходная информация выполненного запроса
INDEX_NAME   SQL_STATEMENT                                               NUM_EXECUTIONS
------------ ----------------------------------------------------------- --------------
PK_PRODUCT   SELECT pid FROM PRODUCT WHERE pid = '100-201-01'                         2
PRODUCT_GI2  SELECT pid, name, price FROM product WHERE promostart = '11              1
PK_PRODUCT   SELECT pid FROM PRODUCT WHERE pid = '100-103-01'                         1
PRODUCT_GI1  SELECT pid FROM product WHERE name = 'SNOW SHOVEL, BASIC 22              1
PRODUCT_GI3  SELECT pid, name FROM product WHERE price = 49.99                        1
PRODUCT_GI1  SELECT pid FROM product WHERE name = 'SNOW SHOVEL, SUPER DE              1
PRODUCT_GI2  SELECT pid, name, price FROM product WHERE promostart = '12              1
PRODUCT_GI3  SELECT pid, name FROM product WHERE price = 19.99                        1

Поскольку второму параметру табличной функции MON_GET_INDEX_USAGE_LIST() присвоено значение NULL, эта функция возвратит информацию о списках использования для всех активных индексов. В предположении, что были выполнены все SQL-операторы, обращающиеся к заданной таблице, результаты этого запроса должны показывать, как используются все доступные индексы. В рассматриваемом случае все используемые запросы были спроектированы таким образом, чтобы компонент DB2 Optimizer использовал все доступные индексы. Однако в реальной ситуации тестирование скорее всего выявило бы неиспользуемые или неэффективно используемые индексы.

Заключение

В результате совершенствования СУБД DB2 за многие годы ее существования стало намного легче получать информацию об использовании индексов. С появлением версии DB2 10.1 существенно упростилось точное отыскание SQL-операторов, которые используют конкретный индекс. Применение списков использования индексов и новой табличной функции MON_GET_INDEX_USAGE_LIST() в сочетании с ранее существовавшей табличной функцией MON_GET_PKG_CACHE_STMT() позволяет быстро оценить, использует ли DB2 Optimizer созданные индексы.


Ресурсы для скачивания


Похожие темы


Комментарии

Войдите или зарегистрируйтесь для того чтобы оставлять комментарии или подписаться на них.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=934838
ArticleTitle=Улучшение анализа использования индексов при работе с DB2 10.1
publish-date=06202013