工作记录-优化大表更新一例
描述:一个大表现有近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分钟，查看历史资料，目前这个过程执行的大致时间在30－40分钟左右。其中主要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执行时间增大
对于sql1，create 表的记录数在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,blocks为0，oracle 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;
执行时间消耗大概在9－10分钟
（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博客 ” ，链接：https://blog.itpub.net/10159839/viewspace-584606/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 树形查找也疯狂&优化措施
- Oracle 分析函数
- DataGuard:Logical Standby Failover
- DataGuard:Logical Standby Switchover
- DataGuard:Logical Standby Create Sample
- DataGuard:Switch Protection Mode
- DataGuard:Physical Standby Failover
- DataGuard:Physical Standby Switchover
- DataGuard:Physical Standby Create Sample
- Unload data from oracle
