Размещено на http://www.allbest.ru/

Введение

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

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

Причины ресурсоемкости запроса могут быть следующие:

- плохая статистика по таблицам, используемым в запросе;

- отсутствие необходимых индексов;

- отсутствие необходимых хинтов или наличие неправильных;

- неэффективно написанный запрос;

- неправильно настроенные параметры инициализации в базе данных.

Оптимизация запроса является важной частью разработки информационных систем. В связи с этим студенты должны понимать причины неэффективности и уметь оптимизировать SQL-запросы.

Целью данной ВКР является проектирование и реализация системы, позволяющей студентам получать подсказки по оптимизации запросов Oracle, не обращаясь к внутренним средствам СУБД, сложным в изучении и использовании. Система будет использоваться на лабораторных работах курса «Базы данных».

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

Объектом исследования является процесс обучения студентов ИТ-направлений основам настройки производительности в SQL-запросов к БД.

Предметом исследования является оптимизация SQL-запросов в СУБД Oracle.

Целью исследования, выполненного в рамках ВКР, является упрощение процесса поиска проблемных SQL-запросов в СУБД Oracle.

Для достижения поставленной цели требуется решить следующие задачи:

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

2. Изучить возможные способы реализации программного средства, недостатки и достоинства каждого из подходов.

3. На основании полученной теоретической базы спроектировать и разработать программное средство, решающее поставленную задачу.

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

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

В главе «Постановка задачи» описаны теоретические основы. Объясняется принцип работы оптимизатора Oracle. Выполнен аналитический обзор, пояснены основные понятия. Проведен анализ программных средств, которые предоставляют возможность обработки плана запроса и получения подсказок по оптимизации.

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

В главе «Реализация» описана разрабатываемая информационная система и произведено тестирование.

Работа прошла апробацию на научной конференции «Будущее науки-2018 в г.Курск», научно-практической конференции «Наукоемкие исследования как основа инновационного развития общества» в г.Самара и научно-практической конференции «Наука и научный потенциал - основа устойчивого развития общества» в г.Перми.

1. Постановка задачи

1.1 Теоретические основы

информационный запрос диагностика

Все SQL-запросы, которые поступают в СУБД, обрабатываются оптимизатором примерно по одной схеме.

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

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

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

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

Пятый этап обработки запроса представляет собой его реальное выполнение.[7]

Схематично этапы обработки запроса представлены на рисунке 1.1.

Рисунок 1.1 Этапы обработки запроса Oracle

Одним из основных преимуществ реляционных СУБД является механизм запросов на основе декларативного языка запросов SQL. При написании запроса пользователь указывает что он хочет получить. За то, как это получить, отвечает СУБД. Поскольку существует потенциально очень большое множество способов выполнить конкретный запрос (существуют различные комбинации способов и порядка соединения таблиц, путей доступа к данным и т.д.), появляется задача выбрать из всего множества способов выполнения запроса оптимальный. За эту задачу отвечает оптимизатор запросов.

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

- Вычислить выражения и операции;

- Преобразовать SQL-операторы;

- Выбрать способ оптимизации - по стоимости или по правилам;

- Выбрать пути доступа;

- Выбрать порядок соединения таблиц;

- Выбрать методы соединения таблиц;

- Определить наиболее эффективный план выполнения.[7]

В Oracle реализовано два подхода к оптимизации запроса, отличающиеся в выборе критериев оптимизации:

- Оптимизация по правилам (RULE BASED). При этом подходе учитываются только способы доступа к данным с зафиксированными приоритетами по эффективности доступа. Данный подход использовался в ранних версиях ORACLE. Он обладает существенным недостатком - не учитывается реальное распределение данных в базе;

- Оптимизация по стоимости (COST BASED). При этом подходе, помимо эффективности различных путей доступа к данным, учитывается так же статистика по распределению данных и ресурсов операционной системы.

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

1.2 Аналитический обзор по теме ВКР

1.2.1 Основные понятия

Селективность = (макс значение - мин значение отобранных данных)/(макс значение - мин значение всех данных)

в общем случае = отобранный интервал / весь интервал

Весь интервал определяется на основе статистики таблицы, это столбец destiny - разряженность: обратный показатель от кол-ва уникальных значений = 1/num_distinct.

Кардинальность = селективность * общее число записей

Кардинальность - это число отобранных записей.

Стоимость - это наилучшая оценка оптимизатором времени выполнения запроса.

cost = (#SRds*sreadtim+#MRds*mreadtim+#CPUCycles/cpuspeed)/sreadtim,

где #SRds - количество одноблочных чтений,

#MRds - количество многоблочных чтений,

#CPUCycles - количество циклов процессора,

sreadtim - время одноблочного чтения,

mreadtim - время многоблочного чтения,

cpuspeed - количество циклов процессора в секунду.

Таким образом, стоимость - это суммарное время, потраченное на одноблочные и многоблочные чтения и работу процессора, деленное на время одноблочного чтения. Стоимость показывает прогнозируемое время выполнения оператора, выраженное в единицах времени одноблочного чтения.[3]

Параметры оптимизатора.

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

- all_rows - оптимизатором производится поиск плана выполнения, который полностью выполняет оператор в самое короткое время.

- first_rows_N - оптимизатором оценивается число записей, которое будет возвращено. При этом полностью анализируется только первый порядок соединения. При этом способе оценивается необходимая доля набора данных. Затем процесс оптимизации повторяется.

- first_rows - оптимизатором производится поиск наиболее дешового плана для возвращения первой записи результирующего множества.[3]

У оптимизатора существует большое количество путей обработки запроса перед оптимизацией:

- Включение предикатов в представления(predicate pushing)

- Уменьшение уровней вложенности запросов(subquery unnesting)

- трансформация типа "Звезда"(star transformation)

- генерация предикатов при помощи транзитивного замыкания(transitive closure)

- генерация предикатов из ограничений целостности

- перезапись запроса(query rewrite)

План запроса показывает, как произошло преобразование запроса.

Табличное сканирование.

Существует четыре стратегии стоимостной оптимизации сканирования таблиц:

- Традиционная. Представляет собой простой запрос на чтение;

- Системная статистика. Учитывается размер таблицы и время запросов на чтение;

- Системная статистика(2). Учитывается стоимость процессорных ресурсов, размер и время запросов на чтение;

- Системная статистика(3). Учитывается кэширование, стоимость процессорных ресурсов, размер и время запросов на чтение.

Табличное сканирование может производиться при помощи параллельного выполнения(parrallel exeqution), при этом могут использоваться секционированные таблицы(partitioned tables).

Оценка стоимости процессорных ресурсов.

Oracle позволяет собрать системную статистику за определенные периоды времени. Для этого выполняют команду:

execute dbms_stats.gather_system_stats('start');

execute dbms_stats.gather_system_stats('stop');

При использовании команды с параметром 'start' получается начальный снимок показателей. С параметром 'stop' получается конечный снимок. Производится обработка статистики об активности процессора и дисковой подсистемы за промежуток времени между 'start' и 'stop'

Результаты хранятся в sys.aux_stats$. Таким образом можно получить следующие параметры:

CPUSPEED - количество циклов процессора в секунду

SREADTIM - время одноблочного чтения

MREADTIM - время многоблочного чтения

В таблице 1.1 приведена классификация типов подзапросов.[3]

Таблица 1.1 - Классификация типов подзапросов

Категория	Характеристики
Коррелированные/ некоррелированные	Коррелированный подзапрос ссылается на столбцы из внешнего блока запроса. Коррелированные подзапросы часто можно трансформировать в соединения
Простые/сложные	Простые подзапросы содержат одну таблицу. Сложные подзапросы содержат множество таблиц. Простые запросы оптимизатор может трансформировать.
Агрегирующие	У оптимизатора есть ограничения на трансформацию агрегированных подзапросов.
Возвращающие одну запись	Обычно становится отправной точкой в запросе
С выражениями in/exists	Подзапросы с in переписывают в подзапросы с exists, после чего могут быть трансформированы в полусоединения.
С выражениями not in/not exists	Подзапросы с not in переписывают в подзапросы с not exists, после чего могут быть трансформированы в антисоединения. Операция not in не является противоположностью in, т.к. нужно учитывать столбцы с null-значениями

1.3 Анализ программных средств для оценки эффективности SQL-запросов

1.3.1 Обзор программных средств

Программные средства, с помощью которых можно получить планы выполнения запросов, делятся на 2 группы:

1. Средства для получения предполагаемого плана выполнения запроса;

2. Средства для получения реального плана выполнения запроса.

К средствам, при помощи которых можно получить предполагаемый план выполнения запроса, относятся Toad, SQL Navigator, PL/SQL Developer и др. В указанных средствах присутствует вкладка «Explain plan», на которой можно посмотреть план запроса в удобном для восприятия виде. Важно учитывать том момент, что реальный план выполнения может отличаться от того, что показывают эти программные средства.

Также предполагаемый план выполнения запроса позволяет получить команда

explain plan for

select …

Реальный план запроса можно получить в SQL Plus. Для этого используется команда

analize table «имя таблицы» compute statistics

1.3.2 Служебные представления - v$sql, v$sqlarea

Доступ к представлениям v$sql и v$sqlarea по умолчанию запрещен, поэтому администратором БД предоставляется право для разработчиков SELECT ANY DICTIONARY.

В представлении v$sqlarea содержится статистка разделяемой области SQL, по одной строке для каждой строки SQL. В нём содержится статистика по операторам SQL, находящимся в памяти, которые разобраны и готовы для выполнения.[2]

Во время обработки Oracle запрос помещается память. Благодаря этому появляется возможность повторно использовать один и тот же SQL, если это требуется в сеансе, при повторном выполнении этим же или другими пользователями, которым может быть нужен один и тот же оператор SQL. Одним из шагов обработки запроса в Oracle является присвоение уникального SQL_HASH_VALUE и SQL_ADDRESS для каждого SQL-оператора..

Запрос к v$sqlаrea является очень важным, т.к. его можно использовать для оценки производительности системы. Всего один запрос или индекс могут значительно снизить эффективность всей системы. При помощи запроса к v$sqlarea можно определить число обращений к диску (disk_reads), выполняемых при работе программы (содержимое столбца sql_text представления v$sqlarea). Обращением к v$sqlarea можно определить часть запроса, на которой стоит сфокусировать усилия по оптимизации. (Если текст запроса превышает лимит в 2000 символов, который установлен для v$sqlarea, то для того, чтобы увидеть полный текст запроса, потребуется объединить v$sqlarea с представлением v$sqltext).[2]

В таблице 1.2 представлен список столбцов, которые содержит представление V$SQLAREA.

Таблица 1.2 - Параметры V$SQLAREA.

Столбец	Описание
SQL_TEXT	Первая тысяча символов текста SQL текущего курсора.
OPTIMIZER_MODE	Режим, в котором выполнялся данный оператор SQL.
ADDRESS	Адрес указателя на родителя данного курсора.
HASH_VALUE	Хеш-значение родительского оператора в библиотечном кэше.
CPU_TIME	Количество микросекунд процессорного времени, потраченного на обработку SQL.
ELAPSED_TIME	Время в мс, затраченное на полный цикл выполнения запроса - от получения команды до завершения передачи результатов пользователю.

1.3.3 Предполагаемый план запроса

Один из способов получения предполагаемого плана запроса - вкладка Explain Plan в программных средствах SQL navigator и SQL developer. На рисунке 1.2 показан результат обработки запроса:

select * from employees e1, EMPLOYEES e2

where e1.EMPLOYEE_ID = e2.manager_id

and exists (select 1 from departments d, LOCATIONS l

where d.LOCATION_ID = l.LOCATION_ID

and d.DEPARTMENT_ID = e1.DEPARTMENT_ID

and d.LOCATION_ID = 15);

Рисунок 1.2 - Вкладка Explain Plan

Предполагаемый план выполнения запроса с Cost и Cardinality можно также получить, выполнив после анализируемого запроса другой запрос, формирующий план выполнения:

select * from mytable

where owner = 'YKV';

select * from table(dbms_xplan.display_cursor());

Результат выполнения показан на рисунке 1.3.

Рисунок 1.3 - Результат работы функции dbms_xplan.display_cursor()

Дополнительно в плане выполнения запроса выдается значение SQL_ID запроса, который можно использовать для получения реального плана выполнения запроса с набором как основных (Cost, Cardinality), так и дополнительных показателей через запрос:

Select * from v$sql_plan where sql_id='bxtranc0h2mum';

1.3.4 Реальный план запроса

Получим реальный план запроса в SQL Plus при помощи команды

SQL> set autotrace on

SQL> select * from employees e1, employees e2

where e1.EMPLOYEE_ID = e2.manager_id

and exists (select 1 from departments d, LOCATIONS l

where d.LOCATION_ID = l.LOCATION_ID

and d.DEPARTMENT_ID = e1.DEPARTMENT_ID

and d.LOCATION_ID = 15);

Рисунок 1.4 - Реальный план запроса

Реальный план выполнения запроса дают динамические представления Oracle: V$SQL_PLAN и V$SQL_PLAN_MONITOR (последнее представление появилось в Oracle 11g).

План выполнения запроса получается из представления Oracle по запросу:

Select * from v$sql_plan where sql_id='SQL_ID';

где SQL_ID - это уникальный идентификатор запроса, который может быть получен из разных источников, например, из представления V$SQL:

Select sql_id from v$sql

where sql_fulltext like '%уникальный фрагмент текста запроса%';

Получим план запроса

select * from mytable

where owner = 'YKV';

Select * from v$sql_plan where sql_id='bxtranc0h2mum';

Результат выполнения представлен на рисунке 1.5

Рисунок 1.5 - Выборка из представления v$sql_plan

Трассировочный файл -- это еще одно средство получение реального плана выполнения. Это довольно сильное средство диагностики и оптимизации запроса. Для получения трассировочного файла ( в Toad или PL/SQL Developer) запускается PL/SQL блок:

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER,LEVEL 12'

TRACEFILE_IDENTIFIER='YKV_2018'

TIMED_STATISTICS=TRUE SQL_TRACE=TRUE;

---Исследуемый запрос, например,

select * from mytable

where owner = 'YKV';

ALTER SESSION SET SQL_TRACE=FALSE;

где первая, третья и последняя строки являются стандартными, а во второй строке пишется идентификатор (любые символы), который включается в имя трассировочного файла. Так, если в качестве идентификатора напишем 'YKV_2018', то имя трассировочного файла будет включать этот идентификатор и будет иметь вид: oraxxx_xxxxxx_ YKV_2018.trc. Результат пишется в соответствующую директорию сервера, которая находится из запроса

select * from mytable

where owner = 'YKV'

sql_text_length=42

sql=select * from mytable

where owner = 'YKV'

Рисунок 1.6 - План запроса из трассировочного файла

Select value from v$parameter p where p.name= 'user_dump_dest';

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

1.3.5 Работа с SQL Tuning Advisor

Ещё одним из средств получения реального плана выполнения запроса с получением рекомендаций по его оптимизации является средство Oracle SQLTUNE.

Для анализа запроса запускается PL/SQL блок (например, в Toad или PL/SQL Developer) , в котором имеются стандартные строки и анализируемый запрос. Вид блока PL/SQL представлен на рисунке 1.7.[2]



Рисунок 1.7 - Блок PL/SQL для запуска задания DBMS_SQLTUNE

Все строки, кроме текста запроса, являются стандартными.

Далее запускают запрос, который выдает на экран текст рекомендаций:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;

Для работы DBMS_SQLTUNE необходимо из-под SYSTEM выдать права на работу с SQLTUNE схеме, в которой будет запускаться PL/SQL блок. Например, для выдачи прав на схему YKV выдается GRANT ADVISOR TO YKV;

По окончанию работы DBMS_SQLTUNE выдает рекомендации при наличии. В противном случае выдается сообщение об отсутствии рекомендаций. Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т.д.

1.3.6 Расшифровка плана запроса

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

Кроме поиска скачков Cost и CPU Cost в строках плана просматривают первый столбец Operation в плане на предмет наличия в нем соединения HASH JOIN. HASH JOIN приводит к соединению таблиц в памяти и, казалось бы, более эффективным, чем вложенные соединения NESTED LOOPS. Вместе с тем, HASH JOIN эффективно при наличии таблиц, хотя бы одна из которых помещаются в память БД или при наличии соединения таблиц с низкоселективными индексами. Недостатком этого соединения является то, что при нехватке памяти для таблицы (таблиц) будут задействованы диски, что существенно затормозит работу запроса.

В связи этим, при наличии высокоселективных индексов, необходимо проверить, не улучшит ли план выполнения хинт USE_NL. USE_NL приводит к соединению по вложенным циклам NESTED LOOPS. Если план будет лучше, то в запросе необходимо оставить этот хинт. При этом в хинте USE_NL в скобках обязательно должны перечисляться все алиасы таблиц, входящих во фразу FROM, в противном случае может возникнуть дефектный план соединения. Этот хинт может быть усилен хинтами ORDERED и INDEX. Следует обратить так же внимание на MERGE JOIN. При большом CPU Cost в строке с MERGE JOIN стоит проверить действие хинта USE_NL для улучшения эффективности запроса.

Особое внимание в плане следует так же уделить строкам в плане с операциями полного сканирования таблиц и индексов в столбец Operation: FULL -- для таблиц и FULL SCAN, FAST FULL SCAN , SKIP SCAN -- для индексов. Причинами полного сканирования могут быть проблемы с индексами: отсутствие индексов, неэффективность индексов, неправильное их применение. При небольшом количестве строк в таблице полное сканировании таблицы FULL может быть нормальным явлением и эффективнее использования индексов.

Наличие в столбце Operation операции MERGE JOIN CARTESIAN говорит, что между какими-то таблицами нет полной связки. Эта операция возникает при наличии во фразе From трех и более таблиц, когда отсутствуют связи между какой-то из пар таблиц. Решением проблемы может быть добавление недостающей связки, иногда помогает использование хинта Ordered.

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

2.Проектирование системы

2.2 Проектирование структур данных и алгоритмов

2.1.1 Определение требований к программному продукту

Методом интервьюирования были выявлены следующие требования:

Функциональные требования:

- Авторизация;

- Ввод запроса;

- Получение подсказок;

- Просмотр статистики запросов.

Нефункциональные требования

- Система должна работать под операционными системами Windows 7, 8, 10;

- Отклик системы не должен превышать 2 секунды;

- Система должна иметь интуитивно понятный интерфейс.

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

Диаграмма показана на рисунке 2.1



Рисунок 2.1 - Use case диаграмма

На рисунке 2.2 представлена диаграмма переходов состояний (State chart diagram). На диаграмме показаны последовательности состояний и переходы между ними, которые в совокупности описывают поведение системы.

Рисунок 2.2 - Диаграмма переходов состояний

2.1.1 Физическая схема БД

В качестве учебной базы данных была выбрана база работников предприятия. Описание сущностей представлено в таблице 2.1

Таблица 2.1 - Описание сущностей БД

Наименование сущности	Столбец	Тип	Ограничение целостности
Jobs	job_id	number(15)	PK
	job_title	varchar(250)	Not null
	min_salary	number(15,2)	-
	max_salary	number(15,2)	-
Employees	employee_id	number(15)	PK
	first_name	varchar(250)	Not null
	last_name	varchar(250)	Not null
	email	varchar(50)	-
	phone_number	varchar(50)	-
	hire_date	date	Not null
	job_id	number(15)	FK
	salary	number(15,2)	-
	department__id	number(15)	FK
	manager_id	number(15)	FK
Departments	department_id	number(15)	PK
	department_name	varchar(250)	Not null
	location_id	number(15)	FK
Locations	location_id	number(15)	PK
	street_address	varchar(250)	-
	postal_code	varchar(250)	-
	city	varchar(250)	Not null
	country_id	number(15)	FK
Countries	country_id	number(15)	PK
	country_name	varchar(250)	Not null
	region_id	number(15)	FK
Regions	region_id	number(15)	PK
	region_name	varchar(250)	Not null

На рисунке 2.3 представлена физическая схема базы данных Employeers.



Рисунок 2.3 - Физическая схема БД

2.1.2 Основной алгоритм работы системы

Алгоритм работы с системой состоит из следующих этапов:

1 этап - авторизация пользователя. На этом этапе производится проверка корректности заполнения полей и соединение с базой данных. При ошибочном вводе выдается сообщение об ошибке.

2 этап - открывается реестр запросов пользователя.

3 этап - открывается форма ввода SQL-запроса

4 этап - запрос обрабатывается средством DBMS_SQLTUNE и выдаются подсказки по оптимизации.

В графическом виде блок-схема алгоритм работы программы представлен на рисунке 2.4.



Рисунок 2.4 - Блок-схема работы алгоритма

2.1.3 Модели бизнес-процессов

Для наглядного представления бизнес-процессов при работе с оптимизацией запросов Oracle в рамках курса Базы данных были сформированы модели бизнес-процессов.

На рисунке 2.5 изображены наборы сервисов для двух целевых пользователей системы: преподавателя и студента.

Для студента необходимо предусмотреть следующие возможности:

- Авторизация в системе

- Отправка запроса

- Получение результата по оптимизации

Для преподавателя необходимо предусмотреть следующие возможности:

- Авторизация в системе

- Просмотр статистики запросов

Рисунок 2.5 - Набор сервисов для обучения оптимизации SQL-запросов

Рисунок 2.6 - Проект сервиса процесса оптимизации запросов



Рисунок 2.7 - Композиция сервисов, определенная на этапе их проектирования

2.2 Выбор средств разработки

2.2.1 Описание средств разработки

Для разработки системы были выбраны следующие программные средства: C++ Builder, Oracle Database 11g.

Выбор обусловлен тем, что программист имеет опыт разработки приложений с использованием C++ Builder. Выбор СУБД Oracle обоснован необходимостью интеграции в курс учебного предмета «Базы данных».

Embarcadero C++Builder 10.3 Community Edition - среда быстрой разработки приложений (RAD) фирмы Embarcadero Technologies, работающая под Windows.

Embarcadero C++Builder - программный продукт, интегрированная среда программирования для разработки программного обеспечения на языке программирования C++.

C++ Builder объединяет в себе комплекс объектных библиотек компилятор, отладчик, редактор кода и многие другие компоненты. Цикл разработки аналогичен Delphi. Большинство компонентов.

C++ Builder содержит инструменты, которые при помощи drag-and-drop действительно делают разработку визуальной, упрощает программирование благодаря встроенному редактору интерфейса.

Oracle Database - объектно-реляционная система управления базами данных компании Oracle. Oracle Database Express Edition (Oracle Database XE) - это бесплатная версия базы данных Oracle Database для начинающих разработчиков и администраторов.

В Oracle Database Express Edition используется 1 ГБ оперативной памяти, а также используется только 1 процессор. Максимальный объём базы данных составляет 11 ГБ, из них от 0,5 до 0,9 ГБ используются словарём данных, временным дисковым пространством и внутренними схемами.

Особенности СУБД Oracle:

- MVCC (англ. MultiVersion Concurrency Control) -- многоверсионность данных для управления параллельными транзакциями;

- Секционирование;

- Автономные транзакции;

- Automatic Storage Management -- автоматическое управление хранением файлов БД;

- Oracle Enterprise Manager -- набор инструментов; предназначенных для управления и мониторинга СУБД Oracle и серверов, на которых они установлены;

- Пакеты;

- Поддержка последовательностей;

- Аналитические функции в SQL;

- Profile manager;

- Oracle Label Security;

- Streams;

- Advanced Queuing;

- Flashback Query;

- RAC (англ. Real Application Clusters);

- RAT (Real Application Testing) -- позволяет значительно снизить затраты на испытание новой конфигурации программного или аппаратного обеспечения, так как способна точно воспроизвести на ней нагрузку рабочего сервера;

- Data Guard -- технология, позволяющая создать резервный сервер, который может работать в паре с основным, снижая нагрузку на него, и который может автоматически заменить основной сервер в случае его отказа или планового отключения (есть вариант с постоянной доступностью резервного сервера для чтения -- Active Data Guard);

- Total Recall -- даёт возможность разгрузить базу данных от устаревшей, редко используемой информации, сохраняя при этом возможность доступа к ней, так что для пользователя базы данных это изменение остаётся незамеченным;

- Объектные типы (в смысле объектно-ориентированного подхода).

- Automatic Database Diagnostic Monitoring -- автоматический мониторинг и диагностика баз для выявления проблем производительности и, возможно, автоматической корректировки (если таковая определена администратором);

- Подсказки для изменения плана выполнения запроса.

2.2.2 Архитектура приложения

Разрабатываемое приложение будет взаимодействовать с сервером БД посредством FireDac. FireDac - это стандартный набор компонентов, позволяющий получить доступ к БД. Подключение к БД Oracle производится с использованием драйвера OCI.

Графическое представление архитектуры приложения показано на рисунке 2.8

Рисунок 2.8 - Архитектура приложения

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

3. Реализация

3.1Реализация системы

3.1.1 Получение подсказок по оптимизации при помощи DBMS_SQLTUNE

Для запуска задания были сформированы два PL/SQL-блока.

Первый блок представлен на рисунке 3.1

Рисунок 3.1 - PL/SQL блок задания для DBMS_SQLTUNE

Рисунок 3.2 - Результат работы DBMS_SQLTUNE

На рисунке 3.2 видно, что рекомендаций по улучшению запроса нет. DBMS_SQLTUNE было выдано следующее сообщение: “There are no recommendations to improve the statement”

Листинг второго PL/SQL блока представлен на рисунке 3.3

Рисунок 3.3 - PL/SQL блок задания для DBMS_SQLTUNE

Во второй SQL-запрос была внесена ошибка. Таблицы employees e1 и employees e2 не соединены. Результат работы DBMS_SQLTUNE в этом случае представлен на рисунках 3.4 и 3.5

Рисунок 3.4 - Результат работы DBMS_SQLTUNE(начало)



Рисунок 3.5 - Результат работы DBMS_SQLTUNE(окончание)

Блок Recommendation содержит следующее предложение: “Consider removing the disconnected table or view from this statement or add a join condition which refers to it.” Средством DBMS_SQLTUNE было предложено удалить несвязанную таблицу из запроса или добавить связку.

3.1.2 Обработка результата и выделение блока рекомендаций

Для хранения результатов работы DBMS_SQLTUNE была создана таблица tuning_res. Описание полей таблицы представлено в таблице 3.1.

Таблица 3.1 - Поля таблицы tuning_res

Поле	Тип	Назначение
id	number	номер п/п
text	clob	Хранение результата работы функции DBMS_SQLTUNE.REPORT_TUNING_TASK()

Вставка в таблицу будет производиться при помощи запроса:

insert into tuning_res(id, text)

SELECT

sq_tuning_res.nextval ,

DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'task_1' )

FROM dual;

Для того чтобы отделить рекомендации от остальных данных, был написан запрос

select case when instr(text, 'Recommendation') > 0 then

substr(text, instr(text, 'Recommendation')+36,

(instr(text, 'EXPLAIN')- instr(text, 'Recommendation'))/2)

end as res

from tuning_res;

Результат выполнения запроса представлен на рисунке 3.6

Рисунок 3.6 - Блок рекомендаций

3.1.3 Разработка приложения

Для обработки на стороне сервера была написана процедура prc_create_task с параметром p_sql_text типа varchar. Код представлен на рисунке 3.7



Рисунок 3.7 - Процедура для обработки запроса

При разработке приложения были созданы классы:

- DmSQLOpimizer

- FmSQLOptimizerList

- FmSQLOptimizerMain

- FmSQLOptimizerRes

DmSQLOpimizer представляет собой компонент TDataModule. На этом компоненте расположены все запросы и соединение с базой данных.

FmSQLOptimizerList, FmSQLOptimizerMain, FmSQLOptimizerRes представляют собой компонент TForm. Класс TForm в С++ Builder позволяет вести разработку в наглядном виде. При разработке используется технология drag-and-drop, что позволяет помещать компоненты(TMemo - поле мемо, TButton - кнопка, Tlabel - метка) на форму и располагать их в нужном месте.

На рисунке 3.8 представлена UML-диаграмма.



Рисунок 3.8 - UML-диаграмма

В ходе реализации были созданы формы авторизации, списка запросов, ввода запроса, форма вывода результата.

Процесс разработки будет рассмотрен подробнее на примере формы FmSQLOptimizerMain.

В первую очередь при разработке приложения в C++ Builder создают DataModule. Он содержит компоненты подключения к базе данных и запросы. На рисунке 3.9 представлен вид класса DmSQLOptimizer.

Рисунок 3.9 - Графическое представление класса DmSQLOptimizer

Далее создают форму и подключают к ней DataModule:

#include "DmSQLOptimizer.h"

Графическое представление класса FmSQLOptimizerMain предсавлено на рисунке 3.10

Рисунок 3.10 - Графическое представление класса FmSQLOptimizerMain

На форму добавляют необходимые компоненты. В данном случае были добавлены следующие компоненты: TMemo, TLabel, TButton, TActionList, TDataSourse.

TActionList представляет собой набор действий. Для данной формы необходимы два действия: ввод и очистка. Состав ACTL_main представлен на рисунке 3.11

Рисунок 3.11 - Состав действий в ACTL_main

Для каждого действия назначаются события OnExecute(событие при выполнении действия) и OnUpdate(доступность действия)

Рисунок 3.12 - События для ACTL_main

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

Рисунок 3.13 - Проверка на заполнение поля

По нажатию кнопки «Ввод» запрос, введенный пользователем, передается в процедуру в качестве входного параметра. Затем запускается процедура и открывается форма с результатом.

Рисунок 3.14 - Обработка нажатия кнопки «Ввод»

При закрытии формы вызывается деструктор. На деструкторе закрывают все открытые формой запросы. Пример деструктора представлен на рисунке 3.15

Рисунок 3.15 - Деструктор формы

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

Форма авторизации представлена на рисунке 3.16.

Рисунок 3.16 - Форма авторизации

Для получения подсказок по оптимизации в форму ввода запроса вводится SQL-запрос. По нажатию кнопки «Ввод» происходит обработка запроса пользователя, по кнопки «Очистить» поле с текстом запроса очищается. Интерфейс формы ввода запроса представлен на рисунке 3.17



Рисунок 3.17 - Интерфейс программы (форма ввода)

Если на предыдущем этапе работы с программой была нажата кнопка «Ввод», открывается форма с выводом результата обработки запроса. Интерфейс формы представлен на рисунке 3.18.

Рисунок 3.18 - Интерфейс программы (форма вывода результата)

3.2 Тестирование системы

Для тестирования системы на первом этапе был введен корректный запрос. На рисунке 3.19 представлен введенный запрос.



Рисунок 3.19 - Форма ввода

На рисунке 3.20 представлен результат работы программы после проверки первого запроса

Рисунок 3.20 - Результат работы программы

Для тестирования работы программы был введен запрос с ошибками. Ход работы представлен на рисунках 3.21 и 3.22



Рисунок 3.21 - Форма ввода(запрос с ошибкой)

После нажатия кнопки «Ввод» программой была выдана подсказка по оптимизации запроса.

Рисунок 3.22 - Интерфейс формы вывода результата

В ходе главы была разработана информационная система для оптимизации SQL-запросов. Проведена разработка на серверной и клиентской стороне. При тестировании программа показала корректную работу. Информационная система удовлетворяет поставленным требованиям.

Заключение

В ходе ВКР была разработана информационная система для помощи в процессе выявления проблемных запросов в СУБД Oracle, предназначенная для целей обучения в рамках курса Базы данных.

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

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

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

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

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

Список использованных источников

1.Миллсап, К. Oracle. Оптимизация производительности/ К. Миллсап, Д. Хольт, 2006 г - 464 с..

2.Oracle documentation. Database PL/SQL packages and Types Reference [Электронный ресурс] - режим доступа: https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDGAJCI

3.Льюис, Дж. Основы стоимостной оптимизации/Дж. Льюис - СПб: Питер, 2007 г - 528 с.

4.Андрианов, И.А. Базы данных. Программирование и администрирование: учебное пособие/ И. А. Андрианов, С. Ю. Ржеуцкая . - Вологда: ВоГУ, 2018. - 71 с.

5.СТО ВоГУ-МУ.1-2019. Выпускная квалификационная работа. Требования к структуре, содержанию и оформлению. Вологда: ВоГУ, 2019 г. - 67 с.

6.Кайт, Т. Oracle для профессионалов. Книга 1. Архитектура и основные особенности/ Т. Кайт - СПб: ООО «ДиаСофтЮП», 2003 г. - 672 с.

7.Аносова, Н. Распределенные базы данных. Лекция 12. Оптимизация выполнения запросов [Электронный ресурс]/Аносова Н., Бородин О., Гаврилов Е., Марасанов А.//НОУ «Интуит» - Режим доступа: https://www.intuit.ru/studies/courses/1145/214/lecture/5523

8.Юрина, К.В. Исследование способов оптимизации SQL-запросов в Oracle/ К.В. Юрина //Будущее науки - 2018: Сборник науч. статей 6-й Международной молодежной научной конференции (25-26 апреля 2018 г.)[в 4. Т]: Т. 3, Юго-Зап. гос. ун-т., Курск: ЗАО «Университетская книга», 2018 г. - 414 с.

9.Юрина, К.В. Разработка системы по оптимизации sql - запросов/ К.В. Юрина//Наукоемкие исследования как основа инновационного развития общества - 2019:Сборник науч. статей Международной научной конференции - Уфа, 2019 г - 228 с.

10.Юрина, К.В. Разработка информационной системы для помощи в оптимизации sql - запросов/ К.В. Юрина//Наука и научный потенциал - основа научного развития общества: Сборник науч. статей всероссийской научно-практической конференции - Пермь, 2019 г - 228 с.

Приложение 1

Файл FmSQLOptimizer.cpp

//---------------------------------------------------------------------------

#include <vcl.h>

#pragma hdrstop

#include "FmSQLOptimizer.h"

#include "SQLOptimizer.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

TFSQLOptimizerMain *FSQLOptimizerMain;

//---------------------------------------------------------------------------

__fastcall TFSQLOptimizerMain::TFSQLOptimizerMain(TComponent* Owner)

: TForm(Owner)

{

if (DSQLOptimizer->Q_text->Active)

{

DSQLOptimizer->Q_text->Close();

}

DSQLOptimizer->Q_text->Open();

DSQLOptimizer->Q_text->Edit();

}

//---------------------------------------------------------------------------

void __fastcall TFSQLOptimizerMain::m_ACT_clearExecute(TObject *Sender)

{

DBM_text->Clear();

}

//---------------------------------------------------------------------------

void __fastcall TFSQLOptimizerMain::m_ACT_saveExecute(TObject *Sender)

{

DSQLOptimizer->Q_text->Edit();

DSQLOptimizer->Q_text->Post();

TFSQLOptimizer *fm = new TFSQLOptimizer(this);

fm->ShowModal();

}

//---------------------------------------------------------------------------

Приложение 2

(обязательное)

Файл FmSQLOptimizer.h

//---------------------------------------------------------------------------

#ifndef FmSQLOptimizerH

#define FmSQLOptimizerH

//---------------------------------------------------------------------------

#include <System.Classes.hpp>

#include <Vcl.Controls.hpp>

#include <Vcl.DBCtrls.hpp>

#include <Vcl.ExtCtrls.hpp>

#include <Vcl.Mask.hpp>

#include <Vcl.StdCtrls.hpp>

#include <Vcl.Controls.hpp>

#include <Vcl.StdCtrls.hpp>

#include <Vcl.Forms.hpp>

#include <System.Actions.hpp>

#include <Vcl.ActnList.hpp>

#include "DmSQLOptimizer.h"

//---------------------------------------------------------------------------

class TFSQLOptimizerMain : public TForm

{

__published: // IDE-managed Components

TPanel *P_main;

TLabel *L_text;

TButton *BTN_save;

TButton *BTN_clear;

TMemo *DBM_text;

TActionList *ActionList1;

TAction *m_ACT_save;

TAction *m_ACT_clear;

void __fastcall m_ACT_clearExecute(TObject *Sender);

void __fastcall m_ACT_saveExecute(TObject *Sender);

private: // User declarations

public: // User declarations

__fastcall TFSQLOptimizerMain(TComponent* Owner);

};

//---------------------------------------------------------------------------

extern PACKAGE TFSQLOptimizerMain *FSQLOptimizerMain;

//---------------------------------------------------------------------------

#endif

Приложение 3

(обязательное)

Файл FmSQLOptimizer.dfm

object FSQLOptimizerMain: TFSQLOptimizerMain

Left = 0

Top = 0

Caption = 'SQLOptimizer'

ClientHeight = 260

ClientWidth = 555

Color = clBtnFace

Font.Charset = DEFAULT_CHARSET

Font.Color = clWindowText

Font.Height = -11

Font.Name = 'Tahoma'

Font.Style = []

OldCreateOrder = False

PixelsPerInch = 96

TextHeight = 13

object P_main: TPanel

Left = 0

Top = 0

Width = 555

Height = 260

Align = alClient

TabOrder = 0

object L_text: TLabel

Left = 30

Top = 37

Width = 118

Height = 13

Caption = #1042#1074#1077#1076#1080#1090#1077' '#1090#1077#1082#1089#1090' '#1079#1072#1087#1088#1086#1089#1072

FocusControl = DBM_text

end

object BTN_save: TButton

Left = 436

Top = 217

Width = 75

Height = 25

Action = m_ACT_save

BiDiMode = bdLeftToRight

ParentBiDiMode = False

TabOrder = 0

end

object BTN_clear: TButton

Left = 154

Top = 217

Width = 75

Height = 25

Action = m_ACT_clear

TabOrder = 1

end

object DBM_text: TMemo

Left = 154

Top = 34

Width = 357

Height = 164

Lines.Strings = (

'Memo1')

TabOrder = 2

end

end

object ActionList1: TActionList

Left = 272

Top = 136

object m_ACT_save: TAction

Caption = #1042#1074#1086#1076

OnExecute = m_ACT_saveExecute

end

object m_ACT_clear: TAction

Caption = #1054#1095#1080#1089#1090#1080#1090#1100

OnExecute = m_ACT_clearExecute

end

end

end

Размещено на Allbest.ru

...