10 как определить время выполнения сегмента кода pl sql

Oracle statistics. Как узнать статистику и время выполнения запроса?

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

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

В данной статье не будет рассказано как анализировать показатели, а будет рассказано как их получить.

Описание компонентов

v$mystat

Представление выводит статистику по последней выполненной транзакции в рамках текущей сессии. Состоит из следующих полей:

v$statname

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

v$timer

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

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, а также находить ответы на вопросы «Выполняется ли сборка по понедельникам быстрее, чем по вторникам?» или «Какая смена работает более производительно, первая или вторая?» Впрочем, я забегаю вперед. Этот тривиальный пример просто демонстрирует основные концепции интервалов. Ваша задача как программиста — найти творческое применение этим концепциям.

Источник