一则系统迁移过后的SQL缓慢案例

11月初,某重要航司的一套生产库进行了机房迁移工作,原数据库实例A(Oracle 11.2.0.3版本)利用DG迁移至新机房,数据库实例A(Oracle 11.2.0.3),数据库版本维持原样,没有进行软件升级,但是,一个应用SQL在新环境下运行时非常缓慢,数据库服务器CPU飙高,Idle接近于0,SQL信息如下:

SELECT /*+ parallel(16) */
 nodeValue
 from (SELECT DISTINCT LPAD(BEGTIME, 4, '0') || '-' ||
 LPAD(ENDTIME, 4, '0') nodeValue
 FROM TRAVELSKYDBA
 WHERE CATEGORY = 'OND'
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA1
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA2
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA3
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA4
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA5)
 WHERE UPPER(nodeValue) <> 'ALL'

既然数据库并没有进行版本升级,CBO优化器方面应该没有不同之处,新老环境的执行计划如下:

老环境:

od-1

新环境:

od-2

两套环境中,同一sql的sql_monitor信息如上图所示,在环境对象统计信息一致的情况下,SQL执行计划没有发生改变,但是SQL的执行效率可以说是大相径庭,新环境下的SQL执行时间,CPU,IO统计信息都发生了非常多的变化(变差)。

在SQL执行计划没有变化情况下,SQL IO WAITED变高,我们将视野转至操作系统层面,在SQL执行期间,操作系统中存在换页情况,并且经确认,该数据库并没有成功开启HugePage,这也就解释了为何该SQL会经历诸多的IO等待,造成很大的物理读

OD20191107-1

OD20191107-2

MemLock没有设置正确,/etc/security/limits.conf 中没有配置* soft memlock 与* hard memlock 参数,具体配置需要参照《Database Startup Fails With Error: Memlock Limit too Small: % to Accommodate Segment Size: % (Doc ID 1511239.1)》文章中的说明

OD20191107-3

经过正确的配置参数后,该SQL执行效率便恢复正常,物理读,IO_TIME都有非常明显的改善。

20191110-od-1