该案例来自西区某客户，该业务应该是停车场出入系统，五一高峰期出现了比较严重的性能问题。通过查询发现出现性能问题的sql是一个分页查询sql。
sql文本为：
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
执行计划和性能统计：
+------------------------------------------------------------------------+
| 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部分：
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，改为
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次。
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的排序是可以去掉的，那么第一个阻塞点就消除了。结合谓词信息：
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)。
仔细分析执行计划，第二个阻塞操作点
-------------------------------------------------------------------------------------------------------------------- | 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文本发现：
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转换。
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改写为：
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连接能使用上，这里的索引都可以不考虑其选择性，其主要目的是保证精确匹配。
**************************************************************************************** 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连接关联索引都有。那么看看优化效果，输入了一个月的时间范围，直接秒出结果。
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的过滤条件过滤性微乎其微)，就是一个性能非常好的分页查询执行计划。
发表回复