ITPub博客

首页 > 应用开发 > IT综合 > 一个优化过程

一个优化过程

原创 IT综合 作者:rainbowbridg 时间:2007-09-26 16:16:23 0 删除 编辑

一次调整优化过程总结
平台:windows 2000 server sp3
数据库:Oracle 9.0.1.1.1

1. 在高峰时段,系统高负载的情况下用statspack一个小时做一次系统的快照。对statspack产生的报告注意其中的Top 5 Wait Events。具体可以参考eygle写的statspace使用指南。本例的等待事件主要是db file scattered read、buffer busy waits和db file sequential read。
2. 对于db file scattered read主要是由于全表扫描造成的,db file sequential read说明当前系统负载很高或者cbo使用了不合适的索引。通过定时(2天一次在晚上12点执行dbms_stats.gather_schema_stats(')过程)统计schema信息并用过程
dbms_support.start_trace_in_session
dbms_support.stop_trace_in_session
跟踪客户端的会话,用tkprof分析跟踪文件:
tkprof sys=no sort=prsdsk,exedsk,fchdsk
通过分析输出文件并获得其中效率低下的sql语句,然后对这些sql语句进行调整。
3. 一般可以通过在表上加上必要的索引来消除全表扫描,并注意调整初始化参数db_file_multiblock_read_count和optimizer_index_cost_adj。这两个参数影响CBO选择的执行计划。如果希望CBO更倾向于使用索引,可以加提示/*+first_rows*/。
4. 经上述调整后,客户仍然抱怨在应用程序中查询某个报表的时候非常慢,需要2个小时以上。对sql语句分析后,发现该报表的sql语句是查询一个嵌套视图,结构大致如下:
create or replace view vw_v1 as select c1,c2,c3 from t1;
create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 group by c2,c3;
create or replace view vw_v3 as select *from vw_v2 union select *from vw_other;
客户端查询的时候是select *from vw_v3 where c1 = :b1;
其中t1表是个分区表,大概有4千万行,每个月增长1千万行。对sql语句分析后认为性能问题主要是由于视图vw_v2中对t1的group by c2,c3造成的,因而调整如下:
create or replace view vw_v2 as select c1,sum(c2),sum(c3) from vw_v1 where c1 = pg_arg.sf_get_c1() group by c2,c3;
在group by前加入where c1 = pg_arg.sf_get_c1(),pg_arg是pl/sql包,在执行该查询前需要执行pg_arg.sp_set_c1(c1)过程设置c1参数。通过这样的调整和改变可以把查询的耗时减少到10分钟以内。
5. 同时用户也在抱怨倒盘(主要是插入、删除操作)时非常缓慢,200人的单位需要2个小时才能导入到数据库中。该操作主要是大表(分区表,每个月增加1000万条记录,总行数现时在4千万行左右)的插入删除操作,在sqlplus里面执行插入操作,高峰时候更是达到了30秒才能插入一条记录。通过alter table modify partition freelists ;增大空闲列表的数目后对性能的提高帮助不大;同时在插入的时候加/*+append*/提示,对性能的提高也是很有限(原来两个小时的操作在一个小时五十分钟完成,基本上没多大的作用)。对大表结构分析后,发现在表上建立了全局分区索引,把该全局分区索引删除并建立普通索引后INSERT语句可以即时响应。另外在插入数据的时候用批量插入的方式进行插入:
/*如下所示:
先定义数组变量
然后把数据一次性fetch出来
最后用forall一次性插入到表中*/
TYPE t_jzny IS TABLE OF char(6);
b_jzny t_jzny;
v_rowcount NUMBER;
CURSOR cur_bulk
IS
SELECT ny
FROM
WHERE ROWNUM <= 6;
…………………
-- 初始化数组
OPEN cur_bulk;

FETCH cur_bulk
BULK COLLECT INTO b_jzny;

CLOSE cur_bulk;
………………………
FORALL v_i IN 1 .. v_rowcount
Insert into
……………..
values
(
………….
B_jzny(v_i);
…………..
)
经以上调整后,200人左右的倒盘操作可以从原来的2个小时提高到10分钟以内。收到立竿见影的效果。

6. 小结:性能调整一般是通过statspack,产生分析跟踪文件来定位效率低下的sql语句。然后通过各种手段,比如可以加入合适的索引,添加合适的hint来调整查询语句;用alter index monitoring usage;跟踪没有使用的索引并把这些索引删除、调整效率低下的索引(尤其使对大表上的索引)、加/*+append*/提示、批量插入等手段调整DML语句。

http://www.itpub.net/adv/banner.php?areaid=2&posid=2

[@more@]Re: 看了好几遍,还是不知道为什么!请指点一二,谢谢!

quote:
最初由 oldv 发布
“pg_arg是pl/sql包,在执行该查询前需要执行pg_arg.sp_set_c1(c1)过程设置c1参数。通过这样的调整和改变可以把查询的耗时减少到10分钟以内。”
pg_arg是oracle自带的么?哪里有相关介绍?为什么它能提高group by操作的效率?

“对大表结构分析后,发现在表上建立了全局分区索引,把该全局分区索引删除并建立普通索引后INSERT语句可以即时响应。”
全局分区索引为什么会对insert的性能影响如此之大?如果真是这样,全局分区索引岂非没有多大意义了?是否存在可以对大表建全局分区索引同时又不对insert的性能影响太大的方法?

以上两个问题实在想不明白,请高手们指点指点!先谢了!!



可能说得不太清楚
1.pg_arg自己写的package
里面有过程和函数
sp_set_c1(c1 in char);
sf_get_c1 return char;
注意视图建立的语句
create or repalce view
...
where c1 = pg_arg.sf_get_c1;
在group by前用where c1 = pg_arg.sf_get_c1;
限定了group的记录
2.全局分区索引的分区都建立在同一个表空间上,在一段时间(大概3个月后)插入操作变得非常慢,30秒钟插入一条记录。删除后插入操作基本上是可以即时响应了;
另外在重建该分区索引并把分区建立在不同的表空间上后,插入操作也可以即时响应,至于后续情况会如何,由于采用了全局非分区索引,已经无从考究了。估计分区索引需要经常性的在线rebuild。

下面是一些使用分区索引的一些tips:
分区索引提高管理性、可用性、性能以及可量度性。索引可以独立分区(全局索引)或者自动连接到表分区方法上(本地索引)。通常来说,应该为OLTP应用使用全局索引,为数据仓库或DSS应用使用本地索引。在可能的情况下,尽量使用易于管理的本地索引。在决定使用何种分区索引前,按顺序考虑以下指导方针:
ü 如果表分区列使索引键的子集,使用本地索引
ü 如果索引唯一,使用全局索引
ü 如果需要易于管理,使用本地索引
ü 如果应用是OLTP,用户需要快速的响应时间,使用全局索引;如果应用是DSS,需要更大的吞吐量,使用本地索引。

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

上一篇: sqlldr的例子
请登录后发表评论 登录
全部评论
  • 博文量
    237
  • 访问量
    1586126