Программа для поддержки процессов выявления проблемных запросов в СУБД Oracle

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

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

ВВЕДЕНИЕ

запрос программный авторизация

Нельзя не учитывать значимость эффективных SQL-запросов в основанных на использовании СУБД Oracle программах. Всего один индекс или плохо написанный запрос могут застопорить работу всей системы.

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

Актуальность работы заключается в том, что БД является неотъемлемой частью практически любой автоматизированной системы, а эффективно написанный запрос позволяет не только увеличить производительность приложения, но и снизить сетевой трафик. Поэтому как студенты, изучающие курс «Базы данных», так и разработчики автоматизированных систем должны понимать работу оптимизатора запросов и существующие возможности настройки, которая может сделать запросы более эффективными и менее рискованными.

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

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

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

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

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

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



1.АНАЛИТИЧЕСКИЙ ОБЗОР

1.1 Общее представление о процессе исполнения SQL-запросов

На рисунке 1.1 показан порядок обработки SQL-запроса на сервере.

Рисунок 1.1 - Порядок обработки SQL-запроса

1.2 Медленные запросы

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

Для каждого такого запроса будут зафиксированы следующие параметры:

время выполнения запроса - количество времени, которое сервер БД затратил на исполнение запроса;

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

количество просмотренных строк таблицы - количество строк, которое пришлось прочитать с диска в процессе обработки SQL-запроса;

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

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

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

1.3 Случайные медленные запросы

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

запросы INSERT, UPDATE могут выполниться медленно из-за ожидания окончания обработки других запросов или из-за занятости дисковой системы сервера в момент выполнения запроса;

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

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



1.4 Проблемные запросы

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

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

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

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

запрос вида - WHERE column=[value], WHERE column>[value], WHERE column<[value], проблема - большое количество просмотренных строк (от 10 до 100 тысяч и более). Это означает, что отсутствует необходимый индекс для поиска по полю или в качестве ответа на запрос возвращается большое количество строк;

запрос вида - WHERE column LIKE `%...%', большое количество просмотренных строк. В процессе исполнения запроса LIKE невозможно использовать индексы, происходит полный перебор данных.

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

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

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

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

Причины неэффективности SQL-запросов в Oracle

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

плохая статистика по индексам и таблицам запроса;

отсутствие и проблемы с индексами;

проблемы с подсказками в запросе;

проблемы с построением запроса;

некорректно настроенные параметры инициализации БД, отвечающие за производительность.

Существует много различных причин неэффективности запроса, вот некоторые из них:

неэффективное соединение таблиц в запросе;

использование конструкций NOT и NOT IN в WHERE;

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

большая длина или вложенность запроса;

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

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

План исполнения запроса

При работе с SQL-запросами часто требуется выяснить каким образом выполняется запрос, какие методы доступа применяются оптимизатором запроса Oracle при исполнении SQL-запроса, какие индексы используются и используются ли они вообще.

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

Прежде всего в БД должна быть создана специальная таблица PLAN_TABLE, при этом нужно дать привилегии на эту таблицу тому пользователю, который будет просматривать и анализировать планы исполнения SQL-запросов. 

Заполнить таблицу PLAN_TABLE можно с помощью команды explain plan for.

Таблица PLAN_TABLE хранит информацию о плане исполнения SQL-запроса. Каждый раз при выполнении команды explain plan for все данные таблицы стираются и формируются новые. 

Пример анализа SQL-запроса:

EXPLAIN PLAN FOR

SELECT *

FROM T1;

После выполнения команды можно посмотреть результат анализа плана исполнения SQL-запроса. Сделать это можно при помощи простой выборки из таблицы PLAN_TABLE, однако это малоинформативно и достаточно неудобно.

Однако команда EXPLAIN PLAN FOR не учитывает значения связываемых переменных, поэтому если в запросе используются такие переменные, то PLAN_TABLE будет содержать план исполнения, который на самом деле не используется при выполнении запроса.

Существует множество различных инструментов для анализа информации PLAN_TABLE, такие инструменты встроены в Toad, PL/SQL Developer, SQL Navigator и другие, но проблема в том, что эти программы не всегда могут оказаться под рукой, поэтому нужно уметь просматривать результат команды explain plan for в SQL Plus. Для этого существует пакет Oracle dbms_xplan. Для того, чтобы получить форматированный отчет результата команды explain plan for можно выполнить следующий запрос:

SELECT * FROM TABLE(dbms_xplan.display(NULL, NULL, `basic'));

Анализ плана исполнения запроса

Основные правила плана выполнения запроса:

план имеет корень - ветвь, которая не имеет родителя;

родительские ветки могут иметь более одного потомка, и идентификатор потомка больше чем идентификатор родителя;

у потомка может быть только одна родительская ветка, это правило справедливо и для нескольких уровней вложенности.

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

сost - стоимость выполнения запроса;

CPU сost - процессорная стоимость выполнения;

IO сost - стоимость операций ввода-вывода;

temp space - показатель использования запросом временного пространства.

Чем большее значение имеет первые три показателя, тем менее эффективен запрос.

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

Анализ планов исполнения запросов имеет определенную последовательность действий:

план исполнения просматривают снизу-вверх, в первую очередь обращают внимание на строки, имеющие большие значения сost и CPU cost;

также следует обратить внимание на наличие в плане исполнения полного сканирования таблиц и индексов: FAST FULL SCAN или FULL SCAN для индексов и FULL для таблиц, также для индексов стоит обратить внимание на значение SKIP SCAN. В плане выполнения, полученном из представления v$sql_plan, для выявления наличия полного сканирования индексов или таблиц нужно смотреть столбец Options (Plan_Options для представления v$sql_plan_monitor);

Кроме того, необходимо посмотреть наличие в плане исполнения значения Hash_Join, так как соединение по Hash_Join приводит к соединению таблиц в памяти и, казалось бы, оно более эффективно, чем вложенные соединения Nested Loops. Однако соединение Hash_Join эффективно при наличии таблиц, хотя бы одна из которых помещается в память БД, или при наличии соединения таблиц с низкоселективными индексами. Минусом такого соединения является то, что при нехватке памяти для таблиц будут задействованы диски, которые сильно затормозят работу запроса (в плане исполнения появится показатель Temp Space). Поэтому при наличии высокоселективных индексов стоит посмотреть, а не улучшит ли план выполнения запроса подсказка оптимизатору Use_NL, приводящая к соединению по вложенным циклам Nested Loops. Если план будет оптимальнее, то нужно оставить эту подсказку. В плане исполнения, полученном из представления v$sql_plan, для выявления Hash_Join следует смотреть столбец Operations;

фразы Merge Join Cartesian также являеется проблемой и означает, что между таблицами нет полной связки. Решением этой проблемы может быть добавление недостающей связки, но иногда помогает использование подсказки Ordered.

Также стоит обращать внимание на детализирующие параметры - CPU_time, Elapsed_time, Buffer Gets, Disk Read и Executions.

СPU_TIME покажет процессорное время выполнения запроса, Elapsed_time - общее время выполнения, Buffer Gets и Disk Read - интенсивность использования памяти и дисков, Executions - количество выполнений запроса;

Значение параметра Disk Reads более 250 тысяч означает частое использование дисков, значение Buffer Gets более 10 миллионов на одно выполнение запроса означает интенсивное использование памяти, то есть в данных случаях в запросе имеются проблемы.

Запрос будет неэффективным если значение Elapsed_time значительно превосходит значение CPU_time (скорей всего это связано с событиями ожидания). Указанные параметры можно получить из служебного представления v$sql или v$sql_monitor по уникальному идентификатору запроса sql_id, а если идентификатор неизвестен, то можно использовать уникальные элементы текста запроса. Для поиска этих параметров в долго работающем запросе стоит использовать представление v$sql_monitor, которое в отличие от представления v$sql имеет столбец sid-сессии, позволяющий выявить запросы, выполняющиеся долго в данной сессии, и их идентификатор sql_id.

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

Широкий спектр информации по всем перечисленным параметрам можно получить из такого мощного средства диагностики эффективности запросов, как AWR.

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

Доступ к этим представлениям по умолчанию запрещен, поэтому администратор БД должен предоставить разработчикам полномочие SELECT ANY DICTIONARY, прежде чем они смогут просматривать эту информацию

Представление v$sqlarea содержит статистку разделяемой области SQL, по одной строке для каждой строки SQL. Оно содержит статистику по операторам SQL, которые находятся в памяти, разобраны и готовы для выполнения.

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

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

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

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

1.5 Методы диагностики производительности запросов

Cуществуют различные способы проверки производительности работы запросов, к примеру, применение трассировочных файлов, средства Оrаclе DBMS_SQLТUNЕ (с рекомендациями по оптимизации запроса), AWR (automatic workload repository) и другие.

Среди всех инструментов диагностики производительности работы запросов наиболее простым и достаточно действенным является просмотр плана выполнения запросов через программы Toad, PL/SQL Developer и др., а также на основе служебных представлений, к примеру, таких как v$sql_plan, v$sql_plan_monitor и v$sql_bind_capture, показывающий значения переменных, которые используются в запросе.

Следует отметить, что, если через служебные представления v$sql_plan и v$sql_plan_monitor можно получить реальный план выполнения запроса, то через подстанoвку текста запроса в программы Toad, PL/SQL Developer и другие аналогичные программы будет получен план исполнения запроса, построенный оптимизатором.

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

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

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

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

соst - стоимость выполнения;

сardinality - кардинальность.

Чем больше значения этих показателей, тем менее эффективен запрос.

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

Текст запроса;

SELECT * FROM TABLE(dbms_xplan.display_cursor());

Реальный план исполнения запроса и указанные выше параметры для анализа запроса можно получить из динамических представлений: V$SQL_PLAN и V$SQL_PLAN_MONITOR.

Значение идентификатора sql_id можно использовать для получения реального плана исполнения запроса:

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

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

SELECT sql_id from v$sql

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

1.10 Недостатки приведенных методов

Toad, PL/SQL Developer и другие аналогичные позволяют получить лишь план запроса, который строится оптимизатором, а не реальный план выполнения.

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

Большие запросы имеют объемный план выполнения и при просмотре плана запроса через стандартную утилиту SQL Plus возникает проблема - план имеет неудобный для чтения вид.



2. ПРОЕКТИРОВАНИЕ ПП

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

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

Основной сегмент пользователей программы - студенты, изучающие курс «Базы данных». Также ПП могут использовать разработчики информационных систем и администраторы баз данных.

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

ПП должен как можно меньше загружать сам сервер;

ПП должно показывать реальный план исполнения запроса, если это возможно;

ПП должен быть кроссплатформенным;

время получения плана запроса не должно быть более 5 секунд;

план запроса должен представляться в удобном для пользователя виде, с выделением проблемных мест запроса;

представление информации должно быть наглядным;

необходима возможность работать с несколькими подключениями к БД.

2.2 Выбор способа реализации программного средства

1. Исполняемый модуль

Содержит код, который будет запускаться при открытии файла. Так как одно из требований к ПП - это кроссплатформенность, то для того, чтобы реализовать его, необходимо либо писать код для каждой платформы, что является неоптимальным, либо писать код на языке программирования Java, но, как известно, принцип «write once, run anywhere» соблюдается не в полной мере, поэтому нельзя дать полные гарантии, что такая реализация программного средства будет корректно работать на разных платформах.

Возможно потребуется установка программы.

2. Веб-приложение

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

На основании обзора каждого из подходов было решено реализовывать программное средство как веб-приложение.

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

Основными критериями при выборе инструментов разработки являлись:

Скорость разработки

Надежность

Производительность

Расширяемость

Простота в использовании

2.3.1 Выбор веб-фреймворка

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

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

Наиболее популярные веб-фреймворки:

Django (Python);

Yii2 (PHP);

Ruby on Rails.

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

Основные особенности Yii2:

Модель MVC (Model, view, controller);

философия простого и элегантного кода;

ActiveRecord для реляционных и NoSQL БД;

Поддержка REST API;

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

Одна из главных целей фреймворка Yii - производительность.

2.3.2 Выбор инструментов для front-end разработки

Выбранный набор инструментов для разработки front-end'a:

1. Bootstrap.

Bootstrap - это полезный набор инструментов для создания веб-приложений, который включает в себя HTML и CSS шаблоны оформления для типографики, кнопок, меток, форм и других различных компонентов веб-интерфейса, включая js-расширения.

Преимущества использования:

Экономия времени -- используются готовые стили и шаблоны;

Высокая скорость разработки;

Гармоничный дизайн -- все компоненты используют единый стиль;

Простота в использовании;

Совместимость с браузерами и устройствами;

Открытое программное обеспечение -- бесплатная загрузка и открытый исходный код;

Backbone.

Backbone - это JavaScript-библиотека, которая основана на шаблоне проектирования Model-View-Presenter (MVP). Backbone.js придает структуру веб-приложениям с помощью моделей с биндигами по ключу, событиями пользователя, коллекций с большим набором методов с перечислимыми сущностями, представлений с обработкой событий.

Undersore.js

В состав этой библиотеки входят более 80 функций, упрощающих и ускоряющих разработку: функциональное связывание, шаблонизация, проверка на равенство и другое.

JQuery

Библиотека JavaScript, упрощающая взаимодействие JavaScript и HTML, также предоставляет удобный API для AJAX.

Less

CSS препроцессор, динамический язык стилей.

2.3.3 Выбор СУБД приложения

Наиболее приспособленной СУБД для применения в веб-среде является MySQL, именно она будет использоваться для хранения пользовательской информации, также стоит учесть, что при работе не потребуется обработки большого объема информации.

Основные значимые преимущества MySQL:

бесплатная СУБД;

интерфейс с PHP;

занимает мало места;

надежность;

быстрая работа;

отличная поддержка со стороны хостинг-провайдеров.

2.3.4 Выбор сборки веб-сервера

В качестве веб-сервера был выбран Apache. Как известно, связка Apache, PHP и MySQL работает достаточно надежно и производительно, к тому же имеется практически на всех хостингах.

Для реализации этой связки будет использоваться XAMPP - дистрибутив Apache, который также содержит PHP и MySQL. XAMPP является кроссплатформенным, для установки требуется лишь запустить инсталлятор и выбрать минимальные настройки.

2.3.5 Выбор системы управления версиями

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

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

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

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

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

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

Основные особенности:

Автодополнение, форматирование, подсветка синтаксиса;

Рефакторинг кода;

Поддержка MVC фреймворков;

HTML, CSS, JavaScript редактор;

Интеграция с системами управления версиями;

PHP UML;

Инструменты для работы с базами данных.

2.4 Выбор и обоснование методологии разработки

Методология -- это совокупность принципов, методов, понятий, средств и способов, которые определяют стиль разработки ПО

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

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

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

Существует несколько методик, которые относятся к гибким методологиям разработки, в частности Scrum, DSDM, экстремальное программирование, FDD.

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

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

В дальнейшей разработке было решено придерживаться методике Scrum.

Для управления задачами и agile-процессами использовалась система управления проектами YouTrack.

2.5 Проектирование архитектуры программной системы

На рисунке 2.1 представлена архитектура системы.

Рисунок 2.1 - Диаграмма архитектуры ПП

Описание сущностей:

Oracle Connection Helper используется для получения соединения с базой данных ORACLE. Соединение устанавливается с помощью функций интерфейса OCI 8 (Oracle Connection Interface);

DataBaseHelper работает с базой данных, выполняет необходимые запросы к БД;

DataManager обрабатывает данные - результаты запросов и представляет их в необходимом формате - массив, объект или JSON;

Models, Views, Controllers - модели, представления и контроллеры веб-приложения.

2.6 Проектирование структуры сайта

Для проектирования структуры сайта использовался метод карт памяти (Mind map).

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

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

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

Для построения интеллект-карты использовалась бесплатная и свободно распространяемая программа XMind.

На рисунке 2.2 представлена структура сайта, созданная с помощью метода карт памяти.

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

Рисунок 2.2 - Структура сайта

Описание структурных элементов:

фигуры желтого цвета -- это сущности, страницы, физические разделы веб-сайта;

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

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



2.7 Диаграмма прецедентов

При проектировании ПП использовался язык UML (Unified Modeling Language) - язык графического описания для объектного моделирования в области разработки ПО. С помощью UML была построена диаграмма прецедентов.

Диаграмма прецедентов показывает связь между актерами и прецедентами.

Актер - множество взаимосвязанных ролей.

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

Диаграмма прецедентов описывает функциональность и поведение.

При создании диаграммы необходимо:

Отделить систему от ее окружающей среды;

Определить актеров и варианты их взаимодействия с системой;

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

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

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



Рисунок 2.3 - Диаграмма прецендентов

Прототипирование пользовательского интерфейса

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

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

На рисунке 2.4 приводится пример прототипа интерфейса панели мониторинга.

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



Рисунок 2.4 - Прототип панели мониторинга

2.9 Алгоритм визуализация плана исполнения запроса

Алгоритм получения плана исполнения запроса проходит в несколько этапов - вначале для полученного запроса выполняется выражение explain plan for, которое заполняет таблицу plan_table данными плана выполнения, после этого происходит выборка из заполненной plan_table, полученные данные обрабатываются и отображаются пользователю.

Так как все запросы к базе данных выполняются на стороне сервера, а отображение информации происходит на стороне клиента, то для передачи данных будет использоваться JSON (JavaScript Object Notation) - текстовый формат для обмена данными.



Рисунок 2.5 - Алгоритм визуализации плана исполнения запроса

2.10 Проектирование схемы БД приложения

База данных хранит информацию о пользователях и доступных им соединениях к БД.

Рисунок 2.6 - Схема базы данных

Таблица user содержит информацию о пользователе, которая включает в себя:

id - уникальный идентификатор пользователя;

username - имя пользователя;

email - электронная почта;

password_hash - хеш-код пароля пользователя;

status - статус пользователя;

auth_key - ключ аутентификации;

created_at - дата создания аккаунта;

updated_at - дата последнего изменения аккаунта.

Таблица connection содержит информацию о данных, необходимых для соединения к базе данных пользователя:

id - уникальный идентификатор;

username - имя пользователя для доступа к БД;

password - пароль;

host - хост;

db_name - имя БД;

is_selected - флаг, указывающий соединение, с которым ведется работа.



3. РЕАЛИЗАЦИЯ ПП

3.1 Разработка основных контроллеров приложения

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

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

Основные действия приложения реализуют два контроллера - SiteController и QueryController.

SiteController реализует основные действия, к которым обращается обычный пользователь:

actionIndex - в зависимости от прав пользователя отображает стартовую страницу. Для авторизированного пользователя - это панель мониторинга, для посетителя отображается посадочная страница (landing page);

actionReg - отображает форму регистрации;

actionLogin, actionLogout используются пользователями для входа в учетную запись или выхода из неё.

actionContact - контактная форма.

QueryController необходим для обмена данными, передает со стороны

сервера JSON, который затем обрабатывается на стороне клиента с помощью языка JavaScript.

Ниже приводится действие actionIndex контроллера SiteController.

public function actionIndex()

{

if (!\Yii::$app->user->isGuest)

{

$currentConnection = ConnectionController::getSelectedConnection();

return $this->render('dashboard',[

'curConnection' => $currentConnection,

]);

}

return $this->render('landing');

}

3.2 Использование интерфейса OCI8 для работы с БД Oracle

OCI8 (Oracle connection interface) позволяет работать с БД Oracle, поддерживает SQL и PL/SQL-выражения, транзакции, коллекции и другое.

Расширение уже входит в версии PHP начиная с 5.3, но также для корректной работы необходимо установить бесплатные библиотеки Oracle Instance Client и системную переменную окружения PATH на папку, в которой находятся библиотеки Oracle.

Для подключения к серверу Orаcle имеется несколько различных функций, стандартная функция - oci_connect(). При вызове этой функции создается соединение к БД Oracle и возвращается ресурс, который нужно использовать для дальнейшей работы с БД.

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

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

public static function getConnection($user)

{

$conn = oci_connect($user ->username, $user ->password,

$user ->host . '/' . $user ->db_name);

if (!$conn)

{

$e = oci_error();

trigger_error(htmlentities($e['message'], ENT_QUOTES),

E_USER_ERROR);

}

return $conn;

}

3.3 Реализация системы регистрации и авторизации пользователей на сайте

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

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

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

В Yii встроен механизм авторизации и аутентификации (auth), который может быть гибко настроен под различные задачи.

Основным компонентом auth-фреймвoрка является компонент user, данный объект содержит информацию о текущем пользователе, которую можно получить в любой момент и из любого места программы.

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

Пароли пользователей не хранятся в открытом виде в БД, в целях безопасности в базе хранится хеш-код пароля.

В Yii2 был реализован механизм хеширования bcrypt, метод generatePasswordHash принимает входной параметр - пароль и возвращает сгенерированный хеш-код:

$hash_password=Yii::$app->getSecurity()->generatePasswordHash(`123);

В результате выполнения данной операции переменная $hash_password будет иметь значение из 64 символов, примерно следующего вида:

$4z$33$doo.o0q9qN/IrWF4RgoH6ejHyS1d.ElmkayY2vQ58DTApgGhU7Rji.

Также методу generatePasswordHash можно передать второй параметр - сложность шифрования, по умолчанию сложность равна 13, это довольно ресурсоемкий процесс, поэтому для текущей задачи ставить значение выше не имеет смысла.

Повторный вызов данного метода генерирует абсолютно другую последовательность символов.

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

public function validatePassword($password)

{

return Yii::$app->security->validatePassword($password,

$this->password_hash);

}

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

Рисунок 3.1 - Форма регистрации

3.4 Разработка редактора подключений к БД

Для подключения к базе данных необходима следующая информация:

имя пользователя;

пароль;

хост;

имя БД.

Редактор подключений необходим для управления этой информацией,

реализации базовых операций - create, update, insert, delete.

3.4.1 Генератор кода

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

Последовательность работы с генератором кода следующая:

перейти на страницу генератора кода;

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

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

Для создания файлов нужно нажать кнопку generate.

3.4.2 Использование gii для создания редактора подключений

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

Рисунок 3.2 - фрагмент формы генерации модели

Код будет сгенерирован с указанным пространством имен.

На основе модели можно создать CRUD контроллер, который будет реализовывать основные операции для работы с моделью - create, read, update, delete.



Рисунок 3.3 - фрагмент формы генерации CRUD контроллера

Также с помощью gii можно создать необходимые формы и представления.

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

Рисунок 3.4 - редактор подключений к БД

3.5 Разработка панели мониторинга

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

Для получения основных сведений о версии БД используется запрос:

SELECT * FROM v$version;

Для получения информации о размере БД используются таблицы dba_data_files и dba_free_space, полученная информация отображается в виде диаграммы.

Информация об объектах пользователя отображается в HTML таблицах, функционал которых расширен с помощью библиотеки DataTables.js, для отображения такой создано специальное представление SimpleDataTable, которое позволяет избежать дублирования кода:

var SimpleDataTable = Backbone.View.extend({

el: null,

table: null,

url: null,

columns: null,

initialize: function (settings)

{

this.el = settings.el;

this.url = settings.url;

this.render(this.url, settings.columns);

},

render: function(url, columns)

{

var sqlTable = this;

var table = this.$el.DataTable( {

"ajax": url,

"columnDefs": [

{ "type": "numeric-comma", targets: 0 }

],

"columns": columns,

});

this.table = table;

},

});

Для создания нового объекта SimpleDataTable нужно передать три

параметра:

el - идентификатор HTML таблицы;

url - адрес, по которому выводится JSON информации для представления;

columns - список столбцов таблицы.

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

var userTables = new SimpleDataTable({ el: '#user-tables-table', url: USER_TABLES_URL,

columns: [

{ "data": "TABLE_NAME" },

{ "data": "NUM_ROWS" },

{ "data": "AVG_SPACE" },

{ "data": "MAX_TRANS" },

{ "data": "TABLE_LOCK" },

]});

Переменная USER_TABLES_URL содержит ссылку на действие контроллера QueryController.

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

Select table_name, num_rows, avg_space, max_trans, table_lock

from user_tables;

3.6 Разработка компонентов для отображения данных SQL-запросов

Для отображения данных SQL-запросов были созданы специальные представления, которые позволили избежать дублирования кода.

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

SELECT * FROM (SELECT sql_fulltext, sql_id, elapsed_time, cpu_time,

disk_reads, executions, child_number, first_load_time, last_load_time FROM v$sql

ORDER BY elapsed_time DESC) WHERE ROWNUM < 10;

QueryController содержит действие, в котором выполняется данный запрос, и весь результат выводится в формате JSON:

public function actionLongrunningqueries() {

if (!\Yii::$app->user->isGuest) {

$currentConnection = ConnectionController::getSelectedConnection();

$qm = QueryManager::getInstance( OracleConnectionManager::getConnection($currentConnection));

$queris = $qm->getLongRunningQueries();

echo json_encode($queris);}

}

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

На рисунке 3.5 представлен компонент, отображающий десять наиболее длительно выполняющихся запросов.

Рисунок 3.5 - Длительно выполняющиеся запросы

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

Рисунок 3.6 - Диалоговое окно с вкладками, отображающими текст и план выполнения выбранного запроса

3.7 Особенности реализации пользовательского интерфейса

Bootstrap имеет систему сеток, которая применятся для создания адаптивной разметки блоков. Система сеток имеет четыре класса:

xs (extra small) - очень маленькие экраны, меньше 768px;

sm (small) - маленькие экраны, от 768px до 992px;

md (middle) - средние экраны, от 992px до 1200px;

lg (large) - большие экраны, более 1200px.

По умолчанию сетка имеет 12 колонок, но это значение может

изменяться.

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

<div class=”row”>

Внутри такого блока добавляется необходимое количество колонок, это делается с помощью указания класса .col-*-* каждому блоку, внутри которого будет размещаться содержимое, минимальная ширина блока равна одной колонке, максимальная двенадцати колонкам.

Ниже представлен пример разметки, один ряд делится на три колонки.

<div class="row">

<div class="col-xs-12 col-sm-6 col-md-4 col-lg-4"></div>

<div class="col-xs-12 col-sm-6 col-md-4 col-lg-4"></div>

<div class="col-xs-12 col-sm-6 col-md-4 col-lg-4"></div>

</div>

Для каждого из размеров экранов указывается количество колонок, так

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

Пользовательский интерфейс разделен на две большие колонки:

левая колонка - боковое меню;

правая колонка - основное содержимое, которое также делится на колонки.

Для реализации удобного представления меню на маленьких экранах

использовались медиа-запросы. Для удобства пользователя на экране смартфона меню занимает примерно 10% от всей рабочей области, не выводится названия ссылок и с помощью медиа-запросов изменяются отступы и ширина родительского контейнера бокового меню. Делается это следующим образом:

@media screen and (max-width: 768px) {

/* styles */ }

Стили, прописанные в теле конструкции @media, будут применяться если рабочая область браузера не превышает 768px.



4. ТЕСТИРОВАНИЕ ПП

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

Чем сложнее ПП, тем больше времени и средств требуется на его тестирование.

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

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

4.1 Методика тестирования ПП

Процесс тестирования по пунктам:

Функциональное тестирование - самая продолжительная стадия, в процессе которой проходит проверка всего заявленного функционала:

проверка корректной работы всех функций веб-сайта;

проверка работоспособности форм;

поиск нерабочих гиперссылок;

тестирование работы счетчиков на страницах веб-сайта;

проверка подгрузки и выгрузки файлов;

просмотр содержимого сайта на соответствие должному контенту.

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

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

Также важна проверка на корректную работу сайта в различных браузерах, то есть кроссбраузерность, а также при разных размерах экрана.

Сегодня существует несколько популярных браузеров - Google Chrome, Mozilla Firefox, Safari, Opera и Internet Explorer, и каждый из этих браузеров придерживается общих рекомендаций по представлению веб-страницы, но также каждый из них обрабатывает программный код в соответствии с особенностями собственного движка. Также процесс проверки осложняется тем, что часто браузеры обновляются, появляются новые версии, и в новых версиях веб-сайт не обязательно будет выглядеть корректно.

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

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

4.2 Функциональное тестирование

Для проверки основных функций ПП создана тестовая таблица moni_test.

Таблица заполнена данными с помощью следующего кода:

begin

for i in 1..200000 loop

insert into moni_test values(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

end loop;

end;

Для проверки получения плана запроса - с помощью SQL Plus был получен план исполнения для следующего запроса:

SELECT count(*) FROM moni_test tab1, moni_test tab2

WHERE tab1.c=tab2.c AND tab1.c=1;

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

Рисунок 4.1 - План выполнения запроса, полученный с помощью SQL Plus

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

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

План исполнения запроса представлен наглядно - в виде таблицы. Потенциально проблемные места плана выделяются цветом. Данный план запроса идентичен представленному раннее (рисунок 4.1).

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

SELECT count(*) FROM moni_test tab1, moni_test tab2

WHERE tab1.c=tab2.c AND tab1.c=1;

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