latch: cache buffers chains引起的响应慢

来源：互联网发布：算法工程师招聘编辑：程序博客网时间：2024/05/16 09:28

今天凌晨升级了SP接口的应用代码，下午SP联调测试时，发现没有返回响应包，超过约定的500ms。查询后台日志，发现响应基本在22s左右，难怪他们反映说没响应。应用还未正式上线，所以不可能是并发应用的数据库争用引起的，排除运维人员查询和统计之后，登录数据库查询。

SELECT event,COUNT (1) from v$session_wait GROUP BY event ORDER BY COUNT(1 ) DESC;

发现偶尔存在latch: cache buffers chains等待事件，根据该事件(脚本太长,就不贴出来了),查询发现时如下语句造成的。

SELECT *

FROM TB_BM_PROD_INST INST

WHERE PROD_TYPE_CD = 'SP_NAME'

AND (INST.PRODUCT_ID = 47 AND

INST.SERV_CODE IN

( SELECT SERV_CODE FROM TB_BM_QOS_CUST WHERE DIAL_ACCT IN (:1 )) OR

INST.CUST_ID IN

( SELECT CUST_ID FROM TB_BM_SP_CUST WHERE SP_ACCOUNT IN (:2 )))

这确实是由于局方需求，修改了SQL逻辑，之前在开发库，相应的索引都已经建了的，看下生产库，是否有建。

SQL> SELECT IND.TABLE_NAME, IND.INDEX_NAME, COL.COLUMN_NAME, STATUS

2 FROM USER_INDEXES IND, USER_IND_COLUMNS COL

3 WHERE IND.INDEX_NAME = COL.INDEX_NAME

4 AND IND.TABLE_NAME IN

5 ('TB_BM_PROD_INST', 'TB_BM_QOS_CUST', 'TB_BM_SP_CUST')

6 ORDER BY IND.TABLE_NAME, COL.COLUMN_NAME;

TABLE_NAME INDEX_NAME COLUMN_NAME STATUS

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

TB_BM_PROD_INST INX_PROD_INST_CREATE_DATE CREATE_DATE VALID

TB_BM_PROD_INST IX_PROD_INST_CUST_ID CUST_ID VALID

TB_BM_PROD_INST AK_KEY_1_TB_BM_PR PROD_INST_ID VALID

TB_BM_PROD_INST INDEX_SERV_CODE SERV_CODE VALID

TB_BM_QOS_CUST INDEX_ACC_NBR ACC_NBR VALID

TB_BM_QOS_CUST PK_TB_BM_QOS_CUST CUST_ID VALID

TB_BM_QOS_CUST INDEX_DIAL_ACCT DIAL_ACCT VALID

TB_BM_QOS_CUST INX_QOS_CUST_OPEN_DATE OPEN_DATE VALID

TB_BM_QOS_CUST INX_CUST_SERV_CODE SERV_CODE VALID

TB_BM_SP_CUST SYS_C0010423 CUST_ID VALID

TB_BM_SP_CUST INX_SP_ACCOUONT SP_ACCOUNT VALID

11 rows selected

查询目前的执行计划：

SQL> explain plan for

2 SELECT *

3 FROM TB_BM_PROD_INST INST

4 WHERE PROD_TYPE_CD = 'SP_NAME'

5 AND (INST.PRODUCT_ID = 47 AND

6 INST.SERV_CODE IN

7 ( SELECT SERV_CODE FROM TB_BM_QOS_CUST WHERE DIAL_ACCT IN (:1 )) OR

8 INST.CUST_ID IN

9 ( SELECT CUST_ID FROM TB_BM_SP_CUST WHERE SP_ACCOUNT IN (:2 )))

10 ;

Explained

SQL> select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1748848417

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

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

| 0 | SELECT STATEMENT | | 32792 | 2754K| 166

|* 1 | FILTER | | | |

|* 2 | TABLE ACCESS FULL | TB_BM_PROD_INST | 381K| 31M| 166

|* 3 | TABLE ACCESS BY INDEX ROWID| TB_BM_SP_CUST | 1 | 14 |

|* 4 | INDEX RANGE SCAN | INX_SP_ACCOUONT | 1 | |

|* 5 | TABLE ACCESS BY INDEX ROWID| TB_BM_QOS_CUST | 1 | 39 |

|* 6 | INDEX RANGE SCAN | INX_CUST_SERV_CODE | 1 | |

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

Predicate Information (identified by operation id):

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

1 - filter( EXISTS (SELECT 0 FROM "TB_BM_SP_CUST" "TB_BM_SP_CUST" WHERE "SP_A

AND "CUST_ID"=:B1) OR "INST"."PRODUCT_ID"=47 AND EXISTS (SELECT 0

"TB_BM_QOS_CUST" WHERE "SERV_CODE"=:B2 AND "DIAL_ACCT"=:1))

PLAN_TABLE_OUTPUT

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

2 - filter("PROD_TYPE_CD"='SP_NAME')

3 - filter("CUST_ID"=:B1)

4 - access("SP_ACCOUNT"=:2)

5 - filter("DIAL_ACCT"=:1)

6 - access("SERV_CODE"=:B1)

25 rows selected

可以发现，OR IN 操作已经转换成AND EXISTS;第2步中，通过"PROD_TYPE_CD"='SP_NAME'过滤后,再和第3和第5步的结果集做全表扫描的FILTER。

优化思路：

既然索引都存在，就尽量使用索引，OR操作可以使用UNION替换,本例用UNION ALL，因为两段逻辑不可能同时成立。

SELECT *

FROM TB_BM_PROD_INST INST

WHERE PROD_TYPE_CD = 'SP_NAME'

AND (INST.PRODUCT_ID = 47 AND

INST.SERV_CODE IN

( SELECT SERV_CODE FROM TB_BM_QOS_CUST WHERE DIAL_ACCT IN (:1 )))

UNION ALL

SELECT *

FROM TB_BM_PROD_INST INST

WHERE INST.CUST_ID IN

( SELECT CUST_ID FROM TB_BM_SP_CUST WHERE SP_ACCOUNT IN (:2 ))

AND (PRODUCT_ID = : 3)

查看执行计划：

SQL> explain plan for

2 SELECT *

3 FROM TB_BM_PROD_INST INST

4 WHERE PROD_TYPE_CD = 'SP_NAME'

5 AND (INST.PRODUCT_ID = 47 AND

6 INST.SERV_CODE IN

7 (SELECT SERV_CODE FROM TB_BM_QOS_CUST WHERE DIAL_ACCT IN (:1)))

8 UNION ALL

9 SELECT *

10 FROM TB_BM_PROD_INST INST

11 WHERE INST.CUST_ID IN

12 (SELECT CUST_ID FROM TB_BM_SP_CUST WHERE SP_ACCOUNT IN (:2))

13 AND (PRODUCT_ID = :3)

14 ;

Explained

SQL> select * from table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2953549902

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

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

| 0 | SELECT STATEMENT | | 2 | 225 |

| 1 | UNION-ALL | | | |

| 2 | NESTED LOOPS | | | |

| 3 | NESTED LOOPS | | 1 | 125 |

| 4 | SORT UNIQUE | | 1 | 39 |

| 5 | TABLE ACCESS BY INDEX ROWID| TB_BM_QOS_CUST | 1 | 39 |

|* 6 | INDEX RANGE SCAN | INDEX_DIAL_ACCT | 1 | |

|* 7 | INDEX RANGE SCAN | INDEX_SERV_CODE | 1 | |

|* 8 | TABLE ACCESS BY INDEX ROWID | TB_BM_PROD_INST | 1 | 86 |

| 9 | NESTED LOOPS | | | |

| 10 | NESTED LOOPS | | 1 | 100 |

| 11 | SORT UNIQUE | | 1 | 14 |

| 12 | TABLE ACCESS BY INDEX ROWID| TB_BM_SP_CUST | 1 | 14 |

|* 13 | INDEX RANGE SCAN | INX_SP_ACCOUONT | 1 | |

|* 14 | INDEX RANGE SCAN | IX_PROD_INST_CUST_ID | 3 | |

|* 15 | TABLE ACCESS BY INDEX ROWID | TB_BM_PROD_INST | 1 | 86 |

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

Predicate Information (identified by operation id):

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

6 - access("DIAL_ACCT"=:1)

7 - access("INST"."SERV_CODE"="SERV_CODE")

8 - filter("INST"."PRODUCT_ID"=47 AND "PROD_TYPE_CD"='SP_NAME')

13 - access("SP_ACCOUNT"=:2)

14 - access("INST"."CUST_ID"="CUST_ID")

15 - filter("PRODUCT_ID"=TO_NUMBER(:3))

32 rows selected

发现所有的索引都用到，而且是NL连接.

比对前后的Bytes，由原来2754K降为225。

再次找SP厂家确认后，响应很快，监控后台的等待事件，没发现异常。