基于Oracle数据库的SQL语句性能优化方法及装置与流程

本发明实施例涉及计算机技术领域，尤其涉及一种基于oracle数据库的sql语句性能优化方法及装置。

背景技术：

oracledatabase，又名oraclerdbms，或简称oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。广泛应用于各行各业，如政府、交通、公安、电信、金融、能源等部门，并已逐渐成为企业信息化建设的重要数据库平台，但随着oracle数据库规模的扩大，数据库用户人数的增加，数据库性能问题越来越突出，因此oracle数据库性能优化成为数据库管理员(databaseadministrator，简称dba)日常工作中最重要的工作之一。

通常来讲数据库80％的性能问题是由于低效的结构化查询语言(structuredquerylanguage，简称sql)引起的，对数据库的优化主要是对低效的sql语句优化，每条sql都需要数据库管理员参与优化，dba因个体差异，导致优化原则不统一，降低了优化sql语句的效率。

技术实现要素：

本发明实施例提供一种基于oracle数据库的sql语句性能优化方法及装置，以统一优化原则，提高优化sql语句的效率。

本发明实施例的一个方面是提供一种基于oracle数据库的sql语句性能优化方法，包括：

采集预设周期内正在执行的sql语句；

生成包括所述sql语句的标识信息和最优执行计划的数据流；

解析所述数据流，获取所述sql语句的标识信息；

将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；

解析所述数据流，获取所述sql语句的标识信息和最优执行计划,获取与所述最优执行计划匹配的绑定程序，生成优化程序；

对所述sql语句进行优化。

本发明实施例的另一个方面是提供一种基于oracle数据库的sql语句性能优化装置，包括：

采集模块，用于采集预设周期内正在执行的sql语句，生成包括所述sql语句的标识信息和最优执行计划的数据流；

分析模块，用于解析所述数据流，获取所述sql语句的标识信息；将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；

所述分析模块还用于解析所述数据流，获取所述sql语句的标识信息和最优执行计划,获取与所述最优执行计划匹配的绑定程序，生成优化程序；

优化模块，用于对所述sql语句进行优化。

本发明实施例提供的基于oracle数据库的sql语句性能优化方法及装置，通过采集预设周期内正在执行的sql语句，生成包括所述sql语句的标识信息和最优执行计划的数据流，将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；以及获取与所述最优执行计划匹配的绑定程序，生成优化程序，并对sql语句进行优化，实现了对低效的sql语句优化，不需要数据库管理员的参与，统一了优化原则，提高了优化sql语句的效率。

附图说明

图1为本发明实施例提供的基于oracle数据库的sql语句性能优化方法流程图；

图2为本发明实施例提供的基于oracle数据库的sql语句性能优化装置的结构图；

图3为现有技术中sql语句性能优化的流程图；

图4为本发明实施例提供的sql语句性能优化的流程图；

图5为本发明实施例提供的基于oracle数据库的sql语句性能优化装置的结构图。

具体实施方式

图1为本发明实施例提供的基于oracle数据库的sql语句性能优化方法流程图。本发明实施例提供了基于oracle数据库的sql语句性能优化方法，该方法步骤如下：

步骤s101、采集预设周期内正在执行的sql语句。

步骤s102、生成包括所述sql语句的标识信息和最优执行计划的数据流。

步骤s103、解析所述数据流，获取所述sql语句的标识信息。

步骤s104、将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序。

步骤s105、解析所述数据流，获取所述sql语句的标识信息和最优执行计划，获取与所述最优执行计划匹配的绑定程序，生成优化程序。

步骤s106、对所述sql语句进行优化。

如图2所示，本发明实施例提供的基于oracle数据库的sql语句性能优化方法主要通过采集模块、分析模块、优化模块来执行。上述步骤s101、步骤s102是由采集模块执行的，步骤s103、步骤s104和步骤s105是由分析模块执行的，步骤s106是由优化模块执行的。下面分别对采集模块、分析模块、优化模块主要执行的步骤进行介绍。

采集模块主要执行步骤如下：

1、通过gv$active_session_history(近几个小时内活动的会话信息)、gv$session(当前连接数据库的会话信息)采集最近5分钟内(采集周期可自行设定)正在执行的sql语句输出sql_id、last_call_et(当前执行时间)。

2、根据1步结果，利用gv$sql(sql语句执行效率情况当前信息)、dba_hist_sqlstat(sql语句执行效率情况历史信息)采集过往历史该sql的平均执行时间、执行次数以及执行计划，输出sql_id、plan_hash_value、avg_et_secs(平均执行时间)。

3、根据1、2两步结果，关联生成sql语句的当前执行时间、过往的平均执行时间、执行次数以及执行计划，输出sql_id、plan_hash_value、last_call_et、avg_et_secs。

4、根据3步结果，采集最长执行时间与最短执行时间的比值大于预设比值，且当前执行时间大于预设时间的sql语句，例如，采集执行计划最长执行时间与最短执行时间大于1.5(比例可自行设定)且当前执行时间大于1s(采集条件可自行设定)的sql语句。输出sql语句对应的sql_id和最优执行计划对应的plan_hash_value。

5、最后将所有sql语句进行格式化生成数据流(比如格式为：sql_id,plan_hash_value#sql_id,plan_hash_value#.....sql_id,plan_hash_value#)作为出参，提供给分析模块作为入参使用。

分析模块主要执行步骤如下：

1、解析基础数据采集模块推送过来的数据流(sql_id,plan_hash_value#sql_id,plan_hash_value#.....sql_id,plan_hash_value#)，提取sql_id。

2、将sql_id作为coe_gen_sql_profile.sql(oracle官方提供的程序)入参，生成sql_id所有执行计划(plan_hash_value)对应的可绑定该执行计划的程序(程序名为coe_xfr_sql_profile_&sql_&plan_hash_value.sql)。

3、根据基础数据采集模块推送过来的数据流作为入参(sql_id,plan_hash_value#sql_id,plan_hash_value#.....sql_id,plan_hash_value#)，提取sql_id和plan_hash_value。

4、将sql_id和plan_hash_value作为入参自动匹配最优执行计划的绑定程序(coe_xfr_sql_profile_&sql_&plan_hash_value.sql)，生成优化程序。

优化模块主要执行步骤如下：

对sql语句进行优化。

本发明实施例通过采集预设周期内正在执行的sql语句，生成包括所述sql语句的标识信息和最优执行计划的数据流，将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；以及获取与所述最优执行计划匹配的绑定程序，生成优化程序；对sql语句进行优化，实现了对低效的sql语句优化，不需要数据库管理员的参与，统一了优化原则，提高了优化sql语句的效率。

图3为现有技术中sql语句性能优化的流程图。如图3所示，由于需要人工干预，运行dbms_xplan.displayawr,以及收集meatadata脚本的时间t1较长，并且t1随着sql复杂度增加而增加，单条sql语句一般需要10分钟。分析并确定优化方案的时间t2也较长，通常单条sql语句保守估计平均所需时长在2小时(120分钟)以上。整个优化过程所需的时间t为t1加t2，与sql语句数量n成正比，即为t＝t1+t2＝130n(分)。

图4为本发明实施例提供的sql语句性能优化的流程图。如图4所示，采集模块主要执行如下步骤：

采集最近一定周期内正在执行的sql语句；

关联生成sql语句的当前执行时间、过往的平均执行时间，以及执行计划；

生成sql语句对应的sql_id和最优执行计划plan_hash_value；

生成sql_id，plan_hash_value#格式的数据流，供分析模块调用。

分析模块主要执行如下步骤：

解析基础数据采集模块推送过来的数据流，提取sql_id；

将sql_id作为入参生成sql_id所有执行计划对应的可绑定该执行计划的程序；

解析基础数据采集模块推送过来的数据流，提取sql_id、plan_hash_value；自动匹配最优执行计划的绑定程序，生成优化程序。

优化模块主要执行如下步骤：

对sql语句进行自动优化。

基础数据采集模块收集时间一般在1分钟内可以完成，分析模块每条sql语句的分析时间可在1分钟内完成，与sql数据量成正比，优化模块优化时间在秒级，可忽略不计。整个优化过程所需要的优化处理时间为n+1,其中，n表示需要优化的sql数。

本发明实施例从基础数据采集、分析到优化完全实现了自动化，实现了系统7*24小时实时自动优化，无需dba为参与，具有实时性、有效性的特点。解决了优化技术门坎高、优化方法和步骤繁琐、优化周期长、优化原则难统一等当前难点。特别是应对系统突发性能问题，更能体现其优势，大大降低系统大大提高了客户体验。

图5为本发明实施例提供的基于oracle数据库的sql语句性能优化装置的结构图。本发明实施例提供的基于oracle数据库的sql语句性能优化装置可以执行基于oracle数据库的sql语句性能优化方法实施例提供的处理流程，如图5所示，基于oracle数据库的sql语句性能优化装置50包括：采集模块51、、分析模块52、优化模块53，其中，采集模块51用于采集预设周期内正在执行的sql语句；生成包括所述sql语句的标识信息和最优执行计划的数据流；分析模块52用于解析所述数据流，获取所述sql语句的标识信息；将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；分析模块52还用于解析所述数据流，获取所述sql语句的标识信息和最优执行计划；获取与所述最优执行计划匹配的绑定程序，生成优化程序；优化模块53用于对所述sql语句进行优化。

本发明实施例提供的基于oracle数据库的sql语句性能优化装置可以具体用于执行上述图1所提供的方法实施例，具体功能此处不再赘述。

本发明实施例通过采集预设周期内正在执行的sql语句，生成包括所述sql语句的标识信息和最优执行计划的数据流，将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；以及获取与所述最优执行计划匹配的绑定程序，生成优化程序，并对sql语句进行优化，实现了对低效的sql语句优化，不需要数据库管理员的参与，统一了优化原则，提高了优化sql语句的效率。

在上述实施例的基础上，采集模块51还用于：生成所述sql语句的当前执行时间、过往的平均执行时间，以及执行计划。

采集模块51具体用于采集最长执行时间与最短执行时间的比值大于预设比值，且当前执行时间大于预设时间的sql语句；生成包括所述sql语句的标识信息和最优执行计划的数据流。

分析模块52具体用于解析所述数据流，获取所述sql语句的标识信息；将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；分析模块52还用于解析所述数据流，获取所述sql语句的标识信息和最优执行计划；获取与所述最优执行计划匹配的绑定程序，生成优化程序。

优化模块53具体用于对sql语句进行优化。

本发明实施例提供的基于oracle数据库的sql语句性能优化装置可以具体用于执行上述图2、4所提供的方法实施例，具体功能此处不再赘述。

本发明实施例从基础数据采集、分析到优化完全实现了自动化，实现了系统7*24小时实时自动优化，无需dba为参与，具有实时性、有效性的特点。解决了优化技术门坎高、优化方法和步骤繁琐、优化周期长、优化原则难统一等当前难点。特别是应对系统突发性能问题，更能体现其优势，大大降低系统大大提高了客户体验。

综上所述，本发明实施例通过采集预设周期内正在执行的sql语句，生成包括所述sql语句的标识信息和最优执行计划的数据流，将所述sql语句的标识信息作为入参，生成所述sql语句的标识信息所有执行计划对应的可绑定所述执行计划的程序；以及获取与所述最优执行计划匹配的绑定程序，生成优化程序；并sql语句进行优化，实现了对低效的sql语句优化，不需要数据库管理员的参与，统一了优化原则，提高了优化sql语句的效率；从基础数据采集、分析到优化完全实现了自动化，实现了系统7*24小时实时自动优化，无需dba为参与，具有实时性、有效性的特点。解决了优化技术门坎高、优化方法和步骤繁琐、优化周期长、优化原则难统一等当前难点。特别是应对系统突发性能问题，更能体现其优势，大大降低系统大大提高了客户体验。

在本发明所提供的几个实施例中，应该理解到，所揭露的装置和方法，可以通过其它的方式实现。例如，以上所描述的装置实施例仅仅是示意性的，例如，所述单元的划分，仅仅为一种逻辑功能划分，实际实现时可以有另外的划分方式，例如多个单元或组件可以结合或者可以集成到另一个系统，或一些特征可以忽略，或不执行。另一点，所显示或讨论的相互之间的耦合或直接耦合或通信连接可以是通过一些接口，装置或单元的间接耦合或通信连接，可以是电性，机械或其它的形式。

所述作为分离部件说明的单元可以是或者也可以不是物理上分开的，作为单元显示的部件可以是或者也可以不是物理单元，即可以位于一个地方，或者也可以分布到多个网络单元上。可以根据实际的需要选择其中的部分或者全部单元来实现本实施例方案的目的。

另外，在本发明各个实施例中的各功能单元可以集成在一个处理单元中，也可以是各个单元单独物理存在，也可以两个或两个以上单元集成在一个单元中。上述集成的单元既可以采用硬件的形式实现，也可以采用硬件加软件功能单元的形式实现。

上述以软件功能单元的形式实现的集成的单元，可以存储在一个计算机可读取存储介质中。上述软件功能单元存储在一个存储介质中，包括若干指令用以使得一台计算机设备(可以是个人计算机，服务器，或者网络设备等)或处理器(processor)执行本发明各个实施例所述方法的部分步骤。而前述的存储介质包括：u盘、移动硬盘、只读存储器(read-onlymemory，rom)、随机存取存储器(randomaccessmemory，ram)、磁碟或者光盘等各种可以存储程序代码的介质。

本领域技术人员可以清楚地了解到，为描述的方便和简洁，仅以上述各功能模块的划分进行举例说明，实际应用中，可以根据需要而将上述功能分配由不同的功能模块完成，即将装置的内部结构划分成不同的功能模块，以完成以上描述的全部或者部分功能。上述描述的装置的具体工作过程，可以参考前述方法实施例中的对应过程，在此不再赘述。

最后应说明的是：以上各实施例仅用以说明本发明的技术方案，而非对其限制；尽管参照前述各实施例对本发明进行了详细的说明，本领域的普通技术人员应当理解：其依然可以对前述各实施例所记载的技术方案进行修改，或者对其中部分或者全部技术特征进行等同替换；而这些修改或者替换，并不使相应技术方案的本质脱离本发明各实施例技术方案的范围。