SQL优化—nested loop优化
跑批时间段22:00-23:00，生成AWR报告
分析sql:SQL_ID='5hfw4smzs2pqw'
执行计划：
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5hfw4smzs2pqw',NULL,'ALL'));
SQL_ID 5hfw4smzs2pqw, child number 0 ------------------------------------- SELECT a.SEQ_NO,a.ACCTNO,a.POST_TXN_CD,b.POST_METHOD_CD,b.AMT_TYPE,a.BIL L_AMT,a.POST_DATE,a.EXPLAIN FROM T_EMPLOYY_A a INNER JOIN T_EMPLOYY_B b ON a.POST_TXN_CD=b.POST_TXN_CD AND a.POST_DATE=:1 AND a.ACCTNO=:2 AND POST_STATUS_CD='0' Plan hash value: 3635671702 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5248 (100)| | | 1 | NESTED LOOPS | | 1 | 71 | 5248 (1)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 71 | 5248 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | T_EMPLOYY_A | 1 | 61 | 5247 (1)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_T_EMPLOYY_B | 1 | | 0 (0)| | | 5 | TABLE ACCESS BY INDEX ROWID| T_EMPLOYY_B | 1 | 10 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$58A6D7F6 3 - SEL$58A6D7F6 / A@SEL$1 4 - SEL$58A6D7F6 / B@SEL$1 5 - SEL$58A6D7F6 / B@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("A"."ACCTNO"=:2 AND "A"."POST_DATE"=:1 AND "POST_STATUS_CD"='0')) 4 - access("A"."POST_TXN_CD"="B"."POST_TXN_CD") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200], "B"."POST_METHOD_CD"[NUMBER,22], "B"."AMT_TYPE"[CHARACTER,2] 2 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200], "B".ROWID[ROWID,10] 3 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200] 4 - "B".ROWID[ROWID,10] 5 - "B"."POST_METHOD_CD"[NUMBER,22], "B"."AMT_TYPE"[CHARACTER,2] Note ----- - this is an adaptive plan
分析：
执行计划：3-->2-->4-->5-->1-->0
第一层循环：根据POST_DATE=:1,ACCTNO=:2,POST_STATUS_CD='0'嵌套循环，全表扫描驱动表T_EMPLOYY_A；
第二层循环：根据a.POST_TXN_CD=b.POST_TXN_CD，嵌套循环，根据索引PK_T_EMPLOYY_B，得到rowid，访问表T_EMPLOYY_B
索引信息：
SQL> select TABLE_NAME,INDEX_NAME,COLUMN_NAME from dba_ind_columns where TABLE_NAME='T_EMPLOYY_A';
TABLE_NAME INDEX_NAME COLUMN_NAME
-------------------- -------------------- ------------------------------
T_EMPLOYY_A PK_T_EMPLOYY_A SEQ_NO
SQL> select TABLE_NAME,INDEX_NAME,COLUMN_NAME from dba_ind_columns where TABLE_NAME='T_EMPLOYY_B';
TABLE_NAME INDEX_NAME COLUMN_NAME
-------------------- -------------------- ------------------------------
T_EMPLOYY_B PK_T_EMPLOYY_B POST_TXN_CD
表T_EMPLOYY_A条件列、连接条件字段没有索引
表的总行数
SQL> select count(*) from SCOTT.T_EMPLOYY_A;
COUNT(*)
----------
2029447
SQL> select count(*) from SCOTT.T_EMPLOYY_B;
COUNT(*)
----------
52
连接列的匹配情况：
SQL> select count(distinct(POST_TXN_CD)) from SCOTT.T_EMPLOYY_A;
COUNT(DISTINCT(POST_TXN_CD))
----------------------------
26
SQL> select count(distinct(POST_TXN_CD)) from SCOTT.T_EMPLOYY_B;
COUNT(DISTINCT(POST_TXN_CD))
----------------------------
52
ACCTNO列在T_EMPLOYY_A表的筛选性
SQL> select count(distinct(ACCTNO)) from SCOTT.T_EMPLOYY_A;
COUNT(DISTINCT(ACCTNO))
-----------------------
225427
POST_DATE列在T_EMPLOYY_A表的筛选性
SQL> select count(distinct(POST_DATE)) from SCOTT.T_EMPLOYY_A;
COUNT(DISTINCT(POST_DATE))
--------------------------
736
SQL> select count(*) from ( select distinct ACCTNO,POST_DATE from SCOTT.T_EMPLOYY_A);
COUNT(*)
----------
1690282
优化建议：1：在表T_EMPLOYY_A的ACCTNO, POST_DATE列创建联合索引
2：利用hind,修改驱动表为T_EMPLOYY_B.但是需要修改SQL语句,需上线调整,所以不使用。
ACCTNO筛选性强，放在联合索引列的前面
创建联合索引
SQL> create index IDX_ACCTNO_POST_DATE on SCOTT.T_EMPLOYY_A (ACCTNO, POST_DATE) tablespace SCOTT_IDX01
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5hfw4smzs2pqw',NULL,'ALL'));
SQL_ID 5hfw4smzs2pqw, child number 0 ------------------------------------- SELECT a.SEQ_NO,a.ACCTNO,a.POST_TXN_CD,b.POST_METHOD_CD,b.AMT_TYPE,a.BIL L_AMT,a.POST_DATE,a.EXPLAIN FROM T_EMPLOYY_A a INNER JOIN T_EMPLOYY_B b ON a.POST_TXN_CD=b.POST_TXN_CD AND a.POST_DATE=:1 AND a.ACCTNO=:2 AND POST_STATUS_CD='0' Plan hash value: 1730680787 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 (100)| | | 1 | NESTED LOOPS | | 1 | 71 | 5 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 71 | 5 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T_EMPLOYY_A | 1 | 61 | 4 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_ACCTNO_POST_DATE | 1 | | 3 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_T_EMPLOYY_B | 1 | | 0 (0)| | | 6 | TABLE ACCESS BY INDEX ROWID | T_EMPLOYY_B | 1 | 10 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$58A6D7F6 3 - SEL$58A6D7F6 / A@SEL$1 4 - SEL$58A6D7F6 / A@SEL$1 5 - SEL$58A6D7F6 / B@SEL$1 6 - SEL$58A6D7F6 / B@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("POST_STATUS_CD"='0') 4 - access("A"."ACCTNO"=:2 AND "A"."POST_DATE"=:1) 5 - access("A"."POST_TXN_CD"="B"."POST_TXN_CD") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200], "B"."POST_METHOD_CD"[NUMBER,22], "B"."AMT_TYPE"[CHARACTER,2] 2 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200], "B".ROWID[ROWID,10] 3 - "A"."SEQ_NO"[NUMBER,22], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8], "A"."POST_TXN_CD"[NUMBER,22], "A"."BILL_AMT"[NUMBER,22], "A"."EXPLAIN"[VARCHAR2,200] 4 - "A".ROWID[ROWID,10], "A"."ACCTNO"[VARCHAR2,40], "A"."POST_DATE"[CHARACTER,8] 5 - "B".ROWID[ROWID,10] 6 - "B"."POST_METHOD_CD"[NUMBER,22], "B"."AMT_TYPE"[CHARACTER,2] Note ----- - this is an adaptive plan
两天跑批时间段22:00-23:00的性能对比
经优化后，数据库逻辑读下降，CPU负载下降
20180808逻辑读：
20180809逻辑读：
