Сжатие таблиц в СУБД Oracle9i RELEASE 2: анализ эффективности

Александр Соколов, Виктор Сусойкин,
компания РДТЕХ

Oracle Magazine RE

Источник: OracleWorld, San Francisco, California, 10-14 November 2002 (http://www.oracle.com/pls/oow/oow_user.show_public? p_event=13&p_type=session&p_session_id=38383)

Предисловие редакторов русского перевода

В данной работе анализируется эффективность механизма сжатия данных, появившегося в СУБД Oracle9i, на примере сжатия таблиц схемы типа "звезда" и нормализованной схемы эталонных тестов TPC-H и TPC-R (то есть, типичных схем в системах поддержки принятия решений). Подчеркнем, сжатие данных, в результате которого может быть достигнута значительная экономия дискового пространства и пространства кеша буферов, не является самоцелью. Современные цены за "мегабайт" дисковой или оперативной памяти делают этот вопрос неактуальным. Главное здесь - повышение общей пропускной способности и уменьшение времени реакции больших систем.

Вместе с тем, следует отметить, этот механизм может быть эффективным и в традиционных интерактивных системах с интенсивным выполнением операций чтения данных с диска. Поскольку сжатие таблиц позволяет уменьшить их размер, это приводит к пропорциональному уменьшению времени, требуемому для резервирования и восстановления базы данных. Кроме того, сжатие таблиц будет несомненно эффективным при работе с табличными пространствами только для чтения (READ ONLY). И все это достигается без каких-либо изменений в приложениях, то есть, этим может заниматься администратор базы данных без привлечения разработчиков приложений.

Данная работа была также с незначительными изменениями опубликована как технический документ (white paper) корпорации Oracle - http://otn.oracle.com / products / bi / pdf / o9ir2_compression_performance_twp.pdf. Некоторые опечатки и ошибки в переводимом документе исправлены по данной публикации. Кроме того, на основании данного документа Микел Посс в соавторстве с Германом Баером (Hermann Baer) опубликовал в Oracle Magazine статью "Decision Speed: Table Compression In Action" (скорость принятия решений: сжатие таблиц на практике) - http://otn.oracle.com / oramag / webcolumns / 2003 / techarticles / poess_tablecomp.htm.

Дополнительно об оптимизации производительности в хранилищах данных можно прочитать в техническом документе "Data Warehouse Performance Enhancements with Oracle9i", An Oracle White Paper, April 2001, http://otn.oracle.com / products / Oracle9i / pdf / o9i_dwperfcomp_dwflow.pdf.


Содержание
  • Почему схема типа "звезда" сжимается лучше нормализованной схемы эталонного теста TPC-H
  • Анализ эффективности
     
  • Лучшие практические методы
  • Заключение
  • Ссылки
  • Ссылки к примечаниям редакторов русского перевода
  • Приложение - SQL-запросы
  • Запрос типа "звезда" номер 1
  • Запрос типа "звезда" номер 2
  • Введение

    Сжатие таблиц (Table Compression) - новое средство, введенное в СУБД Oracle 9i Release 2, может быть использовано для сжатия целых таблиц, секций таблиц и материализованных представлений. Оно радикально уменьшает потребности в дисковом пространстве и кеше буферов и, во многих случаях, повышает производительность выполнения запросов, особенно в системах с интенсивным вводом-выводом. Сжатие ориентировано на приложения поддержки принятия решений и OLAP-приложения, но и в других областях могут быть также получены большие выгоды. После объяснения работы механизма сжатия таблиц в данной работе вводятся два типа схем, обычно используемых в системах поддержки принятия решений (как в OLAP-системах, так и в хранилищах данных), а именно, схемы типа "звезда" и нормализованные схемы. С помощью этих схем в двух основных разделах данной работы анализируется, как сжатие таблиц может привести к огромной экономии пространства, и исследуется влияние сжатия таблиц на запросы.  

    Функционирование механизма сжатия таблиц

    СУБД Oracle9i Release2 сжимает данные, устраняя дубликаты значений в блоках базы данных. В алгоритме используется метод сжатия информации без потерь на основе словаря (lossless dictionary-based compression technique) [4]. Сжатые данные, хранимые в блоках базы данных, являются самодостаточными (self-contained). То есть, вся информация, необходимая для восстановления исходных данных в блоке, находится в самом этом блоке. Алгоритм решает, исходя из длины столбца и количества экземпляров значений, будет ли для конкретного столбца создаваться вход в таблице идентификаторов (словаре). Сжимаются только целые столбцы или последовательности столбцов. Все экземпляры таких значений заменяются короткой ссылкой на таблицу идентификаторов. Для коротких значений и значений с небольшим количеством экземпляров входы в таблице идентификаторов не создаются. Чтобы добиться оптимальной производительности, столбцы в блоке могут быть переупорядочены. Тем не менее, это прозрачно для пользователей.

    По сравнению с другими методами сжатия, в которых для сжатия целой таблицы используется фиксированное количество входов в таблице идентификаторов, обычно 256, реализация механизма сжатия в СУБД Oracle имеет много преимуществ. Во-первых, чтобы получить оптимальные результаты сжатия, в СУБД Oracle количество входов в таблице идентификаторов выбирается системой на уровне блоков во время загрузки данных. Во-вторых, входы в таблице идентификаторов создаются системой и для них не требуется пользовательская настройка. В-третьих, в СУБД Oracle алгоритм сжатия динамически адаптируется к изменениям распределения данных без компрометации механизма сжатия. Следовательно, для создания сжатой таблицы нужно только включить в определение таблицы ключевое слово COMPRESS.

    На рис. 1 показаны различия между хранением данных в сжатом блоке по сравнению с несжатым блоком. Исключая таблицу идентификаторов в начале блока, сжатые блоки базы данных очень похожи на обычные блоки базы данных. Модификации кода, сделанные в СУБД Oracle для реализации механизма сжатия, были существенно локализованы. Были модифицированы только те части кода, которые имеют дело с форматированием блока и доступом к строкам и столбцам. В результате, сжатые блоки полностью прозрачны для пользователей базы данных и любых приложений, а все средства и функции СУБД, которые работают с обычными блоками базы данных, также работают и со сжатыми блоками базы данных.

    Рис. 1. Сжатый блок по сравнению с несжатым блоком

    Надписи на рисунке:
    · Not Compressed - несжатый;
    · Compressed - сжатый;
    · Block - блок;
    · Header Information - информация заголовка;
    · Symbol Table - таблица идентификаторов;
    · Raw Data - данные строк;
    · Free Space - свободное пространство.  

    Конфигурация тестируемых схем (типа "звезда" и нормализованная)

    В моделях схем, проектируемых для хранилищ данных, существует большое разнообразие способов размещения объектов схем. Одна из моделей схем для хранилищ данных - схема типа "звезда" (star schema). Другая схема - схема в третьей нормальной форме (3NF-схема, third normal form schema). Кроме того, некоторые схемы хранилищ данных не являются ни схемами типа "звезда", ни 3NF-схемами, они имеют свойства обеих схем; такие схемы представляются гибридными моделями схем.

    СУБД Oracle9i разработана для поддержки всех схем хранилищ данных наиболее эффективным способом. Некоторые средства могут быть специфическими для одной модели схем (такие, как преобразования запросов типа "звезда", специфические для схем типа "звезда"). Тем не менее, подавляющее большинство средств для хранилищ данных в СУБД Oracle в равной степени применимы для схем типа "звезда", 3NF-схем и гибридных. Основные функциональные возможности для хранилищ данных, такие, как секционирование (включая загрузку данных методом "скользящее окно" - rolling window load technique), параллелизм, материализованные представления и аналитический SQL, реализованы для всех моделей схем. (Прим. ред. С методом "скользящее окно" можно ознакомиться по [5].)

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

    На рис. 2 показан пример схемы типа "звезда", подчеркивающий типичную структуру "звезда", в которой таблица фактов DAILY_ SALES (продажи) - центр "звезда", окруженный таблицами измерений: TIME (время), CUSTOMER (клиент), SALES REGION (регион продаж), ITEM (продукт) и PROMOTION (продвижение). Существует две таблицы итогов, определенные на таблице DAILY_SALES (дневные продажи): WEEKLY_SALES (продажи за неделю) и SALES_AGGR (агрегирование продаж). В таблице WEEKLY_SALES агрегируются продажи из таблицы DAILY_SALES по продуктам и клиентам за каждую неделю. Таблица SALES_AGGR строится на таблице DAILY_SALES с дальнейшим агрегированием по регионам продаж.

    Рис. 2. Типичная схема типа "звезда"

    В нашем втором примере мы показываем сжатие для различного типа нормализованных схем на примере стандартных эталонных тестов (benchmark) для оценки среды поддержки принятия решений TPC-H/TPC-R. (Прим. ред. О тестах TPC см. например, [6].) Схема этих тестов состоит из восьми базовых таблиц, моделирующих хранилище данных типичной среды розничной торговли (см. рис. 3). Таблицы, такие, как PART, SUPPLIER, PARTSUPP и CUSTOMER, содержат относительно статичную информацию о продуктах, которые типичная компания розничной торговли покупает у своих поставщиков (supplier) и продает своим клиентам (customer). Объем этих таблиц составляет примерно 15% от общего объема базы данных. Объем двух самых больших таблиц, LINEITEM и ORDERS, составляет примерно 85% от общего объема базы данных. Они содержат данные об отдельных сделках. В отличие от предыдущего примера эта схема не организована как схема типа "звезда", в ней используется нормализованный [3] подход.

    Рис. 3. Нормализованные схемы эталонных тестов TPC-H и TPC-R  

    Экономия пространства в результате сжатия

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

    Если таблица определена как "сжатая" (compressed), она будет использовать меньше блоков данных на диске, уменьшая, следовательно, потребности в дисковом пространстве. Данные из сжатой таблицы читаются в сжатом формате, и они восстанавливаются только во время доступа к ним. Поскольку данные кешируются в своем сжатом формате, то существенно больше данных может быть размещено в одном и том же объеме кеша буферов (см. рис. 4).

    Рис. 4. Пути доступа к сжатым данным

    Надписи на рисунке:
    · Data Access Path with Compression - пути доступа к сжатым данным;
    · Parallel Query Slave - подчиненный процесс параллельного запроса;
    · Uncompressed when accessed - восстановление сжатых данных при доступе к ним;
    · During direct read blocks are uncompressed immediately - во время прямого чтения блоки восстанавливаются немедленно;
    · Buffer Cache - кеш буферов;
    · Read compressed - чтение в сжатом формате;
    · Compressed Database Table - сжатая таблица базы данных.

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

    КС = #несжатых_блоков/#сжатых_блоков

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

    ЭП = ((#несжатых_блоков-#сжатых_блоков)/#несжатых_блоков)х100

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

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

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

    Схема типа "звезда"

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

    Рис. 5. Коэффициенты сжатия для схемы типа "звезда" и нормализованной схемы

    Рис. 5 иллюстрирует, как хорошо сжимаются данные схемы типа "звезда" и нормализованной схемы. Коэффициенты сжатия для таблицы фактов DAILY SALES и таблицы итогов WEEKLY SALES в схеме типа "звезда" варьируются от 2.9 до 4.0, что приводит к экономии пространства от 67 до 75 процентов. То есть, для сжатого варианта таблицы WEEKLY SALES требуется только 25% дискового пространства и пространства кеша буферов по сравнению с ее несжатым аналогом, тогда как для сжатых вариантов таблиц DAILY SALES и SALES AGGREGATION требуется только 33% ресурсов по сравнению с их несжатыми аналогами. Общая экономия пространства базы данных при сжатии только таблиц фактов, достигнутая на схеме заказчика типа "звезда" приблизительно составляет 67% с коэффициентом сжатия, равным примерно 3.1.  

    Нормализованная схема (эталонные тесты TPC-H и TPC-R)

    В нормализованных схемах для эталонных тестов TPC-H и TPC-R доминируют две таблицы: LINEITEM и ORDERS. Эти таблицы, в которых хранится информация о заказах, похожи на таблицы фактов в схеме типа "звезда", и они содержат приблизительно 75% всех данных. Сжатие таблицы LINEITEM - наибольшее, с коэффициентом сжатия, равным примерно 1.6, тогда как таблица ORDERS сжимается с коэффициентом сжатия, равным примерно 1.2 (см. рис. 5). Это означает, что для сжатых вариантов таблиц LINEITEM и ORDERS требуется приблизительно 60 и 80 процентов от объема несжатых таблиц. Общий коэффициент сжатия для базы данных эталонного теста TPC-H равен примерно1.4, что приводит к экономии пространства, приблизительно равной 29%.  

    Почему схема типа "звезда" сжимается лучше нормализованной схемы эталонного теста TPC-H

    В двух предыдущих разделах мы рассмотрели коэффициенты сжатия и экономию пространства, которые могут быть получены в реальных схемах: схеме типа "звезда" и нормализованной схеме эталонного теста TPC-H. При сжатии всех таблиц фактов и таблиц итогов коэффициент сжатия для схемы типа "звезда" оказался приблизительно равным 3.1, что позволило сэкономить примерно 67% пространства. То есть, пространство, занимаемое базой данных на диске, сократилось в результате сжатия более чем на половину. С другой стороны, для нормализованной схемы, в которой сжимались две самые большие таблицы, эквивалентные таблицам фактов в хранилище данных, коэффициент сжатия оказался приблизительно равным только 1.4, а экономия пространства - 29%.

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

    Рис. 6. Сжатие таблиц схемы типа "звезда" и схемы тестов TPC-H/R

    Внимательное рассмотрение количества различающихся значений в двух столбцах (ADDRESS, REGION_ID) таблицы DAILY_SALES схемы типа "звезда" и в двух столбцах (COMMENT, DISCOUNT) таблицы LINEITEM схемы тестов TPC-H/R, объясняет, почему данные нашего примера схемы типа "звезда" сжимаются лучше данных базы данных тестов TPC-H/R. Для каждого столбца на рис. 6 показано общее количество строк в таблице, общее количество различающихся значений, среднее количество строк в блоке (берется из представления словаря данных DBA_TAB_COL_STATISTICS), вычисленное количество различающихся значений в блоке и измеренное среднее количество различающихся значений в блоке (была исследована репрезентативная выборка из блоков). При вычислении количества различающихся значений в блоке мы предполагаем равномерное распределение строк. То есть, вычисленное количество различающихся значений в блоке равно среднему количеству строк в блоке, если общее количество различающихся значений больше количества строк в блоке, в противном случае, оно равно общему количеству различающихся значений. Рассмотрим следующую равномерно распределенную последовательность номеров {1,2,…5}, которая представляет собой строки, состоящие из одного столбца, значением которого является номер:

     

    Предположим далее, что среднее количество строк в блоке (Block) равно 4. Тогда отображение строк на блоки будет выглядеть следующим образом:

     

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

    Для столбцов схемы типа "звезда" измеренное среднее количество различающихся значений существенно меньше вычисленного количества различающихся значений. Это указывает на то, что данные являются не равномерно распределенными, а кластеризованными. Это очень распространенное явление для таблиц фактов и таблиц итогов в хранилищах данных; почти в каждой среде хранилища данных при периодическом обновлении данных происходит какое-то группирование или сортировка новых данных. Например, ETL-процесс (Прим. ред. ETL - Extraction, Transmission, Loading - технология извлечения, преобразования и загрузки данных.), собирающий новую информацию для таблицы фактов из различных источников, должен перед вставкой данных в таблицу фактов сравнивать и агрегировать их, то есть, сортировать данные. Аналогичная кластеризация данных происходит, естественно, и в таблицах итогов, которая выполняется с помощью группирования данных или специализированных OLAP-операций, таких, как операции суперагрегатного группирования rollup и cube.

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

    Если данные не кластеризуются, как в случае тестов TPC-H и TPC-R, сортировка данных перед их загрузкой может существенно увеличить сжатие. Выбор столбцов для включения в сортировку зависит от их кардинальности и средней длины. В общем, длинные столбцы обеспечивают большее сжатие по сравнению с короткими столбцами. Что касается кардинальности, то оказалось, что сортировка по столбцам с очень низкой кардинальностью, таким, как GENDER (пол) или MARITAL STATUS (семейное положение), менее эффективна по сравнении с сортировкой по столбцам со средней кардинальностью. Оптимальными столбцами для сортировки представляются те, у которых кардинальность на уровне таблицы равна количеству строк в блоке. Сортировка по столбцам, кардинальность которых меньше количества строк в блоке, менее эффективна, поскольку значения столбца уже имеют большую избыточность. Сортировка по столбцам с каким-либо столбцом с более высокой кардинальностью также приводит к более высокому уровню увеличения сжатия.

    Анализ эффективности

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

    Чтобы показать выгоды от сжатия данных для запросов в схеме типа "звезда", мы будем анализировать производительность выполнения 3 запросов типа "звезда", которые также были получены из пользовательской среды примера, рассмотренного в предыдущем разделе. Чтобы показать выгоды от сжатия данных для запросов в нормализованной схеме, мы будем анализировать запросы эталонного теста TPC-H, используя недавно опубликованные результаты теста "TPC-H 100 GB". Полное описание эталонного теста доступно на сайте TPC (www.tpc.org). Заметим, данные об общем затраченном времени "сжатых" прогонов теста извлекаются из опубликованных результатов эталонного теста TPC-H [1], а данные о "несжатых" прогонах получаются во время выполнения фазы настройки теста. В тесте TPC-H выполняется набор из 22 бизнес-запросов, спроектированных для демонстрации функциональных возможностей системы, в некотором смысле представляющих сложные приложения бизнес-анализа [2]. Обсуждение всех 22 запросов далеко выходит за рамки данной работы. Поэтому мы ограничимся обсуждением запросов 1, 6 и 15.

    Примеры запросов типа "звезда"

    В запросах к схеме типа "звезда" используется функциональное средство СУБД Oracle для преобразования запросов типа "звезда" (star transformation). Это преобразование является мощным методом оптимизации запросов типа "звезда", основанном на неявном переписывании исходного текста запроса. Оптимизатор по стоимости СУБД Oracle выбирает преобразование запросов типа "звезда", когда это целесообразно. Преобразование осуществляется с целью эффективного выполнения запросов. СУБД Oracle обрабатывает такие запросы, используя три фазы. Во время первой фазы Oracle обращается ко всем битовым индексам по измерениям, для которых в запросе заданы предикаты. Затем результирующие битовые вектора объединяется с помощью операций над множествами (AND или OR) в зависимости от логики запроса. В этот шаг входит преобразование окончательного битового вектора в идентификаторы строк. Во время второй фазы с помощью этого набора идентификаторов строк из таблицы фактов извлекаются точно те строки, которые необходимы, и выполняется соединение с первым измерением. Во время третьей фазы этот результирующий набор соединяется с таблицами измерений для извлечения детальных данных, необходимых для завершения запроса. Если базовая схема является схемой типа "снежинка" (snowflake schema), то во время этой фазы также выполняется соединение таблиц измерений. Важно отметить, что конечному пользователю никогда не нужно знать какие-либо детали преобразования запросов типа "звезда".

    Запрос типа "звезда" номер 1

    Первый запрос вычисляет итоговую сумму продаж конкретных продуктов за конкретные месяцы 1998 и 1999 годов по конкретным округам. Эти результаты группируются по годам, месяцам, типам округов и продуктам. Полный SQL-оператор можно найти в приложении; на рис. 7 показаны план выполнения оператора и время, затраченное на различных шагах процесса выполнения, как для несжатой таблицы фактов DAILY_SALES, так и для сжатой.

    Рис. 7. План выполнения и время, затраченное на различных фазах выполнения запроса типа "звезда" номер 1

    Надписи на рисунке:

    • Elapsed Times - интервалы общего затраченного времени;
    • Compressed - сжатая;
    • Not Compressed - несжатая;
    • NL - nested loop, соединение типа "вложенный цикл";
    • HJ - hash join, хеш-соединение;
    • Customer - клиент;
    • Time - время;
    • Item - продукт;
    • Local Index Rowid - идентификатор строки из локального индекса;
    • Sales Fact Table - таблица фактов по продажам;
    • Bitmap AND - битовая операция AND;
    • Bitmap merge - слияние битовых векторов;
    • Items-Dimension - измерение Items (продукты);
    • Time-Dimension - измерение Time (время) .

    Оптимизатор СУБД Oracle распознает, что этот запрос - запроса типа "звезда" и преобразует его так, как это было описано выше. Доступ к таблице фактов осуществляется через путь доступа по битовому индексу, базирующемуся на битовой операции AND двух битовых индексов измерений TIME и ITEM. По этим битовым индексам Oracle может эффективно вычислить все квалифицированные идентификаторы строк таблицы DAILY_SALES. Поскольку в таблице фактов содержатся только ссылки на таблицы измерений, а не на их детальные данные, измерения нужно повторно соединять с таблицей фактов. (Прим. ред. Термин повторное соединение ("join back", "join-back", "joinback") определяется в техническом документе [7] следующим образом: "With the transformed SQL, this query is effectively processed in two main phases. In the first phase, all of the necessary rows are retrieved from the fact table using the bitmap indexes… In the second phase of the query (the 'join-back' phase), the dimension tables are joined back to the data set from the first phase." (После преобразования этого SQL-оператора обработка запроса эффективно разбивается на две основные фазы. Во время первой фазы из таблицы фактов с помощью битовых индексов извлекаются все необходимые строки… Во время второй фазы (фазы "повторного соединения") таблицы измерений повторно соединяются с набором данных, полученным в первой фазе.).) Это делается последующими операциями соединения с таблицами ITEM и TIME. В список выборки включены детальные данные из таблицы CUSTOMER, поэтому нам также нужно выполнить соединение с измерением CUSTOMER. Кроме того, в плане выполнения показано время, затраченное на определенных шагах процесса выполнения для сжатой и несжатой таблицы фактов соответственно.

    Запрос типа "звезда" номер 2

    Второй запрос вычисляет сумму объемов продаж, проведенных в первые пять месяцев 1998 и 1999 годов для всех клиентов из Чикаго в конкретных регионах продаж. Результирующий набор группируется по фамилиям клиентов, названиям округов, номерам регионов продаж, годам и месяцам. В отличие от запроса 1 в этом запросе используется материализованное представление WEEKLY_SALES в котором содержатся данные о продажах, агрегированные на уровне недель. Этот SQL-оператор можно найти в приложении. План выполнения запроса и время, затраченное на определенные операции, показаны на рис. 8.

    Этот запрос также преобразовывается функциональным средством оптимизатора СУБД Oracle для преобразования запросов типа "звезда".

    Рис. 8. План выполнения запроса типа "звезда" номер 2

    Надписи на рисунке:

    • Elapsed Times - интервалы общего затраченного времени;
    • Compressed - сжатая ;
    • Not Compressed - несжатая;
    • HJ - hash join, хеш-соединение;
    • Index on Sales Region - индекс по регионам продаж;
    • Time - время;
    • Customer - клиент;
    • Local Index Rowid - идентификатор строки из локального индекса;
    • Weekly Detail Summary Table - таблица итогов продаж по неделям;
    • Bitmap AND - битовая операция AND;
    • Bitmap merge - слияние битовых векторов;
    • Customer-Dimension - измерение Items (продукты);
    • Time-Dimension - измерение Time (время).
    Запрос типа "звезда" номер 3

    Третий запрос - вариант запроса номер 2. Вместо вычисления суммы объемов продаж, проведенных в первые пять месяцев 1998 и 1999 годов, он вычисляет объем продаж за полные два года, 1998 и 1999. План выполнения этого запроса совпадает с планом запроса 2 и явно не показан.

    Обсуждение производительности выполнения запросов типа "звезда"

    (Прим. ред. В данном разделе автор вводит два нетрадиционных для документации Oracle термина: probe table и build table. Происхождение этих терминов можно пояснить, например, следующей фразой из [8] "Hash join uses the smaller input to build a hash table and the larger to probe it" - в хеш-соединениях меньшие по объему данные используются для построения хеш-таблицы, а большие для ее зондирования. Мы будем переводить эти термины как первичная таблица [для построения хеш-таблицы] и вторичная таблица [хеш-соединения] соответственно.

    Мы выполняли описанные выше запросы в несжатой схеме, а затем - в сжатой схеме. На рис. 9 показаны интервалы общего затраченного времени в секундах (Elapsed Time [s]) и коэффициенты повышения производительности для обоих прогонов всех трех запросов типа "звезда" (Q1, Q2 и Q3). Коэффициент повышения производительности вычисляется по следующей формуле:

    где ElaрsedTimeSpeedup - коэффициент повышения производительности, elapsedTime_non_compressed - общее затраченное время в несжатой схеме, elapsedTime_compressed - общее затраченное время в сжатой схеме.

    Каждая пара столбиков показывает интервалы общего затраченного времени для одного запроса. Первый столбик показывает общее затраченное время при выполнении данного запроса в несжатой (non-compressed) схеме, а второй - в сжатой (compressed) схеме. Коэффициент повышения производительности показан в процентах для каждой пары столбиков. Для запроса 1 общее затраченное время уменьшилось на 13%, для запроса 2 - на 15% и для запроса 3 - на 11%. Общее затраченное время на выполнение всех 17 пользовательских запросов уменьшилось на 16.5%.

    Рис. 9. Экономия общего затраченного времени при выполнении запросов к сжатым таблицам

    Повышение производительности выполнения запросов типа "звезда" к сжатой схеме типа "звезда" происходит в результате ускорения доступа к таблицам фактов или таблицам итогов во время второй фазы выполнения запросов. Чем больше коэффициент сжатия, тем большим будет уменьшение общего затраченного времени. Тем не менее, по коэффициенту сжатия невозможно вычислить коэффициент повышения производительности, поскольку последний зависит от количества и заполненности блоков, обрабатываемых запросом. Более того, повышение производительности выполнения запросов зависит от быстродействия подсистемы ввода-вывода. Чем слабее подсистема ввода-вывода, тем больше будет выгод от сжатия.

    Рис. 10. Детальное распределение интервалов времени по различным фазам выполнения запросов типа "звезда"

    На рис. 10 показано распределение интервалов общего затраченного времени выполнения по различным фазам выполнения запросов. Первая фаза выполнения запроса 1 включает в себя доступ к двум битовым индексам (по измерениям CUSTOMER и TIME) и объединение результатов доступа с помощью операции AND. Общее затраченное время выполнения этой фазы приблизительно равно двум секундам независимо от вида схемы, так как эти операции не обращаются к каким-либо сжатым таблицам. Во второй фазе таблица фактов DAILY_SALES соединяется с измерением ITEM (используется хеш-соединение). Большая часть времени выполнения этой фазы затрачивается на просмотр вторичной таблицы хеш-соединения DAILY_SALES, поскольку первичная таблица, используемая для построения хеш-таблицы, ITEM, имеет очень маленький размер. В третьей фазе результирующий набор, полученный во второй фазе, соединяется с двумя измерениями TIME и CUSTOMER. Так же как и в первой фазе, затраченное время выполнения этой фазы приблизительно равно 53 секундам независимо от вида схемы. Небольшие различия во временах выполнения фаз 1 и 3 для "сжатых" и "несжатых" запросов находятся в границах погрешности измерений. Для выполнения запроса 1 к сжатым таблицам требуется приблизительно 472 секунды, а к несжатым таблицам - 544 секунды, общее затраченное время выполнения уменьшилось на 72 секунды или на 13%.

    В этом запросе единственным шагом, в котором используются сжатые таблицы, что приводит к изменению поведения во время исполнения, является обращение к таблице фактов DAILY_SALES. В запросах 2 и 3 - это обращение к таблице итогов WEEKLY_SALES. На рис. 10 для всех трех запросов показаны интервалы времени доступа к битовым индексам (фаза 1), первого повторного соединения (фаза 2), в котором происходит обращение к таблице фактов или таблице итогов, и оставшихся повторных соединений с измерениями (фаза 3).

    Только при первом повторном соединении (соединении с таблицей фактов или таблицей итогов) постоянно видны различия в общем затраченном времени выполнения запросов в случае обращения к сжатым таблицам. В запросе 2, например, разница для этого соединения составляет приблизительно 53 секунды (17%), тогда как при выполнении повторных соединений с другими измерениями особых изменений общего затраченного времени не видно. К таблице фактов, как вторичной таблице в этом хеш-соединении, осуществляется индексный доступ для зондирования квалифицированных строк в измерении CUSTOMER (использованном как первичная таблица). Уменьшение общего затраченного времени для этого хеш-соединения за счет сжатия таблиц ограничивается преимущественно вторичной таблицей. В общем, за счет сжатия больше выгод от операций с большим временем ввода-вывода, чем от операций с небольшим временем ввода-вывода. Следовательно, чем менее ограничивающие предикаты по битовому индексу используются в первой фазе, тем больше квалифицируется строк таблицы фактов или таблицы итогов, что приводит к большей выгоде от сжатия.

    С первого взгляда небольшое уменьшение общего затраченного времени, равное 11%, кажется несогласующимся с большим коэффициентом сжатия таблицы фактов, равным 2.9 (67% экономии пространства). Примите во внимание, что вторая фаза занимает примерно 90% времени выполнения запроса, а экономия пространства, равная 67%, должна была бы уменьшить общее затраченное время приблизительно на 60%. Действительно, это было бы так, если бы читаемые строки объединялись в последовательных блоках. Например, если операция обращается к 5 строкам в 5 разных блоках, размещенных в несжатой таблице с большими промежутками, то независимо от величины коэффициента сжатия, эти 5 строк при сжатии таблицы, вероятно не попадут в один и тот же блок. Следовательно, даже из сжатой таблицы этот запрос будет читать 5 блоков Вот почему операции, в которых не проявляется свойство локальности ссылок, получают меньше выгод от сжатия. Это можно показать, подсчитав количество блоков, необходимых для чтения из сжатой и несжатой таблиц. При доступе к таблице фактов DAILY_SALES запрос 1 читает примерно 92115 блоков в сжатом случае и 94137 блоков в несжатом, разница равна только 2022 блокам или приблизительно 8.15%. Это показывает, что строки, читаемые запросом 1, размещены в таблице с большими промежутками, то есть свойство локальности ссылок отсутствует и, следовательно от сжатия получается меньше выгод (аналогичное поведение наблюдается и у запросов 1 и 2).

    Запросы в эталонном тесте TPC-H

    В этом разделе кратко описаны три запроса в эталонном тесте TPC-H, которые мы будем анализировать в следующих разделах.

    Запрос номер 1 в эталонном тесте TPC-H

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

    Запрос номер 6 в эталонном тесте TPC-H

    Запрос номер 6 обращается только к большой таблице фактов (LINEITEM), выбирая из нее приблизительно 12% строк и возвращая один столбец ответа. Он выполняет совсем немного функций агрегации, что делает его хорошим кандидатом для создания нагрузки на подсистему ввода-вывода.

    Запрос номер 15 в эталонном тесте TPC-H

    Из этой таблицы (1/28 таблицы LINEITEM - данные за 3 месяца) осуществляется выборка по дате и агрегирование по поставщику (Supplier). В результирующем наборе после первого шага агрегирования содержатся данные почти о всех поставщиках. Сам запрос возвращает строку о поставщике, имеющем максимальный доход. Этот запрос возвращает одну строку.

    Обсуждение производительности выполнения запросов в эталонном тесте TPC-H

    На рис. 11 показаны интервалы общего затраченного времени в секундах (Elapsed Time [s]) и коэффициенты повышения производительности выполнения трех запросов к сжатым таблицам в эталонном тесте TPC-H (Q1, Q6 и Q15). Этот рисунок организован так же как и рис. 9. Он показывает, что запрос 1 к сжатой таблице LINEITEM выполняется с относительно небольшим замедлением, которое, вполне возможно, находится в границах погрешности измерений. С другой стороны, запросы 6 и 15 выполняются существенно быстрее. Запрос 6 ускоряется на 35%, а запрос 15 - на 38%.

    Рис. 11. Экономия общего затраченного времени при выполнении запросов к сжатым таблицам

    Суммарный коэффициент повышения производительности вычисляется по следующей формуле:

    где OverallSpeedup - суммарный коэффициент повышения производительности, ElapsedTime_non_compressedQi - общее затраченное время для i-го запроса к несжатой схеме, ElapsedTime_compressedQi - общее затраченное время для i-го запроса к сжатой схеме.

    Суммарный коэффициент повышения производительности выполнения всех 22 запросов к сжатым таблицам по сравнению с запросами к несжатым таблицам в эталонном тесте TPC-H равен приблизительно 10%. Общее затраченное время на тест вставки данных rf1 (Прим. ред. rf - refresh function (функция обновления данных), см. спецификацию стандарта TPC-H [9]) увеличилось приблизительно на 3.9%, тогда как общее затраченное время на тест удаления rf2 уменьшилось приблизительно на 17%. Основная метрика теста TPC-H, QphH@100GB (Прим. ред. QphH - Query-per-Hour Performance Metric, метрика количества запросов, обрабатываемых тестируемой системой в течение часа) увеличилась приблизительно на 10%. При выполнении некоторых запросов мы наблюдали незначительное увеличение потребляемого времени центрального процессора. Как было продемонстрировано в разделе об экономии пространства, данные теста TPC-H сжимаются с коэффициентом сжатия, равным только 1.2 для таблицы ORDERS и 1.6 для таблицы LINEITEM. Запросы же к сжатой базе данных выполняют на 27% меньше обращений к дискам.

    Общее затраченное время выполнения запроса номер 1 увеличилось приблизительно на 2%. Это можно объяснить незначительным увеличением использования процессора, а также тем фактом, что этот запрос требует интенсивной работы процессора. Общие затраты при запросе к сжатым таблицам увеличиваются приблизительно на 2%. При выполнении запроса нет свободного времени процессора, поэтому общее затраченное время выполнения запроса увеличивается примерно на тот же объем времени, что и время процессора. Большое уменьшение использования диска, равное приблизительно 38%, очень мало влияет на этот запрос, так как для него дисковая подсистема не является узким местом.

    Производительность выполнения запроса номер 6 увеличивается приблизительно на 35%. В этом запросе интенсивно выполняются операции ввода-вывода, поэтому доступное время процессора не используется. Следовательно, повышение нагрузки на процессор может быть легко компенсировано без деградации производительности системы.

    Так же как и запрос номер 6, запрос номер 15 выгадывает от сжатия таблиц, показывая 38% повышения производительности. В этом запросе не используется некоторое доступное время процессора, что приводит к уменьшению общего затраченного времени выполнения запроса приблизительно на 8%. Этот запрос больше выгадывает от сжатия, читая из сжатой базы данных на 42% меньше данных по сравнению с несжатой базой данных.

    Использование сжатых таблиц в тесте TPC-H в нашей системной конфигурации уменьшает общее затраченное время выполнения большинства запросов. Но повышает общее затраченное время выполнения оставшихся запросов. Запросы с интенсивными операциями ввода-вывода имеют прямую выгоду от сжатия, поскольку в них уменьшается среднее потребление ресурсов, которые ограничивают повышение производительности системы (то есть "узких мест"), а именно, дисковой подсистемы. Запросы с интенсивным потреблением времени процессора также получают выгоду от меньшей нагрузки на дисковую подсистему. Тем не менее, накладные расходы доступа к сжатым таблицам могут "вредить" общему затраченному времени выполнения этих запросов.

    Лучшие практические методы

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

    Можно сжимать некоторые или все секции секционированных таблиц. Например, в больших хранилищах данных, в которых данные обычно секционированы, можно для максимизации использования пространства сжимать секции, к которым нет частого доступа. Если однако секция модифицируется ETL-процессом (Прим. ред. ETL - Extraction, Transmission, Loading - технология извлечения, преобразования и загрузки данных), то следует оценивать накладные расходы сжатия.

    Для увеличения коэффициента сжатия данные таблиц перед их сжатием могут быть отсортированы. Тем не менее, во многих случаях, как обсуждалось ранее, данные приложений поддержки принятия решений кластеризуются естественным образом, следовательно, очень хорошие коэффициенты сжатия получаются без дополнительных усилий. Дополнительные рекомендации по использованию нового механизма сжатия в СУБД Oracle можно найти на веб-сайте Oracle Technology Network (http://otn.oracle.com).

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

    Заключение

    Стоимость дисковых подсистем может составлять очень большую часть стоимости создания и сопровождения больших хранилищ данных. СУБД Oracle9i Release 2 помогает уменьшить эту стоимость с помощью сжатия данных, хранимых в базе данных Oracle, и это делается без типичной проблемы выбора между экономией пространства и временем доступа к данным.

    Коэффициент сжатия, которого можно достигнуть, зависит от данных и их распределения. В наших тестах, рассмотренных выше, были показаны коэффициенты сжатия от 1.2 до 4.0 (от 17% до 67% экономии пространства). Тем не менее, наблюдались и более высокие коэффициенты сжатия. Например, сжатие детализированных данных разговоров большой телекоммуникационной компании дало в результате коэффициент сжатия, равный 12 (92% экономии пространства). Среди результатов пользовательских тестов это был самый высокий достигнутый коэффициент сжатия. Коэффициент сжатия, равный 5 (80% экономии пространства) был достигнут на агрегированных данных о продажах различным клиентам в различных отраслях промышленности.

    Анализируя запросы к схеме типа "звезда" и 3NF-схеме, мы показали, что сжатие таблицы может привести к значительному повышению производительности выполнения запроса. Более всего это характеризуется уменьшением дискового ввода-вывода и незначительным временем восстановления данных, требуемым для доступа к данным сжатых таблиц.

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

    Пример запросов к схеме типа "звезда" показывает повышение производительности приблизительно на 12-16%, что приводит к общему повышению производительности выполнения всех 17 запросов приблизительно на 16.5%. С другой стороны, повышение производительности запросов в эталонном тесте TPC-H достигает 38%. Общее повышение производительности всех запросов в тесте TPC-H достигает 10%.

    Сжатие таблиц в СУБД Oracle9i Release 2 существенно уменьшает потребности в дисковом пространстве и кеше буферов. Для этого не требуются какие-либо изменения в приложениях.

    Ссылки

    [1] TPC-H 100 GB published 07/15/02 by HP/Oracle on Alpha Server ES45 and Oracle 9iR2 Executive Summary:
    http://www.tpc.org / results / individual_results / HP / es45_ 5578_es.pdf FDR
    http://www.tpc.org / results / FDR / tpch / es45_5578_fdr.pdf.

    [2] Oracle9i Data Warehousing Guide Release 2 (9.2) Part Number A96520-01.

    [3] Date C.J. "An Introduction to Database Systems", Reading, Mass., Addision Wesley Verlag, 1981. (Прим. ред. Имеется русский перевод: Дейт К. "Введение в системы баз данных". 6-е изд. - М: Вильямс, 1999.

    [4] Ziv J. and Lempel A. "A Universal Algorithm for Sequential Data Compression'', IEEE Transactions on Information Theory, Vol. 23, pp. 337--342, 1977.

    Ссылки к примечаниям редакторов русского перевода

    Приложение - SQL-запросы

    Запрос типа "звезда" номер 1
    SELECT T.year, T.month, C.district, I.name,
    SUM(sales)
    FROM customers C,
    daily_sales S,
    items I,
    time T
    WHERE S.item_nr=I.item_nr
    AND S.addr_id=C.addr_id
    AND S.date=T.date
    AND T.year in (1998, 1999)
    AND T.month in (1-05-1998, 1-06-1998, …
    …, 1-03-1999)
    AND C.district in ('CO', 'CA')
    AND I.group = 'classic'
    AND I.item = 'blue gravave'
    GROUP BY T.year, T.month, C.district, I.name;
    
    Запрос типа "звезда" номер 2
    SELECT C.district, C.name,
    T.year, T.month,
    R.region_number,
    SUM(sales)
    FROM customers C,
    time T
    weekly_sales S,
    sales_region R
    WHERE S.region_id=R.region_id
    AND S.addr_id=C.addr_id
    AND S.date=T.date
    AND T.year in (1998, 1999)
    AND T.month in (1,2,3,4,5)
    AND C_district = 'Chicago'
    AND R.region_number in ('234','4565','111','1')
    GROUP BY
    C.district, C.name, T.year, T.month,
    R.region_number;
    


    Опубликовал admin
    15 Ноя, Понедельник 2004г.



    Программирование для чайников.