一次索引搞定的调优例子

最新推荐文章于 2023-07-27 17:14:45 发布

cuiganglan0701

最新推荐文章于 2023-07-27 17:14:45 发布

阅读量65

点赞数

文章标签：操作系统数据库

服务器IBM X5，操作系统是2003 32位 ORACLE 9.2.0.1

4个CPU，13.9G内存

总体反映慢,后来又说汇总慢,原来几十秒的现在需要1个多小时.

远程过去查看了下，当前等待的语句都是：

DELETE t1 a where (a.zd1,a.zd2,a.zd3) in (select distinct zd1,zd2,zd3 from t2 where zd0=:b3 and zd1=:b2 and (nvl(data1,:b1)<>:b1 or nvl(data2,:b1)<>:b1 or nvl(data3,:b1)<>:b1))

这是一个对临时表的删除操作。

运行select a.table_name, last_analyzed
from user_tables a; 看下T2的分析时间是前天的

使用dbms_stats重新分析下该表，仍然不起作用。

运行select sid,type,id1,id2,lmode,request,block from v$lock where sid in (select sid from v$session) order by sid;

发现连接的session不少，但是并没有被阻塞的进程，没有锁住的表。

继续查看表空间的情况，临时表空间已用99.96%，用户使用的表空间争用

最多的数据文件也已经用99.96%，故先扩充该数据文件，然后给临时表空间增加了一个数据文件。

进一步查看ALERT文件，发现该数据库安装的有问题，首先缺省块大小才4096，另外加起来的内存也不过11M，shared_pool 才33M ,pga 11M ,data_buffer 33m多。上面运行的应用本身就比较耗费资源，这种情况出现问题也不例外。

使用dbms_stats重新分析下该表，仍然不起作用。

execute dbms_stats.gather_schema_stats(ownname=> 'ZHTJ' , tabname =>'T2',cascade=> TRUE);

于是调整SGA 总共为1.1G，PGA 给了300M，并为该用户增加空闲1个小时后自动杀掉进程的控制，发现BS有4 天前的进程还在连着，重启服务器。

再次运行慢的汇总模块，这次运行了1分半完成了。查看应用的提示为：

删除指标采集t2 对应的数据使用了1分多，此处提示信息有些误导，以为是删除表T2的语句慢，实际抓出的等待语句还是上面那个删除临时表的语句。而且过了一阵用户多了之后，速度又慢了下来：8-9分钟。

于是还是查看该语句的执行计划：

绑定变量：

Set time on

Set linesize 2000

Set autotrace on explain

Define b1=100 –设定绑定变量并将语句改写了下

delete from t1 a

where (a.zd1,a.zd2,a.zd3) in (select distinct zd1,zd2,zd3 from t2
where zd0=':b3' and zd1=':b2'
and not (nvl(data1,&b1)=&b1 and nvl(data2,&b1)=&b1 and nvl(data3,&b1)=&b1))

发现该语句对于临时表的访问使用了索引，很正常，但是对于表T2，采用的是全表扫描。查看下该表的数据为171万，代价为5350多，都耗费在这个表的访问上了。进一步去查看该表的主键和索引情况，竟然发现表T2上面没有任何索引，也没有主键，很奇怪的一件事情：该表为日采集表，数据量应为最大的表，竟然没有主键和索引。联系开发人员未果，索性先建上一个索引吧：

CREATE INDEX INX_T2 ON T_LR_RXB(ZD0,ZD1,ZD2,ZD3) TABLESPACE INDX;

再次看语句的执行计划，代价已经变到了2！运行程序，车间汇总5秒就出来了

再看分厂19秒结束。

至此我终于碰到了一个索引解决问题的优化，哈哈！ 20120625

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

转载于:http://blog.itpub.net/7177735/viewspace-733790/