摩登主管2540437[20201210]sql语句优化.txt
--//生产系统sql语句，优化有一些特点，拿出来跟大家分享一下。
--//我一般不大愿意写sql优化的博客，因为许多情况要讲清楚很困难，还有就是安全问题。
1.环境：
xxxxx> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
xxxxx> @ sqlid bw49msgp0a9sn
SQL_ID SQLTEXT
------------- ------------------------------------------------------------------------------------------------------------------
bw49msgp0a9sn SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )
xxxxx> @ dpc bw49msgp0a9sn ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bw49msgp0a9sn, child number 0
-------------------------------------
SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ
WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )
Plan hash value: 1158680521
-------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 10 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 21 | | |
|* 4 | TABLE ACCESS FULL | ZY_CWTJ | 7518 | 154K| 2409 (1)| 00:00:29 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / ZY_CWTJ@SEL$1
2 - SEL$1 / ZY_CWTJ@SEL$1
3 - SEL$2
4 - SEL$2 / ZY_CWTJ@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 82
2 - (DATE): 12/09/2020 00:03:19
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JLXH"=)
4 - filter(("BRKS"=:1 AND "BQPB"=0 AND "CZRQ"<:2))
--//已经讲了N多次，开发为什么不仔细想想，这样要扫描ZY_CWTJ2次。另外说明一下复合索引BRKS,BQPB是存在的，实际上选择性很差，
--//基本不会使用这个的索引，我一上来就删除了。
--//另外我真心不愿意提交任何修改报告，我们这里业务流程不复杂，但是时间N久才完成。首先填写工单，而且提交后要等N久，而且我
--//还必须跟踪催促相关人员，我真心不知道我在修好修改语句的情况下，为什么需要很长时间完成修改任务。
--//如果不催促，这样的修改往往是遥遥无期。
xxxxx> @ bind_cap bw49msgp0a9sn ''
C200
------------------------------------------------------------------------------------------------------------------
SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ---- -------- ---------- ------------------- --------------- ------------------- ------------------------------
bw49msgp0a9sn 0 YES :1 1 22 2020-12-10 00:05:00 NUMBER 82
YES :2 2 7 2020-12-10 00:05:00 DATE 2020/12/10 00:03:18
--//注意查询的绑定变量，实际上这个程序是一个job调用的，在凌晨统计信息的一部分。
2.首先我先写出我改写语句：
SELECT XSYS
FROM ( SELECT XSYS, JLXH
FROM ZY_CWTJ
WHERE BRKS = :1 AND BQPB = 0 AND CZRQ < :2
ORDER BY JLXH DESC)
WHERE ROWNUM = 1;
--//这样仅仅扫描1次。看看执行计划，为了了解sql执行情况加入提示gather_plan_statistics。另外说明一下JLXH是主键,顺序递增，
--//上面的语句仅仅会返回1条,不会出现歧义性。执行计划如下：
SQL_ID 4kkk01y9tzmuc, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ XSYS FROM ( SELECT XSYS, JLXH
FROM ZY_CWTJ WHERE BRKS = :1 AND BQPB = 0 AND
CZRQ < :2 ORDER BY JLXH DESC) WHERE ROWNUM = 1
Plan hash value: 1497600757
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 26 (100)| | 1 |00:00:00.01 | 6 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 6 |
| 2 | VIEW | | 1 | 2 | 26 | 26 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 20 | 480 | 26 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
| 4 | INDEX FULL SCAN DESCENDING| PK_ZY_CWTJ | 1 | 3120 | | 8 (0)| 00:00:01 | 400 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / from$_subquery$_001@SEL$1
3 - SEL$2 / ZY_CWTJ@SEL$2
4 - SEL$2 / ZY_CWTJ@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OPT_PARAM('_bloom_filter_enabled' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~==>不知道安装者要修改这2个隐含参数。
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_DESC(@"SEL$2" "ZY_CWTJ"@"SEL$2" ("ZY_CWTJ"."JLXH"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(("BRKS"=:1 AND "CZRQ"<:2 AND "BQPB"=0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "XSYS"[NUMBER,22]
2 - "XSYS"[NUMBER,22]
3 - "XSYS"[NUMBER,22]
4 - "ZY_CWTJ".ROWID[ROWID,10]
--//你可以发现逻辑读仅仅6个，而且我并没有建立新的索引，直接利用主键JLXH的索引。
--//因为查询条件非常特殊，总是查询的日期CZRQ是执行语句的当前时间，也就是表中的最大值。查看绑定变量就清楚了。jLXH是主键，顺序增加。
--//缺点就是如果查询根本不存在的BRKS值，这样就逻辑读很大，要扫描整个索引。我发现这样的情况还真心存在。
--//我估计分院与总院的科室表都是一样的，这样两边的统计也需要扫描整个科室列表。
--//比如BRKS 带入 10000，执行计划的逻辑读上升，执行计划如下：
Plan hash value: 1497600757
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 26 (100)| | 0 |00:00:00.01 | 241 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.01 | 241 |
| 2 | VIEW | | 1 | 2 | 26 | 26 (0)| 00:00:01 | 0 |00:00:00.01 | 241 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 20 | 480 | 26 (0)| 00:00:01 | 0 |00:00:00.01 | 241 |
| 4 | INDEX FULL SCAN DESCENDING| PK_ZY_CWTJ | 1 | 3120 | | 8 (0)| 00:00:01 | 31262 |00:00:00.01 | 64 |
--------------------------------------------------------------------------------------------------------------------------------------
--//基本扫描整个索引，再探查整个表，效率比全表扫描要差许多。看全表扫描的执行计划：
--//这样情况出现逻辑读依旧很大，可以建立这样的索引，充分利用取jlxh最大的特性。
create unique index I_ZY_CWTJ_BRKS_BQPB_JLXH on zy_cwtj (brks, bqpb, jlxh) logging compress 2;
Plan hash value: 2516239191
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 5 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 5 |
| 2 | VIEW | | 1 | 2 | 26 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
|* 3 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 20 | 480 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_ZY_CWTJ_BRKS_BQPB_JLXH | 1 | 40 | | 2 (0)| 00:00:01 | 21 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//这样即可以保持索引很小，查询逻辑读很低。如果查询CZRQ 条件是 '2019/12/8 16:11:15',这样逻辑读会很高。
Plan hash value: 2516239191
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 0 |00:00:00.01 | 171 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 0 |00:00:00.01 | 171 |
| 2 | VIEW | | 1 | 2 | 26 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 171 |
|* 3 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 20 | 480 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 171 |
|* 4 | INDEX RANGE SCAN DESCENDING| I_ZY_CWTJ_BRKS_BQPB_JLXH | 1 | 40 | | 2 (0)| 00:00:01 | 1196 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//但是这样的查询在实际系统中基本不存在的。
3.回到生产系统看看原来的语句的这样的索引效果如何：
create unique index I_ZY_CWTJ_BRKS_BQPB_JLXH on zy_cwtj (brks, bqpb, jlxh) logging compress 2;
--//BRKS 带入 10000，执行计划如下：
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 1 | 9 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 3 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 20 | 420 | 13 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | I_ZY_CWTJ_BRKS_BQPB_JLXH | 1 | 401 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//如果带入存在的BRKS值，逻辑读反而上升。
Plan hash value: 353781330
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 174 |
| 1 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 174 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 173 |
| 3 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 171 |
|* 4 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 20 | 420 | 13 (0)| 00:00:01 | 1199 |00:00:00.01 | 171 |
|* 5 | INDEX RANGE SCAN | I_ZY_CWTJ_BRKS_BQPB_JLXH | 1 | 401 | | 2 (0)| 00:00:01 | 1199 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//禁用上面的索引，设置该索引隐含。继续测试：
create index i_zy_cwtj_laji on zy_cwtj (brks, bqpb, czrq, jlxh) compress 2;
SELECT /*+ gather_plan_statistics */ XSYS FROM ( SELECT XSYS,
JLXH FROM ZY_CWTJ WHERE BRKS = :1 AND BQPB = 0
AND CZRQ < :2 ORDER BY JLXH DESC) WHERE ROWNUM = 1
Plan hash value: 4136658378
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 1 |00:00:00.01 | 171 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 171 | | | |
| 2 | VIEW | | 1 | 20 | 260 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 171 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 20 | 480 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 171 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| ZY_CWTJ | 1 | 20 | 480 | 3 (0)| 00:00:01 | 1176 |00:00:00.01 | 171 | | | |
|* 5 | INDEX RANGE SCAN | I_ZY_CWTJ_LAJI | 1 | 4 | | 2 (0)| 00:00:01 | 1176 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以发现这样的索引并不佳，逻辑读很高。而利用它取jlxh最大值，以及查询CZRQ值都是最大值的特点。重新建立索引如下：
create index i_zy_cwtj_laji on zy_cwtj (brks, bqpb, jlxh,czrq ) compress 2;
--//索引czrq，jlxh交换对比前面的索引。
--//查询存在的BRKS情况。
SQL_ID 8rvdqyj2pj5kp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ XSYS FROM ZY_CWTJ WHERE JLXH =
(SELECT MAX (JLXH) FROM ZY_CWTJ WHERE
BRKS = :1 AND BQPB = 0 AND CZRQ < :2)
Plan hash value: 1977445073
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 5 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | 1 |
| 3 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 2 | 1 |
| 4 | FIRST ROW | | 1 | 1 | 21 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI | 1 | 1 | 21 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1 |
----------------------------------------------------------------------------------------------------------------------------------------------------
--//这样效果最好,因为查询条件 CZRQ < :2,不是等值查询。注意执行计划出现INDEX RANGE SCAN (MIN/MAX).
--//如果查询BRKS在表中不存在，带入10000.
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 1 | 9 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 3 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 2 |
| 4 | FIRST ROW | | 1 | 1 | 21 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI | 1 | 1 | 21 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------------------------------------
--//两者差异不大。
--//这样效果最好,因为查询条件 CZRQ < :2,不是等值查询。注意执行计划出现INDEX RANGE SCAN (MIN/MAX).而不是前面的INDEX RANGE SCAN。
--//即使我日期带入2018/12/10 0:03:18,逻辑读也不会上升很大。
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 0 |00:00:00.01 | 6 |
| 1 | TABLE ACCESS BY INDEX ROWID | ZY_CWTJ | 1 | 1 | 9 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
|* 2 | INDEX UNIQUE SCAN | PK_ZY_CWTJ | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 3 | SORT AGGREGATE | | 1 | 1 | 21 | | | 1 |00:00:00.01 | 6 |
| 4 | FIRST ROW | | 1 | 1 | 21 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI | 1 | 1 | 21 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JLXH"=)
5 - access("BRKS"=:1 AND "BQPB"=0)
filter("CZRQ"<:2)
--//注意Predicate Information部分，仅仅在索引中探查。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69990219/viewspace-2742779/ ，如需转载，请注明出处，否则将追究法律责任。
