CN105069134A

CN105069134A - 一种Oracle统计信息自动收集方法

Info

Publication number: CN105069134A
Application number: CN201510507061.1A
Authority: CN
Inventors: 程永新; 徐华春; 张文宇
Original assignee: SHANGHAI XINJU NETWORK INFORMATION TECHNOLOGY Co Ltd
Current assignee: SHANGHAI XINJU NETWORK INFORMATION TECHNOLOGY Co Ltd
Priority date: 2015-08-18
Filing date: 2015-08-18
Publication date: 2015-11-18
Anticipated expiration: 2035-08-18
Also published as: CN105069134B

Abstract

本发明公开了一种Oracle统计信息自动收集方法，包括如下步骤：a)将数据库表设计为分区表，并按照业务特点划分为不同的统计收集类型；b)操作系统定期调度数据库存储过程，输入数据库表属主、表名或分区名称进行不同粒度的统计信息收集；c)所述数据库存储过程根据输入数据库表属主、表名或分区名称，查找相应的统计收集类型并完成数据库表对象的统计信息收集。本发明将数据库表设计为分区表，新增、修改、删除等操作仅针对当前或某些特定分区，查询及统计根据不同的统计收集类型分别实现，从而能够方便基于属主、表、分区等粒度进行统计信息收集，提高Oracle数据库运行的效率、稳定性以及业务的连续性。

Description

一种Oracle统计信息自动收集方法

技术领域

本发明涉及一种数据库统计信息收集方法，尤其涉及一种Oracle统计信息自动收集方法。

背景技术

统计信息指Oracle数据库优化器(optimizer)统计信息，统计信息描述了Oracle数据库对象的物理特性，比如表中有多少条记录，占用多大的磁盘空间等，就是一个更加详细描述数据库和数据库对象的集合。

当Oracle数据库工作在CBO(CostBasedOptimizer)模式下(Oracle数据库10g以上版本默认并推荐使用该模式)，优化器根据数据字典中记录的对象统计信息，评估SQL语句不同执行计划的成本，从而找到最优的执行计划，使得SQL语句的执行效率最高。

统计信息的准确性对于Oracle数据库执行计划有显著影响，如果统计信息中记录的对象属性(比如表的数据行数量)与实际情况存在较大偏差(一般见于统计信息更新不及时)，会导致Oracle数据库优化器选择错误的执行计划，轻则引起SQL语句执行时间变长，重则可能由于过度消耗系统资源，从而引发数据库整体处理能力下降甚至无法正常处理业务请求。

现有通用的处理方法是：

1、Oracle数据库系统使用默认的统计收集策略收集统计信息

2、数据库管理员手工(或通过shell脚本)收集特定数据库对象的统计信息

3、使用Oracle数据库的统计信息动态采样功能

4、数据比对方法，即扫描数据库所有表的数据行数(rows)，之后与现有的统计信息进行比较，如果差异较大，则进行统计信息收集

上述方法一般适用于数据量较小、业务负载不高、业务类型较单一的情况，但在TB以上级别的大数据量、高业务压力，以及同时进行联机事务处理和数据分析业务类型混合的业务场景下无法保证Oracle数据库统计信息收集的准确性与完整性。现有技术的缺点如下：

1、默认的统计信息收集作业可能影响正常的业务处理。Oracle默认的统计信息收集策略会在工作日每天晚上22点到第二天早上6点，及周末全天，自动收集没有统计信息的和统计信息过旧的对象的统计信息。收集统计信息过程需要消耗大量系统资源，但在此时间窗口内，并非所有业务系统都处于业务闲时，所以在该时间段内执行统计收集作业会造成严重的资源争用，影响正常的业务处理。

2、采用默认收集策略，统计信息收集的完整性不可控。由于默认的时间窗口范围有限，在数据库对象多、数据量大、数据量变化大的情况下会出现规定时间段内无法完成所需对象的统计信息收集操作。在此情况下，数据库对象的统计信息可能长期得不到更新，影响SQL语句的执行效率。数据库管理员可以手工修改并延长作业的时间窗口，但又可能会出现作业的时间窗口与后续业务高峰重合，产生资源争用。

3、采用统计信息动态采样方法，在频繁访问某一对象的时候导致性能下降。所谓动态采样即为：对于没有统计信息的表，Oracle会在执行SQL语句之前对语句涉及到的对象做一次统计信息收集操作。该方法可以得到相对准确的执行计划，但每次执行均需要做动态统计信息收集，在对象访问频繁的情况下，大量的重复采样操作会消耗的过多不必要的系统资源，影响系统运行效率。

4、手工或Shell脚本进行特定对象的统计信息收集。可在一定程度上缓解默认策略带来的问题，但仍有局限性，比如：1、只能收集指定对象统计信息。2、无法自动识别新增对象，无法自动识别并定义收集粒度及限定资源消耗。3、需要根据对象变化经常修改脚本，维护成本大，效率不高。

发明内容

本发明所要解决的技术问题是提供一种Oracle统计信息自动收集方法，能够自动、正确、高效的收集Oracle统计信息，提高Oracle数据库运行的效率、稳定性以及业务的连续性。

本发明为解决上述技术问题而采用的技术方案是提供一种Oracle统计信息自动收集方法，其中，包括如下步骤：a)将数据库表设计为分区表，并按照业务特点划分为不同的统计收集类型；b)操作系统定期调度数据库存储过程，输入数据库表属主、表名或分区名称进行不同粒度的统计信息收集；c)所述数据库存储过程根据输入数据库表属主、表名或分区名称，查找相应的统计收集类型并完成数据库表对象的统计信息收集。

上述的Oracle统计信息自动收集方法，其中，所述步骤a)将数据库表划分为如下的统计收集类型：全表收集：收集所有分区信息；按周期收集：收集周期的长短与预估的数据库表对象的行数成反比。

上述的Oracle统计信息自动收集方法，其中，所述步骤a)对数据库表按如下周期划分统计收集类型：

按当前月分区：需要收集当前月的统计信息；

按上月分区：需要收集上一个月的统计信息；

按当前旬分区：每月三个分区，收集当前日期所在分区的统计信息；

按上旬分区：每月三个分区，收集当前日期前一个旬分区的统计信息；

按当前日分区：需要收集当前日的统计信息；

按前一日分区：需要收集前一日的统计信息。

上述的Oracle统计信息自动收集方法，其中，所述步骤c)还包括根据收集到的数据库表对象的行数统计信息对收集周期进行实时调整；对于统计收集类型为全表收集的数据库表，当收集到的数据库表对象的统计行数超过预设阀值时，更新统计收集类型为按当前月分区。

上述的Oracle统计信息自动收集方法，其中，所述步骤a)预先将所有数据库表的属主、表名、分区名称及其统计收集类型记录存储在数据字典表；所述步骤b)使用操作系统命令crontab，在指定时间点运行主脚本来定期调度数据库存储过程，所述主脚本输入数据库表属主、表名和分区名称进行单表统计信息收集，所述主脚本输入数据库表属主进行多表统计信息收集。

上述的Oracle统计信息自动收集方法，其中，所述主脚本通过扫描数据字典表获取匹配的数据库表的属主、表名和分区名称；对于多表统计信息收集，所述主脚本通过扫描数据字典表获取匹配的属主下所有数据库表。

上述的Oracle统计信息自动收集方法，其中，对于单表统计信息收集，如果所述主脚本通过扫描数据字典表后无法获取匹配的数据库表的属主、表名和分区名称，则判定输入数据库表为新增的数据库表；接着，进一步判断所述新增数据库表是否为用户数据表，若是则对该用户数据表进行全表收集，并更新数据字典表。

本发明对比现有技术有如下的有益效果：本发明提供的Oracle统计信息自动收集方法，将数据库表设计为分区表，新增、修改、删除等操作仅针对当前或某些特定分区，查询及统计根据不同的统计收集类型分别实现，从而能够方便基于属主、表、分区等粒度进行统计信息收集，提高Oracle数据库运行的效率、稳定性以及业务的连续性。

附图说明

图1为本发明Oracle统计信息自动收集流程示意图；

图2为本发明Oracle统计信息自动收集方法的数据库存储过程示意图。

具体实施方式

下面结合附图和实施例对本发明作进一步的描述。

图1为本发明Oracle统计信息自动收集流程示意图。

请参见图1，本发明提供的Oracle统计信息自动收集方法，包括如下步骤：

a)将数据库表设计为分区表，并按照业务特点划分为不同的统计收集类型；

b)操作系统定期调度数据库存储过程，输入数据库表属主、表名或分区名称进行不同粒度的统计信息收集；

c)所述数据库存储过程根据输入数据库表属主、表名或分区名称，查找相应的统计收集类型并完成数据库表对象的统计信息收集。

本发明联合使用操作系统级别计划任务以及调用数据库内部处理方法，依据每套Oracle数据库的表结构及业务特点，将数据库表划分为小表(数据量1G以下)、大表(数据量1G以上)、不按时间分区的分区表、按天分区的分区表、按10天分区的分区表、按月分区的分区表等不同类别，根据业务特点制定相应的统计信息收集策略，并根据模版和框架编写存储过程，通过脚本自动调度运行。本发明通过自动的计划任务、自定义数据库脚本及存储过程等处理方法，可以针对Oracle数据库对象的统计信息进行自动、高效的收集，预防性能问题的发生，减少人工干预，减少系统的运维成本，提高系统的易用性、稳定性和可用性。

本发明的整体架构包括以下四个重要组成部分：

a)策略部分，根据业务变更进行更新，生成并更新策略字典表(以下简称“字典表”)；

b)操作系统级别计划任务(负责定期调度)；

c)主脚本(负责调用存储过程，负责传入数据库库表属主及策略类型)；

d)数据库存储过程(根据输入变量进行相应数据库表等对象的统计信息收集)。

整体流程及相互调用关系如下：

1、数据库表的业务访问策略：

在数据库物理设计阶段，会根据业务访问数据的规则对数据库表进行设计，例如：将数据库表设计为分区表，新增、修改、删除等操作仅针对当前或某些特定分区，查询及统计操作涉及多个分区。

2、策略字典表

初次使用本方案时，需要根据业务的访问策略梳理并生成策略字典表，在字典表中将数据库表按照业务特点划分为不同类型(TYPE)。如果业务对数据的访问方式有变更，也需要对该字典表进行同步更新，例如：

1：全表收集：非分区表、收集所有分区信息

2：按月分区，需要收集当前月的统计信息

3：按月分区，需要收集上一个月的统计信息

4：按旬分区(每月三个分区)，收集当前日期所在分区的统计信息

5：按旬分区(每月三个分区)，收集当前日期前一个旬分区的统计信息

6：按日分区，需要收集当前日的统计信息

7：按日分区，需要收集前一日的统计信息

其他：可进行类似自定义

3、操作系统计划任务

使用操作系统命令crontab，在指定时间点调用主脚本，时间点可以自定义。

4、主脚本

该脚本由操作系统计划任务调度，由三部分组成：

设置环境变量；

扫描数据字典表，获取数据库表的属主、表名、分区名称；

调用数据库存储过程，将上一步骤获取到的变量作为输入参数传递给存储过程。

5、数据库存储过程

1)输入参数及说明如下：

Type：数据库表类型(数值型)；a_owner：属主名；a_tablename：表名；a_partname：分区名。以上参数可以组成：<属主名>+<表名>+<分区名>形式的组合，可以基于属主、表、分区等粒度进行统计信息收集。

2)存储过程执行流程如图2所示：

①存储过程从主脚本处获得传入参数，主脚本命令书写方法为：“脚本名”+“对象的属主名”+“数据库表进行统计收集的类型(TYPE)”，其中后两项为脚本指定的变量。

②存储过程首先在字典表中按照属主名找出该属主的所有表名及数据库表进行统计收集的类型(TYPE)。

③将过程②中的数据库表名与类型(TYPE)进行匹配，按照TYPE的不同值对数据库表按照不同的收集方法进行统计信息收集操作。例如：

TYPE＝1，则收集整张数据库表的统计信息

TYPE＝2，则收集当前月份的分区统计信息

TYPE＝3，则收集前一个月的分区统计信息

TYPE＝4，则收集当前旬的分区统计信息

TYPE＝5，则收集前一个旬的分区统计信息

TYPE＝6，则收集当天的分区统计信息

TYPE＝7，则收集前一天的分区统计信息

此外，本发明可以根据数据的存放规则及业务的访问规则定义新的类型。

④判断是否有字典表中不存在的新增库表

正常情况下所有的数据库表均应该在定期的梳理时补充到字典表中，此类不在字典表中的数据库表属于临时或者人为添加的数据库表。对于此类新增库表，由于无法预判应使用何种方式进行有针对性的统计收集，为避免遗漏，需要针对整个数据库表进行统计信息收集。同时，由于此类的表数据新增，数据量一般不会突然增加非常巨大，所以，全量数据库表统计信息收集操作不会对系统性能造成不可控的影响。

此外，需在步骤⑥中针对新增的数据库表进行记录，提醒数据库管理人员及时更新字典表。

⑤判断新增的数据库表是否为数据库系统表。如果是则忽略该表的统计信息统计信息收集操作，如果为否，则为用户数据表，需要进行统计信息收集操作。

⑥记录步骤③、④、⑤中的操作结果，生成作业日志供数据库管理员检查操作是否成功，是否存在新增数据库表，及时针对发现的问题或隐患进行整改。

⑦所有步骤执行后，整个作业完成。

综上所述，本发明提供的Oracle统计信息自动收集方法，具有如下特点点：1、可自动进行统计信息收集操作，无需人工干预(联合使用数据库存储过程和操作系统Crontab调度)。2、根据字典表及预定规则自动判断需要进行统计收集的数据库表、表分区，实现最小粒度的统计信息收集，减少需要操作的数据库表及分区数量，减少系统开销，极大提高Oracle统计信息收集作业的效率。3、可以根据业务特点针对统计信息收集操作进行灵活定制，比如：可以根据业务规则指定或者排除哪些数据库表等对象。4、可以通过操作系统Crontab调度灵活安排操作的执行时间，根据业务特点及时提供最新的统计信息，为应用程序的正常运行提供可靠保障。比如：针对某种需要在每天特定时间执行的操作，在开始之前对其依赖的数据库表等对象进行统计信息收集，得到最新的统计信息。5、采用模块化思路，将不同业务类型的数据库表作为不同模块进行管理及操作，相互之间独立，避免影响。6、具有较高的可扩展性，可以在现有的存储过程模板基础上进行少量修改即可生成一种针对新业务类型的统计信息收集策略。针对已有的业务类型，可以通过在字典表中添加新的数据库表名称即可完成后续增加的数据库表统计信息收集策略。

本发明提供的Oracle统计信息自动收集方法，具体优点如下：

1.高效性：本发明有针对性的统计信息收集，解决了默认统计信息收集方式下，由于无法划分颗粒度而导致的重复对非必要数据库表、分区进行统计信息收集操作，极大提高了操作效率。

2.自动化：通过策略字典表的方式实现了自动化方式针对不同类型的数据库表采用不同策略进行统计信息收集，避免了数据库管理员手工编写脚本以及不定期修改脚本的复杂操作。

3.可控性：解决了默认情况下，统计收集操作时间长度无法控制的问题。解决了默认情况下，有可能存在的在操作时间窗口内无法完成所有所需数据库表的统计信息收集操作问题。解决了使用传统信息比对方法效率低下的问题。解决了采用统计信息动态采样功能带来的高负载问题。

4.灵活性：本发明采用模块化思路，针对不同类型的数据库表进行分类，可以进行灵活的搭配与组合，可以灵活安排作业执行时间段。

5.及时性、准确性：可以根据业务需求，在指定时间针对指定数据库表、分区进行统计信息收集，避免了系统默认的统一标准无法覆盖所有数据库表的问题。

6.可靠性：所有操作均有完整记录，可以根据记录内容检查操作是否顺利完成。

虽然本发明已以较佳实施例揭示如上，然其并非用以限定本发明，任何本领域技术人员，在不脱离本发明的精神和范围内，当可作些许的修改和完善，因此本发明的保护范围当以权利要求书所界定的为准。

Claims

1.一种Oracle统计信息自动收集方法，其特征在于，包括如下步骤：

2.如权利要求1所述的Oracle统计信息自动收集方法，其特征在于，所述步骤a)将数据库表划分为如下的统计收集类型：

全表收集：收集所有分区信息；

按周期收集：收集周期的长短与预估的数据库表对象的行数成反比。

3.如权利要求2所述的Oracle统计信息自动收集方法，其特征在于，所述步骤a)对数据库表按如下周期划分统计收集类型：

按当前月分区：需要收集当前月的统计信息；

按上月分区：需要收集上一个月的统计信息；

按当前日分区：需要收集当前日的统计信息；

按前一日分区：需要收集前一日的统计信息。

4.如权利要求3所述的Oracle统计信息自动收集方法，其特征在于，所述步骤c)还包括根据收集到的数据库表对象的行数统计信息对收集周期进行实时调整；对于统计收集类型为全表收集的数据库表，当收集到的数据库表对象的统计行数超过预设阀值时，更新统计收集类型为按当前月分区。

5.如权利要求3所述的Oracle统计信息自动收集方法，其特征在于，所述步骤a)预先将所有数据库表的属主、表名、分区名称及其统计收集类型记录存储在数据字典表；所述步骤b)使用操作系统命令crontab，在指定时间点运行主脚本来定期调度数据库存储过程，所述主脚本输入数据库表属主、表名和分区名称进行单表统计信息收集，所述主脚本输入数据库表属主进行多表统计信息收集。

6.如权利要求5所述的Oracle统计信息自动收集方法，其特征在于，所述主脚本通过扫描数据字典表获取匹配的数据库表的属主、表名和分区名称；对于多表统计信息收集，所述主脚本通过扫描数据字典表获取匹配的属主下所有数据库表。

7.如权利要求6所述的Oracle统计信息自动收集方法，其特征在于，对于单表统计信息收集，如果所述主脚本通过扫描数据字典表后无法获取匹配的数据库表的属主、表名和分区名称，则判定输入数据库表为新增的数据库表；接着，进一步判断所述新增数据库表是否为用户数据表，若是则对该用户数据表进行全表收集，并更新数据字典表。