Как посмотреть журнал транзакций oracle

Обновлено: 04.07.2024

Узнайте, как получить идентификатор текущей транзакции базы данных для Oracle, SQL Server, PostgreSQL, MySQL и HSQLDB с помощью SQL-запроса.

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

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

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

По этой причине методы уровня обслуживания аннотируются аннотацией @Transactional .

Таким образом, при выполнении метода @Transactional все операторы SQL, выполняемые в контексте текущего бизнес-варианта использования, будут совместно использовать одну и ту же транзакцию базы данных.

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

Оракул

При использовании Oracle вам необходимо выполнить следующий SQL-запрос:

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

В представлении v$session содержится информация о нашем текущем сеансе или подключении к базе данных. Сопоставляя адрес сеанса между v$транзакцией и v$сеансом представлениями, мы можем найти идентификатор текущей текущей транзакции, указанный в столбце xid в представлении v$транзакция .

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

Oracle назначает идентификатор транзакции только в том случае, если ему необходимо назначить сегмент отмены, что подразумевает выполнение инструкции INSERT, UPDATE или DELETE DML.

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

SQL Server

При использовании SQL Server вам просто нужно выполнить следующий SQL-запрос:

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

PostgreSQL

При использовании сервера PostgreSQL вы можете выполнить следующий SQL-запрос, чтобы получить идентификатор текущей транзакции:

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

MySQL и MariaDB

При использовании MySQL или MariaDB вы можете выполнить следующий SQL-запрос, чтобы получить идентификатор текущей транзакции:

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

Точно так же, как это было в случае с Oracle, начиная с MySQL 5.6, только транзакции чтения и записи будут получать идентификатор транзакции.

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

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

HSQLDB

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

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

рис. Старый "дедовский" debug кода

Добрый день! Работая разработчиком Oracle PL/SQL, часто ли вам приходилось видеть в коде dbms_output.put_line в качестве средства debug-а? Стоит признать, что к сожалению, большинство (по моему личному мнению и опыту) разработчиков Oracle PL/SQL не уделяет должного внимания логированию как к «спасательному кругу» в случае возникновения ошибок. Более того, большая часть разработчиков не совсем понимает зачем нужно логировать информацию об ошибках и самое главное, не совсем понимают что делать и как использовать эту информацию в будущем.

Предисловие

Данным постом хотел бы начать цикл статей посвященных «Логированию ошибок» в Oracle PL/SQL. В первую очередь донести мысль до многих разработчиков, о том как можно построить функционал фиксации, хранения логов в БД. На своем опыте продемонстрировать поэтапный процесс создания полноценного логирования в БД. Рассказать как нам удалось создать логирование ошибок, разработать единую нумерацию событий для их дальнейшей идентификации, как поверх логирования «натянуть» мониторинг событий, создать функционал позволяющий увидеть все текущие ошибки в БД в виде таблиц (с указанием частоты возникновения ошибок и кол-ва и т.д.), графиков (отразить динамику роста кол-ва ошибок) и правильно распределить ресурсы для устранения тех или иных ошибок.

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

Введение

Модель логирования позволяет реализовать:

Единый подход в обработке и хранении событий

Собственную нумерацию и идентификацию событий происходящих в БД (статья)

Единый мониторинг событий (статья в разработке)

Анализ событий происходящих в БД (статья в разработке)

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

Единый подход в обработке и хранении событий

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

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

Наверное сейчас кто-то из читателей может возразить: "Зачем в обязательном порядке?". А всё очень просто, если вы разработчик PL/SQL и вы не согласны с этим правилом, то вот вам пример. Посмотрите на свой текущий проект более внимательно. Скорее всего вы найдете какое-нибудь логирование событий реализованное кем-то, когда-то. Вспомните сколько раз вы обращались к этому логированию при решении багов. Именно в таких ситуациях, когда есть срочность по времени в исправлении бага, вы или ваши коллеги начинают использовать dbms_output.put_line в качестве экспресс-дебага (быстрый способ получения значений переменных используемых в коде). Согласитесь, что для исправления бага мало знать в какой процедуре, в каком запросе и на какой строке возникла ошибка, необходимо знать параметры запроса на которых возникает ошибка. И вот тут нам на помощь приходит "Логирование событий", потому что помимо места возникновения ошибки мы узнаем параметры вызова процедуры, в которой возникает ошибка и это очень упрощает исправление бага.

Первая статья посвящена базовому функционалу «Логирования событий». В простейшей реализации это одна общая таблица и пакет процедур для работы с ней. Для создания и демонстрации логирования, нам необходимо реализовать следующие объекты БД (весь список объектов с их исходными кодами представлен в Git):

Таблица messagelog - единая таблица логов. Именно в данной таблице будет храниться информация о дате и времени события, об объекте где происходит событие, типе события с указанием кода, текста и параметров. В нашем примере, столбец backtrace вынесен в отдельную таблицу messagelog_backtrace для удобства.

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

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

До сих пор вы знакомились с компонентами системы базы данных Oracle: необходимыми файлами и распределением памяти, а также способами их настройки. Теперь пришло время посмотреть, как Oracle обрабатывает пользовательские запросы и как проводит изменение в данных. Важно понимать механизм обработки транзакций SQL, потому что все взаимодействие с базой данных Oracle происходит либо в форме запросов SQL, которые читают данные, либо операций SQL (или PL/SQL), которые модифицируют, вставляют или удаляют данные.

Транзакция – это логическая единица работы в базе данных Oracle, состоящая из одного или более операторов SQL. Транзакция начинается с первого исполняемого опертартора SQL и завершается, когда вы фиксируетет или отказываете транзакцию. Фиксация (commiting) транзакции закрепляет проведенные вами изменения, а откат (roll back) – конечно же, отменяет их. Как только вы зафиксировали транзакцию, все прочие транзакции других пользователей, которые начались после нее, смогут видеть изменения, проведенные вашими транзакциями.

Когда транзакция вообще не может выполниться (скажем, из-за отключения электропитания), то она вся целиком должна быть отменена. Oracle откатывает все изменения, проведенные предшествующими операторами SQL, возвращая данные в исходное состояние (которое они имели перед началом транзакции). Весь процесс построен так, чтобы поддерживать целостность данных – т.е. концепцию «все или ничего».

Следующий простой пример вставки строки описывает то, как Oracle обрабатывает транзакцию.

Фиксация и откат

Вы должны четно понимать два фундаментальных термина, касающихся транзакций: фиксаций (commiting) и откат (rolling back) транзакций. Ниже кратко объясняются оба термина.

Фиксация транзакции

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

Генерирует информацию отмены (undo), которая состоит из значений данных, подлежащих модификации, до изменений. Эти данные сохранятся в сегменте undo, расположенном в табличном пространстве undo.
Он также генерирует данные повторного выполнения (redo), содержащие изменения в блоках данных и в блоках отката, в буфер журнала повторного выполнения. База данных может писать на диск содержимое буферов журнала повторного выполнения перед фиксацией транзакций.
Проводит изменения в буферах базы данных, находящихся в SGA. База данных может писать модифицированные буферы на диск перед фиксацией транзакции.

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

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

Откат транзакции

Отменить изменения, выполненные транзакцией, которые еще не были зафиксированы можно с помощью команды ROLLBACK. В то время как журнал повторного выполнения содержит все изменения, проведенные в транзакции, сегмент отмены (undo) содержит все старые значения, которые существовали до момента проведения изменений. Вы можете либо откатить изменения, проведенные всей транзакцией, либо просто вернуться к маркеру, который поместили ранее внутри транзакции, называемому точкой сохранения (savepoint). Существует несколько типов отката, среди которых перечислены ниже:

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

Независимо от причины отката, процедура всегда одна и та же.

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

Целостность данных и параллелизм данных

База данных была бы не слишком полезной, если бы множество пользователей не могли обращаться к данным и модифицировать их одновременно. Под параллелизмом данных (a concurrency) понимают способность базы данных обеспечивать параллельный доступ для множества пользователей. Чтобы обеспечить согласованные результаты, база данных нуждается в механизме, который гарантирует, что пользователи не будут натыкаться на изменения, проводимые друг другом. Целостность данных (data consistence) - это возможность для пользователя получать согласованное представление данных, включая все изменения, проведенные в них другими пользователями.

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

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

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

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

Писатель базы данных и протокол опережающей записи

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

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

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

Системный номер изменения

Системный номер изменения, или SCN (system change number) – важный оценочный фактор, используемый базой данных Oracle для отслеживания состояния в каждый данный момент времени. Когда вы читаете (SELECT) данные в таблицах, то не затрагиваете состояния базы данных, но когда модифицируете, вставляете или удаляете строку, то состояние базы данных по отношению к тому, каким оно было до операции. Oracle использует SCN для слежения за всеми изменениями, проведенными в базе данных со временем. SCN – это логическая временная метка, используемая Oracle для упорядочивания событий, происходящих с базой данных. SCN очень важен по нескольким причинам, не последняя из которых – восстановление базы данных после сбоя.

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

SCN помогает Oracle определять необходимость восстановления после сбоя, после внезапного прерывания работы экземпляра базы данных или после издания команды SHUTDONW ABORT. Всякий раз, когда база данных выполняет операцию контрольной точки, Oracle пишет команду START SCN в заголовки файлов данных. Управляющий файл поддерживает значение SCN для каждого файла данных, называемый STOP SCN, который обычно устанавливается в бесконечность, и всякий раз, когда экземпляр останавливается нормально (командой SHUTDOWN NORMAL или SHUTDOWN IMMEDIATE). Oracle копирует номер START SCN в заголовках файлов данных в номера STOP SCN ля файлов данных в управляющем файле. Когда вы перезапускаете базу данных после успешного останова, нет необходимости ни в каком восстановлении, потому что номера SCN в файлах данных и управляющих файлах соответствуют. С другой стороны, внезапное прерывание работы экземпляра не оставляет времени на приведение в соответствие номеров SCN, и Oracle обнаруживает необходимость восстановления экземпляра, потому что отличаются номера SCN в файлах данных с одной стороны, и управляющем файле - с другой. Они играют ключевую роль в восстановлении базы данных. Oracle определяет, на сколько нужно вернуться, применяя архивные журналы повторного выполнения во время восстановления на основе SCN.

Управление отменой

Когда вы проводите изменения в базе данных, вы должны иметь возможность отменить или откатить это изменение при необходимости. Информация, необходимая для отмены или отката изменений транзакции, которая в основном состоит из информации таблицы, предшествующей изменению, называется данными отмены (векторами изменений) и хранится в записях отмены (undo records). При выдаче команды ROLLBACK Oracle использует эти записи отмены для замены измененных данных их исходными версиями. Записи отмены жизненно важны для восстановления базы данных, когда незавершенные или незафиксированные транзакции должны быть отменены, чтобы оставить базу в согласованном состоянии.

Oracle настоятельно рекомендует использовать средство автоматического управления изменениями (Automatic Undo Management - AUM), при котором сам сервер oracle будет поддерживать и управлять сегментами отмены (отката). Все, что вам нужно сделать – это предоставить выделенное табличное пространство undo и установить параметр инициализации UNDO_MANAGEMENT в auto. Oracle создаст необходимое количество сегментов отмены, которые структурно подобны традиционным сегментам отката, и будет расширять их по мере необходимости. Нет ничего необычного в том, что будут создаваться новые сегменты отмен, а старые – деативизироваться в зависимости от количества транзакций, проводимых в базе данных.

Поскольку Oracle самостоятельно управляет размерами индивидуальных сегментов отмены, два решения, которые вы должны принять, касаются размера табличного пространства undo и установки инициализационного параметра UNDO_RETINTION (который определяет, насколько долго Oracle будет стараться хранить для вас записи об отмене в табличном пространстве undo). Помните, что ваше табличное пространство undo должно не только вместить все долговременные транзакции, но так же быть достаточно большим, чтобы позволить работать всем средства ретроспективы (flashback), которые вы можете реализовать в вашей базе данных; средства ретроспективы Oracle позволяют отменять изменение данных на различных уровнях. Некоторые из них, такие как Flashback Query, Flashback Versions Query и Flashback Table используют данные отмены.

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

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

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

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

v$mystat

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

v$statname

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

v$timer

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

Поле	Тип	Описание
hsecs	number	количество времени в сотых долях секунды
con_id	number	идентификатор контейнера (для мультиарендной контейнерной базы данных CDB)

dbms_utility.get_cpu_time

Функция, которая возвращает время работы процессора (CPU) в сотых долях секунды.

select dbms_utility.get_cpu_time from dual; --результат 815

Права

Для выполнения представления у пользователя (в моем случае PROD) должен быть доступ на все таблицы, подключаемся к БД с помощью пользователя SYS с ролью SYSDBA и выполняем скрипты ниже:

grant select on v_$statname to prod; grant select on v_$mystat to prod; grant select on v_$timer to prod; grant execute on dbms_utility to prod;

Измерение общего и CPU времени на запрос

Логика простая - мы будем записывать значения времени в переменные до выполнения запроса и после, затем вычитать время завершения от времени начала и получать итоговый результат. Вывод результата будет осуществляться с помощью dbms_output.put_line , чтобы данные выводилилсь в SQL Developer перед выполнением скриптов выполним команду включающую вывод, а затем будет логика.

--включаем вывод данных в output set serveroutput on; declare v_t1 number; --время начала v_t2 number; --время завершения v_cpu1 number; --время ЦПУ до v_cpu2 number; --время ЦПУ после v_t_res number; --общее время выполнения v_cpu_res number; --общее время выполнения CPU begin --фиксируем начало --помещаяем общее время и время ЦПУ в переменные select t.hsecs ,dbms_utility.get_cpu_time into v_t1 ,v_cpu1 from v$timer t; --запустим 1000 раз цикл, который обновит дату у первых попавшихся строк for rec in 1..1000 loop update prod.operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение --снова помещаяем общее время и время ЦПУ в переменные select t.hsecs ,dbms_utility.get_cpu_time into v_t2 ,v_cpu2 from v$timer t; --считаем общее время выполнения в сотых долях секунды v_t_res := v_t2 - v_t1; --считаем общее время ЦПУ в сотых долях секунды v_cpu_res := v_cpu2 - v_cpu1; --выводим результат на экран dbms_output.put_line('Общее время выполнения в секундах: '||to_char(v_t_res/100,'0.00')); dbms_output.put_line('Общее время ЦПУ в секундах: '||to_char(v_cpu_res/100,'0.00')); --откатим изменения rollback; end;

Результат:

Статистика

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

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

Для быстрого доступа к данным, соединим все ранее представленные скрипты по выводу данных по статистки, времени ЦПУ, общему времени и "упакуем" полученный запрос в представление. Вот как это будет выглядеть:

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

Запустим программу (если будут ошибки, пишите в комментариях), пока результат мы не увидим, но статистику уже запишем:

--тест скрипта №1 begin --фиксируем начало работы fix_inf_prc(1,'before'); --скрипт №1 for rec in 1..100 loop update operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение работы fix_inf_prc(1,'after'); rollback; end; --тест скрипта №2 begin --фиксируем начало работы fix_inf_prc(2,'before'); --скрипт №2 for rec in 1..1000 --увеличим количесто циклов loop update operation set op_date = trunc(sysdate) where rownum = 1 and op_date != trunc(sysdate); end loop; --фиксируем завершение работы fix_inf_prc(2,'after'); rollback; end;

Вывод результатов

Вывод статистики скриптом

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

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

select * from stats_report_vw where abs(diff) > 1 --фильтр по полю diff and name like '%ime%' --фильтр по полю name order by abs(diff);

Результат:

Вывод статистики через dbms_output

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

create or replace procedure stats_report_prc (p_diff_count in number default 0, p_like_name in varchar2 default null, p_sort_field_num in number default 4) is begin --выводим статистику dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 16) || lpad('Run2', 16) || lpad('Diff', 16)); for rec in (select s.name, to_char(s.script1, '999G999G999G999') script1, to_char(s.script2, '999G999G999G999') script2, to_char(s.diff, '999G999G999G999') diff from stats_report_vw s where s.name not like '%TIME. %' --исключаем ETIME, CTIME and abs(s.diff) > p_diff_count --фильтр по величине отличий and (p_like_name is null or lower(s.name) like lower('%'|| p_like_name ||'%')) --фильтр по имени order by p_sort_field_num) loop dbms_output.put_line(rpad(rec.name, 30) || lpad(rec.script1, 16) || lpad(rec.script2, 16) || lpad(rec.diff, 16)); end loop; --добавим отступ dbms_output.put_line(chr(9)); --выводим данные о времени dbms_output.put_line(rpad('Name', 30) || lpad('Run1 (sec)', 16) || lpad('Run2 (sec)', 16) || lpad('Diff (sec)', 16)); for rec in (select s.name, rtrim(to_char(s.script1/100, 'FM999999999999990.99999'), '.') script1, rtrim(to_char(s.script2/100, 'FM999999999999990.99999'), '.') script2, rtrim(to_char(s.diff/100, 'FM999999999999990.99999'), '.') diff from stats_report_vw s where s.name like '%TIME. %') --выводим только ETIME, CTIME loop dbms_output.put_line(rpad(rec.name, 30) || lpad(rec.script1, 16) || lpad(rec.script2, 16) || lpad(rec.diff, 16)); end loop; end;

Запустим отчет и посмотрим что получилось:

set serveroutput on; begin stats_report_prc(1); -- выдодить где разница больше 1 end;

Результат:

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

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

Если открыть файл журнала транзакций *.LDF или файл резервной копии журнала *.TRN в любом двоичном редакторе, то информация, которую вы увидите, будет мало чем информативна. Ниже представлен фрагмент LDF-файла:

Функция fn_dbblog

Давайте посмотрим, как с ней работать:

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

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

Например, выберем только транзакции на вставку строк в таблицу:

Чтобы увидеть транзакции на удаление строк, выполните следующий скрипт:

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

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

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

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

Функция fn_dump_dblog

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

Т.к. функция fn_dump_dblog возвращает так же, как и fn_dblog 129 столбцов, то желательно сократить этот набор полей

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

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

Для восстановления БД из копии журнала транзакций до определённого момента времени или до конкретной транзакции, вам необходимо:

DBCC PAGE

Ещё одна полезная команда DBCC PAGE, но также, как и две предыдущих функции –недокументированная. Она позволяет просматривать содержимое файлов MDF и LDF. Её синтаксис:

Для просмотра содержимого первой страницы журнала транзакций БД AdventureWorks2012, необходимо выполнить:

По умолчанию результат команды DBCC PAGE не выводится в SQL Server Management Studio и для её отображения первым шагом необходимо включить флаг трассировки 3604:

И теперь повторно выполните команду:

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

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

Use ApexSQL Log

ApexSQL Log – это инструмент, который позволяет работать с журналом транзакций SQL Server в наглядном виде. Он позволяет просматривать текущий журнал транзакций в режиме реального времени, обращаться к резервным копиям журнала транзакций, как обычным, так и созданных в режиме компрессии. При этом приложение самостоятельно считывает данные из резервных копий БД, чтобы получить всю необходимую информацию для успешного восстановления. С помощью ApexSQL Log вы можете просматривать цепочки транзакций, которые произошли в вашей БД, даже те, которые были совершены до установки утилиты. В отличии от недокументированных и неподдерживаемых функций, рассмотренных выше, вы получите наглядную информацию о том, какие операции происходили над объектами, сможете увидеть старое и новое значение.

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

На шаге Select SQL logs to analyze, выберите записи, которые нужно прочитать. Убедитесь, что они образуют полную цепочку

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

Нажмите Open

Полный результат можно будет увидеть в табличном виде

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

Чтобы избежать нечитаемых шестнадцатеричных значений, недокументированных функций, непонятного содержимого колонок, запросов со сложной конструкцией, сложных сценариев получения данных, неполных данных операций UPDATE, а также проблем с получением BLOB значений из журнала транзакций SQL Server, используйте программу ApexSQL Log. Она за вас выполнит все сложные операции и предоставит результат в читабельном виде. Кроме того, она позволит вам с помощью одного нажатия отменить или повторно выполнить нужную транзакцию.

Читайте также: