随笔 - 404  文章 - 4  评论 - 0  阅读 - 25万

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逻辑读:

 

 

 

 

 

posted on   HelonTian  阅读(5346)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

点击右上角即可分享
微信分享提示