[Oracle] How to Improve SQL Performance by adding local partition Indexes

[Oracle] a case of increasing SQL Performance by adding local partition indexes received help from colleagues today, saying that a select query would take more than one minute on Oracle, he hopes to get results within 5s. The SQL statement is as follows: [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. acc Tstringdate 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 ('audio art') And Upper (tgt. databasename) = Upper ('gdw _ TABLES ') And Upper (tgt. tablename) = Upper ('ssa _ slng_lstg_mtrc_sd') <strong> AND src. acctstringdat E = 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 According to the simple analysis, this SQL statement is the INNER JOIN of three tables (meta_dbql_table_usage_exp_hst, DR_QRY_LOG_EXP_HST, rows). The data volume of these three tables is in the million level, all are partition tables (with acctstringdate as the partition key). The execution plan is as follows: [SQL] partition | Id | Operation | Name | Rows | Bytes | Cost | Pstart | Pstop | Partition -------------------------------------------------- California | 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 | * 15 | table access full | META_DBQL_TABLE_USAGE_EXP_HST | 8959 | 586K | 154 | KEY | 16 | partition range iterator | 1 | 2 | KEY | * 17 | index range scan | DR_QRY_LOG_EXP_HST_IDX | 1 | 2 | KEY | ------------------------------------------------------------------------------------------------------------------------ Predicate Informatio N (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") = 'audio art' 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) locate the problem. You can see from the table connection method in the execution plan above. Two NESTED loops are performed between the three tables, the problem occurs in the nested loop at the innermost layer (table full scan is performed for both tables), because the tables are millions of (the amount of data after instant filtering is not small ), A performance problem occurs when META_DBQL_TABLE_USAGE_EXP_HST scans the entire table too many times in the internal table (the driving table. If you can convert a full table scan to an index, the performance can be greatly improved. The following describes the nested loop: NESTED links divide the dataset to be processed into an External LOOP (driving data source) and an internal LOOP (Driven Data Source). The External LOOP is executed only once, the number of times an internal loop is executed is equal to the number of data returned by an external loop. The advantage of this connection is that the memory usage is very small. If the drive data source is limited and the drive table has an index on the connection column, this connection method is efficient. The following are the indexes on these three tables: [SQL] SQL> select index_name, table_name from user_indexes where table_name in ('Dr _ QRY_LOG_EXP_HST ', upper ('meta _ dbql_table_usage_exp_hst '), upper ('meta _ dr_qry_log_tgt_all_hs INDEX_NAME TABLE_NAME using META_DR_QRY_LOG_TGT_ALL_IDX META_DR_QRY_LOG_TGT_ALL_HST META_DBQL_T USAGE_EHST_IDX META_DBQL_TABLE_USAGE_EXP_HST DR_QRY_LOG_EXP_HST_IDX DR_QRY_LOG_EXP_HST create index "GV ". "META_DR_QRY_LOG_TGT_ALL_IDX" ON "GV ". "META_DR_QRY_LOG_TGT_ALL_HST" ("STATEMENT_ID", "ACCTSTRINGDATE") create index "GV ". "META_DBQL_TUSAGE_EHST_IDX" ON "GV ". "META_DBQL_TABLE_USAGE_EXP_HST" ("QUERYID", "ACCTSTRINGDATE") create index "GV ". "DR_QRY_LOG_EXP_HST_IDX" ON "GV ". "DR_QRY_LOG_EXP_HST" ("QU ERYID "," ACCTSTRINGDATE ") All three indexes are local partition indexes (both contain the partition key acctstringdate). Obviously, DR_QRY_LOG_EXP_HST table has fewer indexes, because it joins the table meta_dr_qry_log_tgt_all_hst on statement_id, you should create a local partition index on its statement_id as follows: [SQL] create index DR_QRY_LOG_EXP_HST_IDX2 on gv. DR_QRY_LOG_EXP_HST (statement_id, ACCTSTRINGDATE) local; Performance Comparison new execution plan: [SQL] Explain -------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart | Pstop | Bytes | 0 | select statement | 1 | 159 | 4838 | 1 | sort unique | | 1 | 159 | 4838 | * 2 | table access by local index rowid | META_DBQL_TABLE_USAGE_EXP_HST | 1 | 67 | 3 | 3 | NESTE D loops | 1 | 159 | 4816 | 4 | nested loops | 18 | 1656 | 4762 | 5 | partition range iterator | 1 | 56 | 4746 | KEY | 14 | * 6 | table access full | META_DR_QRY_LOG_TGT_ALL_HST | 1 | 56 | 4746 | KEY | 14 | 7 | partition range iterator | 18 | 648 | 16 | KEY | 14 | * 8 | table access by local index rowid | DR_QRY_LOG_EXP_HST | 18 | 648 | 16 | KEY | 14 | * 9 | <strong> INDEX RA Nge scan | DR_QRY_LOG_EXP_HST_IDX2 </strong> | 31 | 15 | KEY | 14 | 10 | partition range iterator | 1 | 2 | KEY | * 11 | INDEX range scan | META_DBQL_TUSAGE_EHST_IDX | 1 | 2 | KEY | encrypted Predicate Information (identified by operation id) :----------------------- ---------------------------- 2-filter (UPPER ("TGT ". "SYSTEMNAME") <> UPPER ("SRC ". "SYSTEMNAME") or upper ("TGT ". "DATABASENAME") <> UPPER ("SRC ". "DATABASENAME") or upper ("TGT ". "TABLENAME") <> UPPER ("SRC ". "TABLENAME") AND "SRC ". "SYSTEMNAME" = "RL ". "SYSTEMNAME") 6-filter (UPPER ("TGT ". "SYSTEMNAME") = 'audio art' 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 and "TGT ". "DATABASENAME" is not null and "TGT ". "TABLENAME" is not null) 8-filter ("RL ". "SYSTEMNAME" = "TGT ". "SYSTEMNAME") 9-access ("RL ". "STATEMENT_ID" = "TGT ". "STATEMENT_ID" AND "RL ". "ACCTSTRINGDATE"> SYSDATE @! -30 AND "RL ". "ACCTSTRINGDATE" is not null) 11-access ("SRC ". "QUERYID" = "RL ". "QUERYID" AND "SRC ". "ACCTSTRINGDATE" = "RL ". "ACCTSTRINGDATE") filter ("SRC ". "ACCTSTRINGDATE" = "TGT ". "ACCTSTRINGDATE" AND "SRC ". "ACCTSTRINGDATE"> SYSDATE @! -30) The new execution plan shows that its first nested loop uses the newly created index. The following is the execution time: [plain] used time: 00: 00: 02.16 two seconds, far beyond the expected 5 s :) method to summarize the Efficient Conditions of nested loop: the drive data source is limited and the drive table has an index on the connection column.