首席DBA用SQL洪荒之力，造一把通向数据库的钥匙(5)

发布时间：2021-01-08 08:45 所属栏目：53 来源：网络整理

导读：知道了问题所在,该如何处理呢?修改结构无疑成本太高,不具备可操作性.这里所采取的策略是“局部有序”.利用修改语句中条件的范围,由开放区间变为封闭区间,影响基数的选择.(关于这部分,大家有兴趣可多看看《基于成本

知道了问题所在,该如何处理呢?修改结构无疑成本太高,不具备可操作性.这里所采取的策略是“局部有序”.利用修改语句中条件的范围,由开放区间变为封闭区间,影响基数的选择.(关于这部分,大家有兴趣可多看看《基于成本的Oracle优化》一书)

如仍然不起作用,可考虑进一步细化分段或干脆采用“逐条提取+批绑定”的方式解决.

一个小小的数据类型设置不当,会为我们后面的工作带来的多大的麻烦.

这里会描述一次完整的优化过程,看看DBA是如何“抽丝剥茧”,发现问题本质的.

这个案例本身不是为了说明某种技术,而是展现了DBA在分析处理问题时的一种处理方式.其采用的方法往往是根据自己掌握的知识,分析判断某种可能性,然后再验证确认是否是这个原因.在不断的抛出疑问,不断的验证纠错中,逐步接近问题的本质.

也想通过这个示例,告知广大开发人员,DBA优化语句的不容易.

这是某数据仓库系统,有一个作业在某天出现较大延迟.为了不影响明天的业务系统,必须在今天解决这个问题.经和开发人员的沟通,该业务的SQL语句没有修改,相关的数据结构也没有变更相类似的其他业务(SQL语句相似的)也都正常运行,数据库系统本身也没有异常.

修改后执行计划,跟其他类似SQL相同了.整个计划可概述为”HASH JOIN” + “FULL TABLE SCAN”.经测试,速度略有提升,但是整个运行时间仍然超过2个小时.

开始了第一次尝试,开始想到的方法很简单,既然类似的SQL执行效率没问题,而这个SQL由于其他SQL执行计划偏差较大,我可以手工采取固化执行计划的方法.这里使用了抽取OUTLINE的方式.经测试,对速度提升不大,不知问题主因.

第二次尝试,从等待事件角度入手.首先考虑的是和缓存有关的问题.

不要盲目相信别人的话,优化之前先按照故有流程检验一遍,做到心中有数.对于此例来说,可以大大加快问题的解决.

Q&A

Q1：ANSI 的SQL标准,会一直推出新版本吗? 后续版本是否会加入新的语法和特性呢?

A1：这个问题没有仔细考虑过,ANSI-SQL的标准一直在变化,不同的数据库根据自身情况实现了它的子集.从我个人角度来看,未来ANSI-SQL可能会对大数据、数据挖掘方向有所考虑,加入部分新语法或特性.毕竟SQL接口作为人们最为熟悉的数据访问接口,未来在大数据等方向大有可为.

Q2：优化SQL最终的目的是不是改变SQL执行计划?

A2：第一目的,是理解现有优化器选择的行为,并考虑是否是最佳选择.第二目的,是在优化器功能有所局限的情况下,通过人工介入的方式,让数据库以更优的方式执行SQL.毕竟人要比电脑更理解数据.

Q3：能不能介绍一下开发中,数据类型的选择对数据库的影响?

A3：数据类型在优化层面,主要可从以下角度考虑：

Q4：能不能介绍下oracle数据迁移的常用方式和利弊?

A4：这个有很多,取决于迁移的需求,比如常用的：

1.备份、恢复；2.逻辑导入、导出(含传输表空间等)；3.DATAGUARD；4.LOG SYNC(例如OGG等)；5.程序同步……利弊,主要取决于成本、代价了,每种方案都有自身的适用场景.

Q5：请问必须全表扫描的语句有什么优化思路?

A5：必须用全表扫描的情况,就适用于分享中的“DoDo”原则第二条,尽量让其更快的完成.可考虑的策略有：

Q6：对于group by语句如何优化?

A6：对于分组来说,Oracle 11g以后的版本提供了HASH GROUP BY的实现.HASH是个重内存消耗操作,可从内存使用角度基于优化考虑.

Q7：访问路径是会缓存起来的,怎么判断回收没用的缓存中的访问路径呢?

A7：一般不需要考虑回收问题,如果非要做可从内存信息中了解此执行计划是否最近被使用,使用DBMS包清除即可.

Q8：oracle发现在云机上安装之后,在并发性方面不行,这是为什么?

A8：不同云的实现策略不同.并发性方面,可考虑从vCPU使用、IO等方面着手.这方面经验不多,抱歉!

Q9：全表扫描想办法修改为索引全表扫描是否合适?使用with子句来优化sql,这个手段如何?

A9：将全表扫描修改为索引全扫描,根本原则是能够缩小访问量,即让数据库干更少的活.

WITH子句,定义查询块,一个目的是减少多次引用,但也有可能出现不允许执行查询语句变形的情况,要具体分情况分析.

文章出处：DBAplus社群

（编辑：ASP站长网）