Удмуртский Государственный Университет реферат

Вид материала

Реферат

Содержание Sql> select /*+ index прод_пк*/ ид from продавцы where ид =110; (1) Select /*+ index офис*/ офис_код from продавцы where офис_код =и01;) Ранжирование методов доступа Метод доступа Индекс на основе одного столбца Объединение с сортировкой и слиянием Sql> select ид from продавцы where ид >=110; (2) Анализ запросов с целью повышения скорости их выполнения Sql> select ид from продавцы where должность='менеджер'', (4) Sql> alter session set optimizer_goal= Метод извлечения строк метода доступа

Подобный материал:

• «Удмуртский государственный университет», 302.5kb.
• Удмуртский Государственный Университет реферат, 203.39kb.
• Удмуртский Государственный Университет (наименование учебного заведения) Кафедра Информационной, 225.78kb.
• Удмуртский Государственный Университет Институт экономики и управления Кафедра финансов, 1976.72kb.
• Фгбоу впо «Удмуртский государственный университет», 68.29kb.
• Контрольная работа экз вопросы фгбоу впо «удмуртский государственный университет», 1136.39kb.
• Удмуртский государственный университет, 70.06kb.
• 57. Шквырина Анжелика Вячеславовна заявка на участие в I международном симпозиуме, 30.69kb.
• Субъективные основания социальной реальности: пространство смысла 09. 00. 11. социальная, 219.67kb.
• Представления об обществе в картине мира населения древней руси XI xiii вв. 07. 00., 653.67kb.

Министерство образования РФ

Удмуртский Государственный Университет


Реферат:

Настройка приложений под Oracle.

Оптимизация запросов Oracle.


Выполнил: студент гр.38-41

Сергеев А.С.

Проверил: Вотинцев А.А.


Ижевск - 2003


Введение


Понятие настройки производительности приложения (application tuning) относится к на­стройке используемых в приложении SQL-команд. Если приложение обладает графическим интерфейсом пользователя, то настройка приложения заключается также в настройке анало­гов SQL-команд. Иначе говоря, все действия, выполняемые на уровне графического интер­фейса пользователя, могут быть отображены на соответствующие SQL-команды (SELECT, INSERT, UPDATE и DELETE) различной сложности. В любом случае рекомендуется сначала настроить используемое приложение, а затем приступить к настройке остальных компонентов СУРБД.

По разным субъективным оценкам общая производительность системы в среднем на 80% зависит от приложения, а остальные 20% приходятся на собственно базу данных. Однако следует отметить, что такое распределение ответственности за производительность системы соблюдается не всегда. Иногда, все может быть как раз наоборот — основное влияние на про­изводительность СУРБД могут оказывать не приложения, а базы данных.

Для администратора баз данных это означает, что производительность СУРБД зависит от конкретной ситуации и является величиной относительной. Несмотря на эти замечания, все же нужно еще раз подчеркнуть основное правило, которого следует придерживаться при вы­полнении настройки:

Прежде всего, необходимо настроить приложение. Необходимость выполнения этого условия определяется двумя причинами. Во-первых, производительность большинства современных СУРБД во многом определяется производи­тельностью используемых приложений. Во-вторых, даже если это не так, администратор ба­зы данных может классифицировать тип приложения, используемого для конкретной СУРБД. Это значит, что администратор должен просмотреть приложение и выделить в нем все, что касается транзакций: код доступа и SQL-код. Даже если код приложения является достаточно эффективным с точки зрения доступа к базам данных, этот анализ поможет глубже разо­браться в используемых методах взаимодействия программы с базами данных.

Необходимо сосредоточить основное внимание на тех 20% текста программы, которые ответственны за 80% производительности всего приложения. А затем, если будет время и по­зволит руководство, можно приступить к работе над остальной частью приложения и попы­таться извлечь из него максимум возможного.

Оптимизатор

Оптимизатор представляет собой программный продукт, который является частью СУРБД и предназначен для оптимизации — поиска наиболее эффективного способа доступа SQL-кода к данным. Для оптимизатора следует выбрать такую последовательность доступа, которая обеспечит самый эффективный в рамках СУРБД Oracle путь доступа к данным и формирование плана выполнения, основанного на найденных методах. Под методом досту­па (access path) в нашем случае подразумевается вариант алгоритма доступа к затребованным из базы данным, а под планом выполнения (execution plan) — последовательность выполняе­мых действий, которые сопровождают выбранные методы доступа.

Технология оптимизатора Oracle, а также технологии, используемые для оптимизаторов дру­гими разработчиками программного обеспечения, значительно развились за несколько послед­них лет. При работе с ранними версиями Oracle программисту приходилось быть очень внимательным при разработке SQL-кода, поскольку упорядочение операторов внут­ри SQL-выражения могло в значительной степени повлиять на его производительность. Анало­гичную работу по оптимизации приходилось выполнять и администратору базы данных. Одна­ко, чем лучше используемый оптимизатор, тем меньше оптимизирующих действий приходится выполнять пользователям, программистам и администратору базы данных; соответственно не­обходимая работа по оптимизации в OracleSi по сравнению с предыдущими версиями сущест­венно уменьшилась. Тем не менее даже при наличии широкого круга современных оптимизато­ров программистам следует знать основные принципы оптимизации и при необходимости уметь их применять. Однако современным программистам уже не нужно иметь те глубокие знания, которые были необходимы ранее для выполнения оптимизации вручную...

Существует два основных вида оптимизаторов.

• Оптимизатор, основанный на анализе заданных правил (rule-based optimizer). Этот оп­тимизатор выбирает методы доступа на основе предположения о статичности СУРБД и в соответствии с заданной разработчиком системой правил выбора методов доступа к данным. Хотя приоритеты разных правил зависят от самого разработчика, их можно довольно легко сравнить и найти соответствие между результатами, полученными с помощью разных оптимизаторов.

• Оптимизатор, основанный на анализе затрат (cost-based optimizer). Для этого оптими­затора выбор метода доступа основан на хранимой внутренней статистике, которая обычно используется совместно со словарем данных СУРБД. Администратор базы данных должен время от времени выполнять специальные процедуры обновления этих статистических данных.

В Oracle версий 7.х и выше оптимизаторы могут работать по принципу анализа, как правил, так и затрат одновременно. В Oracle б.х работа оптимизатора основана на анализе правил, и в полной мере реализовать технологию анализа затрат он не может. Во всех этих версиях для управления оптимизатором, программисту предоставляется возможность использовать специализированные указания (hint), которые размещаются непосредственно в строках кода SQL-выражения. Они принимаются во внимание оптимизатором, хотя и не всегда выполняются, поскольку носят реко­мендательный характер. При отсутствии синтаксических ошибок оптимизатор обычно следует по­лученному указанию. Приведем пример выражения SELECT, содержащего указание оптимизатору об использовании индекса для таблицы, из которой извлекаются данные.


SQL> SELECT /*+ INDEX ПРОД_ПК*/ ИД FROM ПРОДАВЦЫ WHERE ИД =110; (1)


Заметим то, что указание следует сразу же после SQL-команды (SELECT, INSERT, UPDATE, DELETE). Указание для оптимизатора, как и любой другой комментарий SQL*Plus, начинается с символов /*, за которыми следует знак +, и заканчивается символами */. В данном примере предполагается, что для указанной таблицы существует только один индекс (первичный ключ); для задания конкретного индекса из нескольких имеющихся в указании следует использовать имя таблицы и имя нужного индекса.(SQL> SELECT /*+ INDEX ОФИС*/ ОФИС_КОД FROM ПРОДАВЦЫ WHERE ОФИС_КОД =И01;)

Оптимизатор воспринимает только одно указание для данного выражения или блока выражений; все остальные будут проигнорированы. Кроме того, все указания с неверным синтаксисом также игнорируются без вывода какого-либо сообщения об ошибке. Чтобы выявить факт игнорирования указания оптимизатором, необходимо воспользоваться планом выполнения, который более под­робно описывается ниже

Ранжирование методов доступа

Оптимизатор Oracle при работе использует приведенные в таблице 1 ранги методов досту­па – от самого быстрого в верхней части до самого медленного в нижней части этого перечня.

Таблица 1

Ранги	Метод доступа
1	Одна строка по ее идентификатору
2	Одна строка по объединению кластеров
3	Одна строка по хэш-ключу кластера с уникальным или первичным ключом
4	Одна строка по уникальному или первичному ключу
5	Объединение кластеров
6	Кэш-ключ кластера
7	Индекс кластера
8	Составной индекс
9	Индекс на основе одного столбца
10	Ограниченный диапазон поиска по индексированным столбцам
11	Неограниченный диапазон поиска по индексированным столбцам
12	Объединение с сортировкой и слиянием
13	Поиск максимального или минимального значения по индексированным столбцам
14	Упорядочение по индексированным столбцам
15	Полное сканирование таблицы

Таким образом, если оптимизатор основан на анализе правил, то он будет выбирать из всех доступных для данного запроса методов самый быстрый. Покажем тот же запрос, который был приведен ранее, но без указания для оптимизатора и с изменением оператора WHERE. (Запрос с неограниченным диапазоном поиска без)


SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >=110; (2)


При использовании в Oracle стратегии, основанной на анализе правил, и наличии первич­ного ключа поля ИД таблицы ПРОДАВЦЫ, для оптимизации кода в (1) бу­дет применен метод доступа 11 (неограниченный диапазон поиска по индексированным столбцам). Неограниченный диапазон поиска проявляется в том случае, если в операторе WHERE использован один из операторов сравнения, за исключением проверки равенства (неравенства) — "больше" (>), "больше или равно" (>=), "меньше" (<) и "меньше или равно" (<=). В результате диапазон поиска ограничивается только с одной стороны— сверху или снизу. Вторая граница диапазона поиска до начала выполнения запроса неизвестна.

Если оператор WHERE содержит ограниченный диапазон поиска (т.е. содержит два услов­ных оператора — по одному с каждой границы диапазона), то обе границы диапазона будут известны еще во время синтаксического анализа выражения. Превратить неограниченное WHERE в ограниченное можно и с помощью литерала максимального значения (если оно из­вестно) или с помощью предполагаемого (теоретического) максимального значения для столбца, исходя из априорных знаний о данных в столбце. Однако использование оператора МАХ (оператора определения максимального значения) наряду с исходным неограниченным диапазоном поиска крайне нежелательно, так как в табл. 1 ранг этого метода поиска (13) ниже ранга неограниченного (11) и ограниченного (10) диапазонов поиска. Использование оператора МАХ для поиска максимального значения приведет только к замедлению выполне­ния запроса.


SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >=103 and ИД <=110; (3)


5QL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >103 AND ИД <=MAX(ИД);

Анализ запросов с целью повышения скорости их выполнения

Вся работа, связанная с попытками изменения формулировки запросов для повышения эффективности их выполнения, называется коррекцией запросов (query rewriting). Приведенные примеры являются достаточно стандартными, и с их помощью можно понять основные принципы оптимизации. Например, для извлечения максимального значения из столбца потребуется время, сравнимое со временем полного сканирования таблицы — независимо от того, индексирован ли этот столбец. Это становится ясным при ознакомлении с таблицей рангов методов досту­па. Однако существуют менее очевидные и более экзотические способы коррекции запросов. Теперь, после знакомства с работой оптимизатора на основе анализа правил, можно при­ступить к рассмотрению работы оптимизатора, основанного на анализе затрат, на примерах, показанных далее:


SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ='МЕНЕДЖЕР'', (4)

SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ=' ПРОДАВЕЦ '', (5)


Допустим, что приведенные запросы созданы для достаточно большой торговой организации с 10 менеджерами, 1000 продавцов и общим числом сотрудников — около 6000. Анализируя эти запросы, можно заключить, что из двух типов оп­тимизации лучше выбрать оптимизацию, основанную на анализе затрат, а не оптимизацию, ос­нованную на анализе правил. Действительно, если применяется оптимизация, основанная на анализе правил, то при наличии неуникального индекса по столбцу ДОЛЖНОСТЬ для обоих запросов будет выбран метод доступа 9 (индекс на основе одного столбца). С другой стороны, при использовании оптимизации, основанной на анализе затрат, знание некоторых характери­стик распределения данных (например, того, что строки с данными о продавцах составля­ют 1/6 всех строк, а строки с данными о менеджерах составляют 1/600 часть всех строк) позволяет применять неуникальный индекс для запроса в (4). Однако для выполнения запроса в (5) будет уместно и эффективно полное сканирование таблицы (т.е. исполь­зование метода доступа 15). Таким образом, выбор вариантов методов доступа базируется на априорной информации о конкретной структуре данных.

При необходимости доступа к значительной части строк какой-либо таблицы полное ска­нирование является более эффективным, чем индексное. Дело в том, что для сканирования индекса и извлечения строки требуются, по крайней мере, две операции чтения для каждой строки, а в некоторых случаях и больше — в зависимости от количества уникальных данных в индексе. А при полном сканировании таблицы для извлечения строки требуется только од­на операция чтения. При доступе к большому количеству строк — как, например, в запросе (5) — становится очевидной неэффективность использования индекса по срав­нению с полным сканированием таблицы, при котором строки считываются непосредственно из таблицы. Помимо уменьшения общего количества операций чтения записей таблицы, пол­ное сканирование при определенных обстоятельствах обладает еще одним преимуществом по сравнению с индексным сканированием. Дело в том, что большое количество строк таблицы часто требуется извлекать последовательно или в порядке, близком к последовательному. Очевидно, что если считывается индекс, потом таблица, потом опять индекс, а потом опять таб­лица, последовательный порядок извлечения данных соблюдаться не будет. Таким образом, для запроса, показанного в (5), использование индекса только замедлит выполнение.

Использование индекса гораздо предпочтительнее полного сканирования таблицы при работе с запросом, показанным в (4). В этом случае потребуется извлечь незначи­тельное количество строк (10), а общее количество операций чтения будет равняться 20 ( т.е. по 2 операции чтения для извлечения каждой из 10 строк). При использовании полного скани­рования таблицы придется выполнить

6000 операций чтения, поскольку при этом неизвестно точное расположение искомых строк и для их поиска придется просмотреть всю таблицу. За­прос, приведенный в (4), является классическим примером случая, когда необхо­димо использовать индекс, особенно если такой запрос выполняется часто. Из этого срав­нительного анализа можно сделать следующий вывод. Пользователь и программист, которые привыкли применять оптимизацию только на основе анализа правил, могут даже не догады­ваться о существовании более эффективного способа выполнения запроса (5), выбор которого основан на оптимизации согласно анализу затрат.


Задание режима оптимизации


Одним из важнейших вопросов при работе с оптимизатором является способ выбора ре­жима его работы. Дело в том, что описанные выше виды оптимизации могут быть заданы на уровне экземпляра, сеанса или выражения. Для указания режима оптимизации на уровне эк­земпляра в файле параметров init.ora следует использовать приведенные ниже значения па­раметра OPTIMIZER_MODE.

• CHOOSE. При установке этого значения будет выбрана оптимизация, основанная на ана­лизе затрат, при наличии у администратора базы данных соответствующих статисти­ческих данных. В противном случае будет использована оптимизация, основанная на анализе правил.

• RULE. При установке этого значения будет использована оптимизация, основанная на анализе правил.

• FIRST ROWS. Это значение используется для минимизации времени отклика, т.е. для сведения к минимуму временного интервала между вводом запроса в СУРБД и появ­лением результатов на экране. При этом будет выбран вариант оптимизации, основан­ный на анализе затрат (при наличии соответствующих статистических данных). Это значение следует использовать только в интерактивном приложении с множеством экранных форм вывода информации. Типичный пример— OLTP-системы и некото­рые DSS-системы.

• ALL ROWS. При установке этого значения будет использована оптимизация, основанная на анализе затрат (опять же, при наличии соответствующих статистических данных) для минимизации общего количества строк, проходящих через систему за единицу времени (в транзакциях за секунду). Это значение следует использовать при работе с системами пакетной обработки или большими DSS-системами.

Для указания режима оптимизации на уровне сеанса следует применить показанное ниже DDL-выражение, в котором для параметра режим задается одно из приведенных выше значе­ний (CHOOSE, RULE, FIRST_ROWS, ALL_ROWS):

SQL> ALTER SESSION SET OPTIMIZER_GOAL=<;>

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

Для задания режима оптимизации на уровне выражения следует использовать перечис­ленные в табл. 2 ключевые слова методов доступа либо использовать одно из при­веденных выше значений (CHOOSE0 RULE, FIRST ROWS,ALL ROWS).


Таблица 2

Ключевое слово	Метод извлечения строк метода доступа
ROWID	Для извлечения строк используется их идентификатор
CLUSTER	Сканирование ключа кластера
HASH	Сканирование хэш-индекса
INDEX	Сканирование индекса
INDEX_ASC	Сканирование индекса в порядке возрастания
NDEX_DESC	Сканирование индекса в порядке убывания
AND_EQUAL	Использование нескольких индексов со слиянием результатов
ORDERED	Использование порядка таблиц, указанного в предложении FROM, в качестве порядка их объе­динения
USE_NL	Использование вложенных циклов для объединения таблиц USE_MERGE Использование сортировки со слиянием для объединения таблиц FULL Полное сканирование таблицы

В Oracle есть средства позволяющие отслеживать метод выборки данных из таблицы, и режим работы оптимизатора (например Explain Plan в PL/SQL). Ниже на рисунке показано Explain Plan Window в котором видна распечатка запроса (select * from продавцы) и отображено следующее: режим оптимизации на уровне сеанса CHOOSE (также может быть RULE, FIRST_ROWS, ALL_ROWS), способ доступа к данным TABLE ACCESS FULL (полный доступ к таблице), а также название объекта и пользователь.



Ниже показан рисунок Explain Plan Window для запроса (select * from продавцы where ИД=105)


В данном случае: режим оптимизации на уровне сеанса RULE, способ доступа к данным TABLE ACCESS BY ROWID (доступ к таблице по идентификатору колонки), объект ПРОД_РК (идентификатор таблицы).


Заключение.

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

Замечания:

1. введение не проработано, взято без модификации, некоторые фразы введения не очень корректны
   
2. недостаточно хорошо описаны hint, желательно более подробно описать каждый hint и привести примеры.
   
3. Не очень последовательное изложение материала.
   
4. Добавить больше примеров.
   
5. Не описана настройка приложений?