工作記錄-優化大表更新一例

描述:一個大表現有近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部落格 ” ，連結：http://blog.itpub.net/10159839/viewspace-584606/，如需轉載，請註明出處，否則將追究法律責任。