ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 工作记录-优化大表更新一例

工作记录-优化大表更新一例

原创 Linux操作系统 作者:oracle_kai 时间:2009-04-03 19:18:04 0 删除 编辑

描述:一个大表现有近5千万笔记录,每天会有更新,更新的记录在700万笔到1000万笔左右,而且现在因为业务需要(业务需求的合理性不在此讨论之列),更新的记录还会成倍增加,而现在每天运行的时间就已经耗时在30-40分钟左右,调优已势在必行。

 

现状调查

各个表的记录数及查询时全表扫描时间

10:42:37 AM SQL> Select Count(*) From product_dimension_exchange1;

  COUNT(*)

----------

   6884346

10:42:55 AM SQL> Select Count(*) From product_dimension_exchange2;

  COUNT(*)

----------

     30965

10:53:43 AM SQL> Select Count(*) From product_dimension_exchange3;

  COUNT(*)

----------

  47721206

10:54:36 AM SQL> Select Count(*) From product_dimension_exchange4;

  COUNT(*)

----------

  54605552

11:02:18 AM SQL>

 

启用并行,指定全表扫描product_dimension一次的时间时间消耗:75

12:05:39 PM SQL> Select /*+ full(a) parallel(a 32) */ Count(*) From product_dimension a;

  COUNT(*)

----------

  54636517

 

12:06:54 PM SQL>

 

product_dimension  etl总的执行时间和主要sql执行时间

 

查询4号的proc_log,得到product_dimension  etl的总时间及每个主要sql的执行时间,如下

Select Logtime, Sqltext, Sqletime, Sqlstime, (Sqletime - Sqlstime) * 24 * 60 * 60 cost_time   From Proc_Log

Where Procname = 'dimension_increment' And To_Char(Logtime, 'yyyymmdd') >= '20090204' And Upper(Runresult) = 'SUCC'

 

从返回的结果中得到本次过程总的时间在35分钟,查看历史资料,目前这个过程执行的大致时间在3040分钟左右。其中主要sql执行时间 :

sql1: create table product_dimension_exchange1   cost time :334              rows:680

sql2: create table product_dimension_exchange2   cost time :15                rows:30

sql3: create table product_dimension_exchange3   cost time :121             rows: 4770

 

sql4: insert /*+ append parallel(degree 8) */ into product_dimension_exchange4 select * from product_dimension_exchange1   cost time :144

sql5: insert /*+ append parallel(degree 8) */ into product_dimension_exchange4 select * from product_dimension_exchange2   cost time :1

sql6: insert /*+ append parallel(degree 8) */ into product_dimension_exchange4 select * from product_dimension_exchange3   cost time :1280

 

时间消耗集中在sql1,sql3,sql4,sql6,

 

问题分析与解决

sql1语句的分析

问题点1 :没有或者错误的统计信息,导致了oracle cbo选择效率底下的执行计划

问题点2:对大表PRODUCT_DIMENSION不必要的全表扫描,导致了sql执行时间增大

 

对于sql1create 表的记录数在680万,时间消耗在334秒,比sql3 的执行时间还要多。

分析sql1对应的sql语句,explain plan for  sql1,可以看到sql1 表连接是nl(nest loop) 方式,依上面得到的表的记录数,

都是大表在做join,一般不会选择nl方式,oracle cbo 优化器选择的不恰当的执行计划,而cbo是依赖于表及索引

的统计信息的收集,查看二个表的统计信息,

 

SQL> Select table_name,num_rows,blocks,avg_row_len,last_analyzed From all_tables Where lower(table_name) In ('product_dimension_temp','product_dimension');

 

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED

------------------------------ ---------- ---------- ----------- -------------

PRODUCT_DIMENSION                                               

PRODUCT_DIMENSION_TEMP                  0          0           0 16-Jun-08 10:

 

PRODUCT_DIMENSION_TEMP  显示统计时间是08年的,且当时统计num_rows,blocks0oracle cbo认为是个小表(实际上该表有800多笔记录),且此时表PRODUCT_DIMENSION 在列CHANNELID, PRODUCTID, DATASOURCE上有一个复合index,且这个索引具有高度的可选择性(唯一性),此时非常适合走nl连接,oracle cbo据此选择nl连接,但实际上其执行效率却相对hash要低。见下面的测试

1:17:24 PM SQL>   

 create table product_dimension_klj1 parallel nologging As

Select/*+use_hash(pt,p) */ p.productkey,pt.productid,pt.channelid,pt.categoryid,pt.datasource,pt.countrycode,pt.productname,pt.productdesc,pt.srcimagesize,pt.statuscode,

pt.mfid,pt.mfname,pt.upc,pt.uniquecode,pt.isbn,pt.sku,pt.adddate,pt.lastchangedate

from product_klj pt join edw.product_dimension p on pt.productid=p.productid

  and pt.channelid=p.channelid and pt.datasource=p.datasource

 

Table created

 

1:19:18 PM SQL>

 

执行hints 提示,让其走hash连接,时间消耗大概在114秒。

 

4:06:29 PM SQL>  

create table product_dimension_klj1 parallel nologging As

select  /*+ use_nl(pt,p) */ p.productkey,pt.productid,pt.channelid,pt.categoryid,pt.datasource,

pt.countrycode,pt.productname,pt.productdesc,pt.srcimagesize,pt.statuscode,

pt.mfid,pt.mfname,pt.upc,pt.uniquecode,pt.isbn,pt.sku,pt.adddate,pt.lastchangedate

from product_klj pt join edw.product_dimension p on pt.productid=p.productid

and pt.channelid=p.channelid and pt.datasource=p.datasource

 

Table created

4:10:16 PM SQL>

 

继续分析sql1语句及其执行计划,cost主要消耗是在product_dimension表的扫描上,该表现有的复合index,但缺少productkey这一列,结合该语句的功能,可以modify这个索引,

彻底避免掉NL 下的 TABLE ACCESS BY INDEX ROWID 的消耗,以及hash连接下的TABLE ACCESS FULL 全表扫描.

下面是测试

product_dimension_klj  是测试表,通过ctas方式 edw.product_dimension 得到,可以代替edw.product_dimension 做测试

 

SQL> create index IDX2_PROD_klj_UNION1 on PRODUCT_DIMENSION_klj (CHANNELID, PRODUCTID, DATASOURCE,productkey) parallel nologging;

3:08:13 PM SQL>  

create table product_dimension_klj1 parallel nologging As

select p.productkey,pt.productid,pt.channelid,pt.categoryid,pt.datasource,

pt.countrycode,pt.productname,pt.productdesc,pt.srcimagesize,pt.statuscode,

pt.mfid,pt.mfname,pt.upc,pt.uniquecode,pt.isbn,pt.sku,pt.adddate,pt.lastchangedate

from product_klj pt join product_dimension_klj p on pt.productid=p.productid

and pt.channelid=p.channelid and pt.datasource=p.datasource

 

Table created

 

3:09:50 PM SQL>

添加索引后执行时间在63

 

sql1的优化结束

 

sql4,sql6语句的分析

sql4,sql6都是往表 PRODUCT_DIMENSION_EXCHANGE4  insert操作,hints提示采用append模式,其执行时间为何要比其全表扫描时间还长?

 

问题1 PRODUCT_DIMENSION_EXCHANGE4 表上有一个主键索引

问题2 PRODUCT_DIMENSION_EXCHANGE4 是一个分区表,在这个环境中并没有好处

问题3 没有正确的启用parallel dml

 

SQL> create table PRODUCT_DIMENSION_klj4 parallel nologging as select * from PRODUCT_DIMENSION_EXCHANGE4 where 1=0;

 

11:16:24 AM SQL> insert /*+ append parallel(b 8) */ into product_dimension_klj4 b select * from product_dimension_exchange3;

47721206 rows inserted

11:24:10 AM SQL> rollback;

 

PRODUCT_DIMENSION_klj4 非分区且无索引上执行要9分钟多一点。

 

继续测试enable dml parallel

 

11:29:40 AM SQL> drop table product_dimension_klj4 purge;

Table dropped

11:29:58 AM SQL> create table PRODUCT_DIMENSION_klj4 parallel nologging as select * from PRODUCT_DIMENSION_EXCHANGE4 where 1=0;

Table created

11:30:07 AM SQL> alter session enable parallel dml;

11:30:33 AM SQL> insert /*+ append parallel(b 16) */ into product_dimension_klj4 b select * from product_dimension_exchange3;

47721206 rows inserted

11:32:20 AM SQL> rollback;

 

启动并行度到16的时候,时间大致为2分钟,这个速度还是比较满意的。

再来测试上面的速度提升时parallel dml来的,而不是sga data buffer cache的结果

 

11:34:07 AM SQL> drop table product_dimension_klj4 purge;

Table dropped

11:34:14 AM SQL> create table PRODUCT_DIMENSION_klj4 parallel nologging as select * from PRODUCT_DIMENSION_EXCHANGE4 where 1=0;

Table created

11:34:25 AM SQL> alter session disable parallel dml;

Session altered

11:34:46 AM SQL>

11:34:52 AM SQL>

11:34:53 AM SQL> insert /*+ append parallel(b 16) */ into product_dimension_klj4 b select * from product_dimension_exchange3;

47721206 rows inserted

11:42:17 AM SQL>

 

禁用parallel后的执行时间为7分半,说明parallel dml起到效果

 

继续测试,如果product_dimension_klj4 的结构和PRODUCT_DIMENSION_EXCHANGE4 一摸一样,其结果呢?

重建为分区表,不加索引

11:51:49 AM SQL> insert /*+ append parallel(b 16) */ into product_dimension_klj4 b select * from product_dimension_exchange3;

47721206 rows inserted

12:01:17 PM SQL> rollback;

执行时间消耗大概在910分钟

2 增加主键索引

12:08:32 PM SQL> alter table PRODUCT_DIMENSION_klj4

              2    add primary key (PRODUCTKEY)

              3    using index

              4    tablespace USERS_TBS

              5  ;

Table altered

12:08:36 PM SQL>

12:08:44 PM SQL> insert /*+ append parallel(b 16) */ into product_dimension_klj4 b select * from product_dimension_exchange3;

47721206 rows inserted

12:31:19 PM SQL>

 

时间消耗约23分钟

 

sql6优化了,sql4同样也就不攻自破了。

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    174153