SQL调优之八:关于SQL调优

总概

  SQL调优是指对未达到预期的SQL语句进行诊断和修复

关于SQL调优

  SQL调优是一个反复的过程,是一步一步的将SQL语句的性能提升到预期的目标。

  它是对一个已经实施完成了的应用的问题的解决,相反的,应用设计则是在实施之前,就应该已经设定好了安全和性能的目标。

SQL调优的目的

  一次典型的调优过程,需要达到以下的其中一个目的:

  • 减少用户响应时间,即从用户发出命令到得到响应这一段时间
  • 提高吞吐,即让一条SQL只使用最少的资源

  对于一个响应时间问题,你可以参考,一个在线售书应用的用户在更新购物车的时候卡住了三分钟。

  相对的,吞吐问题,则想象在一个数据仓库中,一条并行运行三分钟的语句消耗了整个DB服务器的CPU资源,导致其它查询无法运行。

  这两者之间的三分钟是不一样的。

在做SQL调优之前你需要了解的

  • 熟悉数据库的结构,请看Oracle Database Concepts
  • SQL和PL/SQL方面的知识
  • 熟悉数据库提供的调优工具

SQL调优的任务和工具

  SQL调优分为主动还有被动,主动的情况下,你可以使用SQL Tuning Advisor来判断你是否可以让SQL运行得更好,当然,也可以用其他方式主动获取一些可能有性能问题的语句。

被动方式,一般是开发人员主动跟你说某某SQL有问题,或者是数据库受了影响的情况下你去解决问题。

  无论哪种方式,一般来说,SQL调优包含以下的任务:

1,识别高负载的SQL

  查看执行历史来找到哪些SQL导致了数据库的性能以及资源问题

2,收集性能相关的数据

  优化器统计信息对于SQL调优来说尤为重要,如果统计信息不存在或者不准确的话,优化器就可能无法生成最优的执行计划。

  其他会影响SQL执行性能的相关数据包括:表或者视图的结构,以及相关的可用的索引的定义

3,判断导致性能问题的原因

 a)效率低下的SQL语句写法

  如果一条SQL本身写的很差,导致它要做很多不必要的工作的话,那么优化器也没办法提升这条语句的性能。比如说:

  •  没有添加连接条件,导致了笛卡尔连接
  •  使用了hints指定一个大表来作为驱动表
  •  使用了UNION代替UNION ALL
  •  在一个外部查询里,每一行都要执行一次子查询

 b)次优的执行计划

  在某些时候,优化器会选择次优的数据读取方式,比如说当谓词的列的选择性低的时候,执行计划可能选择使用全表扫描来获取数据而不是索引,这个时候,如果过滤的数值占多数的话,全表可能没错,但假如过滤的数值只有一个,那用全表的性能就要差很多。并且还会影响到后续选什么连接方式。

  你可以将不怎么样的执行计划和好的执行计划进行对比,然后再联系最近发生的变动,比如说数据量。以此来判断性能下降的原因。

 c)数据获取结构的缺失

  比如说索引或者物化视图

 d)不新鲜的优化器信息

  当表上的统计信息不是最新的时候,比如说DML操作之后,执行计划就可能基于这些信息做出错误的判断。

 e)硬件问题

  CPU, 内存,I/O问题

4,定义问题的范围

  解决问题的方法的生效范围和问题本身的级别是相匹配的。比如说DB级别的问题跟SQL语句级别的问题。

  举个例子,shared pool太小,游标老化太快,导致大量的硬解析。解决方法就是在数据库级别修改初始化参数来增大share pool,这种解决方案是对所有会话生效的。

  相对的,如果一条SQL没有使用对性能有帮助的索引,结果你在数据库级别应用了某种解决方案,那么它可能会导致其他会话的性能问题。

5,在问题SQL上应用正确的操作

 不同情况的SQL应该应用不同的操作。比如说:

  • 重写SQL语句,使用绑定变量来避免不必要的硬解析,使用等式连接,移除where里面的函数,以及把一条复杂语句拆分成多个小的语句,这些都是重写的范围。
  • 非重写语句,重构用户对象。比如说使用新的数据获取方式,或者重新排序索引里面的列。你也可以对一个表进行分区,指定驱动值,甚至改变数据库的设计。
6,避免SQL性能回退

  为了确保一条SQL语句一直使用性能良好的执行计划,并且会去使用更好的执行计划,你可以使用优化器统计信息(绑定,收集之类的),SQL profiles以及SQL plan baselines。 

SQL调优工具

  SQL调优工具可以分为自动和手动的,自动的可以帮你提供一些诊断信息,以及建议或者正确的操作。手动的则需要自己实施所有的步骤。

  所有的调优工具都是基于数据库的动态性能视图,以及数据库收集的统计信息,度量值。

自动调优工具

  数据库提供了用于SQL调优的几个建议器。另外,SPM(SQL PLAN MANAGEMENT)则是可以用来避免性能回退的一个机制,同时也可以用来提高性能。

  所有的自动调优工具可以使用都可以使用SQL Tuning Sets作为输入,STS是包含了执行统计信息以及执行文本的SQL集合。

1,Automatic Database Diagnostic Monitor (ADDM)

  ADDM是数据库的一个内置的自我诊断工具。它可以自动定位一个性能问题的原因,提供解决问题的建议,以及预期的益处。ADDM也会识别那些不需要采取操作的区域。

  ADDM和其他建议器一样,使用AWR里面的信息来定位可能的性能问题,包括高负载的SQL。

  ADDM的报告可能会显示什么导致了性能问题,并且可能会给出建议的解决方案。比如说它可能会报告一条SELECT语句消耗了大量的CPU,然后建议你跑SQL TUNING ADVISOR去获取优化建议。

2,SQL Tuning Advisor

  STA可以用来识别问题语句,并且建议怎么去提升该类语句的性能。当运行在数据库维护窗口期间的时候,它也就是我们常说的Automatic SQL Tuning Advisor。

  STA获取一条或者多条SQL作为输入,然后使用Automatic Tuning Optimizer来对SQL进行优化。

  建议器会做以下几个步骤:

  • 检查统计信息是否缺失或者过旧
  • 创建SQL Profiles

   SQL Profiles是一条SQL语句的辅助信息。一个SQL Profiles包含了在自动SQL调优期间发现的,能够对优化器进行更正的信息。这些信息可以提升优化器对基数(cardinality)的预估准确度,以此来更准确地生成良好的执行计划。

  • 探索是否一个不同的获取数据方式能够显著提升SQL性能(即全表或者索引)
  • 识别那些自己选择了次优执行计划的SQL

  STA的输出里会给出一些合理的建议以及预期的优化结果。这些建议可能是对一些对象的统计信息进行收集,创建索引,重构SQL语句或者创建SQL Profile。

3,SQL Access Advisor

  SQL Access Advisor是一个内部诊断工具,用来生成一些关于物化视图,索引,以及物化视图日志的建议,包括create, drop 和retain。

  SQL Access Advisor可以使用实际的负载作为输入,也可以从schema那里导入假定的负载。

  SQL Access Adviosr会在空间使用量以及查询性能之间做取舍,对新的或者原本就存在的物化视图以及索引,给出成本性价比最高的配置建议。它也会对分区做出响应的建议。

4,SQL Plan Management

  SPM是一个预防性的机制。它可以让优化器自动管理执行计划,确保数据库只使用已知的或者已经验证过的执行计划。

  这个机制可以创建一个SQL Plan Baseline,baseline包含了一个或者多个已经被接受了的SQL执行计划。通过使用baseline,SPM可以预防执行计划因为环境的变化而导致的性能回退,同时,它也允许优化器发现和使用更好的执行计划。

5,SQL Performance Analyzer

  SQL Performance Analyzer可以判断一个变更对SQL工作负载的影响。系统的变更,比如说数据库升级或者增加新的索引都可能导致执行计划的变化。通过使用SQL Performance Analyzer, 你可以准确地预测到系统变更对SQL性能的影响。使用这些信息你可以在SQL性能退化的时候对数据库进行调优,或者当SQL性能提升的时候识别和管理增益。

个人调优工具

  在某些情况下,你可能需要一些手动的工具来获取你想要的信息,比如说没有自动调优工具的权限。

执行计划

可以通过以下方式获取执行计划:

  • EXPLAIN PLAN

  这个方式可以让你查看优化器预估要采用的执行计划,并没有实际运行该语句

  • AUTOTRACE

  这个方式除了能够提供执行计划的信息外,还能够提供执行的统计信息,比如说磁盘读以及内存读。

  • V$SQL_PLAN和相关的视图

  执行过的SQL的执行计划仍然会存在于share pool里面。

你可以使用DBMS_XPLAN包来获取explain plan以及v4$sql_plan方式获取的执行计划。

实时的SQL监控以及实时的数据库操作

  数据库的实时的SQL监控功能可以让你在SQL运行的时候监控它们的性能。

  默认的,当一条语句并行运行,或者在单次执行里面消耗了最少5秒的CPU或者I/O时间的时候,Real-Time SQL monitoring就会自动启用。

  一个数据库操作指的是用户或者应用自己定义的数据库任务的集合,比如说,一次batch job或ETL过程。你可以定义,监控以及汇报数据库操作。

  实时的数据库操作提供了对复合操作的自动监控。数据库在执行刚开始的时候就自动监控并行查询,DML和DDL语句。

  Oracle Enterprise Manager Cloud Control (Cloud Control)也提供了方便使用的SQL监控页面。

  另一方面,你也可以使用V$SQL_MONITOR and V$SQL_PLAN_MONITOR来监控SQL的相关信息。

  你可以结合以下视图来获取SQL执行的更多信息。
  V$ACTIVE_SESSION_HISTORY
  V$SESSION
  V$SESSION_LONGOPS
  V$SQL
  V$SQL_PLAN

Application Tracing

  一个SQL的跟踪文件可以提供单个SQL的性能信息:解析次数,物理和逻辑读,library cache的未命中数等等。

  可以通过使用DBMS_MONITOR和DBMS_SESSION包来对指定的会话进行跟踪。

  对trace file的分析可以使用以下两个格式化工具:

  TKPROF,可以把跟踪文件变成可读的格式化文件

  trcsess,可以基于session ID, client ID和service ID把多个跟踪文件整合成一个。然后再通过TKPROF对输出的文件进行格式化。

  端对端的应用跟踪简化了对性能问题的诊断。因为正常情况下,单个客户端可能会发起多个会话运行多条SQL,这种情况下对session的跟踪就显得有点力不足了。

  使用client ID来追踪应用运行情况就可以贯穿数据库的所有层面。

优化器Hints

  hint是在SQL语句里面加一些注释来指引优化器做什么操作。在测试或者开发环境,加hint可以用来测试某个获取数据的方式的性能。

  比如说强制使用索引,来测试使用索引的性能: 

  SELECT /*+ INDEX (employees emp_department_ix) */
  employee_id, department_id
  FROM employees
  WHERE department_id > 50;

SQL优化工具的用户界面

  以前的OEM工具显得臃肿,并且不是很好用。

  但是最新的OEM cloud control,流畅度好了很多,并且集成了很多工具,可以试着用用看。

  题外话:虽然说以前的观点是有些DBA只会复制粘贴命令,脱离脚本就不知道该做什么了。但是,现在的数据库产品越来越多,在原理差异性不是很大的情况下,善用工具其实是很好的。

posted @ 2019-09-25 22:31  Ryan_Wo  阅读(3783)  评论(0编辑  收藏  举报