解析一个通过添加本地分区索引提高SQL性能的案例

今天接到同事求助，说有一个select query，在Oracle上要跑一分多钟，他希望能在5s内出结果，以下就是解决这个问题的方法，需要的朋友可以参考下 该sql如下： 代码如下:

Select /*+ parallel(src, 8) */ distinct src.systemname as systemname ,



 
今天接到同事求助，说有一个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 meta_dbql_table_usage_exp_hst src

 inner join DR_QRY_LOG_EXP_HST rl on

 src.acctstringdate = rl.acctstringdate

 and src.queryid = rl.queryid

 And Src.Systemname = Rl.Systemname

 and src.acctstringdate > sysdate - 30

 And Rl.Acctstringdate > Sysdate - 30

 inner join meta_dr_qry_log_tgt_all_hst tgt on

 upper(tgt.systemname) = upper('MOZART')

 And Upper(tgt.Databasename) = Upper('GDW_TABLES')

 And Upper(tgt.Tablename) = Upper('SSA_SLNG_LSTG_MTRC_SD')

 AND src.acctstringdate = tgt.acctstringdate

 and rl.statement_id = tgt.statement_id

 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 | | 59 | 1076K| 4900 | | |

| 9 | BUFFER SORT | | | | | | |

| 10 | PX RECEIVE | | | | | | |

| 11 | PX SEND BROADCAST | :TQ10000 | | | | | |

| 12 | PARTITION RANGE ITERATOR | | 1 | 56 | 4746 | KEY | 14 |

|* 13 | TABLE ACCESS FULL | META_DR_QRY_LOG_TGT_ALL_HST | 1 | 56 | 4746 | KEY | 14 |

| 14 | PX BLOCK ITERATOR | | 59 | 586K| 154 | KEY | KEY |

|* 15 | TABLE ACCESS FULL | META_DBQL_TABLE_USAGE_EXP_HST | 59 | 586K| 154 | KEY | KEY |

| 16 | PARTITION RANGE ITERATOR | | 1 | | 2 | KEY | KEY |

|* 17 | INDEX RANGE SCAN | DR_QRY_LOG_EXP_HST_IDX | 1 | | 2 | KEY | KEY |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

 6 - filter("RL"."STATEMENT_ID"="TGT"."STATEMENT_ID" AND "RL"."SYSTEMNAME"="TGT"."SYSTEMNAME" AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")

 13 - filter(UPPER("TGT"."SYSTEMNAME")='MOZART' AND UPPER("TGT"."DATABASENAME")='GDW_TABLES' AND

 UPPER("TGT"."TABLENAME")='SSA_SLNG_LSTG_MTRC_SD' AND "TGT"."ACCTSTRINGDATE">SYSDATE@!-30 AND "TGT"."SYSTEMNAME" IS NOT NULL

 "TGT"."DATABASENAME" IS NOT NULL AND "TGT"."TABLENAME" IS NOT NULL)

 15 - filter("SRC"."ACCTSTRINGDATE"="TGT"."ACCTSTRINGDATE" AND (UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") OR

 UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") OR UPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME")) AND

 "SRC"."ACCTSTRINGDATE">SYSDATE@!-30)

 17 - access("SRC"."QUERYID"="RL"."QUERYID" AND "SRC"."ACCTSTRINGDATE"="RL"."ACCTSTRINGDATE")

 filter("RL"."ACCTSTRINGDATE">SYSDATE@!-30)