我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
案例：SQL优化案例：分页查询
该案例来自西区某客户，该业务应该是停车场出入系统，五一高峰期出现了比较严重的性能问题。通过查询发现出现性能问题的SQL是一个分页查询。
SQL文本为：
SELECT*
FROM(SELECTpage.*, ROWNUM AS rnFROM(SELECTA .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 updatebyFROMp_park_in ALEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_IDLEFT JOIN p_place P ON A .PLACE_ID = P .place_idLEFT JOIN p_place_door d_in ON (A .PLACE_ID = d_in.PD_PLACE_IDAND A .PARKIN_ENTRY_NO = d_in.pd_noAND d_in.PD_TYPE = 0)LEFT JOIN p_place_door d_out ON (A .PLACE_ID = d_out.PD_PLACE_IDAND b.PARKOUT_EXIT_NO = d_out.pd_noAND d_out.PD_TYPE = 1)WHERE1 = 1AND A .PARKIN_TIME >= '1682672708000'AND A .PARKIN_TIME <= '1682759108000'AND P .PLACE_STATUS = '1'ORDER BYPARKIN_TIME DESC,PARKOUT_TIME DESC,createtime DESC) pageWHEREROWNUM <= 10)
WHERErn >= 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 ANDSYS_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 BYPARKIN_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 BYPARKIN_TIME DESC,createtime DESC) pageWHEREROWNUM <= 10000)ORDER BYPARKIN_TIME DESC,PARKOUT_TIME DESC,createtime DESC)WHEREROWNUM <= 10)
WHERErn >= 1
最终开发认为PARKOUT_TIME的排序是可以去掉的，那么第一个阻塞点就消除了。结合谓词信息：
WHERE1 = 1AND 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_IDAND b.PARKOUT_EXIT_NO = d_out.pd_noAND 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_name2 from test.t a,test.t2 b,TEST.T1 c3 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(SELECTpage.*, ROWNUM AS rnFROM(SELECTA .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 updatebyFROMp_park_in ALEFT JOIN p_park_out b ON A .PARKIN_INFO_ID = b.PARKOUT_IN_IDLEFT JOIN p_place P ON A .PLACE_ID = P .place_idLEFT JOIN p_place_door d_in ON (A .PLACE_ID = d_in.PD_PLACE_IDAND A .PARKIN_ENTRY_NO = d_in.pd_noAND d_in.PD_TYPE = 0)LEFT JOIN p_place_door d_out ON (b.PARKOUT_PLACEID = d_out.PD_PLACE_IDAND b.PARKOUT_EXIT_NO = d_out.pd_noAND d_out.PD_TYPE = 1)WHERE1 = 1AND A .PARKIN_TIME >= '1682672708000'AND A .PARKIN_TIME <= '1682759108000'AND P .PLACE_STATUS = '1'ORDER BYPARKIN_TIME DESC,createtime DESC) pageWHEREROWNUM <= 10)
WHERErn >= 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 ASCNNNNNVY PLACE_ID 2 ASCNNNNNVY PARKIN_ENTRY_NO 3 ASCIDX_PARKIN_TIME NNNNNVY PARKIN_CAR_NO 1 ASCNNNNNVY SYS_NC00028$ 2 DESCNNNNNVY SYS_NC00029$ 3 DESCIDX_PPI_PARKIN_TIME NNNNNVY SYS_NC00027$ 1 DESCPK_P_PARK_IN UNNNNVY PARKIN_INFO_ID 1 ASCP_PARK_OUT IDX_P_PARK_OUT NNNNNVY PARKOUT_IN_ID 1 ASCNNNNNVY PARKOUT_EXIT_NO 2 ASCNNNNNVY CREATETIME 3 ASCIDX_P_PARK_OUT_IN_ID NNNNNVY PARKOUT_IN_ID 1 ASCPK_P_PARK_OUT UNNNNVY PARKOUT_INFO_ID 1 ASCP_PLACE IDX_P_PLACE NNNNNVY PLACE_STATUS 1 ASCIDX_P_PLACE_ID NNNNNVY PLACE_ID 1 ASCNNNNNVY PLACE_STATUS 2 ASCSYS_C006913 UNNNNVY PLACE_ID 1 ASCP_PLACE_DOOR IDX_PD_TYPE_ID NNNNNVY PD_TYPE 1 ASCNNNNNVY PD_PLACE_ID 2 ASCNNNNNVY PD_NO 3 ASCPK_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') ANDSYS_OP_DESCEND("PARKIN_TIME")<=HEXTORAW('38FDBAE4B6B8AEFF') )filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PARKIN_TIME"))>=1682672708000 ANDSYS_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"(+) ANDTO_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 calls0 db block gets105 consistent gets0 physical reads0 redo size4268 bytes sent via SQL*Net to client520 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)10 rows processed
优化效果非常明显，无论输入多少时间范围都是秒出结果。当一个分页查询的执行计划都是只有COUNT STOPKEY+NL连接+并且谓词都是access(没有filter或者filter的过滤条件过滤性微乎其微)，就是一个性能非常好的分页查询执行计划。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
