[20170104]一条sql优化.txt

简介: [20170104]一条sql优化.txt --生产系统不明原因重启,看了1下,顺便看了前后的awr报表,发现一条语句,其实问题没什么,只不过这种现象在开发很普遍,做一点点记录.

[20170104]一条sql优化.txt

--生产系统不明原因重启,看了1下,顺便看了前后的awr报表,发现一条语句,其实问题没什么,只不过这种现象在开发很普遍,做一点点记录.

1.环境:
xxxxxx> @ &r/ver1

PORT_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例子.参考连接:http://www.itpub.net/thread-2048496-1-1.html

--//可以想像开发如下写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.

目录
相关文章
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
SQL 存储 监控
SQL日志优化策略:提升数据库日志记录效率
通过以上方法结合起来运行调整方案, 可以显著地提升SQL环境下面向各种搜索引擎服务平台所需要满足标准条件下之数据库登记作业流程综合表现; 同时还能确保系统稳健运行并满越用户体验预期目标.
601 6
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
1118 9
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
558 11
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享,主要内容包括以下四个部分: 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
2488 0