Цель
Зачем вообще нужна эта статистика или любые другие показатели? В ходе разработки часто возникает вопрос, а что же быстрее работает? Эффективно ли расходуются ресурсы базы? Если написать код по другому станет ли лучше?
Для ответов на эти вопросы нужно как-то понимать какие ресурсы тратят программы, а самое главное нужна возможность сравнивать разные решения между собой, чтобы отдать предпочтение самому оптимальному способу.
В данной статье не будет рассказано как анализировать показатели, а будет рассказано как их получить.
Описание компонентов
v$mystat
Представление выводит статистику по последней выполненной транзакции в рамках текущей сессии. Состоит из следующих полей:
Поле | Тип | Описание |
---|---|---|
sid | number | идентификатор текущей сессии, полную информацию можно увидеть в v$session |
statistic# | number | идентификатор значения статистики, ссылается на преставление v$statname |
value | number | значение статистики |
con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
v$statname
Справочник с описанием значений статистики, нужен для связи с предыдущим представлением. Состоит из следующих полей:
Поле | Тип | Описание |
---|---|---|
statistic# | number | идентификатор значения статистики, ссылается на преставление v$statname |
name | varchar2(64) | расшифровка значения статистики (постоянное значение) |
class | number | показывает к какому классу данных относится статистика, ниже описаны возможные значения: 1 - Пользователь 2 - Redo 4 - Постановка в очередь 8 - Кэш 16 - ОС 32 - Реальные Кластеры приложений 64 - SQL 128 - Отладка |
stat_id | number | идентификатор статистики (в этой статье не понадобится) |
display_name | varchar2(64) | детальная расшифровка значения статистики (можем меняться с новыми релизами) |
con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
v$timer
Это простое представление, которое возвращает время в сотых долях секунды, мы будет фиксировать время до и после теста, и затем отнимать одно значение от другого. Состоит из двух полей:
Поле | Тип | Описание |
---|---|---|
hsecs | number | количество времени в сотых долях секунды |
con_id | number | идентификатор контейнера (для мультиарендной контейнерной базы данных CDB) |
dbms_utility.get_cpu_time
Функция, которая возвращает время работы процессора (CPU) в сотых долях секунды.
Пример вызова:
Права
Для выполнения представления у пользователя (в моем случае PROD) должен быть доступ на все таблицы, подключаемся к БД с помощью пользователя SYS с ролью SYSDBA и выполняем скрипты ниже:
Измерение общего и CPU времени на запрос
Логика простая - мы будем записывать значения времени в переменные до выполнения запроса и после, затем вычитать время завершения от времени начала и получать итоговый результат. Вывод результата будет осуществляться с помощью dbms_output.put_line, чтобы данные выводилилсь в SQL Developer перед выполнением скриптов выполним команду включающую вывод, а затем будет логика.
Результат:
Статистика
Получение статистики по одному запросу
Алгоритм для одного запроса будет довольно простой:
- Запускаем нужный скрипт, например, изменим ранее сгенерированные данные:
--обновим пять первых попавшихся строк update prod.operation set op_date = trunc(sysdate) where rownum <=5 and op_date != trunc(sysdate);
- Сразу после выполняем селект статистики:
select s.name, b.value from v$statname s join v$mystat b on s.statistic# = b.statistic#;
Результат:
Получение статистики по нескольким запросам и ее сравнение
Есть два способа:
- Выполнять алгоритм по замеру статистики для одного запроса (описано выше), результат сохранить, затем выполнить второй запрос, результат также сохранить и потом их сравнить.
- Написать небольшую программу для сбора статистики, которая будет автоматически собирать и сравнивать статистику двух запросов.
Программа по сбору статистики
Создание структуры хранения
Чтобы сравнивать данные по статистике разных запросов, эти данные нужно куда-то сохранять. Для этого создадим обычную таблицу:
Строка | Описание |
---|---|
create global temporary table stats_tbl | создаем временную таблицу с именем stats_tbl |
run_type varchar2(15 char), | для фиксации стадии сбора информации "до" запуска скрипта, "после", "итог" (сюда писать уже высчитанные значения) |
inf_type varchar2(15 char), | для указания типа информации т.е. мы умеем собирать статистику, измерять время ЦПУ и общее время, т.е. 3 разных типа информации, в будущем типы будут добавляться |
name varchar2(80 char), | название показателя |
value integer | значение показателя |
create_time timestamp default systimestamp | дата создания строки, со значением по умолчанию |
on commit preserve rows; | Временные таблицы хранят данные в течение транзакции или в течение сессии, тут указывается, что хранить нужно в течение сессии |
Создание представления для быстрого доступа к данным
Для быстрого доступа к данным, соединим все ранее представленные скрипты по выводу данных по статистки, времени ЦПУ, общему времени и "упакуем" полученный запрос в представление. Вот как это будет выглядеть:
После создания VIEW, чтобы получить информацию достаточно выполнить простой запрос:

Создание процедуры фиксации статистики
До и после выполнения наших скриптов, нам нужно записывать данные "до" запуска и "после", будем записать результаты с помощью процедуры ниже:
Пояснение отдельных частей кода:
Строка | Описание |
---|---|
p_script_num in char | первый входной параметр, предназначен для определения номера тестируемого скрипта |
p_run_type in stats_tbl.run_type%type | второй входной параметр, принимает на вход два значения "before" или "after", тут если мы фиксируем статистику до перед выполнением скрипта указываем "before", если после то соответственно "after" |
inf_type varchar2(15 char), | для указания типа информации т.е. мы умеем собирать статистику, измерять время ЦПУ и общее время, т.е. 3 разных типа информации, в будущем типы будут добавляться |
pragma autonomous_transaction; | процедура будет транзакцией, внутри основной транзакции, например, в рамках теста мы сделали update строк и в конце нужно будет отменить изменения, но данные по статистике должны сохраниться |
select row_number() over(partition by name, run_type order by s.id desc) rn | это для удаления дублей от запусков, например запустим два раза процедуру с параметром "after", будут дублироваться данные, эта функция группирует такие строки и проставляет номера, 1-самая актуальная, 2 - старее. |
or run_type = 'final'||p_script_num | это условие, чтобы не дублировались финальные данные |
on commit preserve rows; | Временные таблицы хранят данные в течение транзакции или в течение сессии, тут указывается, что хранить нужно в течение сессии |
Запустим программу (если будут ошибки, пишите в комментариях), пока результат мы не увидим, но статистику уже запишем:
Вывод результатов
Вывод статистики скриптом
Сразу обернем такой селект в VIEW для дальнейшего использования и расширения, также для лучшей читаемости, добавим форматирование для числа, чтобы тысячные доли разделялись запятыми:
Запускаем созданный VIEW, по желанию добавляем фильтры и сортируем:
Результат:
Вывод статистики через dbms_output
Создадим процедуру stats_report_prc и на вход будем подавать два фильтра, плюс я выведу отдельно от таблицы со статисткой показатели времени и буду выводить их в секундах:
Запустим отчет и посмотрим что получилось:
Результат:
Спасибо за внимание!