調(diào)優(yōu)思想之-分布式優(yōu)化

aqcjsy1

部分文章發(fā)布在itpub上，網(wǎng)民aqcjsy1

分布式優(yōu)化

在現(xiàn)實(shí)系統(tǒng)中，由于業(yè)務(wù)的擴(kuò)張或者為了保持已有系統(tǒng)的投資，往往一個(gè)應(yīng)用通常會(huì)涉及到多個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的訪問(wèn)，比如說(shuō)CRM系統(tǒng)即需要訪問(wèn)營(yíng)銷系統(tǒng)中的一小部分?jǐn)?shù)據(jù)，也需要訪問(wèn)客戶回訪平臺(tái)的部分?jǐn)?shù)據(jù)，而這些數(shù)據(jù)庫(kù)系統(tǒng)往往由不同的部門(mén)或者公司開(kāi)發(fā)或維護(hù)。由于各種不同的原因如信息機(jī)密以及部門(mén)、公司等出于自身利益的考慮，往往這些數(shù)據(jù)庫(kù)難以進(jìn)行較好的整合，因此新開(kāi)發(fā)的應(yīng)用就不得不進(jìn)行分布式的處理，通常這些系統(tǒng)之間又需要能夠訪問(wèn)彼此實(shí)時(shí)的數(shù)據(jù)，而且對(duì)性能又有一定的要求，比如說(shuō)客戶要求系統(tǒng)的響應(yīng)時(shí)間不得超過(guò)3秒，此時(shí)如果事務(wù)在異地涉及的數(shù)據(jù)訪問(wèn)量超過(guò)百萬(wàn)，直接通過(guò)dblink進(jìn)行訪問(wèn)查詢?cè)诤芏嗲闆r下會(huì)出現(xiàn)響應(yīng)時(shí)間很慢的情況。

 

總的來(lái)說(shuō)，只要一個(gè)系統(tǒng)訪問(wèn)的數(shù)據(jù)量不大或者對(duì)系統(tǒng)性能要求不是非常嚴(yán)格，使用driving_site提示足以滿足要求，但是如果系統(tǒng)對(duì)響應(yīng)時(shí)間的要求比較高，driving_site將無(wú)法滿足要求，在這種情況下，我們可能需要重寫(xiě)拆分語(yǔ)句，以便最小化數(shù)據(jù)的傳輸。

 

考慮下列下列生產(chǎn)中的一個(gè)例子，cb_client有360萬(wàn)記錄，cb_task大約150多萬(wàn)記錄，customer有450萬(wàn)記錄，operatorbranch幾千條，其中cb_client和cb_task在另一個(gè)數(shù)據(jù)庫(kù)中，通過(guò)dblink連接訪問(wèn)。

with t as(

        select a.account,

               a.branchno,

               a.activeid,

               b.exectime,

               (case

                 when a.taskstatus = '500' and a.callstatus not in ('5515003', '5515004') then

                  1

                 when a.taskstatus = '600' and a.callstatus = '5512004' then

                  1

                 when a.taskstatus = '500' and a.callstatus = '5515003' then

                  1.3

                 when a.taskstatus = '500' and a.callstatus = '5515004' then

                  1.4

                 else

                  4

               end) status

          from cb_client a, cb_task b

         where a.id = b.id

           and a.foreigntype = 'htcrm'

           and instr('81,82,83,85,86,88,89,90,91,93,94,,96,97,99', a.activeid) > 0

           and to_char(b.exectime, 'yyyymmdd') >= 20100101),

        p as(

        select account, branchno, exectime, status

          from (select t.account,

                       t.branchno,

                       t.exectime,

                       t.status,

                       row_number() over(partition by t.account, t.branchno order by t.status, t.exectime) rn

                  from t

                 /*where instr(v_c_tasktype, t.activeid) > 0*/)

         where rn < 2)

        select y.l_branch_no,

               y.l_total,

               y.l_tel_done,

               --y.l_email_done,

               y.l_face_done,

               y.l_auto_done,

               y.l_intime_done,

               y.l_intime_undo,

               to_char(round(y.l_intime_done / y.l_total, 4) * 100, 990.99) || '%' en_intime_done,

               to_char(round(y.l_intime_undo / y.l_total, 4) * 100, 990.99) || '%' en_intime_undo

          from (select x.l_branch_no,

                       count(1) as l_total,

                       sum(case when x.is_intime = 1 and x.status = 1 then 1 else 0 end ) as l_tel_done,

                       --sum(case when x.is_intime = 1 and x.status = 1.2 then 1 else 0 end ) as l_email_done,

                       sum(case when x.is_intime = 1 and x.status = 1.3 then 1 else 0 end ) as l_face_done,

                       sum(case when x.is_intime = 1 and x.status = 1.4 then 1 else 0 end ) as l_auto_done,

                       sum(case when x.is_intime = 1 then 1 else 0 end ) as l_intime_done,

                       sum(case when x.is_intime = 0 then 1 else 0 end ) as l_intime_undo

                  from (select c.vc_customer_no,

                               c.l_branch_no,

                               (case

                                 when p.status>=1 and p.status <=1.4 and p.exectime is not null and

                                      (to_char(p.exectime, 'yyyymmdd') >= 20100101 or 20100101 = 0) and

                                      (to_char(p.exectime, 'yyyymmdd') <= 20110101 or 20110101 = 0) then

                                  1

                                 else

                                  0

                               end) is_intime,

                               p.status

                          from customer c, p

                         where c.vc_customer_no = p.account(+)

                           and c.l_branch_no = p.branchno(+)

                           and c.c_status = '1'

                           and (c.l_reg_date >= 19800101 or 19800101 = 0)

                           and (c.l_reg_date <= 20091231 or 20091231 = 0)) x

                 group by x.l_branch_no) y

         where exists (select 1

                  from operatorbranch op

                 where op.vc_operator_no = 8888

                   and op.l_branch_no = y.l_branch_no)

         order by y.l_branch_no;

 

在具有32G內(nèi)存，6*4核的系統(tǒng)中這個(gè)語(yǔ)句需要執(zhí)行將近90秒，而該查詢有比較頻繁，客戶要求最好越快越好，一定要在30秒內(nèi)響應(yīng)。其原始執(zhí)行計(jì)劃如下：

因?yàn)檫@個(gè)語(yǔ)句的涉及了兩個(gè)系統(tǒng)的訪問(wèn)，其中每個(gè)系統(tǒng)所要訪問(wèn)的數(shù)據(jù)量相差不是很大。所以在那邊做都不合適，最好結(jié)局是各自在一邊處理。因此，在本例中，分布式常用的hint “driving_site”解決不了我們的實(shí)際問(wèn)題，我們必須使用進(jìn)行拆分sql語(yǔ)句，將前面的兩個(gè)with子句拆分成臨時(shí)表，并使用driving_site提示讓查詢引擎在遠(yuǎn)程系統(tǒng)完成結(jié)果集的處理。這樣網(wǎng)絡(luò)的傳輸就可以最小化。

 

另外，由于每個(gè)結(jié)果集的大小仍然是100w和400w左右，因此我們?cè)谥鞑樵冎袑?duì)customer使用了parallel_index并行掃描可覆蓋索引。

Create table t as

with t as(

        select /*+ driving_site(a)*/a.account,

               a.branchno,

               a.activeid,

               b.exectime,

               (case

                 when a.taskstatus = '500' and a.callstatus not in ('5515003', '5515004') then

                  1

                 when a.taskstatus = '600' and a.callstatus = '5512004' then

                  1

                 when a.taskstatus = '500' and a.callstatus = '5515003' then

                  1.3

                 when a.taskstatus = '500' and a.callstatus = '5515004' then

                  1.4

                 else

                  4

               end) status

          from cb_client a, cb_task b

         where a.id = b.id

           and a.foreigntype = 'htcrm'

           and a.activeid in ('81’,’82’,’83’,’85’,’86’,’88’,’89’,’90’,’91’,’93’,’94’,’96’,’97’,’99’) > 0

           and to_char(b.exectime, 'yyyymmdd') >= 20100101),

        p as(

        select account, branchno, exectime, status

          from (select t.account,

                       t.branchno,

                       t.exectime,

                       t.status,

                       row_number() over(partition by t.account, t.branchno order by t.status, t.exectime) rn

                  from t

                 /*where instr(v_c_tasktype, t.activeid) > 0*/)

         where rn = 1);

 

        select y.l_branch_no,

               y.l_total,

               y.l_tel_done,

               --y.l_email_done,

               y.l_face_done,

               y.l_auto_done,

               y.l_intime_done,

               y.l_intime_undo,

               to_char(round(y.l_intime_done / y.l_total, 4) * 100, 990.99) || '%' en_intime_done,

               to_char(round(y.l_intime_undo / y.l_total, 4) * 100, 990.99) || '%' en_intime_undo

          from (select x.l_branch_no,

                       count(1) as l_total,

                       sum(case when x.is_intime = 1 and x.status = 1 then 1 else 0 end ) as l_tel_done,

                       --sum(case when x.is_intime = 1 and x.status = 1.2 then 1 else 0 end ) as l_email_done,

                       sum(case when x.is_intime = 1 and x.status = 1.3 then 1 else 0 end ) as l_face_done,

                       sum(case when x.is_intime = 1 and x.status = 1.4 then 1 else 0 end ) as l_auto_done,

                       sum(case when x.is_intime = 1 then 1 else 0 end ) as l_intime_done,

                       sum(case when x.is_intime = 0 then 1 else 0 end ) as l_intime_undo

                  from (select /*+ parallel_index(c,4) */c.vc_customer_no,

                               c.l_branch_no,

                               (case

                                 when p.status>=1 and p.status <=1.4 and p.exectime is not null and

                                      (to_char(p.exectime, 'yyyymmdd') >= 20100101 or 20100101 = 0) and

                                      (to_char(p.exectime, 'yyyymmdd') <= 20110101 or 20110101 = 0) then

                                  1

                                 else

                                  0

                               end) is_intime,

                               p.status

                          from customer c, p

                         where c.vc_customer_no = p.account(+)

                           and c.l_branch_no = p.branchno(+)

                           and c.c_status = '1'

                           and (c.l_reg_date >= 19800101 or 19800101 = 0)

                           and (c.l_reg_date <= 20091231 or 20091231 = 0)) x

                 group by x.l_branch_no) y

         where exists (select /*+ hash_sj */1

                  from operatorbranch op

                 where op.vc_operator_no = 8888

                   and op.l_branch_no = y.l_branch_no)

         order by y.l_branch_no;

 

最后優(yōu)化完成后，響應(yīng)時(shí)間為子查詢約8s，主查詢沒(méi)有超過(guò)2s。兩者前后的執(zhí)行計(jì)劃除了主查詢使用并行以外，并沒(méi)有發(fā)生其他的變化。

 

最后，在涉及并行和分布式的場(chǎng)合中，將parallel_execution_message_size參數(shù)調(diào)到4k有一定的性能收益，但不是很大。

 

筆記本藍(lán)屏，shit。。

注：對(duì)于業(yè)務(wù)不是很復(fù)雜的情況，如果只是簡(jiǎn)單的查詢遠(yuǎn)程的數(shù)據(jù)，最好還是使用GG或流等機(jī)制實(shí)現(xiàn)兩庫(kù)同步能夠獲得比分布式好的多的性能。