今天接到同事求助，说有一个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)
)
Andtgt.Systemname is not null
Andtgt.Databasename Is Not Null
Andtgt.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|||||||
|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 NOTNULL
"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") ORUPPER("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)
定位问题从 上面执行计划中的表连接方式可以知道，这三个表之间进行了两次NESTED LOOP，问题出现在最里层的NESTEDLOOP（对两个表都做了TABLE FULLSCAN），因为表都是百万级别的（即时过滤后的数据量也不小），性能问题就出现在内表（即被驱动 表）META_DBQL_TABLE_USAGE_EXP_HST做了太多次的全表扫描。如果能把全表扫描转换成索引，则性能可以大幅度提高。
下面是NESTED LOOP的介绍：嵌套连接把要处理的数据集分为外部循环（驱动数据源）和内部循环（被驱动数据源），外部循环只执行一次，内部循环执行的次数等于外部循环执行返回的数据个数。
这种连接的好处是内存使用非常少。
如果驱动数据源有限，且被驱动表在连接列上有相应的索引，则这种连接方式才是高效的。
下面是这三个表上索引的情况：
复制代码 代码如下:
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_NAMETABLE_NAME
------------------------------------------------------------ ------------------------------------------------------------
META_DR_QRY_LOG_TGT_ALL_IDXMETA_DR_QRY_LOG_TGT_ALL_HST
META_DBQL_TUSAGE_EHST_IDXMETA_DBQL_TABLE_USAGE_EXP_HST
DR_QRY_LOG_EXP_HST_IDXDR_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" ("QUERYID", "ACCTSTRINGDATE")
这 三个索引都是本地分区索引（都包含分区键acctstringdate），很显然，DR_QRY_LOG_EXP_HST表少了个索引，因为它与表 meta_dr_qry_log_tgt_all_hst在statement_id上做join，因此应该在它的statement_id上也创建本地分区索引如下：
复制代码 代码如下:
create index DR_QRY_LOG_EXP_HST_IDX2 on gv.DR_QRY_LOG_EXP_HST (statement_id,ACCTSTRINGDATE) local;
性能对比新的执行计划如下：
复制代码 代码如下:
------------------------------------------------------------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes | Cost| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|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 |NESTED 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 RANGE SCAN|DR_QRY_LOG_EXP_HST_IDX2</STRONG>|31 ||15 |KEY |14 |
|10 |PARTITION RANGE ITERATOR||1 ||2 |KEY |KEY |
|* 11 |INDEX RANGE SCAN| META_DBQL_TUSAGE_EHST_IDX|1 ||2 |KEY |KEY |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((UPPER("TGT"."SYSTEMNAME")<>UPPER("SRC"."SYSTEMNAME") OR
UPPER("TGT"."DATABASENAME")<>UPPER("SRC"."DATABASENAME") ORUPPER("TGT"."TABLENAME")<>UPPER("SRC"."TABLENAME"))
AND "SRC"."SYSTEMNAME"="RL"."SYSTEMNAME")
6 - 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 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)
从新的的执行计划可以看出，它的第一个NESTED LOOP果然用了最新创建的索引。下面是执行时间：
复制代码 代码如下:
已用时间:00: 00: 02.16
两秒种搞定，远远超出他期望的5s ：）方法总结NESTED LOOP高效的条件：驱动数据源有限，且被驱动表在连接列上有相应的索引。
