oracle並行查詢一例薦

今天碰到一個開發人員反映SQL執行時間過長。根本無法得到結果集。
        看到伺服器壓力也沒有很高，估計又是一個非常消耗磁碟的查詢。果然，發現是一個200w的表和一個超過1100w表的HASH JOIN .
        簡單的幫助優化了一個SQL後，SQL如下:

select    count(ui.usin_uid_fk)
    from table1 av, table2 ui
where av.av_usse_activatedate >= to_date(`20090102`, `yyyymmdd`)
     and av.av_usse_activatedate < to_date(`20090401`, `yyyymmdd`)
     and av.av_usse_uid_fk = ui.usin_uid_fk
     and ui.usin_mcnc_fk =XXX%`

不難想象執行的不是很理想。近20分鐘的執行時間，真是讓人崩潰。

COUNT(UI.USIN_UID_FK)
———————
1918591

Elapsed: 00:19:03.07
Statistics
———————————————————-
                    0    recursive calls
                    0    db block gets
     32921639    consistent gets
         352073    physical reads
                    0    redo size
                395    bytes sent via SQL*Net to client
                503    bytes received via SQL*Net from client
                    2    SQL*Net roundtrips to/from client
                    0    sorts (memory)
                    0    sorts (disk)
                    1    rows processed

對於那張TABLE2的大表（符合條件的超過1100w），決定試圖通過並行來提高執行速度。SQL如下：

select /*+parallel (tbl_userinfo 4)*/ count(ui.usin_uid_fk)
from table1 av, table2 ui
where av.av_usse_activatedate >= to_date(`20090101`, `yyyymmdd`)
and av.av_usse_activatedate < to_date(`20090401`, `yyyymmdd`)
and av.av_usse_uid_fk = ui.usin_uid_fk
and ui.usin_mcnc_fk like `XXX%`;

執行效果還是非常明顯的。從19分鐘多到1分45秒！其中consistent gets更是減少了一個數量級 -:)

COUNT(UI.USIN_UID_FK)
———————
1918591

Elapsed: 00:01:45.15

Statistics
———————————————————-
                    0    recursive calls
                    0    db block gets
        2571109    consistent gets
         124523    physical reads
                    0    redo size
                395    bytes sent via SQL*Net to client
                504    bytes received via SQL*Net from client
                    2    SQL*Net roundtrips to/from client
                    0    sorts (memory)
                    0    sorts (disk)
                    1    rows processed

      因為這個伺服器為2×4核心的cpu，應該可以算是8個CPU，所以應該可以通過增加並行度來進一步減少執行時間。如下SQL：

SQL> select /*+parallel (tbl_userinfo 8)*/ count(ui.usin_uid_fk)
    2        from table1 av, table2 ui
    3     where av.av_usse_activatedate >= to_date(`20090101`, `yyyymmdd`)
    4         and av.av_usse_activatedate < to_date(`20090401`, `yyyymmdd`)
    5         and av.av_usse_uid_fk = ui.usin_uid_fk
    6         and ui.usin_mcnc_fk like `460%`;

COUNT(UI.USIN_UID_FK)
———————
1949033

Elapsed: 00:00:20.60

Statistics
———————————————————-
                    0    recursive calls
                    0    db block gets
        2607524    consistent gets
            55050    physical reads
                    0    redo size
                395    bytes sent via SQL*Net to client
                503    bytes received via SQL*Net from client
                    2    SQL*Net roundtrips to/from client
                    0    sorts (memory)
                    0    sorts (disk)
                    1    rows processed

可以說還是比較理想的。只有20S左右了。雖然最大並行度可以到CPU*2，但是效果未必會好。進一步做一個16個並行度的SQL執行測試。

COUNT(UI.USIN_UID_FK)
———————
1949033

Elapsed: 00:00:20.64

Statistics
———————————————————-
                    0    recursive calls
                    0    db block gets
        2607524    consistent gets
            55299    physical reads
                    0    redo size
                395    bytes sent via SQL*Net to client
                504    bytes received via SQL*Net from client
                    2    SQL*Net roundtrips to/from client
                    0    sorts (memory)
                    0    sorts (disk)
                    1    rows processed

       沒有任何提高，並且執行時間還稍高於並行度為8的SQL。
       通過以上測試我們不難發現：
       在處理大量資料查詢，例如出現HASH JOIN的情況下，並行查詢非常有效果的。也就是說並行查詢在資料倉儲這樣的應用中會“大顯身手”。
        但是並行查詢的使用還是有很多限制的。例如相對較小的資料查詢和連線是會適得其反的。盲目增加並行度也是大忌，相對來講，並行度和CPU數相同比較好。這裡的CPU數應該是指的核心數。例如伺服器中有一個CPU是4核心的，並行度為4是好的。
        技術很難有十全十美的，最重要的是對於特定技術的使用要恰到好處，保證揚長避短。 -:)
—————————-
以上測試環境：
ORACLE 9.2.0.4
RHEL 4 U4

oracle並行查詢一例薦

相關文章