客户的每小时redolog日志量大，配合AWR和LOGMINER检查发现是由一条update语句引起。这条语句大概每小时执行80次左右，不仅产生了大量的重做日志，而且逻辑读也很高。
语句类似update tb_test_log set object_id=1 where owner='SYS'，是对表tb_test_log按一定的频率，把满足条件owner='SYS'的记录中的object_id修改为1，而且满足条件的记录占了整个表的一半左右。但实际上在每次更新时，满足条件owner='SYS'的记录中绝大部分object_id已经是1.
以下尝试优化：
DB Version:12.1.0.2.0
OS:CentOS 6.6
#建测试表
create table tb_test_log tablespace users as select * from dba_objects;
insert into tb_test_log select * from tb_test_log;
commit;
insert into tb_test_log select * from tb_test_log;
commit;
insert into tb_test_log select * from tb_test_log;
commit;
#查看测试表的大小，大概100MB
select bytes from dba_segments where segment_name=upper('tb_test_log');
/*
BYTES
109051904
*/
#满足条件owner='SYS'的记录大概占了46%
select count(decode(owner,'SYS',1,null))/count(1) from tb_test_log;
/*
0.461732733062479
*/
#优化前SQL
update tb_test_log set object_id=1 where owner='SYS';
#新建会话统计数据记录表，用于后面的重做日志和逻辑读的计算
declare
v_count number;
begin
select count(1) into v_count from dba_tables where table_name='T_STAT_TEMP';
if v_count=1 then
execute immediate 'truncate table t_stat_temp';
else
execute immediate 'create table t_stat_temp(snap_date date,name varchar2(100),value int)';
end if;
end;
会话1：
#查看会话1的会话ID
select sid from v$mystat where rownum