[20201210]sql语句优化.txt

来源：这里教程网时间：2026-03-03 16:18:07 作者：

[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部分，仅仅在索引中探查。