解析一個通過添加本地分區索引提高SQL性能的案例

今天接到同事求助，說有一個select query，在Oracle上要跑一分多鐘，他希望能在5s內出結果，以下就是解決這個問題的方法，需要的朋友可以參考下  

該sql如下：

復制代碼 代碼如下:
Select  /*+ parallel(src, 8) */ distinct
  src.systemname as systemname
  ,  src.databasename as databasename
  ,  src.tablename as tablename
  ,  src.username as username
from  <STRONG>meta_dbql_table_usage_exp_hst</STRONG> src
 inner <STRONG>join DR_QRY_LOG_EXP_HST</STRONG> rl on
  <STRONG>src.acctstringdate = rl.acctstringdate
  and src.queryid = rl.queryid</STRONG>
  And Src.Systemname = Rl.Systemname
  and src.acctstringdate > sysdate - 30
  And Rl.Acctstringdate > Sysdate - 30
 inner join  <STRONG>meta_dr_qry_log_tgt_all_hst </STRONG>tgt on
  upper(tgt.systemname) = upper('MOZART')
  And Upper(tgt.Databasename) = Upper('GDW_TABLES')
  And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')
  <STRONG>AND src.acctstringdate = tgt.acctstringdate
  and rl.statement_id = tgt.statement_id</STRONG>
  and rl.systemname = tgt.systemname
  And Tgt.Acctstringdate > Sysdate - 30
  And Not(
    Upper(Tgt.Systemname)=Upper(src.systemname)
    And
    Upper(Tgt.Databasename) = Upper(Src.Databasename)
    And
    Upper(Tgt.Tablename) = Upper(Src.Tablename)
    )
  And   tgt.Systemname is not null
  And   tgt.Databasename Is Not Null
  And   tgt.tablename is not null
 

SQL的簡單分析
總 得來看，這個SQL就是三個表 (meta_dbql_table_usage_exp_hst，DR_QRY_LOG_EXP_HST，meta_dr_qry_log_tgt_all_hst） 的INNER JOIN，這三個表數據量都在百萬級別，且都是分區表（以acctstringdate為分區鍵），執行計劃如下：

復制代碼 代碼如下:
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |     1 |   159 |  8654 |       |       |
|   1 |  PX COORDINATOR                        |                               |       |       |       |       |       |
|   2 |   PX SEND QC (RANDOM)                  | :TQ10002                      |     1 |   159 |  8654 |       |       |
|   3 |    SORT UNIQUE                         |                               |     1 |   159 |  8654 |       |       |
|   4 |     PX RECEIVE                         |                               |     1 |    36 |     3 |       |       |
|   5 |      PX SEND HASH                      | :TQ10001                      |     1 |    36 |     3 |       |       |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID| DR_QRY_LOG_EXP_HST            |     1 |    36 |     3 |       |       |
|   7 |        NESTED LOOPS                    |                               |     1 |   159 |  8633 |       |       |
|   8 |         NESTED LOOPS                   |                               |  8959 |  1076K|  4900 |       |       |
|   9 |          BUFFER SORT                   |                               |       |       |       |       |       |
|  10 |           PX RECEIVE             &nb