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

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



СОДЕРЖАНИЕ

Введение

1 Анализ предметной области

1.1 Анализ существующих систем дистанционного обучения

1.2 План запроса

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

1.4 Рекомендации по улучшению запроса

3 Проектирование

3.1 Проектирование метода получения стоимости запроса

4 Реализация

4.1 Средства разработки

4.2 Разработка структуры данных

4.3 Разработка алгоритмов

4.4 Интерфейс пользователя

5 Тестирование

5.2 Результаты тестирования

Заключение

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

Приложение 1

Приложение 2

Приложение 3

Приложение 4

Приложение 5



Введение

данные база запрос оценка

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

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

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

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

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

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

В ходе выпускной квалификационной работы была модифицирована система дистанционного практикума по курсу «Базы данных. SQL». В рамках доработки был добавлен метод экспресс-оценки эффективности запроса и его визуальное представление учащемуся.

SQL (structured query language) - язык структурированных запросов. В настоящее время язык SQL является основным стандартным языком для создания и работы с данными в реляционных базах данных[1]. Существует множество систем управления базами данных (СУБД), которые поддерживают этот язык программирования, например MySQL, Oracle, SQLite, PostgreSQL и многие другие.

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

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

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

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

проектирование алгоритма экспресс-оценки;

доработать существующую схему базы данных для хранения результатов оценки;

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

создать интерфейс для администратора системы;

реализовать вывод результатов оценки пользователю;

реализовать вывод плана запроса пользователю.

Объектом исследования являются SQL запросы клиентов системы дистанционного практикума.

Предметом исследования являются алгоритмы и способы реализации алгоритма оценки эффективности SQL запроса.

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

Научная новизна работы заключается в следующем:

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

Практическая значимость результатов магистерской диссертации заключается в следующем:

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

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



1 Анализ предметной области

1.1 Анализ существующих систем дистанционного обучения

На портале http://www.sql-ex.ru/ [3] есть краткий курс по языку SQL и справочник по DML. Кроме того есть форум для обсуждения и помощи по задачам.

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

?select: выборка данных;

?Insert: вставка новых данных;

?Delete: удаление данных;

?Update: модификация данных.

Все упражнения проверяются автоматически.

Упражнения на выборку разбиты на несколько этапов:

?обучающий этап (без ограничений по времени);

?этап с контролем времени;

?оптимизационный этап;

По результатам решения задач на сайте ведется рейтинг пользователей.

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

?Microsoft SQL Server;

?PostgreSQL.

?MySQL;

?Oracle;

При последующих этапах доступна только Microsoft SQL Server, поэтому пользователям приходится придерживаться синтаксиса этой СУБД.

На сайте можно посмотреть план запроса и затраты на его выполнение. Пример вывода этой информации представлен на рисунках 1 и 2. План запроса строит СУБД Microsoft SQL Server.

Рисунок 1 - Вывод плана запроса

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

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

Сертификат Basic knowledge может получить любой пользователь, который решил все рейтинговые упражнения до № 63 включительно по оператору SELECT и 20 упражнений по DML;

Сертификат All requirements может быть получен пользователями, решившими 125 рейтинговых упражнений по оператору SELECT и все имеющиеся упражнения по DML.

Отметим положительные стороны данной системы:

Возможность бесплатного обучения;

задачи на несколько видов запросов;

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

Ведется рейтинг участников;

При решении задач доступны подсказки;

Есть теоретический курс и справочник по SQL;

При затруднении с решением есть возможность получить помощь сообщества на форуме.

К отрицательной стороне можно отнести ограничение в выборе СУБД для построения плана запроса.

Отметим, что в сети есть множество сайтов для online - обучения основам SQL. На них можно пройти обучение основам SQL (DML). Но не в одной из найденных мной систем дистанционного обучения не было обнаружено функциональности по обучению настройки производительности СУБД Oracle на основе оптимизатора запросов.

Ресурс PostgreSQL Exercises предоставляет ряд вопросов и объяснений, построенных на одном простом наборе данных. Он предназначен для использования в качестве партнера для хорошей книги или документации Postgres. Упражнения на этом сайте варьируются от простых select и where, объединений и операторов case, до агрегатов, оконных функций и рекурсивных запросов. Данный ресурс предоставляет возможность ознакомится с практикой написания запросов к СУБД Postgres. На рисунке 3 изображена рабочая область данного инструмента.

Рисунок 3 - Рабочая область PostgreSQL Exercises.

Отметим положительные стороны данной системы:

Возможность бесплатного обучения;

Задачи на несколько видов запросов;

Ведется рейтинг участников;

При решении задач доступны подсказки;

Есть теоретический курс и справочник по SQL;

При затруднении с решением есть возможность получить помощь сообщества на форуме.

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

Ресурс SQLBolt предоставляет возможность изучать синтаксис SQL изучать теорию выполняя задания, представленные на сайте. Есть навигация по темам. Интерфейс представлен на рисунке 4.

Рисунок 4 -Интерфейс SQLBolt.

Положительные стороны данной системы:

возможность бесплатного обучения;

задачи на несколько видов запросов;

ведется рейтинг участников;

при решении задач доступны подсказки;

есть теоретический курс и справочник по SQL;

при затруднении с решением есть возможность получить помощь сообщества на форуме.

Возможность выбора нескольких СУБД

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

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

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

На данном ресурсе можно попробовать свои силы в написании SQL запросов на несколько СУБД. Так же там представлено множество других возможностей тестирования по языкам про гаммирования.

Рисунок 5 - Интерфейс HackerRank.

Положительные стороны данной системы:

возможность бесплатного обучения;

задачи на несколько видов запросов;

ведется рейтинг участников;

при решении задач доступны подсказки;

есть теоретический курс и справочник по SQL;

при затруднении с решением есть возможность получить помощь сообщества на форуме.

Возможность выбора нескольких СУБД

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

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

Запросы к базе данных могут быть двух видов:

изменение структуры данных (создание, изменение или удаление таблиц, представлений, триггеров, индексов и так далее);

доступ к данным (вставка новых данных, удаление, обновление или выборка сохраненных данных).

Первый вид запросов осуществляется средствами DDL (Data Definition Language - язык описания данных). Такие запросы выполняются однократно при необходимости изменения схемы базы данных.

Второй вид запросов осуществляется средствами DML (Data Manipulation Language - язык манипулирования данными.). Эти запросы выполняются при многократном обращении к данным.

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

Рисунок 5 - Интерфейс HackerRank

Показатель времени базы данных можно увидеть на странице Top Activity в Enterprise Manager-е (Рисунок 1), где можно увидеть количество активных сессий с системой управления базами данных и время потраченное СУБД на обработку запроса, т.е. Database Time.

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

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

В СУБД Oracle существуют сотни таблиц, хранящих различную системную информацию, информацию о работу СУБ. Статистика в этих таблицах довольно разная, а период за который была собрана эта статистика может быть очень большим, соответственно объем и информации хранящийся в этих таблицах довольно большой. Анализ такого большого объема данных вручную может занять месяцы, а результат анализа уже может быть не актуальным. Для автоматического анализа показателей базы данных создан диагностическое хранилище Automatic Workload Repository (AWR). Принцип работы Automatic Workload Repository заключается в следующем: периодически (по умолчанию - раз в час) снимается диагностическая информация с базы данных -метрики, основную статистику, статистику по SQL-запросам и так далее. Данные AWR передаются в базу данных и используются для построения диагностических отчетов.

AWR-отчет по сохраняется в формате HTML. Так же есть возможность сохранять результаты в формате Performance Hub, - данный формат отображает статистику работы базы данных в удобной и наглядной графической форме (Рисунок 6).

Рисунок 6 - Performance Hub отчет.

Встроенный в базу данных анализатор Automatic Database Diagnostic Monitor (ADDM) помогает интерпретировать диагностическую информацию и выявлять причину плохой производительности. AWR-отчет представляет собой объемный документ с множеством различной информации о базе данных. ADDM интерпретирует статистику, хранящуюся в Workload Repository и ищет причины возникновения проблем производительности. ADDM анализирует трату Database Time, сопоставляет его с активностью в сессиях и создает отчет с конкретными рекомендациями по устранению выявленных проблем. ADDM не просто ищет проблемные места, и уведомляет администратора базы данных, а выявляет причины возникновения проблем производительности и сортирует их по степени влияния на базу данных. ADDM-отчеты сохраняются в AWR-репозитории для исторического анализа. Real-Time ADDM это средство аварийного мониторинга, используемое при невозможности обычного соединения с базой данных в силу неработоспособности СУБД или иных причин. Данное средство создает особое диагностическое соединение, и снимает диагностическую информацию напрямую из памяти СУБД. Встроенный в базу данных анализатор помогает в процессе диагностике проблем и может определить причину возникновения проблем. Для подробного анализа выполнения SQL-запросов в Enterprise Manager существует специальное окно Real-Time SQL Monitoring, позволяющее следить выполнением конкретного SQL-запроса, план выполнения каждого запроса, и в каком месте плана запроса происходит наиболее затратные операции. План считается неактуальным, если между построением плана и выполнением запроса изменилась структура базы данных (например, изменился один из индексов у запрашиваемой таблицы). Если план найти не удалось или он неактуален, то оптимизатор запросов Oracle строит всевозможные планы запроса и выбирает оптимальный. В Real-Time SQL Monitoring имеются очень интересные показатели, такие как Actual Rows и Estimated Rows. При помощи Real-Time SQL Monitoring можно контролировать процесс выполнения PL/SQL-процедур. Так как в процессе обучения языку SQL у пользователей будет отсутствовать возможность посмотреть AWR отчеты, то необходимо реализовать частичный вывод информации пользователю о его запросе для анализа.

Анализ запроса состоит из двух частей: синтаксический анализ и семантический анализ [2]. Синтаксический анализ проверяет правильность написания SQL-запроса. Семантический анализ проверяет наличие указанных объектов (таблицы, представления и прочее) и доступ к ним. Анализ запроса выполняет СУБД.

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

Эффективность выполнения запросов доступа к данным является критичным параметром производительности базы данных. Под эффективность выполнения запросов можно понимать время обработки запроса. Скорость выполнения запроса зависит от различных факторов:

Скорость соединения

Архитектура системы

Аппаратное обеспечение сервера приложений

Структура базы данных

Размер выборки

Структура запроса

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

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

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

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

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

Несмотря на все указанные недостатки использования времени выполнения запроса, многие системы, а также программисты при написании SQL запросов к СУБД продолжают использовать данную метрику как качественную.

1.2 План запроса

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

Существует два вида плана запроса:

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

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

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

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

Toad

SQL Navigator

PL/SQL Developer

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

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

Cardinality (или Rows) - кардинальность.

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

Приведем пример плана выполнения запроса полученного в Toad

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S

WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup')

AND SB.RANK >70 AND ROWNUM <2 ORDER BY D.SHORTNAME;

На рисунке 7 можно увидеть графическое отображение плана запроса, полученного с помощью инструмента Toad.

Рисунок 7 - Графическое отображение плана запроса.

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

Вместе с тем, многолетний опыт оптимизации показывает, что качественный анализ эффективности запроса требует, помимо Cost и Cardinality, рассмотрения других дополнительных показателей:

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

IO Cost - стоимость ввода-вывода;

Temp Space - показатель использования дискового пространства.

Если дисковое пространство используется (при нехватке оперативной памяти для выполнения запроса, как правило, для проведения сортировок, группировок и т.д.), то с большой вероятностью можно говорить о неэффективности запроса. Указанные дополнительные параметры с соответствующей настройкой можно увидеть в PL/SQL Developer и Toad при их соответствующей настройке. Для PL/SQL Developer в окне с планом выполнения надо выбрать изображение гаечного ключа, войти в окно Preferensec добавить дополнительные параметры в Select Column, после чего и нажать OK. В Toad в плане выполнения по правой кнопке мыши выбирается директива Display Mode, а далее Graphic, после чего появляется дерево, в котором по каждому листу нажатием мышки можно увидеть дополнительные параметры: CPU Cost, IO Cost, Cardinality. Структура плана запроса, указанного выше, в виде дерева приведена ниже.

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

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

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

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

Реальный план выполнения запроса и указанный выше перечень характеристик для анализа ресурсоемкого запроса дают динамические представления 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

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

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

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

TRACEFILE_IDENTIFIER='M_2013'

TIMED_STATISTICS=TRUE SQL_TRACE=TRUE;

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

Select * from AGREEMENT where ISN=138518816;

ALTER SESSION SET SQL_TRACE=FALSE;

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

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

Трассировочный файл для удобства чтения расшифровывается утилитой Tkprof.

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

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

DECLARE

my_task_name varchar2(30);sqltext clob;rep_tuning clob;

BEGIN

Begin DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task');

exception when others then NULL; end;

MY_SQLTEXT:= '

--текст запроса (без точки с запятой в конце запроса)

' ;

MY_TASK_NAME:=DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => my_sqltext,

TIME_LIMIT => 60, --задается время выполнения в секундах

TASK_NAME =>'my_sql_tuning_task', DESCRIPTION=> my_task_name ,

SCOPE => DBMS_SQLTUNE.scope_comprehensive);

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_task');

exception when others then null;end;

END;

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

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

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

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

В результате работы SQLTUNE выдает рекомендации (если Oracle посчитает, что есть что рекомендовать). Рекомендациями могут быть: собрать статистику, построить индекс, запустить команду создания нового эффективного плана и т. д.

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

Анализ плана выполнения запроса имеет определенную последовательность действий. Рассмотрим на примере плана выполнения запроса из представление V$SQL_PLAN для ранее приведенного запроса

SELECT D.ISN FROM SUBDUTY SB, DICTI D, SUBHUMAN S

WHERE SB.ISN=S.DUTYISN AND S.ISN=D.ISN AND S.DEPTISN =C.GET('prolonggroup')

AND SB.RANK >70 AND ROWNUM <2 ORDER BY D.SHORTNAME;

При анализе план просматривается снизу вверх. В процессе просмотра в первую очередь обращается внимание на строки с большими Cost, CPU Cost.

Как видно из плана, резкий скачек этих значений имеется в 4-ой строке. Причиной такого скачка является 5-я строка с INDEX FULL SCAN, указывающая на наличие полного сканирование индекса X_DICTI_NAME таблицы DICTI. С этих строк и надо начинать поиск причины ресурсоемкости запроса. После нахождения строки с большим Cost и CPU Cost продолжается просмотр плана снизу вверх до следующего большого CPU Cost и т.д. При этом, если CPU Cost в строке близок к CPU Cost первой строки (максимальное значение), то найденная строка является определяющей в ресурсоемкости запроса и с ней в первую очередь надо искать причину ресурсоемкости запроса.

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

В связи с чем, при наличии высокоселективных индексов, целесообразно посмотреть, а не улучшит ли план выполнения хинт 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.

1.4 Рекомендации по улучшению запроса

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

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

Анализ статистики - оптимизатор рекомендует собирать статистику по объектам с отсутствующей или устаревшей статистикой. Дополнительная статистика для этих объектов хранится в профиле SQL.

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

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

Анализ пути доступа - оптимизатор исследует влияние новых или измененных индексов на путь доступа. Его рекомендации по индексам относятся к конкретному утверждению, поэтому при необходимости он также предлагает использовать SQL Access Advisor для проверки влияния этих индексов на репрезентативную рабочую нагрузку SQL.

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

Функции автоматической настройки SQL доступны из Enterprise Manager на странице «Advisor Central» или из PL / SQL с использованием пакета DBMS_SQLTUNE. Эта статья будет посвящена API PL / SQL, поскольку интерфейс Enterprise Manager достаточно интуитивен.

Чтобы получить доступ к API консультанта по настройке SQL, пользователю необходимо предоставить привилегию ADVISOR.

CONN sys/password AS SYSDBA

GRANT ADVISOR TO user;

GRANT ADMINISTER SQL MANAGEMENT OBJECT TO user;

CONNuser / user

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

DECLARE

tune_task_id VARCHAR2(50);

BEGIN

tune_task_id := DBMS_SQLTUNE.create_tuning_task (

begin_snap => 766,

end_snap => 939,

sql_id =>'19v5guvssdfds,

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 30,

task_name =>'19v5guvssdfds _tuning_task',

description =>'Tuning task for statement 19v5guvssdfds ');

END;

Если указан параметр TASK_NAME, его значение возвращается в качестве идентификатора задачи настройки SQL. Если он опущен, возвращается сгенерированное системой имя типа «TASK_1478». Если для параметра SCOPE установлено значение scope_limited, анализ профилирования SQL опускается. Параметр TIME_LIMIT просто ограничивает время, которое оптимизатор может потратить на составление рекомендаций. Следующие примеры будут ссылаться на последний набор настроек, поскольку он не имеет внешних зависимостей, кроме схемы SCOTT. NVL в операторе SQL был введен, чтобы вызвать реакцию оптимизатора. Кроме того, мы можем удалить статистику из одной таблицы, чтобы спровоцировать ее еще больше.

EXEC DBMS_STATS.delete_table_stats('TEST','EMP');

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

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name=>'tuning_task');

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

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

SET LONG 9000;

SET PAGESIZE 900

SET LINESIZE 150

SELECT DBMS_SQLTUNE.report_tuning_task('tuning_task') AS recommendations FROM dual;

SETPAGESIZE 30

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

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name =>'19v5guvssdfds _tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name =>'19v5guvssdfds _tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name =>'tuning_task');

DBMS_SQLTUNE.drop_tuning_task (task_name => `tuning_task');

END;

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

Так же можно использовать наборы настроек SQL - это группа операторов вместе с контекстом их выполнения. Они могут быть созданы автоматически через Enterprise Manager или вручную, если у вас есть необходимые привилегии.



3 Проектирование

3.1 Варианты использования системы

На рисунке 2 представлена диаграмма Use Case, на которой отображены возможные действия ролей системы: администратор, обучающийся.

Рисунок 8 - Диаграмма UseCase

3.2 Проектирование метода получения стоимости запроса

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

Существуют разные мнения относительно объема программных архитектур:

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

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

?То, что является фундаментальным для понимания системы в ее среде.

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

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

Архитектура программного обеспечения демонстрирует следующее:

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

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

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

?Повторяющиеся стили: подобно архитектуре здания, дисциплина в архитектуре программного обеспечения разработала стандартные способы решения возникающих проблем. Эти «стандартные способы» называются различными именами на разных уровнях абстракции. Общие термины для повторяющихся решений: архитектурный стиль, тактика, эталонная архитектура и архитектурный паттерн.

?Концептуальная целостность: архитектура системы программного обеспечения представляет общее видение того, что она должна делать и как она должна это делать. Это видение должно быть отделено от его реализации. Архитектор берет на себя роль «хранителя видения», следя за тем, чтобы дополнения к системе соответствовали архитектуре, следовательно, сохраняя концептуальную целостность.

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

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

Серверная часть приложения разбита на слои, отвечающие за определенный вид задач. Всего можно выделить 4 три слоя: слой, отвечающий за работу пользовательского интерфейса - User Interface(UI), слой сервисов отвечающих за логику работы приложения - Business Service(BS) , слой для работы с базой данных - Data Access(DA) и слой хранения данных.

В разработанном приложении реализован механизм вычисления стоимости запроса по таймеру. Каждый определенный промежуток времени запускается процедура вычисления стоимости запроса. Это необходимо как для вычисления стоимости запроса для уже существующих в системе решений, так и для вновь написанных. На момент реализации решения таких решений насчитывается 74000, что требует особого внимания к ресурсам при реализации алгоритмов. Процедура запускается через определенный промежуток времени, выполняет поиск решений, для которых еще нет записей о стоимости запроса, для каждого запроса происходит поиск SQL_ID - уникального идентификатора запроса. Если идентификатор запроса не найден, то происходит повторное выполнение запроса пользователя к системе. После его выполнения идентификатор должен присутствовать в системном представлении V$SQL. Далее из представления V$SQL_PLAN происходит выборка плана запроса по идентификатору запроса. Далее результат выборки записывается в созданную таблицу STATUS_COST_SQL.

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

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

Рисунок 10 - Алгоритм получения плана запроса.



4 Реализация

4.1 Средства разработки

Система дистанционного практикума реализована на кроссплатформенных инструментах:

web-интерфейс написан на языке Perl [4,5];

модуль управления ходом проверки написан на языке C++ [6,7];

шаблоны страниц сайта написаны на языке HTML;

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

обучение курсу SQL происходит на СУБД Oracle.

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

web-сервер Apache 2.2;

интерпретатор Perl;

СУБД Firebird 2.5

IBExpert для администрирования и работы с базой данных системы;

СУБД Oracle 11.2

среда разработки Visual Studio.

IntelliJ IDEA 2019.2

JDK 1.8

Apache maven

4.2 Разработка структуры данных

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

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

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

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

В таблице PROBLEMS хранятся данные о задачах. В столбце ID_PRB содержится номер задачи (первичный ключ таблицы). В таблице STATUS хранится информация о решениях задач. В столбце ID_STAT содержится номер присланного пользователем решения (первичный ключ таблицы). Добавим связанную таблицу STATUS_COST_SQL со следующими полями:

ID_STAT - номер решения (внешний ключ);

COST - вычисленная стоимость решения;

DATE_UPDATE - дата обновления стоимости решения

Когда система вычисляет стоимость решения, данные сохраняются в таблицу STATUS_COST_SQL

4.3 Разработка алгоритмов

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

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

В файле pom.xml указываем необходимые зависимости: Spring mvc Spring boot.

Apache Maven - это инструмент управления и сборки программного обеспечения. Основываясь на концепции объектной модели проекта (POM), Maven может управлять сборкой проекта, составлением отчетов и документацией из центрального репозитория.

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

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

Spring Boot позволяет легко создавать автономные производственные приложения на основе Spring, которые можно «просто запустить». Мы принимаем взвешенное мнение о платформе Spring и сторонних библиотеках, чтобы вы могли начать работу с минимальными усилиями. Большинству приложений Spring Boot требуется очень небольшая конфигурация Spring. Возможности Spring Boot представленны ниже.

Создание автономных приложений Spring

Внедрить Tomcat, Jetty или Undertow напрямую (не нужно развертывать файлы WAR)

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

Автоматическая настройка Spring и сторонних библиотек при любой возможности

Обеспечить готовые к работе функции, такие как метрики, проверки работоспособности и внешняя конфигурация

Абсолютно нет генерации кода и не требуется настройка XML

Основной особенностью Spring является реализация принципа инверсии управления (IoC). IoC также известен как внедрение зависимостей (DI). Это процесс, при котором объекты определяют свои зависимости (то есть другие объекты, с которыми они работают) только через аргументы конструктора, аргументы метода фабрики или свойства, которые устанавливаются в экземпляре объекта после того, как он создан или возвращен из метода фабрики. Затем контейнер внедряет эти зависимости при создании компонента. Этот процесс в своей основе является инверсией (отсюда и имя, инверсия управления) самого компонента, управляющего созданием экземпляров или расположением его зависимостей, с помощью прямого конструирования классов или механизма, такого как шаблон Service Locator.

Пакеты org.springframework.beans и org.springframework.context являются основой для контейнера IoC Spring Framework. Интерфейс BeanFactory предоставляет расширенный механизм конфигурации, способный управлять объектами любого типа. ApplicationContext - это подчиненный интерфейс BeanFactory. Добавляет:

Обработка ресурса сообщения (для использования в интернационализации)

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

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

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

Интерфейс org.springframework.context.ApplicationContext представляет контейнер IoC Spring и отвечает за создание, настройку и сборку bean-компонентов. Контейнер получает инструкции о том, какие объекты создавать, настраивать и собирать, читая метаданные конфигурации. Метаданные конфигурации представлены в виде XML, аннотаций Java или кода Java. Это позволяет вам выражать объекты, составляющие ваше приложение, и богатую взаимозависимость между этими объектами. Несколько реализаций интерфейса ApplicationContext поставляются с Spring. В автономных приложениях обычно создается экземпляр ClassPathXmlApplicationContext или FileSystemXmlApplicationContext. Хотя XML является традиционным форматом для определения метаданных конфигурации, вы можете указать контейнеру использовать аннотации Java или код в качестве формата метаданных, предоставив небольшой объем конфигурации XML для декларативного включения поддержки этих дополнительных форматов метаданных.

В листинге 1 приведен пример конфигурации зависимости в файле pom.xml для фреймворка Spring boot.

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>2.0.0.RELEASE</version>

</parent>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<dependency>

<groupId>com.h2database</groupId>

<artifactId>h2</artifactId>

<scope>runtime</scope>

...