【知识拓展】Oracle 数据库扫描规则之关注使用SQL Profile的SQL

在之前的文章中我们曾经讨论过扫描规则之分区表及索引跳扫相关知识，今天我们来一起看一下SQL Profile相关规则的起源、检查方法及在测试过程中发现该类SQL后应如何应对。

为什么Oracle要引入SQL Profile呢？

SQL Profile主要是对优化器生成的错误执行计划的弥补和纠正。

优化器主要依据统计信息来生成执行计划，一般统计信息主要包括表的记录行数，取值的分布如distinct值、最大值最小值等一些直方图类型的整体形状信息，而对于一些细节却没有收录，例如当大部分数据是随机均匀分布的，只有小部分数据只集中在几个数据块中时，这种情况下，对于大多数数据访问来说，优化器可以生成正确的执行计划，然而在访问小部分数据的特定SQL而言，依据现有的统计信息，就无法生成有效的执行计划了，需要重新调整执行计划。此外，当生产遇到SQL执行计划不对时，往往由于时间紧张，无法通过修改SQL文本重新提交版本来解决，此时也需要一种方案实现生产应急，解决问题。

因此，Oralce10g引入了SQL Profile(SQL概要)概念，利用除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息、运行中的采样数据，帮助优化器调整、稳定目标SQL的执行计划, 以及实现在无法修改应用中的SQL文本的情况下令SQL语句按指定的执行计划运行。图1（来源于网络）详细描述了SQL PROFILE影响SQL执行计划的步骤。

图1 SQL PROFILE影响SQL执行计划的步骤

SQLProfile有两种类型：一种是Automatic类型，另一种是Manual类型。

Automatic类型的SQL Profile在Automatic SQL Tunning阶段由SQL tuning advisor创建，本质是针对目标SQL的一些额外的对象信息，与原目标SQL的相关统计信息等内容共同作用得到新的执行计划，Automatic类型的SQL Profile主要是用来调整执行计划，而不能完全固定目标SQL的执行计划，若原来统计信息等内容发生变化后，即便Automatic SQL Profile没有改变，该SQL的执行计划也可能会变化。

例如，由于某些原因，优化器为一条SQL生成了全表扫描的执行计划，而在SQL Tunning阶段，Oracle通过运行全表扫描和索引扫描两种方式动态采样这条SQL数据并分析，比对旧执行计划与新执行计划，发现新执行计划（即索引扫描）耗时更短，就将SQL与新执行计划保存到SQL Profile中，再次执行时，优化器将使用新执行计划，待统计信息更新后，优化器会重新生成执行计划，选取最优计划。

Automatic类型的SQL Profile生成主要有如下几个步骤：

1）创建自动调整任务

2）执行自动调整任务

3)接受创建的SQL Profile

Manual类型的SQL Profile本质上就是一堆Hint的组合，可以在不修改SQL语句和执行环境的条件下，通过DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程或使用脚本coe_xfr_sql_profile.sql，创建SQL Profile，在其中添加合适的hint，起到稳定或改变目标SQL的执行计划的作用。

使用IMPORT_SQL_PROFILE过程创建Manual类型SQL Profile的示例如下：

SQL Profile对于以下类型语句有效：

SELECT语句；

UPDATE语句；

INSERT语句（仅当使用SELECT子句时有效）；

DELETE语句；

CREATE语句（仅当使用SELECT子句时有效）；

MERGE语句（仅当作UPDATE和INSERT操作时有效）。

为什么扫描规则中要加入检查“使用SQL Profile 的SQL”的规则呢？

虽然SQL Profile可以无需修改SQL而迅速解决问题，但这种额外调整执行计划的情况常常无法很好适应数据变化，且使用了SQL Profile的SQL也会因SQL文本发生变化导致执行计划变化从而SQL Profile失效，因此为了避免发生未知的性能变化，从应用程序本身来解决SQL问题是排除隐患的更好方法。另一方面，若由于数据分布等不可优化的原因导致确实需要使用SQL Profile时，也需分析清楚潜在的变化情况和性能影响。因此，在测试过程中扫描出存在SQL Profile 的sql，并作为重点关注项进行分析是非常必要的。

如何获取到SQLProfile信息？

命令行下使用DBMS_XPLAN.DISPLAY分析SQL执行计划，会看到Note中有类似下面这样的提示，就表明该SQL使用了SQL Profile：

定期对数据库进行扫描时可以通过访问DBA_SQL_PROFILES表，获取SQL Profile的名字、类型、SQL文本、签名等详细信息。

如何应对存在SQL Profile的SQL？

当我们在测试过程中，通过扫描数据库发现存在使用SQL Profile的SQL时，首先依据应用需求及数据分布情况等分析这个SQL Profile是否是必须的。若为必须使用，则需做好记录，并提交给生产运维经理，请其关注。若测试时使用了SQL Profile，在投产上线时也需更新到生产数据库中，才能使系统各项指标达到测试时效果。

结束语：关于SQL Profile规则的介绍就到这里，扫描数据库的目的是发现潜在问题，相同规则对不同应用系统会有不同的影响，具体影响程度还需依据应用特点具体分析。后续我们将继续讨论其他常用的数据库扫描规则，欢迎大家关注及给出建议。