Июль/Август 2003 Профессионалу администратору Джонатан Льюис Оптимизация по стоимости в СУБД Oracle9i (How Cost-based Optimisation works in 9i, by Jonathan Lewis, JL Computer Consultancy (UK)) Источник: доклад на конференции OracleWorld-2002, Copenhagen, Denmark, июнь 2002, http://www.oracle.com/pls/oow/oow_user.show_public?p_event=12&p_type=session&p_session_id=15281 Эксперимент Всегда немного рискованно диктовать читателю, как поведет себя СУБД при работе с заданным набором данных – существует столько различий в установках параметров и используемых функциональных средствах, что в двух "одинаковых" ситуациях поведение СУБД будет совершенно различным. Тем не менее создайте две таблицы, как это показано ниже (идеально в табличном пространстве с размером блоков данных, равным 4 Кб, если в вашей базе данных стандартный размер блоков данных равен 8 Кб). Затем по столбцу N1 создайте для них индексы и с помощью оператора ANALYZE TABLE с параметром COMPUTE STATISTICS соберите статистики по этим таблицам. В результате вы, вероятно, построите хорошую платформу для начала исследований оптимизатора по стоимости (Cost Based Optimiser, CBO). create table t1 as select trunc((rownum-1)/15) n1,rpad(rownum,100) v1 from all_objects where rownum <= 3000; create table t2 as select mod(rownum,200) n1, rpad(rownum,100) v1 from all_objects where rownum <= 3000; Создав таблицы, заполненные данными, и индексы, вы можете убедиться в идентичности двух наборов данных, выполнив для этого следующие запросы: select * from t1 minus select * from t2; -- Не возвращается никаких строк. select * from t2 minus select * from t1; -- Не возвращается никаких строк. select * from t1 intersect select * from t2; -- Возвращается 3000 строк. Теперь выполните следующую пару запросов и проверьте пути доступа к базе данных при выполнении этих запросов (execution paths): select * from t1 where n1 = 45; select * from t2 where n1 = 45; Вероятно, вы обнаружите, что несмотря на идентичность двух запросов и одинаковое количество возвращаемых строк (пятнадцать), пути доступа к базе данных будут разными. В запросе таблицы T1 будет использоваться индекс, а в запросе таблицы T2 будет выполняться просмотр таблицы (tablescan), и это несмотря на то, что в обеих таблицах содержатся совершенно одинаковые наборы данных. Почему? Ответ заключается в способе размещения данных в таблицах. В таблице T1, созданной с использованием функции trunc(), все пятнадцать запрошенных строк (targeted rows) размещаются в одном и том же блоке, а в таблице T2, созданной с использованием функции mod(), пятнадцать запрошенных строк разбросаны по пятнадцати разным блокам. Заметим: здесь нет перекоса по частоте использования различных значений (в обеих таблицах каждое значение от 1 до 200 встречается точно пять раз). Следовательно, СУБД Oracle с помощью индексов может совершенно точно оценить количество различных блоков, к которым нужно обратиться для извлечения данных: Путь доступа Количество блоков Таблица T1 по индексу 1 блок индекса + 1 блок таблицы, всего 2 блока Таблица T2 по индексу 1 блок индекса + 15 блоков таблицы, всего 16 блоков Мои таблицы размещаются в 96 блоках, а значение параметра db_file_multiblock_read_count (количество блоков, которые будут прочитаны за один вызов системной операции чтения) равно 8. Таким образом СУБД Oracle предполагает, что она может просмотреть всю таблицу, выдав только 12 (ceil(96/8)) запросов на чтение (в некоторых версиях СУБД Oracle требуется еще одна дополнительная операция чтения для извлечения блока с заголовком сегмента). На самом деле в самых последних версиях СУБД Oracle параметр db_file_multiblock_read_count для вычисления стоимости корректируется с помощью экспоненциальной формулы или, возможно, геометрической типа "убывающей" функции, поэтому, если вы установите значение этого параметра, равное 8, СУБД Oracle будет использовать (в версиях 8.1.7 и 9.0.1, по крайней мере) скорректированное значение, близкое к 6,59. Итак, в моей версии СУБД Oracle оценивается, что для полного просмотра таблицы потребуется 15 вызовов операций чтения. Алгоритм оценки стоимости в СУБД Oracle начинает просто с оценки количества запросов на чтение, необходимых для извлечения данных (при обращении к индексам каждый доступ к блокам индекса считается реальным физическим запросом на чтение). Следовательно, стоимость индексного пути доступа к таблице T1 по оценке СУБД будет равна 2, стоимость индексного пути доступа к таблице T2 будет равна 16, а стоимость просмотра таблицы T1 или T2 будет равна 15. Итак, СУБД Oracle будет использовать индексный доступ к таблице T1 (так как 2 меньше 15) и просмотр таблицы T2 (так как 16 больше 15). Алгоритмы соединения Рассмотрев в общих чертах то, как СУБД Oracle оценивает индексный путь доступа, можно приступить к исследованию вопроса, почему мы можем столкнуться с неожиданным увеличением количества просмотров таблиц после перехода к более новой версии СУБД Oracle или перехода от оптимизации по синтаксису (Rule-based) к оптимизации по стоимости (Cost-based). Соединения типа Nested Loop (вложенный цикл) Соединение типа nested loop выполняется таким способом, который интуитивно известен среднему программисту. Для соединения таблицы tablex с таблицей tabley сначала выбираются строки из tablex, а затем для каждой строки, выбранной из tablex, ищутся строки в tabley. Следовательно, общая стоимость данного соединения будет равна: стоимости поиска в таблице tablex плюс стоимости одной операции поиска в таблице tabley, умноженной на количество строк, которые были найдены в таблице tablex. В качестве примера возьмем данные выше статистики для таблиц T1 и T2: мы показали, что стоимость индексного доступа к таблице T1 равна 2, а стоимость просмотра таблицы T2 равна 15. Предположим, мы написали запрос, который соединяет T2 с T1, и при выборке из таблицы T2 мы используем некоторый предикат, который позволяет выбрать 12 строк. Также предположим, что СУБД Oracle правильно оценивает (а это случается часто), что наш запрос таблицы T2 выберет 12 строк, и тогда вычисленная стоимость данного соединения будет равна: 15 (стоимость просмотра таблицы T2) плюс 2 (стоимость одной операции индексного поиска в таблице T1) x 12 (кардинальность выборки из таблицы T2). Общая стоимость: 15 + 12 * 2 = 39. Важно отметить, что при вычислении стоимости СУБД Oracle не делает никаких предположений о кешировании блоков – каждая из 12 операций поиска в таблице T1 рассматривается как операция физического ввода-вывода, а "логический ввод-вывод" отсутствует. Это, весьма вероятно, приводит к большой переоценке. Соединения типа Hash (хэш-соединения) Если мы рассмотрим механизм хэш-соединений, мы обнаружим совершенно другую историю. В хэш-соединениях СУБД Oracle по начальным предикатам определяет таблицу, у которой будет наименьший результирующий набор строк. Затем СУБД извлекает данные из этой таблицы и хэширует их в хэш-таблице (по возможности в оперативной памяти), используя хэш-функцию столбцов соединения и выделяя в хэш-таблице очень большое количество хэш-групп (hash-buckets), чтобы в одной хэш-группе могло оказаться не больше одной строки. После этого СУБД Oracle начинает извлекать данные из второй таблицы (снова используя начальные предикаты), вычисляет для каждой строки хэш-значение и проверяет, есть ли в хэш-таблице соответствующая хэш-группа, содержащая совпадающую строку первой таблицы. В двух шагах извлечения данных в хэш-соединениях теоретически и совершенно независимо может использоваться индексный доступ, однако чаще всего на самом деле в обоих шагах выполняется просмотр таблиц. Мы не будем рассматривать, что происходит, когда наборы данных слишком велики, чтобы полностью разместиться в памяти, в идеальной ситуации стоимость такого соединения равна: стоимости просмотра первой таблицы плюс стоимости (обычно очень небольшая) создания в памяти хэш-таблицы плюс стоимости просмотра второй таблицы. Применяя этот метод к нашим таблицам T1 и T2 и сделав некоторые предположения об объеме возвращаемых данных, мы сможем определить стоимость данного соединения примерно следующим образом: 15 (стоимость просмотра таблицы T2) плюс 1 (стоимость создания очень небольшой хэш-таблицы) плюс 15 (стоимость просмотра таблицы T1). Общая стоимость: 31. Ясно, что СУБД Oracle в данном случае может получить меньшую стоимость выполнения хэш-соединения с просмотром таблиц. Кроме того, эта стоимость, вероятно, достаточно реалистична в одном несомненно известном смысле – табличные просмотры обычно требуют физического ввода-вывода и практически не используют преимущества кеширования данных. Новые возможности Oracle 9i Рассматривая вышеизложенное, вы обратите внимание, что СУБД Oracle делает несколько "наивных" предположений об извлечении данных. Прежде всего, существует неустранимое предположение, что многоблочное чтение будет стоить столько же, сколько стоит чтение одного блока (требуется одно и то же время). Вероятно, это немного нереалистично. Во-вторых, существует жестко запрограммированное предположение о вероятном количестве блоков, используемых в операциях многоблочного чтения. Если вы установите значение системного параметра db_file_multiblock_read_count, равным 64, СУБД Oracle будет предполагать (с целью использования при вычислении стоимости), что она будет просматривать таблицы в режиме многоблочного чтения по (приблизительно) 26 блоков за каждую операцию. И снова это кажется нереалистичным, хотя фактически достижимое количество блоков, прочитанных за одну операцию многоблочного чтения, может со временем изменяться. Более того, СУБД Oracle не различает стоимость ЦП при просмотре каждой строки таблицы и стоимость ЦП при поиске нескольких строк, проверяя небольшое количество элементов индекса. В СУБД Oracle 8.1 начали уделять внимание этим проблемам, введя несколько параметров файла init.ora, которые мы рассмотрим во время презентации. (Прим. пер. Например, параметры OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ. Примеры применения этих параметров приведены на слайдах презентации автора, доступных по указанному в начале статьи веб-адресу. В частности, интересны примеры изменения стоимости индексного доступа по сравнению с полным просмотром таблиц.) В СУБД Oracle 9 пошли еще дальше, разрешив СУБД "изучать" физические" характеристики системы. В частности, вы с помощью пакета dbms_stats можете заставить СУБД Oracle регистрировать интервальные показатели производительности для определения: средней скорости работы ЦП; типичного времени выполнения запроса на чтение одного блока; типичного времени выполнения запроса на многоблочное чтение; типичного количества блоков в запросах на многоблочное чтение, выдаваемых процессами Oracle. Репрезентативные статистики могут собираться в важные, характерные периоды времени, такие, как утро понедельника, типичная ночная обработка пакетных заданий, конец месяца, вторая половина дня в пятницу. Затем СУБД Oracle можно заставить использовать соответствующие наборы статистик для моделирования будущего поведения: введения коэффициентов для "корректного" количества блоков в операциях многоблочного чтения при вычислении стоимости просмотра таблиц и т. д. Заключение В СУБД Oracle 9 АБД получает больше возможностей по информированию оптимизатора по стоимости об истинном функционировании системы. Более того, этот механизм очень прост, он базируется на восприятии аспектов поведения системы с человеческой точки зрения. Это неизбежный шаг для использования стратегий оптимизации в промышленных системах, но исследование этих новых средств оптимизации должно стать важной частью должностных инструкций АБД. Благодарности Материал данной статьи и соответствующая презентация первоначально были представлены в декабре 2001 г. на годовой конференции UKOUG (Ассоциация пользователей СУБД Oracle в Великобритании).