Oracle statistics. Как узнать статистику и время выполнения запроса?
Зачем вообще нужна эта статистика или любые другие показатели? В ходе разработки часто возникает вопрос, а что же быстрее работает? Эффективно ли расходуются ресурсы базы? Если написать код по другому станет ли лучше?
Для ответов на эти вопросы нужно как-то понимать какие ресурсы тратят программы, а самое главное нужна возможность сравнивать разные решения между собой, чтобы отдать предпочтение самому оптимальному способу.
В данной статье не будет рассказано как анализировать показатели, а будет рассказано как их получить.
Описание компонентов
v$mystat
Представление выводит статистику по последней выполненной транзакции в рамках текущей сессии. Состоит из следующих полей:
| Поле | Тип | Описание |
|---|---|---|
| sid | number | идентификатор текущей сессии, полную информацию можно увидеть в v$session |
| statistic# | number | идентификатор значения статистики, ссылается на преставление v$statname |
| value | number | значение статистики |
| con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
v$statname
Справочник с описанием значений статистики, нужен для связи с предыдущим представлением. Состоит из следующих полей:
v$timer
Это простое представление, которое возвращает время в сотых долях секунды, мы будет фиксировать время до и после теста, и затем отнимать одно значение от другого. Состоит из двух полей:
| Поле | Тип | Описание |
|---|---|---|
| hsecs | number | количество времени в сотых долях секунды |
| con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
dbms_utility.get_cpu_time
Функция, которая возвращает время работы процессора (CPU) в сотых долях секунды.
Права
Для выполнения представления у пользователя (в моем случае PROD) должен быть доступ на все таблицы, подключаемся к БД с помощью пользователя SYS с ролью SYSDBA и выполняем скрипты ниже:
Измерение общего и CPU времени на запрос
Результат:
Статистика
Получение статистики по одному запросу
Создание представления для быстрого доступа к данным
Для быстрого доступа к данным, соединим все ранее представленные скрипты по выводу данных по статистки, времени ЦПУ, общему времени и «упакуем» полученный запрос в представление. Вот как это будет выглядеть:
После создания VIEW, чтобы получить информацию достаточно выполнить простой запрос:
Создание процедуры фиксации статистики
До и после выполнения наших скриптов, нам нужно записывать данные «до» запуска и «после», будем записать результаты с помощью процедуры ниже:
Запустим программу (если будут ошибки, пишите в комментариях), пока результат мы не увидим, но статистику уже запишем:
Вывод результатов
Вывод статистики скриптом
Сразу обернем такой селект в VIEW для дальнейшего использования и расширения, также для лучшей читаемости, добавим форматирование для числа, чтобы тысячные доли разделялись запятыми:
Запускаем созданный VIEW, по желанию добавляем фильтры и сортируем:
Результат:
Вывод статистики через dbms_output
Создадим процедуру stats_report_prc и на вход будем подавать два фильтра, плюс я выведу отдельно от таблицы со статисткой показатели времени и буду выводить их в секундах:
Запустим отчет и посмотрим что получилось:
Результат:
Как записать время выполнения в хранимой процедуре Oracle
Я хочу записать время выполнения определенных хранимых процедур SELECT в Oracle. Я разбил это на следующие шаги.
ВАЖНОЕ ПРИМЕЧАНИЕ. Оператор SELECT выполняется несколько минут.
Происходит вот что:
Таблица LOG НЕ должна обновляться до завершения всей процедуры.
По сути, происходит то, что процедура немедленно вставляет, а затем обновляет таблицу LOG «BEFORE», инструкция SELECT завершается.
Я попытался обернуть и вложить дополнительные операторы BEGIN и END. Хранимая процедура по-прежнему выполняет инструкцию «UPDATE» в конце процедуры ДО возврата инструкции SELECT.
Есть ли свойство, которое я могу установить в PROC, чтобы заставить процедуру не выполнять следующую команду, пока предыдущая команда не завершится. Не имеет смысла, что процедура не работает по порядку?
2 ответа
Я перечитал вопрос и комментарии Джастина, и, основываясь на его предложении, придумал для него кодовое решение. Сначала общая настройка структур базы данных:
Затем нам понадобится хранимая процедура с автономной транзакцией для регистрации времени и табличная функция, которая позволяет нам запрашивать данные из коллекции. Мы можем передать курсор в функцию в Select, чтобы проверить, что она работает:
Теперь оригинальная процедура, которая будет вызывать функцию, передающую ref-курсор, а затем перенаправлять этот курсор в свой параметр для клиентского приложения:
И, наконец, фрагмент кода для проверки того, что только после того, как клиентское приложение извлечет данные из табличной функции, будет запись в журнале за прошедшее время:
Операторы управления выполнением программы PL/SQL
Операторы условного перехода (IF …)
Существует три модификации оператора условного перехода:
Во всех модификациях если «условие» или «условие1″ истинно (TRUE), то выполняется «последовательность команд» или «1-я последовательность команд» и управление передается на первый оператор после END IF. Если же оно ложно (FALSE), то:
Все это справедливо, если внутри последовательности команд нет операторов, осуществляющих переход за пределы этой последовательности.
Метки и оператор безусловного перехода (GOTO)
В любом месте программы может быть поставлена метка, имеющая синтаксис: >
Оператор GOTO позволяет осуществить безусловный переход к метке, имя которой должно быть уникальным внутри программы или блока PL/SQL. Например, управление передается вниз к помеченному оператору:
В следующем примере управление передается вверх к помеченной последовательности операторов:
Следует отметить, что использование GOTO (особенно в тех случаях, когда метка предшествует оператору GOTO) может привести к сложным, нераспознаваемым кодам ошибок, которые трудно обрабатывать. Поэтому реже используйте GOTO, тем более что этот оператор нельзя использовать для выполнения перехода:
Операторы цикла (LOOP, WHILE…LOOP и FOR…LOOP)
Циклы служат для повторяемого выполнения последовательности команд. В PL/SQL используются три модификации операторов цикла: LOOP, WHILE…LOOP и FOR…LOOP.
Цикл LOOP имеет следующий синтаксис:
и приводит к бесконечному повторению последовательности команд, если внутри нее нет команд EXIT (выход из цикла), RAISE (вызов обработчика исключительных ситуаций) или GOTO (безусловный переход). Например,
LOOP
последовательность команд;
IF условие THEN EXIT;
END LOOP;
приведет к выходу из цикла после выполнения последовательности команд, как только условие станет истинным.
Цикл WHILE предназначен для повторения последовательности команд, пока условие остается истинным:
WHILE условие LOOP
последовательность команд;
END LOOP;
Наиболее распространен цикл FOR, имеющий следующий синтаксис:
FOR индекс IN [REVERSE] нижняя_граница..верхняя_граница LOOP
последовательность команд;
END LOOP;
Здесь индекс (счетчик циклов) изменяется от нижней до верхней границы с шагом 1, а при использовании «REVERSE» – от верхней до нижней границы с шагом. Например,
FOR i IN 1..3 LOOP — для i = 1, 2, 3
последовательность команд; — цикл выполняется 3 раза
END LOOP;
FOR i IN REVERSE 1..3 LOOP — для i = 3, 2, 1
последовательность команд; — цикл выполняется 3 раза
END LOOP;
Отметим, что в последнем случае пределы диапазона указываются в возрастающем, а не убывающем порядке.
Если нижняя граница равна верхней, последовательность выполняется один раз.
Если нижняя граница больше верхней, последовательность не выполняется, и управление переходит к следующему за циклом оператору.
Пределы диапазона цикла могут быть литералами, переменными или выражениями, но должны быть целыми числами. Например, допустимы следующие диапазоны:
Объявлять индекс не нужно – он объявлен неявно как локальная переменная типа integer.
PL/SQL позволяет определять диапазон цикла динамически во время выполнения. Например:
SELECT COUNT(otdel) INTO shtat_count FROM shtat;
FOR i IN 1..shtat_count LOOP
…
END LOOP;
Значение «shtat_count» – неизвестно во времени компиляции; предложение SELECT определяет это значение во время выполнения.
Индекс может использоваться в выражениях внутри цикла, но не может изменяться. Например:
FOR ctr IN 1..10 LOOP
…
IF NOT finished THEN
INSERT INTO … VALUES (ctr, …); — правильно
factor := ctr * 2; — правильно
…
ELSE
ctr := 10; — неправильно
END IF;
END LOOP;
Индекс определен только внутри цикла и на него нельзя ссылаться снаружи цикла. После выполнения цикла индекс неопределен. Например:
FOR ctr IN 1..10 LOOP
…
END LOOP;
sum := ctr – 1; — неверно
Подобно PL/SQL блокам, циклы могут быть помечены. Метка устанавливается в начале оператора LOOP, следующим образом:
Имя метки может также появляться в конце утверждения LOOP как в примере:
Помеченные циклы используются для улучшения чтения программы (разборчивости). С любой формой утверждения EXIT можно завершать не только текущий цикл, но и любой внешний цикл. Для этого маркируйте внешний цикл, который надо завершить, и используйте метку в утверждении EXIT, следующим образом:
<>
LOOP
…
LOOP
…
EXIT outer WHEN … — завершаются оба цикла
END LOOP;
…
END LOOP outer;
Если требуется преждевременно выйти из вложенного цикла FOR, маркируйте цикл и используйте метку в утверждении EXIT. Например:
<>
FOR i IN 1..5 LOOP
…
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT outer WHEN s1%NOTFOUND; — завершаются оба цикла
…
END LOOP;
END LOOP outer;
– управление передается сюда
Операторы EXIT, EXIT-WHEN и NULL
EXIT используется для завершения цикла, когда дальнейшая обработка нежелательна или невозможна. Внутри цикла можно помещать один или большее
количество операторов EXIT. Имеются две формы EXIT: EXIT и EXIT-WHEN.
По оператору EXIT цикл завершается немедленно и управление переходит к следующему за END LOOP оператору. Например:
LOOP
…
IF … THEN
…
EXIT; — цикл завершается немедленно
END IF;
END LOOP;
– управление переходит сюда
По оператору EXIT-WHEN цикл завершиться только в том случае, когда становится истинным условие в предложении WHEN. Например:
LOOP
FETCH s1 INTO …
EXIT WHEN s1%NOTFOUND; — конец цикла, если условие верно
…
END LOOP;
CLOSE s1;
Оператор EXIT-WHEN позволяет завершать цикл преждевременно. Например, следующий цикл обычно выполняется десять раз, но как только не находится значение s1, цикл завершается независимо от того сколько раз цикл выполнился.
FOR j IN 1..10 LOOP
FETCH s1 INTO ShRec;
EXIT WHEN s1%NOTFOUND; — выход при отсутствии возвращаемой строки
…
END LOOP;
NULL – пустой оператор; он передает управление к следующему за ним оператору.
Однако, к нему может передаваться управление и его наличие часто улучшает читаемость программы. Он также полезен для создания фиктивных подпрограмм для резервирования областей определения функций и процедур при отладке программ.
Запись опубликована 09.04.2010 в 6:32 дп и размещена в рубрике Oracle7 краткий справочник. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Основы языка PL/SQL
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
Ниже приведен пример простого блока кода PL/SQL:
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
Еще один простой вид цикла позволяет выполнять конструкция LOOP. EXIT. WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Неявные курсоры
Неявные курсоры автоматически применяются Oracle всякий раз, когда в коде PL/SQL используется оператор SELECT. Они могут использоваться лишь в тех операторах, которые возвращают одну строку. В случае если SQL-оператор возвращает более одной строки, будет выдаваться сообщение об ошибке.
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Типы данных INTERVAL в PL/SQL: интервал между датами
Объявление интервальных переменных в PL/SQL
По сравнению с другими объявлениями переменных PL/SQL синтаксис объявлений переменных обоих типов INTERVAL несколько необычен. Помимо того, что имена этих типов состоят из нескольких слов, для них задается не одно, а два значения, определяющих точность:
Здесь имя_переменной — имя объявляемой переменной INTERVAL ; точность_лет — количество цифр (от 0 до 4), выделенное для представления количества лет (по умолчанию 2); точность_дней — количество цифр (от 0 до 9), выделенное для представления количества дней (по умолчанию 2); точность_долей_секунды — количество цифр (от 0 до 9), выделенное для представления количества долей секунды (по умолчанию 6).
Доли секунды указываются потому, что значения типа INTERVAL DAY TO SECOND могут определять интервалы с указанной точностью до долей секунды. Значения типа INTERVAL YEAR TO MONTH не могут содержать долей месяца, и последние для них не задаются.
Когда используются типы INTERVAL в PL/SQL
Используйте типы данных INTERVAL во всех случаях, когда вам потребуется обрабатывать промежутки времени. В этом разделе приведены два примера; хочется верить, что они вызовут у вас интерес и помогут представить, как эти типы данных могли бы использоваться в создаваемых вами системах.
Вычисление разности между двумя значениями даты/времени
Типы INTERVAL удобно использовать для вычисления разности между двумя значениями даты/времени. В следующем примере вычисляется срок работы сотрудника:
Непосредственное вычисление количества лет и месяцев работы выполняется в следующей строке:
Здесь YEAR TO MONTH — часть синтаксиса выражения, возвращающего интервал. Подробнее о нем рассказывается далее в этой главе. Как видите, вычисление продолжительности интервала сводится к простому вычитанию одной даты из другой. Без типа данных INTERVAL нам пришлось бы программировать вычисления самостоятельно:
Тип INTERVAL YEAR TO MONTH выполняет округление значений, и вы должны знать о возможных последствиях этой операции. За подробностями обращайтесь к разделу «Арифметические операции над значениями даты/времени».
Обозначение периода времени
В этом примере анализируется работа конвейерной сборки. Важной метрикой эффективности является время, необходимое для сборки каждого продукта. Сокращение этого интервала повышает эффективность работы конвейера, поэтому начальство желает постоянно контролировать его продолжительность. В нашем примере каждому продукту присваивается контрольный номер, используемый для его идентификации в процессе сборки. Информация хранится в следующей таблице:
При передаче интервалов программам PL/SQL и из них необходимо использовать ключевое слово UNCONSTRAINED (см. далее раздел «Типы данных INTERVAL без ограничений»). Хранение времени сборки в таблице упрощает анализ данных. Мы можем легко определить минимальное, максимальное и среднее время сборки при помощи простых функций SQL, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.