日志redo生成量和查找redo增长的sql

 

***********************************************************************每小时产生的归档量；
select trunc(first_time, 'HH24') "TIME",
sum(BLOCK_SIZE * BLOCKS) / 1024 / 1024 / 1024 "SIZE(GB)"
from v$archived_log
where dest_id = 1
group by trunc(first_time, 'HH24')
order by trunc(first_time, 'HH24') desc;

***********************************************************************每日归档量；
select trunc(completion_time) time, sum(mb) / 1024 day_gb
from (select thread#,
name,
completion_time,
blocks * block_size / 1024 / 1024 mb
from v$archived_log
where dest_id=1)---源库
group by trunc(completion_time)
order by 1;

***********************************************************************每日不同节点归档量
select thread#, trunc(completion_time), sum(mb) / 1024 day_gb
from (select thread#,
name,
completion_time,
blocks * block_size / 1024 / 1024 mb
from v$archived_log
where dest_id=1)---源库
group by thread#, trunc(completion_time)
order by 2

*****************************************************************************

 

以下引用https://blog.csdn.net/u010692693/article/details/75332492

1.从历史视图块改变查找更改最多的段
select * from (
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time> sysdate - 120/1440
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dhsso.object_name
order by 3 desc);

 

2. 从awr视图中找出步骤1中排序靠前的对象涉及的SQL。

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text, 4000, 1),
dhss.instance_number,
dhss.sql_id,
executions_delta,
rows_processed_delta
FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst
WHERE UPPER(dhst.sql_text) LIKE '%***%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_Number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id
order by to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI');

 

3. 从ASH相关视图找到执行这些SQL的session、module和machine。

 

1. select * from dba_hist_active_sess_history WHERE sql_id = 'g893rmm0rmjra';
2. select * from v$active_session_history where sql_Id = 'g893rmm0rmjra';

g893rmm0rmjra是SQL_ID,替换第二步查询的结果SQL_ID列

4. dba_soure 看看是否有存储过程包含这个SQL。