该案例来自西区某客户，该业务应该是停车场出入系统，五一高峰期出现了比较严重的性能问题。通过查询发现出现性能问题的sql是一个分页查询sql。
sql文本为：
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SELECT * FROM ( SELECT page.*, ROWNUM AS rn FROM ( SELECT A .PARKIN_INFO_ID AS parkininfoid, A .CAR_ID AS carid, A .PLACE_ID AS placeid, P .PLACE_NAME AS placename, A .PARKIN_CAR_NO AS parkincarno, A .PARKIN_CARD_NO AS parkincardno, A .PARKIN_CAR_TYPE AS parkincartype, A .PARKIN_ENTRY_NO AS parkinentryno, A .PARKIN_TIME AS parkintime, A .PARKIN_PIC AS parkinpic, b.PARKOUT_EXIT_NO AS parkoutexitno, b.PARKOUT_TIME AS parkouttime, b.PARKOUT_PIC AS parkoutpic, A .PARKIN_ENTRY_NO AS poindoorno, b.PARKOUT_EXIT_NO AS pooutdoorno, A .createtime AS createtime, A .createby AS createby, A .updatetime AS updatetime, A .updateby AS updateby FROM p_park_in A LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID LEFT JOIN p_place P ON A .PLACE_ID = P .place_id LEFT JOIN p_place_door d_in ON ( A .PLACE_ID = d_in.PD_PLACE_ID AND A .PARKIN_ENTRY_NO = d_in.pd_no AND d_in.PD_TYPE = 0 ) LEFT JOIN p_place_door d_out ON ( A .PLACE_ID = d_out.PD_PLACE_ID AND b.PARKOUT_EXIT_NO = d_out.pd_no AND d_out.PD_TYPE = 1 ) WHERE 1 = 1 AND A .PARKIN_TIME >= '1682672708000' AND A .PARKIN_TIME <= '1682759108000' AND P .PLACE_STATUS = '1' ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC ) page WHERE ROWNUM <= 10 ) WHERE rn >= 1 |
执行计划和性能统计：
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
+------------------------------------------------------------------------+ | infromation from v$sqlstats | +------------------------------------------------------------------------+ CPU(MS) ELA(MS) DISK GET ROWS ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) PLSQL JAVA EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCH PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC PER EXEC SQL_PROFILE ---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- -------- -------- --------------- 9 3,073 5,405 0 937,358 10 5 0 0 2,573 0 0 0 +------------------------------------------------------------------------+ | information from v$sql | +------------------------------------------------------------------------+ PLAN CHI USER CPU(MS) ELA(MS) DISK GET ROWS ROWS APPLI(MS) CONCUR(MS) CLUSTER(MS) USER_IO(MS) FIRST_LOAD_TIME EXEC HASH VALUE NUM NAME PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE EXEC PRE FETCH PER EXEC PER EXEC PER EXEC PER EXEC LAST_LOAD_TIME ---------- ------------- ---- ---------- -------- -------- -------- ------------ ----------- --------- --------- ---------- ----------- ----------- ---------------------- 9 981367741 0 LZPARKING 3,073 5,405 0 937,358 10 5 0 0 2,573 0 04-30/19:4.04-30/19:4 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 210K(100)| | |* 1 | VIEW | | 10 | 6340 | | 210K (1)| 00:42:04 | |* 2 | COUNT STOPKEY | | | | | | | | 3 | VIEW | | 25578 | 15M| | 210K (1)| 00:42:04 | |* 4 | SORT ORDER BY STOPKEY | | 25578 | 15M| 16M| 210K (1)| 00:42:04 | |* 5 | HASH JOIN RIGHT OUTER | | 25578 | 15M| | 206K (1)| 00:41:24 | |* 6 | INDEX FAST FULL SCAN | IDX_PD_TYPE_ID | 810 | 30780 | | 10 (0)| 00:00:01 | | 7 | VIEW | | 25578 | 14M| | 206K (1)| 00:41:24 | | 8 | NESTED LOOPS OUTER | | 25578 | 9042K| | 206K (1)| 00:41:24 | |* 9 | HASH JOIN RIGHT OUTER | | 25268 | 7032K| | 111K (1)| 00:22:15 | |* 10 | INDEX FAST FULL SCAN | IDX_PD_TYPE_ID | 816 | 31008 | | 10 (0)| 00:00:01 | |* 11 | HASH JOIN | | 25268 | 6094K| | 111K (1)| 00:22:15 | | 12 | TABLE ACCESS BY INDEX ROWID| P_PLACE | 1683 | 99297 | | 786 (0)| 00:00:10 | |* 13 | INDEX RANGE SCAN | IDX_P_PLACE | 1683 | | | 9 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID| P_PARK_IN | 34646 | 6360K| | 110K (1)| 00:22:06 | |* 15 | INDEX RANGE SCAN | IDX_PPI_PARKIN_TIME | 173K| | | 434 (1)| 00:00:06 | | 16 | TABLE ACCESS BY INDEX ROWID | P_PARK_OUT | 1 | 77 | | 4 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | IDX_P_PARK_OUT | 1 | | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=10) 4 - filter(ROWNUM<=10) 5 - access("B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO" AND "A"."PLACE_ID"="D_OUT"."PD_PLACE_ID") 6 - filter(TO_NUMBER("D_OUT"."PD_TYPE")=1) 9 - access("A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO" AND "A"."PLACE_ID"="D_IN"."PD_PLACE_ID") 10 - filter(TO_NUMBER("D_IN"."PD_TYPE")=0) 11 - access("A"."PLACE_ID"="P"."PLACE_ID") 13 - access("P"."PLACE_STATUS"=1) 15 - access("A"."SYS_NC00027$">=HEXTORAW('38FDBAE2C7EDFF') AND "A"."SYS_NC00027$"<=HEXTORAW('38FDBAE3BAB1FF') ) filter((SYS_OP_UNDESCEND("A"."SYS_NC00027$")>=1682768770000 AND SYS_OP_UNDESCEND("A"."SYS_NC00027$")<=1682855170000)) 17 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID") |
据业务人员反应，该sql是一个没有绑定变量的sql，PARKIN_TIME是页面的输入条件，当选取的时间范围越大，该sql性能消耗越大，查询时间越长。每一天的数据量大概在35w，当时间范围超过1天时，基本要等很久页面才会返回数据。
关于分页查询的优化，还是有套路的，而且个人认为所有数据库的分页查询优化思路都是一样的。一个完美的分页查询sql的执行计划，应该是一套非阻塞操作，其手段就是利用排序字段列的表对应的索引(免排序)作为驱动+NL连接(合适的索引实现精确匹配)+STOPKEY。基本无论传入什么输入条件，结果都应该是秒出的。很明显本案例的执行计划不是一个优秀的分页执行计划。
首先提一下执行计划的非阻塞操作。非阻塞操作其实就是当查询一个块的数据，不用等待其操作完成就可以按执行计划继续往下走或者返回客户端。比如全表扫描、索引范围扫描、NL连接都是非阻塞操作。常见的阻塞操作比如排序、HASH连接、VIEW关键字等等。对于分页查询的优化思路其实就是避免阻塞操作，并且尽量保证每一条记录的驱动都能最终返回(少做无用功)。
回到本案例，分析分页查询的第一个阻塞操作点order by部分：
|
1 2 3 4 |
ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC |
PARKIN_TIME和createtime来自表p_park_in，PARKOUT_TIME来自p_park_out，当排序字段来自多个表时，是没法通过索引去消除order by的阻塞操作的。发现第一个问题之后，立刻与开发沟通。
给出的建议有两种方法：
- 是否能去掉PARKOUT_TIME的order by，改为
|
1 |
ORDER BY PARKIN_TIME DESC, createtime DESC |
- 如果不能去掉，可以改写sql分页部分，order by ORDER BY PARKIN_TIME DESC, createtime DESC之后，rownum<=10改成rownum<=10000，再根据这10000条记录做ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC，相当于排序了两次，应该没有人去翻页1000次。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ORDER BY PARKIN_TIME DESC, createtime DESC ) page WHERE ROWNUM <= 10000 ) ORDER BY PARKIN_TIME DESC, PARKOUT_TIME DESC, createtime DESC) WHERE ROWNUM <= 10 ) WHERE rn >= 1 |
最终开发认为PARKOUT_TIME的排序是可以去掉的，那么第一个阻塞点就消除了。结合谓词信息：
|
1 2 3 4 5 |
WHERE 1 = 1 AND A .PARKIN_TIME >= '1682672708000' AND A .PARKIN_TIME <= '1682759108000' AND P .PLACE_STATUS = '1' |
第一个优化建议：
创建索引PARKIN_TIME、CREATE_TIME组合索引。由该索引开始驱动，同时避免了排序阻塞操作(SORT ORDER BY STOPKEY)。
仔细分析执行计划，第二个阻塞操作点
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 210K(100)| | |* 1 | VIEW | | 10 | 6340 | | 210K (1)| 00:42:04 | |* 2 | COUNT STOPKEY | | | | | | | | 3 | VIEW | | 25578 | 15M| | 210K (1)| 00:42:04 | |* 4 | SORT ORDER BY STOPKEY | | 25578 | 15M| 16M| 210K (1)| 00:42:04 | |* 5 | HASH JOIN RIGHT OUTER | | 25578 | 15M| | 206K (1)| 00:41:24 | |* 6 | INDEX FAST FULL SCAN | IDX_PD_TYPE_ID | 810 | 30780 | | 10 (0)| 00:00:01 | | 7 | VIEW | | 25578 | 14M| | 206K (1)| 00:41:24 | | 8 | NESTED LOOPS OUTER | | 25578 | 9042K| | 206K (1)| 00:41:24 | |* 9 | HASH JOIN RIGHT OUTER | | 25268 | 7032K| | 111K (1)| 00:22:15 | |* 10 | INDEX FAST FULL SCAN | IDX_PD_TYPE_ID | 816 | 31008 | | 10 (0)| 00:00:01 | |* 11 | HASH JOIN | | 25268 | 6094K| | 111K (1)| 00:22:15 | | 12 | TABLE ACCESS BY INDEX ROWID| P_PLACE | 1683 | 99297 | | 786 (0)| 00:00:10 | |* 13 | INDEX RANGE SCAN | IDX_P_PLACE | 1683 | | | 9 (0)| 00:00:01 | | 14 | TABLE ACCESS BY INDEX ROWID| P_PARK_IN | 34646 | 6360K| | 110K (1)| 00:22:06 | |* 15 | INDEX RANGE SCAN | IDX_PPI_PARKIN_TIME | 173K| | | 434 (1)| 00:00:06 | | 16 | TABLE ACCESS BY INDEX ROWID | P_PARK_OUT | 1 | 77 | | 4 (0)| 00:00:01 | |* 17 | INDEX RANGE SCAN | IDX_P_PARK_OUT | 1 | | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------------- |
ID=7的view关键字，说明VIEW下的连接需要关联查询完成当作一个整体才能与ID=6做HASH外连接。
为何会出现VIEW呢？为什么oracle不能做view merge的查询转换呢？view merge相关概念参考之前的一篇文章。
仔细分析sql文本发现：
|
1 2 3 4 5 |
LEFT JOIN p_place_door d_out ON ( A .PLACE_ID = d_out.PD_PLACE_ID AND b.PARKOUT_EXIT_NO = d_out.pd_no AND d_out.PD_TYPE = 1 ) |
与表p_place_door关联时，关联字段有表P_PARK_IN的PLACE_ID，还有表P_PARK_OUT的PARKOUT_EXIT_NO。如果oracle做了view merge，那么会导致d表同时与a和b都外连接，这在oracle中是不允许的，会报出ORA-01417错误。所以CBO不会对这样的view做merge转换。
|
1 2 3 4 5 6 7 |
SQL> select a.object_id,b.object_name,c.data_object_id,A.object_name 2 from test.t a,test.t2 b,TEST.T1 c 3 where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+); where a.object_id=b.object_id and a.data_object_id=c.data_object_id(+) and b.object_type=c.object_type(+) * ERROR at line 3: ORA-01417: a table may be outer joined to at most one other table |
那么要消除VIEW必须要CBO去把这个view merge掉，解决办法只有d表要么都与P_PARK_IN的字段关联，要么都与P_PARK_OUT的字段关联。
跟开发沟通之后P_PARK_OUT表的PARKOUT_PLACEID可以替代P_PARK_IN的PLACE_ID字段。
最终sql改写为：
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
SELECT * FROM ( SELECT page.*, ROWNUM AS rn FROM ( SELECT A .PARKIN_INFO_ID AS parkininfoid, A .CAR_ID AS carid, A .PLACE_ID AS placeid, P .PLACE_NAME AS placename, A .PARKIN_CAR_NO AS parkincarno, A .PARKIN_CARD_NO AS parkincardno, A .PARKIN_CAR_TYPE AS parkincartype, A .PARKIN_ENTRY_NO AS parkinentryno, A .PARKIN_TIME AS parkintime, A .PARKIN_PIC AS parkinpic, b.PARKOUT_EXIT_NO AS parkoutexitno, b.PARKOUT_TIME AS parkouttime, b.PARKOUT_PIC AS parkoutpic, A .PARKIN_ENTRY_NO AS poindoorno, b.PARKOUT_EXIT_NO AS pooutdoorno, A .createtime AS createtime, A .createby AS createby, A .updatetime AS updatetime, A .updateby AS updateby FROM p_park_in A LEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_ID LEFT JOIN p_place P ON A .PLACE_ID = P .place_id LEFT JOIN p_place_door d_in ON ( A .PLACE_ID = d_in.PD_PLACE_ID AND A .PARKIN_ENTRY_NO = d_in.pd_no AND d_in.PD_TYPE = 0 ) LEFT JOIN p_place_door d_out ON ( b.PARKOUT_PLACEID = d_out.PD_PLACE_ID AND b.PARKOUT_EXIT_NO = d_out.pd_no AND d_out.PD_TYPE = 1 ) WHERE 1 = 1 AND A .PARKIN_TIME >= '1682672708000' AND A .PARKIN_TIME <= '1682759108000' AND P .PLACE_STATUS = '1' ORDER BY PARKIN_TIME DESC, createtime DESC ) page WHERE ROWNUM <= 10 ) WHERE rn >= 1 |
消除了阻塞操作点之后，需要查看后续关联的对象上是否有对应的索引，保证NL连接能使用上，这里的索引都可以不考虑其选择性，其主要目的是保证精确匹配。
|
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 29 |
**************************************************************************************** INDEX INFO ****ucptdvs "UNIQUENESS COMPRESSION PARTITIONED TEMPORARY VISIBILITY SEGMENT_CREATED"** **************************************************************************************** TABLE TABLE Index COLUMN Col OWNER NAME Name UCPTDVS NAME Pos DESC --------------- ----------------------------------- ------------------------------ ------- ------------------------- ---- ---- LZPARKING P_PARK_IN IDX_INFOID NNNNNVY PARKIN_INFO_ID 1 ASC NNNNNVY PLACE_ID 2 ASC NNNNNVY PARKIN_ENTRY_NO 3 ASC IDX_PARKIN_TIME NNNNNVY PARKIN_CAR_NO 1 ASC NNNNNVY SYS_NC00028$ 2 DESC NNNNNVY SYS_NC00029$ 3 DESC IDX_PPI_PARKIN_TIME NNNNNVY SYS_NC00027$ 1 DESC PK_P_PARK_IN UNNNNVY PARKIN_INFO_ID 1 ASC P_PARK_OUT IDX_P_PARK_OUT NNNNNVY PARKOUT_IN_ID 1 ASC NNNNNVY PARKOUT_EXIT_NO 2 ASC NNNNNVY CREATETIME 3 ASC IDX_P_PARK_OUT_IN_ID NNNNNVY PARKOUT_IN_ID 1 ASC PK_P_PARK_OUT UNNNNVY PARKOUT_INFO_ID 1 ASC P_PLACE IDX_P_PLACE NNNNNVY PLACE_STATUS 1 ASC IDX_P_PLACE_ID NNNNNVY PLACE_ID 1 ASC NNNNNVY PLACE_STATUS 2 ASC SYS_C006913 UNNNNVY PLACE_ID 1 ASC P_PLACE_DOOR IDX_PD_TYPE_ID NNNNNVY PD_TYPE 1 ASC NNNNNVY PD_PLACE_ID 2 ASC NNNNNVY PD_NO 3 ASC PK_P_PLACE_DOOR UNNNNVY PD_ID 1 ASC |
后续NL连接关联索引都有。那么看看优化效果，输入了一个月的时间范围，直接秒出结果。
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
10 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3360519549 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 1268 | 17 (0)| 00:00:01 | |* 1 | VIEW | | 2 | 1268 | 17 (0)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 2 | 1242 | 17 (0)| 00:00:01 | | 4 | NESTED LOOPS OUTER | | 2 | 806 | 17 (0)| 00:00:01 | | 5 | NESTED LOOPS OUTER | | 2 | 730 | 16 (0)| 00:00:01 | | 6 | NESTED LOOPS OUTER | | 2 | 542 | 8 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 2 | 466 | 7 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID| P_PARK_IN | 3 | 522 | 4 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | IDX_PARK_IN_CREATE_TIME | 1 | | 3 (0)| 00:00:01 | |* 10 | TABLE ACCESS BY INDEX ROWID| P_PLACE | 1 | 59 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | PK_P_PLACE | 1 | | 0 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | IDX_P_PLACE_DOOR_1 | 1 | 38 | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | P_PARK_OUT | 1 | 94 | 4 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | IDX_P_PARK_OUT_IN_ID | 1 | | 3 (0)| 00:00:01 | |* 15 | INDEX RANGE SCAN | IDX_P_PLACE_DOOR_1 | 1 | 38 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=1) 2 - filter(ROWNUM<=10) 9 - access(SYS_OP_DESCEND("PARKIN_TIME")>=HEXTORAW('38FDBAE3C3F4AEFF') AND SYS_OP_DESCEND("PARKIN_TIME")<=HEXTORAW('38FDBAE4B6B8AEFF') ) filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))>=1682672708000 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))<=1682759108000) 10 - filter("P"."PLACE_STATUS"=1) 11 - access("A"."PLACE_ID"="P"."PLACE_ID") 12 - access("A"."PLACE_ID"="D_IN"."PD_PLACE_ID"(+) AND "A"."PARKIN_ENTRY_NO"="D_IN"."PD_NO"(+) AND TO_NUMBER("PD_TYPE"(+))=0) 14 - access("A"."PARKIN_INFO_ID"="B"."PARKOUT_IN_ID"(+)) 15 - access("B"."PARKOUT_PLACEID"="D_OUT"."PD_PLACE_ID"(+) AND "B"."PARKOUT_EXIT_NO"="D_OUT"."PD_NO"(+) AND TO_NUMBER("PD_TYPE"(+))=1) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 105 consistent gets 0 physical reads 0 redo size 4268 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed |
优化效果非常明显，无论输入多少时间范围都是秒出结果。当一个分页查询的执行计划都是只有COUNT STOPKEY+NL连接+并且谓词都是access(没有filter或者filter的过滤条件过滤性微乎其微)，就是一个性能非常好的分页查询执行计划。