oracle程序跑的很慢,oracle程序性能优化实录-优化实例一（从1小时到5分钟）

1.1业务场景说明

根据两个业务明细数据，根据当前月份生成合并的明细和汇总。当前月份根据当前时间取得，每日执行一次，每个月反复进行，直到当月结束，保留当月的最后一次数据作为历史存留。

涉及到的表：

(1)sf_chargemonth：业务明细数据，按月做分区，分区关键字chargemonth，每月数据100万。

(2)sf_ystzmonth：第二张业务明细数据，无分区。每月数据2万。

(3)RT_2109_suo_mx：合并的明细数据，无分区，合并后数据规模为每月100万。

(4)RT_2109_suo：合并的汇总数据，每月数据50条。

1.2原执行过程

原来的执行过程共分三个步骤，执行时间近1小时：

(1)删除掉RT_2109_suo和RT_2109_suo_mx当月的数据。

Delete from RT_2109_suo

where chargemonth='201703';

Delete from RT_2109_suo_mx

where chargemonth=‘201703’;

(2)合并明细数据并添加到RT_2109_suo_mx。

Insert into

RT_2109_suo_mx(chargemonth,dept_id,housecode,field1)

Select ‘201703’,

dept_id,housecode,sum(field1)field1

(select

dept_id,housecode,field1 from sf_chargemonth where

substr(chargemonth,1,6)=’201703’

Union all

Select

dept_id,housecode,field1 from sf_ystzmonth where

substr(chargemonth,1,6)<=’201703’ and

cwmonth=’201703’

) a Group by

dept_id,housecode

(3)汇总RT_2109_suo_mx到RT_2109_suo。

Insert into

RT_2109_suo(chargemonth,dept_id,field1)

Select

‘201703’,dept_id,sum(field1) field1

From

RT_2109_suo_mx

Where

chargemonth=’201703’

Group by

dept_id;

1.3优化过程

1、RT_2109_suo_mx表结构的优化

该表每月固定生成，并且都是以月作为一个关键字，首先考虑的是给该表做分区。

CREATE TABLE

RT_2109_suo_mx2

PARTITION

BY list(CHARGEMONTH) (

PARTITIONp201703 values ('201703'),

PARTITIONp201704 values ('201704')

) as select *

from RT_2109_suo_mx;

droptable RT_2109_suo_mx;

altertable RT_2109_suo_mx2 rename to

RT_2109_suo_mx;

altertable RT_2109_suo_mx add primary key (CHARGEMONTH,

DEPT_ID,HOUSECODE);

2、RT_2109_suo_mx删除的优化

原来的删除语句为：Delete

from RT_2109_suo_mx where chargemonth=’201703’;

原来的删除只是利用了索引。表做分区后的分析效果如下：

加入分区以后，删除时用到了分区，加快了操作的速度。

但是，一个分区的数据量仍然是百万级的，直接进行delete仍然较慢，时间达到的半小时以上。

测试了几种办法：

(1)删除该分区，然后再添加该分区：alter

table RT_2109_suo_mx drop partitionp201703;

Alter table RT_2109_suo_mx

add partitionp201703 values(‘201703’)。删除的速度很快，但是，删除分区以后，表的索引失效。

(2)Truncate该分区：alter

table RT_2109_suo_mx truncate

partitionp20103;删除的速度很快，但是，表的索引失效。

(3)Truncate分区并更新索引：alter

table RT_2109_suo_mx truncate partitionp20103 update

indexes。删除的速度很快，索引也正常

3、合并明细数据语句的优化

这块主要是针对两个明细表的查询。

select

dept_id,housecode,field1 from sf_chargemonth where

substr(chargemonth,1,6)=’201703’。这个表是分区的，同时使用了substr，分区也没有利用上，索引也没有用上，把这个语句单独拿出来，查询的时间超过150秒。

修改为：select

dept_id,housecode,field1 from sf_chargemonth where chargemonth like

’201703%’。这就利用上了分区条件，把这个语句单独拿出来，查询的时间在1秒以内。

select

dept_id,housecode,field1 from sf_ystzmonth where

substr(chargemonth,1,6)<=’201703’ and

cwmonth=’201703’。这个语句，substr(chargemonth,1,6)肯定是没有利用上索引，cwmonth这个条件字段没有索引。前面的chargemonth这个条件，不太好修改。Cwmonth经过业务考虑，增加索引。

4、汇总语句的优化

这块还主要是针对查询语句的优化。

(1)原来的效率

Select

‘201703’,dept_id,sum(field1) field1

From

RT_2109_suo_mx

Where chargemonth=’201703’

and rttype=’25日报表’。这个条件在前面的描述中未加。SQL的分析效果如下，没有利用上分区，全表扫描，只利用了主键索引：

(2)修改为：Select

‘201703’,dept_id,sum(field1) field1

From

RT_2109_suo_mx

Where

chargemonth=’201703’。单分区扫描。

(3)指定分区：Select

‘201703’,dept_id,sum(field1) field1

From RT_2109_suo_mx

partition(p201703)

Where chargemonth=’201703’

and rttype=’25日报表’。

还是进行全表扫描，只利用了主键索引。

(4)考虑业务的上要求，目前rttype只有一个值，可以不考虑这个查询条件。索引优化为：

Select

‘201703’,dept_id,sum(field1) field1 From RT_2109_suo_mx

Where

chargemonth=’201703’；

或

Select

‘201703’,dept_id,sum(field1) field1 From

RT_2109_suo_mxpartition(p201703)

1.4优化后执行过程

1、调整两个表的设置：

(1)RT_2109_suo_mx修改为按chargemonth做分区

(2)sf_ystzmonth增加索引cwmonth

2、执行过程还是三个步骤，执行时间不到5分钟：

(1)删除掉RT_2109_suo和RT_2109_suo_mx当月的数据。

Delete from RT_2109_suo

where chargemonth=’201703’;

alter table RT_2109_suo_mx

truncate partitionp20103 update indexes;

(2)合并明细数据并添加到RT_2109_suo_mx。

Insert into

RT_2109_suo_mx(chargemonth,dept_id,housecode,field1)

Select ‘201703’,

dept_id,housecode,sum(field1) field1

(select

dept_id,housecode,field1 from sf_chargemonth

wherechargemonthlike ’201703%’

Union all

Select

dept_id,housecode,field1 from sf_ystzmonth where

substr(chargemonth,1,6)<=’201703’ and

cwmonth=’201703’

) a Group by

dept_id,housecode

(3)汇总RT_2109_suo_mx到RT_2109_suo。

Insert into

RT_2109_suo(chargemonth,dept_id,field1)

Select

‘201703’,dept_id,sum(field1) field1

From

RT_2109_suo_mx

Where

chargemonth=’201703’

Group by

dept_id;