[20151203]一条sql语句的优化.txt
--很久没有看生产系统的awr报表，昨天下午看了一下，我非常讨厌看生产系统的awr报表，看着开发写的sql语句有时候心情会非常不好。
--实际上拿生产系统的sql语句写blog非常困难，别人没有你的环境(即使表述出来，也要浪费很长的篇幅)，写出来的东西大部分仅仅自
--己读得懂。
1.环境：
SYSTEM@192.168.99.105:1521/dbcn> @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
sql_id=18rkuzabjzj2f
SELECT CP_YZMX.XMMC
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1;
sql_id=7dys4vnppxu76
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1;
--执行计划如下：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5a4qbg8wx7zz2, child number 0
-------------------------------------
select /* test 18rkuzabjzj2f */ /*+ gather_plan_statistics */
CP_YZMX.XMMC FROM EMR_DJMX , emr_djfl , CP_YZMX WHERE EMR_DJMX.DJID
=emr_djfl.Djid AND emr_djfl.DJID =CP_YZMX.DJID AND CP_YZMX.xmdm
=EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 2599026261
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8487 (100)| | 4163 |00:00:00.22 | 32194 | | | |
|* 1 | HASH JOIN | | 1 | 3 | 99 | 8487 (1)| 00:01:42 | 4163 |00:00:00.22 | 32194 | 3886K| 3886K| 286K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 9 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 5 | JOIN FILTER USE | :BF0000 | 1 | 98 | 2352 | 8485 (1)| 00:01:42 | 4163 |00:00:00.22 | 32191 | | | |
|* 6 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 2352 | 8485 (1)| 00:01:42 | 4163 |00:00:00.22 | 32191 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F04857F1
3 - SEL$F04857F1 / EMR_DJMX@SEL$1
4 - SEL$F04857F1 / EMR_DJMX@SEL$1
6 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
4 - access("EMR_DJMX"."XMID"=:N1)
6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
42 rows selected.
--仅仅显示查询的字段不同。做一点小小的说明：EMR_DJMX, emr_djfl属于应用的字典表都很小。仅仅CP_YZMX表很大，目前250M。
-- CP_YZMX.XMDM 汉语拼音转义过来就是 项目代码。
-- CP_YZMX.XMMC 表示的就是项目名称。
-- CP_YZMX 表示 CP表示临床路径 YZMX表示 医嘱明细
--很明显第1条语句会出现出一串一样的名称，它输出的全部做这个项目代码的项目名称。第2条也是类似的情况。
--其实更让我吃惊的是EMR_DJMX表里面竟然没有相关的xmmc(项目名称)的字段。
--这样的查询语句随着表CP_YZMX表变大，查询会越来越慢。
2.优化:
--也许有人会讲像第1条语句建立CP_YZMX.XMDM的索引不就ok了吗？当然加上CP_YZMX.XMMC建立的符合索引更佳(实际上仅仅建立
--CP_YZMX.XMDM索引根本没用,因为这个字段选择性很差，我看统计仅仅28xx个不同的值),仔细想想好像是快的,但是随着CP_YZMX记录的
--增加,索引扫描的范围会越来越大。
--如果真正优化我觉得应该写成：
SELECT CP_YZMX.XMMC
FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND CP_YZMX.XMDM = :1 and rownum=1;
--当然还给建立符合索引 CP_YZMX.XMDM +CP_YZMX.XMMC. 或者CP_YZMX.XMDM。
--我明显感觉开发数据结构存在问题，不知道为什么字典表里面没有xmmc(项目名称)字段，也许还有其它字典保存这个信息。
3.接着看第2条语句：
--先看执行计划：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 0 |00:00:00.01 | 2 | | | |
|* 1 | HASH JOIN | | 1 | 2 | 62 | 8488 (1)| 00:01:42 | 0 |00:00:00.01 | 2 | 2345K| 2345K|37888 (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 0 | 1 | 8 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 0 | 98 | 686 | 8485 (1)| 00:01:42 | 0 |00:00:00.01 | 0 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 0 | 98 | 686 | 8485 (1)| 00:01:42 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / EMR_DJMX@SEL$1
6 - SEL$1 / EMR_DJMX@SEL$1
7 - SEL$1 / EMR_DJFL@SEL$1
8 - SEL$1 / EMR_DJFL@SEL$1
10 - SEL$1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 24024
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
6 - access("EMR_DJMX"."XMID"=:N1)
7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
10 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
--我这里查询结果是0，这也是这句话隐藏很深的原因，大部分情况下都是没有结果(逻辑读很小)，因为这个表记录的是临床路径的医嘱明细，许多项
--目不是临床路径的项目(看来dba熟悉业务也很重要)，如果换成前面的525，再看看执行计划。
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 4163 |00:00:00.19 | 32196 | | | |
|* 1 | HASH JOIN | | 1 | 2 | 62 | 8488 (1)| 00:01:42 | 4163 |00:00:00.19 | 32196 | 2981K| 2981K| 265K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 4163 |00:00:00.19 | 32191 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 4163 |00:00:00.19 | 32191 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--查询返回4163条，全部是一样的内容。
--最先想到后面加入rownum=1的条件，执行计划如下：
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5nrjjgv8rw3zf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1 and
rownum=1
Plan hash value: 2932374829
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3204 (100)| | 1 |00:00:00.19 | 32179 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.19 | 32179 | | | |
| 2 | MERGE JOIN | | 1 | 3 | 159 | 3204 (1)| 00:00:39 | 1 |00:00:00.19 | 32179 | | | |
| 3 | MERGE JOIN | | 1 | 41 | 902 | 3201 (1)| 00:00:39 | 1 |00:00:00.19 | 32176 | | | |
| 4 | SORT JOIN | | 1 | 37 | 259 | 3197 (1)| 00:00:39 | 1 |00:00:00.19 | 32170 | 142K| 142K| 126K (0)|
|* 5 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 37 | 259 | 3197 (1)| 00:00:39 | 4163 |00:00:00.19 | 32170 | 1025K| 1025K| |
|* 6 | SORT JOIN | | 1 | 40 | 320 | 4 (25)| 00:00:01 | 1 |00:00:00.01 | 6 | 73728 | 73728 | |
| 7 | TABLE ACCESS STORAGE FULL FIRST ROWS| EMR_DJFL | 1 | 40 | 320 | 3 (0)| 00:00:01 | 40 |00:00:00.01 | 6 | 1025K| 1025K| |
|* 8 | SORT JOIN | | 1 | 1 | 16 | 3 (34)| 00:00:01 | 1 |00:00:00.01 | 3 | 73728 | 73728 | |
| 9 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 10 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / CP_YZMX@SEL$1
7 - SEL$1 / EMR_DJFL@SEL$1
9 - SEL$1 / EMR_DJMX@SEL$1
10 - SEL$1 / EMR_DJMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
6 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
filter("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
8 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
filter(("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJMX"."DJID"="EMR_DJFL"."DJID"))
10 - access("EMR_DJMX"."XMID"=:N1)
--说明：我们的系统使用硬件是exadata，第1次看到Operation =TABLE ACCESS STORAGE FULL FIRST ROWS,逻辑读并不小32170,32170*8/1024=251M.
--改变连接顺序，加入提示：/*+ leading (EMR_DJMX , emr_djfl , CP_YZMX ) */
Plan hash value: 2276510683
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 6912 (100)| | 1 |00:00:00.02 | 3727 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.02 | 3727 | | | |
|* 2 | HASH JOIN | | 1 | 2 | 62 | 6912 (1)| 00:01:23 | 1 |00:00:00.02 | 3727 | 2981K| 2981K| 282K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 8 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 9 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 10 | JOIN FILTER USE | :BF0000 | 1 | 80 | 560 | 6909 (1)| 00:01:23 | 1 |00:00:00.02 | 3722 | | | |
|* 11 | TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX | 1 | 80 | 560 | 6909 (1)| 00:01:23 | 1 |00:00:00.02 | 3722 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--逻辑读减少不少。实际上注意看CP_YZMX逻辑读3722，也就是逻辑读读到3772时，找到满足条件的记录，还可以发现一个奇怪的现象：
--前面的 TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX cost= 3197,而后面的cost=6909,当然主要差异在 E-Rows 不同,前面37，
--而后者80。
--再仔细看显示字段根本没有CP_YZMX表的字段，查询修改如下：
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND EXISTS
(SELECT 1
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :1
AND CP_YZMX.xmdm = EMR_DJMX.XMID
AND emr_djfl.DJID = CP_YZMX.DJID);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8112tdu600w31, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX,
emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid AND EXISTS
(SELECT 1 FROM CP_YZMX WHERE
CP_YZMX.XMDM = :N1 AND CP_YZMX.xmdm =
EMR_DJMX.XMID AND emr_djfl.DJID = CP_YZMX.DJID)
Plan hash value: 130981308
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 8488 (100)| | 1 |00:00:00.03 | 3727 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 1 | 31 | 8488 (1)| 00:01:42 | 1 |00:00:00.03 | 3727 | 2981K| 2981K| 282K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 7 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 8 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
| 9 | JOIN FILTER USE | :BF0000 | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 1 |00:00:00.03 | 3722 | | | |
|* 10 | TABLE ACCESS STORAGE FULL | CP_YZMX | 1 | 98 | 686 | 8485 (1)| 00:01:42 | 1 |00:00:00.03 | 3722 | 1025K| 1025K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
5 - SEL$5DA710D3 / EMR_DJMX@SEL$1
6 - SEL$5DA710D3 / EMR_DJMX@SEL$1
7 - SEL$5DA710D3 / EMR_DJFL@SEL$1
8 - SEL$5DA710D3 / EMR_DJFL@SEL$1
10 - SEL$5DA710D3 / CP_YZMX@SEL$2
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJFL"."DJID"="CP_YZMX"."DJID")
6 - access("EMR_DJMX"."XMID"=:N1)
7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
10 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
50 rows selected.
--实际上如果不用CP_YZMX表，查询可以修改如下：
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX, emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid and
EMR_DJMX.XMID=:N1;
SYSTEM@192.168.99.105:1521/dbcn> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4sjt49mk99kk3, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX,
emr_djfl WHERE EMR_DJMX.DJID = emr_djfl.Djid and EMR_DJMX.XMID=:N1
Plan hash value: 582457811
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 3 (100)| | 1 |00:00:00.01 | 6 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMR_DJMX | 1 | 1 | 16 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | | | |
|* 4 | INDEX RANGE SCAN | I_EMR_DJMX_XMID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 1025K| 1025K| |
|* 5 | INDEX UNIQUE SCAN | PK_EMR_DJFL | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1025K| 1025K| |
| 6 | TABLE ACCESS BY INDEX ROWID | EMR_DJFL | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / EMR_DJMX@SEL$1
4 - SEL$1 / EMR_DJMX@SEL$1
5 - SEL$1 / EMR_DJFL@SEL$1
6 - SEL$1 / EMR_DJFL@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMR_DJMX"."XMID"=:N1)
5 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
--仅仅6个逻辑读。
--当然要把逻辑读进一步降低，还要建立CP_YZMX.XMDM的垃圾索引。当然我的优化建立在修改结果集的情况。
总结:
--1.每次看生产系统的awr报表心情总是不好，一些有问题的语句awr看不到，像第2条，如果仅仅关注逻辑读很难发现第2条语句存在问题。因为大部分查询返回0行。
--2.数据结构存在问题，也许还有1个表保存项目名称。
--3.开发写sql语句到底想一下没有，看着这样开发团队，真令人失望。
--4.在完成后才发现第1个还可以这样写：
SELECT CP_YZMX.XMMC
FROM CP_YZMX
WHERE CP_YZMX.XMDM = :1 and rownum=1
AND EXISTS
(SELECT 1
FROM EMR_DJMX, emr_djfl
WHERE EMR_DJMX.DJID = emr_djfl.Djid
AND emr_djfl.DJID = CP_YZMX.DJID
AND CP_YZMX.xmdm = EMR_DJMX.XMID)；
--甚至后面的exists都不要，真不知道开发要表达的意思，只能说明开发写sql思路混乱。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-1852195/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
