CN107729496B

CN107729496B - 基于甲骨文In-Memory的SQL语句调优方法

Info

Publication number: CN107729496B
Application number: CN201710980293.8A
Authority: CN
Inventors: 杜伟业; 江华
Original assignee: Zhuhai Map Cisco Software Co Ltd
Current assignee: Zhuhai Map Cisco Software Co Ltd
Priority date: 2017-10-19
Filing date: 2017-10-19
Publication date: 2018-08-28
Anticipated expiration: 2037-10-19
Also published as: CN107729496A

Abstract

本发明公开了一种基于甲骨文In‑Memory的SQL语句调优方法，涉及数据库SQL优化技术领域。该方法，通过对SQL语句包含的数据库对象，包括表，物化视图和视图的基表，按照平衡搜索算法产生不同的组合，并对每一个组合，把数据库对象放到In‑Memory或者当所有数据库对象都在In‑Memory的时候将数据库对象从In‑Memory移出，并且添加Oracle优化器提示，从而产生一个新的语义相等的SQL。新生成的替代SQL具有不同的执行计划和性能，通过解释执行计划和测试运行，找到性能最优的替代SQL，最终实现调优。

Description

基于甲骨文In-Memory的SQL语句调优方法

技术领域

本发明涉及数据库SQL优化技术领域，尤其涉及一种基于甲骨文In-Memory的SQL语句调优方法。

背景技术

目前，甲骨文12C推出了In-Memory特性。把SQL语句包含的数据库对象放到In-Memory或者当所有数据库对象都在In-Memory的时候将数据库对象从In-Memory移出(Ordepopulate from In-Memory if all objects are already in In-Memory)，并且对SQL语句添加相关的Oracle优化器提示，将会极大地改变SQL语句的执行计划和性能。In-Memory是甲骨文12C推出的一个新特性，它允许表的数据以列格式存储的同时，保留以前的行存储格式。这种双格式的体系结构使数据有两种存储方式，适合在线事务处理系统的行存储格式和适合优化分析处理系统的列存储格式。

而由于In-Memory大小的限制，用户会非常谨慎地选择哪些数据库对象放到In-Memory会让SQL的性能最好。另外一个问题是，数据库SQL优化器对给定SQL的In-Memory数据库对象很敏感，选择不同的数据库对象到In-Memory，SQL语句的成本估算和执行计划将会改变。将某条SQL语句所有的数据库对象放到In-Memory,不一定就能得到最好的性能。有时候，将某条SQL语句的少数几个数据库对象放到In-Memory,会得到一个好的执行计划；相反，将更多的数据库对象放到In-Memory,会得到一个差的执行计划。

但是，市场上还没有能够提供针对单条或者多条SQL语句的关于In-Memory特性的调优方案。因此，如何为一条或者多条SQL语句选择数据库对象放到甲骨文In-Memory，对应用程序开发者和数据库管理员仍然非常困难。

目前市场上还没有能够提供针对单条SQL语句的关于In-Memory特性的调优方案。

发明内容

本发明的目的在于提供一种基于甲骨文In-Memory的SQL语句调优方法，从而解决现有技术中存在的前述问题。

为了实现上述目的，本发明采用的技术方案如下：

一种基于甲骨文In-Memory的SQL语句调优方法，包括如下步骤：

S1，从一条或者多条原始SQL语句中获取其包含的所有或者部分数据库对象，所述数据库对象包括表、物化视图和/或视图的基表，重复的所述数据库对象只保留一个；

S2，对S1获取到的所述数据库对象，根据平衡搜索算法，按照包含的数据库对象数量递增的顺序产生一系列的数据库对象组合，所述数据库对象数量为：1，2，3，……N，N为获取到的所有的数据库对象的数量；

S3，对于S2产生的每个所述数据库对象组合，在原始SQL语句中为组合中的数据库对象添加相应的数据库优化器提示。

优选地，S2中，所述平衡搜索算法包括如下步骤：

A1，创建队列Q[1]，将T[1]…T[N-1]放入队列Q[1]，输出T[1]…T[N]；

A2，X＝2；

A3，创建队列Q[X]；如果队列Q[X-1]非空，执行如下步骤：

A301，从Q[X-1]中取出一个组合C1；

A302，如果组合C1的数据库对象个数小于X，向组合C1添加一个数据库对象，形成新的组合C2；否则将组合C1中编号最大的数据库对象替换掉，形成新的组合C2；

A303，如果组合C2中不包含数据库对象T[N]，将组合C2放入队列Q[X-1]和Q[X]；

A304，输出组合C2；

A305，如果队列Q[X-1]为非空，跳转到步骤A301；

A306，如果队列Q[X-1]为空，跳转到步骤A4；

A4，将X加1；如果X>N，算法结束；否则，跳转到A3；

其中，T[1..N]代表SQL语句中的候选数据库对象，N为数据库对象的编号，代表SQL语句中第几个表，T[1]简写成T1，T[2]简写成T2，以此类推；

Q[1..X]代表队列，X代表第X个队列并且该队列的元素是X个数据库对象的组合，其中1<＝X<＝N，Q[1]简写成Q1，Q[2]简写成Q2，以此类推。

优选地，S3中，在原始SQL语句中，按照组合中包含的数据库对象数量递增的顺序为所有组合中的数据库对象添加相应的数据库优化器提示。

本发明的有益效果是：本发明实施例提供的基于甲骨文In-Memory的SQL语句调优方法，通过对SQL语句包含的数据库对象，包括表，物化视图和视图的基表，按照平衡搜索算法产生不同的组合，并对每一个组合，把数据库对象放到In-Memory或者当所有数据库对象都在In-Memory的时候将数据库对象从In-Memory移出，并且添加Oracle优化器提示，从而产生一个新的语义相等的SQL。新生成的替代SQL具有不同的执行计划和性能，通过解释执行计划和测试运行，找到性能最优的替代SQL，最终实现调优。

附图说明

图1是本实施使用的平衡搜索方法工作原理示意图；

图2是现有技术中使用的平衡搜索方法工作原理示意图；

图3是平衡搜索算法示例图。

具体实施方式

为了使本发明的目的、技术方案及优点更加清楚明白，以下结合附图，对本发明进行进一步详细说明。应当理解，此处所描述的具体实施方式仅仅用以解释本发明，并不用于限定本发明。

本发明实施例提供了一种基于甲骨文In-Memory的SQL语句调优方法，包括如下步骤：

在本发明的一个优选实施例中，S2中，所述平衡搜索算法包括如下步骤：

A1，创建队列Q[1]，将T[1]…T[N-1]放入队列Q[1]，输出T[1]…T[N]；

A2，X＝2；

A3，创建队列Q[X]；如果队列Q[X-1]非空，执行如下步骤：

A301，从Q[X-1]中取出一个组合C1；

A304，输出组合C2；

A305，如果队列Q[X-1]为非空，跳转到步骤A301；

A306，如果队列Q[X-1]为空，跳转到步骤A4；

A4，将X加1；如果X>N，算法结束；否则，跳转到A3；

在本发明的一个优选实施例中，S3中，在原始SQL语句中，按照组合中包含的数据库对象数量递增的顺序为所有组合中的数据库对象添加相应的数据库优化器提示。

具体实施例

本发明实施例提供的上述方法中，对于步骤S1的实施过程，可通过如下示例进行理解：

示例1从一条SQL语句获取数据库对象

SQL：

SELECT*

FROM T1,T2,T3

获取的数据库对象：T1,T2,T3。

示例2从两条SQL语句获取数据库对象

SQL 1：

SELECT*

FROM T1,T2,T3；

SQL 2:

SELECT*

FROM T1,T4；

获取的数据库对象：T1,T2,T3,T4。

本发明实施例提供的上述方法中，对于步骤S2中的平衡搜索算法及其实现过程，说明如下：

与本发明实施例提供的平衡搜索算法相比，现有技术中，挑选数据库对象到甲骨文In-Memory具有挑选的数据库对象组合在无法遍历时不平衡的问题，可以参见如下的示例。

比如，一个SQL语句如下:

上述SQL语句中可以放到甲骨文In-Memory的数据库对象被高亮显示，表EMPLOYEE,DEPARTMENT和第二个Union查询的EMPLOYEE是可以放到In-Memory的潜在候选对象，如下表中所示。

EMPLOYEE(S1)

DEPARTMENT(S1.1)

EMPLOYEE(S2)

如果从三个表中选择一个表，可能的解决方案如下：

EMPLOYEE(S1)

DEPARTMENT(S1.1)

EMPLOYEE(S2)

如果从三个表中选择两个表，可能的解决方案如下：

EMPLOYEE(S1),DEPARTMENT(S1.1)

EMPLOYEE(S1),EMPLOYEE(S2)

DEPARTMENT(S1.1),EMPLOYEE(S2)

如果从三个表中选择三个表，可能的解决方案如下：

EMPLOYEE(S1),DEPARTMENT(S1.1),EMPLOYEE(S2)

因此，对于这么简单的SQL就有7个组合。如果使用组合公式来计算一下，当一条SQL语句有不同数量的数据库对象时会有多少个组合：

可以采用如下组合公式进行计算：

对于有5个表的SQL语句，总共可能的组合数是：

TC＝C(5,1)+C(5,2)+C(5,3)+C(5,4)+C(5,5)

＝5+10+10+5+1＝31

对于一条有少量表的SQL语句，总共的组合还算小，但是当表的数量增加后，其组合数就会非常的庞大，如下表中举例：

表的数量	5	10	15	20	25	30
							组合数	31	1023	32,767	1,048,575	33,554,431	1,073,741,823

从上表中的数据可以看到，随着表的数量增加，组合数会呈指数级增长。对于人或者工具来说，尝试In-Memory数据库对象的所有组合是非常耗时的。

本发明实施例使用的平衡搜索方法不仅有广度优先的搜索能力，并且在候选对象树中相同层次上实现了候选对象的均衡选择。这个算法提供了比传统平衡算法更好的解决方案，特别是配额被用尽的情况下。其工作原理如图1所示，当达到配额时，本算法仍然能够提供平衡的搜索结果，避免结果都集中在搜索树的某个分支。

而传统的平衡搜索方法中，其工作原理可如图2所示，搜索顺序仍然是固定顺序的。如果达到配额，搜索可能会在虚线箭头方向上的任何一点停止，这意味着搜索结果集中在左分支。如果它不是一棵至少有两个孩子的二叉树，情况会变得更糟。

本发明实施例，S2中，对S1获取的所有或者部分数据库对象，根据平衡搜索算法，按照包含的数据库对象数量递增的顺序产生一系列的数据库对象组合，即先产生一个数据库对象的组合，然后产生两个数据库对象的组合，三个数据库对象的组合，直到所有对象的组合。

平衡搜索算法的实现过程可参见如下示例：

假设T[1..N]代表SQL语句中的候选数据库对象,N为数据库对象的编号，代表SQL语句中第几个表，T[1]可简写成T1，T[2]可简写成T2，以此类推。

假设Q[1..X]代表队列，X代表第X个队列并且该队列的元素是X个对象的组合，其中1<＝X<＝N。Q[1]可简写成Q1，Q[2]可简写成Q2，以此类推。

作为例子，X＝1时队列Q[X]如下表所示：

T4

T3

T2

T1

作为例子，X＝2时队列Q[X]如下表所示：

T4,T5

T3,T4

T2,T3

T1,T2

作为例子，候选对象的组合如下：

C＝(T1,T4,T5)。

平衡搜索算法步骤1:

1)创建队列Q[1]；

2)将T[1]…T[N-1]放入队列Q[1]；

3)输出T[1]…T[N]；

X＝2；

平衡搜索算法步骤2:

1)创建队列Q[X]；

2)如果队列Q[X-1]非空，执行如下步骤：

i.从Q[X-1]取出一个组合C1；

ii.如果组合C1的数据库对象个数小于X，向组合C1添加一个数据库对象,形成新的组合C2；否则将组合C1中编号最大的数据库对象替换掉，形成新的组合C2。

例子1向组合C1添加一个数据库对象

添加前：组合C1＝(T1)

添加后：组合C2＝(T1,T2)

例子2将组合C1中编号最大的数据库对象替换掉

换掉前：组合C1＝(T1,T2)

换掉后：组合C2＝(T1,T3)

iii.如果组合C2中不包含数据库对象T[N],将组合C2放入队列

Q[X-1]和队列Q[X]；

iv.输出组合C2；

v.如果队列Q[X-1]为非空，跳转到步骤2)；如果队列Q[X-1]为空，跳转到步骤3)；

3)如果队列Q[X-1]为空，执行如下步骤

i.将X加1；

ii.如果X>N,算法结束；否则，跳转到平衡搜索算法步骤2。

假设SQL语句有5个表，分别是T1,T2,T3,T4,T5，则按照上述平衡搜索算法得到的结果如图3所示。

本发明实施例中，S3中，在原始SQL语句中，按照组合中包含的数据库对象数量递增的顺序为所有组合中的数据库对象添加相应的数据库优化器提示，即先为一个数据库对象添加数据库优化器提示生成新的替代SQL语句，然后为两个数据库对象添加数据库优化器提示生成新的替代SQL语句，之后为三个数据库对象添加数据库优化器提示生成新的替代SQL语句，以此类推。

该步骤的实现过程可参见如下三个示例：

示例1对于步骤二产生的组合C1＝(T[1])，如果T1表已经在In-Memory。对原始SQL语句添加数据库优化器提示，得到四条替代SQL语句:

原始SQL:

SELECT*

FROM T1,T2,T3

替代SQL:

SELECT/*+FULL(T1)*/*

FROM T1,T2,T3

SELECT/*+PX_JOIN_FILTER(T1)*/*

FROM T1,T2,T3

SELECT/*+NO_PX_JOIN_FILTER(T1)*/*

FROM T1,T2,T3

SELECT/*+INDEX(T1)*/*

FROM T1,T2,T3

示例2对于步骤二产生的组合C1＝(T[1])，T1表不在In-Memory。对原始SQL语句添加数据库优化器提示，得到1条替代SQL语句:

ALTER TABLE T1INMEMORY；

SELECT/*+FULL(T1)*/*

FROM T1,T2,T3；

示例3对于步骤二产生的组合C2＝(T[1]，T[2])，T1,T2表在In-Memory。对原始SQL语句添加数据库优化器提示，得到4条替代SQL语句:

SELECT/*+FULL(T1)FULL(T2)*/*

FROM T1,T2,T3

SELECT/*+PX_JOIN_FILTER(T1)PX_JOIN_FILTER(T2)*/*

FROM T1,T2,T3

SELECT/*+NO_PX_JOIN_FILTER(T1)NO_PX_JOIN_FILTER(T2)*/*

FROM T1,T2,T3

SELECT/*+INDEX(T1)INDEX(T2)*/*

FROM T1,T2,T3。

通过采用本发明公开的上述技术方案，得到了如下有益的效果：本发明实施例提供的基于甲骨文In-Memory的SQL语句调优方法，通过对SQL语句包含的数据库对象，包括表，物化视图和视图的基表，按照平衡搜索算法产生不同的组合，并对每一个组合，把数据库对象放到In-Memory或者当所有数据库对象都在In-Memory的时候将数据库对象从In-Memory移出，并且添加Oracle优化器提示，从而产生一个新的语义相等的SQL。新生成的替代SQL具有不同的执行计划和性能，通过解释执行计划和测试运行，找到性能最优的替代SQL，最终实现调优。

以上所述仅是本发明的优选实施方式，应当指出，对于本技术领域的普通技术人员来说，在不脱离本发明原理的前提下，还可以做出若干改进和润饰，这些改进和润饰也应视本发明的保护范围。

Claims

1.一种基于甲骨文In-Memory的SQL语句调优方法，其特征在于，包括如下步骤：

2.根据权利要求1所述的基于甲骨文In-Memory的SQL语句调优方法，其特征在于，S2中，所述平衡搜索算法包括如下步骤：

A1，创建队列Q[1]，将T[1]…T[N]放入队列Q[1]，输出T[1]…T[N]，然后，从队列Q[1]中删除T[N]，在队列Q[1]中保留T[1]…T[N-1]；

A2，X＝2；

A3，创建队列Q[X]；如果队列Q[X-1]非空，执行如下步骤：

A301，从Q[X-1]中取出一个组合C1；

A304，输出组合C2；

A305，如果队列Q[X-1]为非空，跳转到步骤A301；

A306，如果队列Q[X-1]为空，跳转到步骤A4；

A4，将X加1；如果X>N,算法结束；否则，跳转到A3；

其中，T[1]…T[K-1]，T[K]代表SQL语句中的候选数据库对象，K≤N，K为数据库对象的编号，代表SQL语句中第几个表，T[1]简写成T1，T[2]简写成T2，以此类推；

Q[1]…Q[X-1]，Q[X]代表队列，X代表第X个队列并且该队列的元素是X个数据库对象的组合，其中1<＝X<＝N，Q[1]简写成Q1，Q[2]简写成Q2，以此类推。

3.根据权利要求1所述的基于甲骨文In-Memory的SQL语句调优方法，其特征在于，S3中，在原始SQL语句中，按照组合中包含的数据库对象数量递增的顺序为所有组合中的数据库对象添加相应的数据库优化器提示。