Oracle SQL调优和诊断从哪入手？

Oracle数据库技术 2020-11-06

1273

出品丨TeacherWhat

题图：Hands@Photo by Toa Heftiba on Unsplash

关键字：Oracle、SQL、调优、诊断、手把手数据库入门、Database

正文约2000字，建议阅读时间5分钟

目录结构：

1. 如何定位SQL问题

2. SQL相关的问题类别

3. 诊断SQL性能问题需要的相关信息

4. 基本信息

5. 获取执行计划的主要方法和工具

本公众号文章仅代表个人观点，与任何公司无关。

SQL调优和诊断（一）概述

本系列文章将介绍Oracle SQL调优和诊断的基本方法和相关工具的使用。

本文作为概要，包括如何定位SQL问题、SQL相关的问题类别以及诊断SQL性能问题需要的相关信息。

如何定位SQL问题

我们在解决SQL相关问题时，需要像解决数据库全体性能问题时一样，自底（OS）向上一步一步进行缩小范围（Narrow Down），做到有的放矢。

个人非常赞同Christian Antognini在其Troubleshooting Oracle Performance一书中介绍的定位过程，如下图：

▲摘自Troubleshooting Oracle Performance, 2nd Edition Christian Antognini

一般情况下，定位过程如下：

1.首先排除数据库以外的因素，总体上查看操作系统层面（OS 、H/W、网络等）、应用层有没有问题

2.如果确定是数据库的问题后，开始进一步缩小范围。

3.查看数据库的总体负载，明确已知条件，进一步进行判断：

    数据库系统级别的问题？
    某个会话（Session）级别的问题？
    某个特定SQL的问题？

4.如果是系统级别的问题，要试图根据已知条件和信息，找到系统中消耗资源最多的SQL。

5.如果是会话级别问题,同样也要根据已知条件和信息，找到消耗资源最多的SQL。
如果不能够定位到SQL级别，尝试从应用层或者全体数据库级别进行调优和诊断。


6.最终定位到某个或某些SQL，利用本章介绍的工具和方法，进行SQL级别调优和诊断。


复制

根据上面的方法，确认是某些SQL后，便可以针对SQL相关的问题进行调优和诊断了。

SQL相关的问题类别

一般来讲，SQL相关的问题可以分为以下几类：

1.SQL编译错误或者其他应用上的错误（如标识符无效：ORA-00904、唯一约束错误ORA-00001等）

2.SQL执行时性能问题

3.SQL执行结果错误

4.其他问题（如由于某些SQL执行导致的ORA-600等错误）

复制

对于【1.SQL编译错误或者其他应用上的错误】，通常是由于某些应用上的问题，在SQL解析或者执行过程中发生，可以根据错误内容和指导内容来进行解决。如：唯一约束错误ORA-00001，可以查看是否有唯一约束例的数据重复等。

$ oerr ora 1
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
// For Trusted Oracle configured in DBMS MAC mode, you may see
// this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.

复制

对于一些通过提示信息无法解决的情况下，也可以通过设定ErrorStack等来诊断问题。

具体可参考文章【基础知识】ORACLE数据库错误概述

对于其他SQL相关问题如：性能问题和结果错误等，我们可以通过取得执行计划等相关信息进一步进行分析。

诊断SQL性能问题需要的相关信息

为了诊断SQL性能问题，我们通常需要取得下面的信息：

基本信息

1.SQL文内容
2.SQL的执行计划
3.SQL trace（10046） 和 Optimizer Trace（10053）
4.优化器的详细信息 (如：CBO/RBO ? 参数设置OPTIMIZER_GOAL  OPTIMIZER_MODE等)

复制

其他信息

有时候根据具体情况，我们可能还需要以下的信息


5.SQL文中使用的表、索引、视图等定义信息，以及收集的统计信息、并行情况、压缩情况等
6.与优化器相关的初始化参数
7.处理的行数和处理时间等
8.应用程序的相关信息。（绑定变量、循环执行、过多的Commit等）
9.重现Case（在别的环境中也可以重现）
10.其他的一些变更信息（如升级到最新版本，修改应用等）


复制

获取执行计划的主要方法和工具

在大多数的情况下，诊断SQL性能问题是由于错误地选择了执行计划导致的问题，所以我们首先了解一下获取执行计划的一些方法和工具。

一般我们可以通过以下的方法和工具，获取SQL的执行计划和执行信息。


・EXPLAIN PLAN
・Autotrace (SQL*PLUS)
・动态视图
・AWR SQL Report
・StatsPack SQL Report
・DBMS_XPLAN
・SQLTXPLAIN(SQLT)
・SQL Tuning Health-Check Script (SQLHC)
・10046/10053 Trace
・Real-Time SQL Monitoring


复制

关于相关信息的详细内容，将在以后的文章中进行介绍。

——End——

专注于技术不限于技术！

用碎片化的时间，一点一滴地提高数据库技术和个人能力。

欢迎关注！