1 背景:
Oracle 数据库在执行sql时,会自动的选择较优的执行计划。但有时oracle自动选择的执行计划不是最佳的执行计划,在执行的时候可能会造成效率问题,甚至会hang住。
2 问题提出:
A省近期20多天来在进行boss同步日志的生成时,经常出现程序长时间的运行没有相应,初步怀疑程序中的sql:
------------------------------------------------------------------------------ --
SELECT a.column01,
a . column02 ,
a . column03 ,
a . column04 ,
b . column05 ,
to_char ( a . column06 , ' yyyymmddhh24miss ' ) ,
a . column07 ,
b . column08 ,
c . column09 ,
a . column10 ,
a . column11 ,
b . column12 ,
b . column13 ,
a . column14 ,
a . column15
FROM big_table01 a , small_table01 b , small_table02 b
WHERE (((( a . myid > : b0 AND a . myid < : b1 ) AND a . servid = b . servid ) AND
a . icpid = c . icpid ) AND a . icpid = b . icpid )
ORDER BY a . myid
中涉及的表有锁表现象。
3 问题分析:
2007年5月18号登录A省业务数据库检查,没有发现sql中的表有锁表现象,而且由于程序仅仅是将查询的结果输出到日志文件,就算是锁表也不会影响到select的过程。
在进一步的检查中发现,数据库中挂死程序对应的等待事件为:
-------------------- --
PX Deq: Execute Reply
该等待事件是oracle在执行并发执行(PARALLEL EXECUTE)时,分成多个子进程进行操作,必须等待所有的子进程完成后才返回执行结果。
查询该sql的执行计划:
-------------------------------------------------------- --
……(略正常部分执行计划)
1 PARALLEL_TO_SERIAL SELECT A1 . C0 C0 , A1 . C1 C1 , A1 . C2 C2 , A1 . C3 C3 , A
1. C4 C4 , A1 . C5 C5 , A1 . C6 C6 , A1 . C7 C7 , A
2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "PK_ICP") */ A1 . C1 C0 , A1 . C11 C1
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
7 PARALLEL_COMBINED_WITH_PARENT
8 PARALLEL_COMBINED_WITH_PARENT
9 PARALLEL_FROM_SERIAL
11 PARALLEL_FROM_SERIAL
13 PARALLEL_COMBINED_WITH_PARENT
14 PARALLEL_COMBINED_WITH_PARENT
从上面的执行计划可以看到,以上的sql确实走了并发执行。由于BIG_TABLE01是分区表,oracle认为同时的对各个分区做并发执行是最优的执行计划,由于A省misc数据库是RAC机制,执行的时候,相关的子进程也被分发到另一个节点上,可能在长时间执行的过程中(执行频率比较高,每分钟执行一次),某些分发到另一节点的子进程找不到对应的上级进程,而上级进程需等待子进程的返回结果,于是就出现了长时间的等待,造成了程序长期的挂住——这也就是我们一开始看到的PX Deq: Execute Reply等待事件。
4 问题解决:
由于oracle采用了不恰当的执行计划,所以才导致执行的时候长时间的等待。因此,在这里我们用alter table big_table01 noparallel;/ alter index IDX_SUBNHISTORY_HISTORYID noparallel命令取消表和索引的并发执行属性。通过观察其执行计划,发现其已经不再走并发执行了:
重启应用后,运行15小时以上没有发现挂死现象了。
5 改进与预防措施:
该问题可能是A省的特殊问题,同样的sql在B省业务数据库中(也是rac)执行,观察其执行计划,看到其sql没有走并发执行。因此作为个案处理