A case in Oracle to improve SQL performance by adding a local partitioning index

I got a colleague asking for help today, saying that there is a select query that runs for more than a minute on Oracle, and he hopes to produce results within 5s, as follows:

 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</stron g> src inner <strong>join dr_qry_log_exp_hst</strong> rl on <strong>src.acctstringdate = Rl.acct StringDate and Src.queryid = rl.queryid</strong> and Src.systemname = Rl.systemname and Src.acctstringda Te > 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.state  ment_id = tgt.statement_id</strong> and Rl.systemname = Tgt.systemname and tgt.acctstringdate > Sysdate- 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 are NOT null; 

A simple analysis of SQL

In a way, this SQL is the inner JOIN of three tables (META_DBQL_TABLE_USAGE_EXP_HST,DR_QRY_LOG_EXP_HST,META_DR_QRY_LOG_TGT_ALL_HST), These three table data volumes are millions, and are partitioned tables (with Acctstringdate as the partitioning key), and the execution plan is as follows:

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

---------------------------------------------------------- ---------  
| 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 |  

     |       |       |       |       |  
|  |            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 |  

|   8959 |   586k|   154 |   KEY |  
KEY |           |* 15 | TABLE ACCESS Full |  

Meta_dbql_table_usage_exp_hst |   8959 |   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)