Oracle transaction stats
[EN]
Information about the number of completed transactions allows you to quickly assess the load on the system. Plan allocation of CPU, IO resources
Count transaction view
Information on the number of transactions by year, month, day
For the year - Statistics by year
SELECT TO_CHAR(FIRST_TIME,'YYYY') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY')
ORDER BY 1
Monthly - Statistics for months of the current year
SELECT TO_CHAR(FIRST_TIME,'MM') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM')
ORDER BY 1
For days - Statistics by day of the current year
SELECT TO_CHAR(FIRST_TIME,'MM-dd') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM-dd')
ORDER BY 1
Dead transaction
Transactions that could not be rolled back. Data from system table x$ktuxe
select ktuxeusn,
ktuxeslt,
ktuxesqn,
ktuxesta,
ktuxesiz
from x$ktuxe
where ktuxesta <> 'INACTIVE'
and ktuxecfl like 'ÞAD%'
order by ktuxesiz asc
Rollback progress
Information on processed blocks by transaction is displayed. However, if the value in the "used_ublk" column decreases, this signals a rollback of the changed data.
select ses.username,
ses.sid,
substr(ses.program, 1, 19) command,
tra.used_ublk
from v$session ses,
v$transaction tr
awhere ses.saddr = tra.ses_addr
Transaction recovery by SMON
List of sessions whose changes are rolled back by SMON (the session was terminated in the OS)
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0, 'unknown',
sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated"
from v$fast_start_transactions
[RU]
Информация о количестве выполненных транзакций позволяет быстро оценить нагрузку на систему. Спланировать выделение ресурсов CPU, IO
Count transaction view
Информация по количеству транзакций в разрезе год, месяц, день
For the year - Статистика по годам
SELECT TO_CHAR(FIRST_TIME,'YYYY') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY')
ORDER BY 1
Monthly - Статистика по месяцам текущего года
SELECT TO_CHAR(FIRST_TIME,'MM') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM')
ORDER BY 1
For days - Статистика по дням текущего года
SELECT TO_CHAR(FIRST_TIME,'MM-dd') INTERVAL,
round(SUM(NEXT_CHANGE#-FIRST_CHANGE#)/1000) "COUNT"
FROM V$LOG_HISTORY
where to_char(first_time,'yyyy')=$$CURRENT_YEAR
GROUP BY TO_CHAR(FIRST_TIME,'MM-dd')
ORDER BY 1
Dead transaction
Транзакции по которым не удалось провести откат. Данные из системной таблицы x$ktuxe
select ktuxeusn,
ktuxeslt,
ktuxesqn,
ktuxesta,
ktuxesiz
from x$ktuxe
where ktuxesta <> 'INACTIVE'
and ktuxecfl like 'ÞAD%'
order by ktuxesiz asc
Rollback progress
Отображается информация по обработанным блокам транзакцией. При этом, если значение в столбце "used_ublk" уменьшается, это сигнализирует об откате измененных данных.
select ses.username,
ses.sid,
substr(ses.program, 1, 19) command,
tra.used_ublk
from v$session ses,
v$transaction tr
awhere ses.saddr = tra.ses_addr
Transaction recovery by SMON
Список сессий чьи изменения откатывает SMON (сессия была завершена в ОС)
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0, 'unknown',
sysdate+(((undoblockstotal-undoblocksdone)/(undoblocksdone/cputime))/86400)) "Estimated"
from v$fast_start_transactions