出品丨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——
专注于技术不限于技术!
用碎片化的时间,一点一滴地提高数据库技术和个人能力。
欢迎关注!
![3f5b3e23f7b0626612fe5e056b75b7be.png](https://i-blog.csdnimg.cn/blog_migrate/10b6001a41be2f28609acdf8d312e30c.jpeg)
你知道Oracle数据库除了SGA和PGA,还有MGA么?
![c417ba363beadb9c38a19eb2291e6377.png](https://i-blog.csdnimg.cn/blog_migrate/d3421891f23c9d918123a431626dc28d.jpeg)
Index Unique Scans我们要说的 | Oracle官方博客转载
![22a873179f7a3ed2e56ddb856f4233b3.png](https://i-blog.csdnimg.cn/blog_migrate/aa2d245d3c6376106d1f00fbde9aaeb0.jpeg)
手把手教你在Windows 10安装Oracle 19c(详细图文附踩坑指南)
![107da2d6af33ad889720c3e91c6623e0.png](https://i-blog.csdnimg.cn/blog_migrate/5c14b168467871a5d6ae11fd40d6e773.jpeg)
网罗收集10046的各种Case,方便trace信息的收集 | Oracle官方博客转载
![337e8bf61ab4bd1d948790761b2a9db6.png](https://i-blog.csdnimg.cn/blog_migrate/0f3580ff97254c1acb93f9204921810f.jpeg)
读了这些数据库经典书,你已经超过了90%的Oracle技术者(文末彩蛋)