智能扫描因素影响Exadata性能

 

案例概要:

某Exadata客户的应用开发人员反映,他们Exadata数据库上的一个存储过程,运行时间非常久,让帮忙优化这个存储过程。

我们知道,存储过程就像一个黑匣子,里面可能有很多SQL语句,要优化存储过程,首先就必须找出这个存储过程中具体哪些SQL语句分别花费了多少时间。优化掉存储过程中这些花费时间比较久的SQL语句,整个存储过程所花费的时候自然就短了。

案例分析及处理:

定位存储过程中每一步花费的时间

分析存储过程中每一个步骤花费了多少时间,则必须使用dbms_profiler工具包,调用该工具包则可以详细地记录被调试的存储过程中每一步所花费的时间。

dbms_profiler工具包具体的安装过程不在本书中详细介绍。

可以使用以下PL/SQL来调用dbms_profiler工具分析需要被优化的存储过程,具体命令如下。

通过这种方式,最终定位出这个存储过程的绝大部分的时间都在执行一个inert into select语句,dbms_profiler工具捕获的具体信息如下所示。

从dbms_profiler工具输出可以看出inert into select语句这条SQL语句执行了1次,花费了1798秒,也即该存储过程中花费时间最长的部分。既然已经定位到具体的SQL语句,后面的事情就简单了。

优化具体SQL语句

下面来看看为什么这个inert into select 语句执行这么慢?单独收集该具体insert into select这个SQL语句的10046事件,生成的10046事件日志内容通过tkprof工具格式化之后的内容具体如下。

可以看出,这个inert into select 语句的主要等待事件为cell multiblock physical read,也即传统数据库中的db file scatterd read,绝大部分的时候花费在后面部分的select语句上。可以看出,单独执行后面部分的select语句时,SQL语句如果进行了智能扫描,花费的时间一定会比之前的全表扫描少很多。

尝试让这个insert into select语句也进行智能扫描,看看效率是否能有所提升。在执行insert into select语句之前,先在会话级别设置强制直接路径读取,具体命令如下。

收集设置了强制直路径读取的10046事件,生成的10046事件日志内容通过tkprof工具格式化之后的内容如下。

可以看出,在会话级别设置了的强制直路径读取后,该insert into select语句的性能大幅提升,从以前的1798秒到现在的21秒。

案例解决方案:

经过上面的测试结果,该存储过程的优化方案也已经出炉,那就是稍稍修改存储过程,在insert into select语句之前加上参数设置,具体命令如下。

在insert into select语句结束之后加上参数设置,具体命令如下。

注意:不要在整个存储过程的最前面加上alter session set “_serial_direct_read”=always;语句,这将会导致整个存储过程中的所有SQL语句都进行智能扫描操作,而在真实场景中,并不是所有的SQL语句都适合智能扫描,有些SQL语句使用智能扫描后,反而可能导致整个数据库系统出现IO瓶颈。

未经允许不得转载:Oracle一体机用户组 » 智能扫描因素影响Exadata性能

相关推荐