本文共 11294 字，大约阅读时间需要 37 分钟。
[20170104]一条sql优化.txt
--生产系统不明原因重启,看了1下,顺便看了前后的awr报表,发现一条语句,其实问题没什么,只不过这种现象在开发很普遍,做一点点记录.
1.环境:
xxxxxx> @ &r/ver1PORT_STRING VERSION BANNER
------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi$ cat gf3wvnjzfnysy.sql
SELECT DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE (staff_dict.emp_no = staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class = '门诊医生');xxxxxx> alter session set statistics_level=all;
Session altered.xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 07drst9ks3xjm, child number 0 ------------------------------------- SELECT DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE (staff_dict.emp_no = staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class = '门诊医生') Plan hash value: 3073008191 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | HASH UNIQUE | | 1 | 781 | 35145 | 6 (34)| 00:00:01 | 63 |00:00:00.01 | 941 | 795K| 795K| 1187K (0)| | 2 | NESTED LOOPS | | 1 | 781 | 35145 | 5 (20)| 00:00:01 | 915 |00:00:00.01 | 941 | | | | |* 3 | HASH JOIN | | 1 | 781 | 31240 | 5 (20)| 00:00:01 | 929 |00:00:00.01 | 10 | 877K| 877K| 1105K (0)| | 4 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | | |* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | | |* 6 | INDEX UNIQUE SCAN | PK_STAFF_DICT | 929 | 1 | 5 | 0 (0)| | 915 |00:00:00.01 | 931 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 4 - SEL$1 / DEPT_DICT@SEL$1 5 - SEL$1 / STAFF_VS_GROUP@SEL$1 6 - SEL$1 / STAFF_DICT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("STAFF_VS_GROUP"."GROUP_CODE"="DEPT_DICT"."DEPT_CODE") 5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='门诊医生') 6 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")--//逻辑读941,问题主要出在第2步执行的NESTED LOOPS,导致逻辑读上升.优化的问题是减少逻辑读,减少响应时间.
--//加入提示use_hash(STAFF_DICT).$ cat gf3wvnjzfnysy.sql
SELECT /*+ use_hash(STAFF_DICT) */ DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE (staff_dict.emp_no = staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class = '门诊医生');xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID c4mbqwykjp1n0, child number 0 ------------------------------------- SELECT /*+ use_hash(STAFF_DICT) */ DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE (staff_dict.emp_no = staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class = '门诊医生') Plan hash value: 1733666958 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 1 | HASH UNIQUE | | 1 | 781 | 35145 | 9 (23)| 00:00:01 | 63 |00:00:00.01 | 23 | 795K| 795K| 1186K (0)| |* 2 | HASH JOIN | | 1 | 781 | 35145 | 8 (13)| 00:00:01 | 915 |00:00:00.01 | 23 | 833K| 833K| 1139K (0)| |* 3 | HASH JOIN | | 1 | 781 | 31240 | 5 (20)| 00:00:01 | 929 |00:00:00.01 | 10 | 877K| 877K| 1182K (0)| | 4 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | | |* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | | | 6 | INDEX FAST FULL SCAN | PK_STAFF_DICT | 1 | 3530 | 17650 | 3 (0)| 00:00:01 | 3544 |00:00:00.01 | 13 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 4 - SEL$1 / DEPT_DICT@SEL$1 5 - SEL$1 / STAFF_VS_GROUP@SEL$1 6 - SEL$1 / STAFF_DICT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO") 3 - access("STAFF_VS_GROUP"."GROUP_CODE"="DEPT_DICT"."DEPT_CODE") 5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='门诊医生')--//逻辑读下降到23,视乎优化完成.实际上如果你仔细看上面的sql,我个人现在对dinstinct很敏感,查询的字段是dept_dict.dept_name,
--//staff_vs_group.group_code. 而字段staff_vs_group.group_code与这个字段dept_dict.dept_code是一样的,很明显查询仅仅是1个 --//表dept_dict,经典的使用extists例子.参考连接:--//可以想像开发如下写sql语句,把需要的表列出来,写成连接需要的条件,然后写出需要显示的字段,重复加一个distinct,ok解决问题.
--//正确的应该这样写.$ cat gf3wvnjzfnysy.sql4
SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash111(staff_vs_group staff_dict) */1 FROM staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '门诊医生' AND staff_dict.emp_no = staff_vs_group.emp_no );xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4mummndvnvj6m, child number 0 ------------------------------------- SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash111(staff_vs_group staff_dict) */1 FROM staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '门诊医生' AND staff_dict.emp_no = staff_vs_group.emp_no ) Plan hash value: 1139953391 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 2 | 52 | 5 (20)| 00:00:01 | 63 |00:00:00.01 | 956 | 877K| 877K| 1111K (0)| | 2 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | | | 3 | VIEW | VW_SQ_1 | 1 | 781 | 4686 | 2 (0)| 00:00:01 | 929 |00:00:00.01 | 949 | | | | | 4 | NESTED LOOPS | | 1 | 781 | 19525 | 2 (0)| 00:00:01 | 929 |00:00:00.01 | 949 | | | | |* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 4 | | | | |* 6 | INDEX UNIQUE SCAN | PK_STAFF_DICT | 943 | 1 | 5 | 0 (0)| | 929 |00:00:00.01 | 945 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C772B8D1 2 - SEL$C772B8D1 / DEPT_DICT@SEL$1 3 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2 4 - SEL$683B0107 5 - SEL$683B0107 / STAFF_VS_GROUP@SEL$2 6 - SEL$683B0107 / STAFF_DICT@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GROUP_CODE"="DEPT_DICT"."DEPT_CODE") 5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='门诊医生') 6 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")--//逻辑读956,有点高.加入提示/*+ use_hash(staff_vs_group staff_dict) */
$ cat gf3wvnjzfnysy.sql4
SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash(staff_vs_group staff_dict) */1 FROM staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '门诊医生' AND staff_dict.emp_no = staff_vs_group.emp_no );xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 9jqqu3djrdgvg, child number 0 ------------------------------------- SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash(staff_vs_group staff_dict) */1 FROM staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '门诊医生' AND staff_dict.emp_no = staff_vs_group.emp_no ) Plan hash value: 335906791 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 2 | 52 | 8 (13)| 00:00:01 | 63 |00:00:00.01 | 24 | 877K| 877K| 1166K (0)| | 2 | INDEX FAST FULL SCAN | I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | | | 3 | VIEW | VW_SQ_1 | 1 | 781 | 4686 | 6 (17)| 00:00:01 | 929 |00:00:00.01 | 17 | | | | |* 4 | HASH JOIN | | 1 | 781 | 19525 | 6 (17)| 00:00:01 | 929 |00:00:00.01 | 17 | 972K| 972K| 1207K (0)| |* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | | | 6 | INDEX FAST FULL SCAN| PK_STAFF_DICT | 1 | 3530 | 17650 | 3 (0)| 00:00:01 | 3544 |00:00:00.01 | 14 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$C772B8D1 2 - SEL$C772B8D1 / DEPT_DICT@SEL$1 3 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2 4 - SEL$683B0107 5 - SEL$683B0107 / STAFF_VS_GROUP@SEL$2 6 - SEL$683B0107 / STAFF_DICT@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GROUP_CODE"="DEPT_DICT"."DEPT_CODE") 4 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO") 5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='门诊医生')--//逻辑读24.
转载地址：http://ssbcl.baihongyu.com/