【实验】
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/