oracle 过程优化,一次调整优化过程总结

╰+攻爆jí腚メ 2023-01-18 15:26 89阅读 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

SELECT ny

FROM

WHERE ROWNUM <= 6;

…………………