Oracle SQL 自动化审核工具的实现

本文由 dbaplus 社群授权转载。

一、背景

我们客户现场的 Oracle 运维团队需要对开发团队提交上来的 Oracle 数据库 SQL 脚本进行评审。众所周知，这个活儿看起来高大上，实际上单靠人工检查的话，耗时费事、效率低下且机械重复，是很难长期实施的。

根据 SRE 以软件工程方法解决运维问题的逻辑，我们当然需要使用自动化的工具来解决这个问题。

二、自动化审查

首先，Oracle 运维团队将 SQL 评审经验总结为上百个评审规则，例如：

• 所有新建对象的 SQL 都需要在对象名的前面加上用户名；
• 创建 SEQUENCE 的 SQL 语句，需要指定 CACHE 值不小于 200；
• delete 和 update 等 DML 语句，必须带 where 条件；
• ……

用这些评审规则去审核一个个 SQL，仍然是非常苦逼的活儿，我们需要一个自动化的工具来实现。为了不重复制造轮子，最好的方法当然是找一个开源的工具进行二次开发，经过团队讨论和反复验证后，最终采用了开源的 SOAR 进行二次开发实现。

SOAR 工具原来是基于 MySQL 数据库进行开发的，可客户现场 SQL 检查是基于 Oracle 的 SQL 脚本。尽管 Oracle 和 MySQL 在语法上有明显的差别，但上述的分析框架和逻辑是可以重用的，我们主要是通过屏蔽 SOAR 自带的 SQL 检查规则，通过添加自定义规则实现。

1、SOAR 组成

SOAR，即 SQL Optimizer And Rewriter，是一款 SQL 智能优化与改写工具，由小米运维 DBA 团队出品。SOAR 主要由语法解析器、集成环境、优化建议、重写逻辑、工具集五大模块组成。

2、与其他工具对比

3、功能特性

• 跨平台支持（支持 Linux、Mac 环境，Windows 环境理论上也支持，不过未全面测试）；
• 支持基于启发式算法的语句优化；
• 支持复杂查询的多列索引优化（UPDATE, INSERT, DELETE, SELECT）；
• 支持 EXPLAIN 信息丰富解读；
• 支持 SQL 指纹、压缩和美化；
• 支持同一张表多条 ALTER 请求合并；
• 支持自定义规则的 SQL 改写。

三、工具框架安装

操作系统版本：CentOS 7.2。

1、安装 Go 环境

这里使用二进制包来安装，下载二进制安装包：

 复制代码

$ wget https://dl.google.com/go/go1.10.2.linux-amd64.tar.gz
$ tar zxvf go1.10.2.linux-amd64.tar.gz

配置环境变量：

 复制代码

#go 的安装目录
exportGOROOT= 解压的 go 的目录
exportGOPATH= 解压的 go 的目录
exportPATH=$PATH:$GOROOT/bin

查看 Go 版本：

2、安装 Git 客户端

使用具有安装权限的用户执行以下命令：

 复制代码

$ yum -yinstallgit

查看 Git 客户端版本：

3、下载 SOAR 源码并编译

新建 workspace 目录：

 复制代码

$mkdir workspace
$cdworkspace

下载 SOAR 源码并编译：

 复制代码

$goget-d github.com/XiaoMi/soar
$cd${GOPATH}/src/github.com/XiaoMi/soar &&make

安装验证：

 复制代码

$cd${GOPATH}/bin
$echo'select * from film'| ./soar

四、规则开发

1、下载 goland IDE

https://www.jetbrains.com/go/

2、打开下载的 soar 源代码工程

 复制代码

$ gitclonehttps://github.com/XiaoMi/soar.git

3、基于 SOAR 的启发式检查规则进行二次开发，主要增加规则代码：

并在配置文件中屏蔽 SOAR 自带默认检查规则：

将 SOAR 可执行文件以及 soar.yaml 放到需要执行的目录 soar_path：

 复制代码

$cd$soar_path
$./soar -config ./soar.yaml -query 待评审和检查的文件绝对路径

五、图形化界面

我们还针对 SOAR 提供的 web 图形化界面的小工具，进行了定制改造。让这款小工具可以进一步开放给开发团队的同事使用。大致步骤如下：

1、安装 Python

 复制代码

$ yuminstallpython36 python36-pip
$ pipinstallFlask
$ pipinstallpymysql
$ pipinstallpycryptodome

若 Crypto 模块找不到, 则需要在 Python 的依赖库目录 Lib\site-packages 中将 crypto 重命名为 Crypto。

2、下载 soar-web 并启动

 复制代码

$ wget https://codeload.github.com/xiyangxixian/soar-web/zip/master-Osoar-web-master.zip
$ unzip soar-web.zip
$ cd soar-web-matster

将上述二次开发的 SOAR 执行文件以及 soar.yaml 文件上传到指定目录下：

并修改 core/common.py 文件：

最终开放给开发团队效果如下图：

六、小结

至此，这个 Oracle SQL 审核的小工具就开发完成了。通过一些简易的配置和开发实现 90% Oracle SQL 的自动化审核，极大简化了现场 DBA 的工作量。当然，还可以实现更多种类数据库的支持，留待更多 DBA 同仁去探索一番。

作者介绍：

梁铭图，新炬网络首席架构师，十多年数据库运维、数据库设计、数据治理以及系统规划建设经验，拥有 Oracle OCM、Togaf 企业架构师（鉴定级）、IBM CATE 等认证，曾获 dbaplus 年度 MVP 以及华为云 MVP 等荣誉，并参与数据资产管理国家标准的编写工作。在数据库运维管理和架构设计、运维体系规划、数据资产管理方面有深入研究。

原文链接：

https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650791880&idx=2&sn=80b2500fb1de80b4e6135cca28598eaa&chksm=f3f96a5dc48ee34b26e01b5e48002dc4b40f0ba84a9a7a918e2f8b179fa95fa5ec0e809ecf8d&scene=27#wechat_redirect