Oracle: generate redo data
[EN]
Viewing information on objects when working with which the maximum amount of data is generated in the REDO logs of the Oracle database
Top objects the generate data redo
Statistics on objects when working with which create the maximum amount of data in REDO logs
select *
from (SELECT to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||' - ' ||
to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
dhso.object_name,
dhso.object_type,
dhso.tablespace_name,
sum(db_block_changes_delta) as db_block_changes,
to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER())
* 100, 2), '99.00')||' %' as REDO_PERCENT
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#(+)
AND dhss.dataobj# = dhso.dataobj#(+)
AND begin_interval_time BETWEEN
to_date('<date_start>','dd.mm.yyyy hh24:mi:ss') AND
to_date('<date_end>','dd.mm.yyyy hh24:mi:ss')
GROUP BY dhso.object_name,dhso.object_type,dhso.tablespace_name
ORDER BY db_block_changes desc)
Size REDO archived
Statistics on the size of archivelog files generated by day
select s."DateFirst",
round(sum(s."Bytes")/1024/1024/1024, 2)||' GB' "GB",
(select dest_name||'='||destination
from V$ARCHIVE_DEST where dest_id=s.dest_id)
from (select TRUNC(first_time) "DateFirst",
blocks*block_size "Bytes",
dest_id
from V$ARCHIVED_LOG) s
group by s."DateFirst", dest_id
order by 1 desc, 3
[RU]
Просмотр информации по объектам при работе с которыми формируется максимальное количество данных в журналах REDO базы данных Oracle
Top objects the generate data redo
Статистика по объектам при работе с которыми создается максимальное количество данных в журналах REDO
select *
from (SELECT to_char(min(begin_interval_time), 'DD-Mon-YY HH24:MI') ||' - ' ||
to_char(max(begin_interval_time), 'DD-Mon-YY HH24:MI') as WHEN,
dhso.object_name,
dhso.object_type,
dhso.tablespace_name,
sum(db_block_changes_delta) as db_block_changes,
to_char(round((RATIO_TO_REPORT(sum(db_block_changes_delta)) OVER())
* 100, 2), '99.00')||' %' as REDO_PERCENT
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#(+)
AND dhss.dataobj# = dhso.dataobj#(+)
AND begin_interval_time BETWEEN
to_date('<date_start>','dd.mm.yyyy hh24:mi:ss') AND
to_date('<date_end>','dd.mm.yyyy hh24:mi:ss')
GROUP BY dhso.object_name,dhso.object_type,dhso.tablespace_name
ORDER BY db_block_changes desc)
Size REDO archived
Статистика по размеру файлов archivelog сформированных по дням
select s."DateFirst",
round(sum(s."Bytes")/1024/1024/1024, 2)||' GB' "GB",
(select dest_name||'='||destination
from V$ARCHIVE_DEST where dest_id=s.dest_id)
from (select TRUNC(first_time) "DateFirst",
blocks*block_size "Bytes",
dest_id
from V$ARCHIVED_LOG) s
group by s."DateFirst", dest_id
order by 1 desc, 3