050.【实验：监控频繁commit产生的等待】

【实验】
1.实验前生成AWR
select count(*) from T_GROUP1 t;

--4326720

--注：执行5分31秒后停止，更新记录262000条
declare
cursor cur_group is
select t.biz_date,rowid from t_group1 t;
lv_biz_date varchar2(20);
lv_count_num pls_integer :=0;
lv_start_time_num pls_integer;
begin
lv_start_time_num := dbms_utility.get_time;
for cur_rec in cur_group loop
lv_count_num :=lv_count_num+1;
lv_biz_date :=cur_rec.biz_date;
update t_group1
set biz_date1=lv_biz_date
where rowid=cur_rec.rowid;
commit;
if mod(lv_count_num,1000)=0 then
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
end if;
end loop;
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
end;

2.实验后生成AWR报表
Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
log file parallel write 161,493 192 1 53.3 System I/O
CPU time 127 35.2
ARCH wait on ATTACH 40 114 2,862 31.8 Network
db file sequential read 11,901 111 9 30.9 User I/O
reliable message 257 110 427 30.5 Other

3.结论及优化方案
3.1分析
日志文件写入用的等待时间最长。
测试环境是虚拟机，保存数据的表空间是USERS，对应的数据文件在/oradata/db1/目录下。而日志文件保存在/u01/ora10g/oradata/ora10g1/目录。归档日志在/u01/ora10g/archivelog目录。

3.2 优化方案：将归档日志放到/archive/db1/目录下。
select * from v$parameter t where t.NAME like '%dest%';
alter system set log_archive_dest_1='location="/archive/db1/", valid_for=(ONLINE_LOGFILE,ALL_ROLES)' scope=both;
执行第1步中的代码：执行5分33秒，更新33万行

3.3AWR报表中的等待
Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
log file parallel write 62,129 242 4 65.7 System I/O
CPU time 135 36.8
db file sequential read 10,429 127 12 34.4 User I/O
log file switch completion 102 85 837 23.2 Configuration
LGWR-LNS wait on channel 2,795 30 11 8.2 Other
【小结】
1.log file parallel write的总等待数大幅减少，但总的用时有上升。更新的记录数由26万，上升到33万，更新效率提高。
2.产生了log file switch completion等待事件。试分析，是因为log_archive_dest_2设置了DG备份地址，而DG备机没有开，导致log file switch。
3.关闭log_archive_dest_state_2，解决log file switch completion等待事件，测试如下



3.4重新执行第1步中的代码（解决log file switch completion等待事件）
alter system set log_archive_dest_state_2='defer' scope=both;
共执行5分28秒，更新42.6万条记录

AWR报表
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
log file parallel write 39,433 310 8 88.4 System I/O
db file sequential read 13,262 175 13 50.0 User I/O
CPU time 157 44.8
reliable message 99 50 500 14.1 Other
log file sequential read 290 32 110 9.1 System I/O
【小结】
1.log file switch completion从TOP5等待事件消失，说明上一节的分析是正确的；
2.log file parallel write的等待数下降，说明log file switch completion等待事件同时伴有log file parallel write等待事件。
3.更新的记录数由33万上升到了42.6万，更新效率有明显提高。

4.将代码改为1000条commit一次
declare
cursor cur_group is
select t.biz_date,rowid from t_group1 t;
lv_biz_date varchar2(20);
lv_count_num pls_integer :=0;
lv_start_time_num pls_integer;
begin
lv_start_time_num := dbms_utility.get_time;
for cur_rec in cur_group loop
lv_count_num :=lv_count_num+1;
lv_biz_date :=cur_rec.biz_date;
update t_group1
set biz_date1=lv_biz_date
where rowid=cur_rec.rowid;
if mod(lv_count_num,1000)=0 then
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
commit;
end if;
end loop;
dbms_application_info.set_module('Records Processed:'||lv_count_num,'Elapsed:'||(dbms_utility.get_time - lv_start_time_num)/100 ||' sec');
end;
--执行结果：执行了5分28秒，更新了206万条记录
--AWR报表：
Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 226 64.5
log file parallel write 2,369 94 40 26.8 System I/O
db file sequential read 52,480 75 1 21.3 User I/O
log file sequential read 919 74 80 21.1 System I/O
control file parallel write 751 26 34 7.3 System I/O
【小结】每1条改为第1000条提交后
1.log file parallel write比上节测试等待数减少了95%以上
2.更新的记录数由42.6万上升到206万，效率提高了5倍
3.改变commit的频率，也是一个调优的好方法

【调优方法】
1.一组SQL或PL/SQL的调优方法：可以使用dbms_workload_repository.create_snapshot，设置一个较短的监控区间，然后利用AWR报表收集性能信息（如等待事件）。
2.当然，单条SQL的等待事件，也可以利用10046事件来收集。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060034/