换一个角度,我们从Oracle提供的数据库内部的v$metric_history与dba_hist_sysmetric_history动态视图中可以从“ Average Synchronous Single-Block Read Latency、I/O Megabytes per Second、I/O Requests per Second”等指标中辅助定位:
今年以来,公司多套RAC数据库(版本:11.2.0.4)ASM实例频繁报出ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O错误。这些数据库早在2016年时印象中便出现了此类报错,并且报错并非单个数据库偶发现象,而是大面积爆发,从3月至今仍然有些许数据库会抛出零星错误出现。
什么是ORA-27090?<参考 Doc ID 579108.1 >文中提到,此类报错的原因是操作系统异步IO请求参数 aio-max-nr 过低并且需要调整并发未完成的异步请求数目,避免异步IO请求失败的情况, Oracle官方文档中的建议值如下:
The "aio-max-nr" kernel limit is too low.
io_setup(4096, 0x66e1588) = -1 EAGAIN (Resource temporarily unavailable),
SELECT a.ID,
a.SEG_FLIGHT_ID,
a.SEG_DEP_STATION_CODE,
a.SEG_ARR_STATION_CODE,
a.SEG_CLASS_SEQUENCE,
a.SEG_TRAFFIC_RESTRICTION_CODE,
a.SEG_DEP_TERMINAL,
a.SEG_ARR_TERMINAL,
a.SEG_OC_TYPE,
a.SEG_OC_MAP_NUMBER,
a.SEG_OC_CLASS_MAPPING,
a.SEG_DATA_SOURCE
FROM T_SEGMENT a,
(SELECT id
FROM
(SELECT t.id
FROM T_FLIGHT t,
T_TASK ta
WHERE 1=1
AND ta.id IN ('11008',
'11003',
'11014',
'11012',
'14001',
'11001')
AND t.FLT_AIRLINE_CODE = ta.FLT_AIRLINE_CODE
AND t.FLT_FLIGHT_NO = ta.FLT_FLIGHT_NO
AND t.FLT_FLIGHT_DATE = ta.FLT_FLIGHT_DATE
AND t.FLT_OC_FLIGHT_ID IS NULL
AND t.IS_VALID = 1
AND nullif('AAAA',t.FLT_OFFICE) IS NOT NULL )
LEFT JOIN t_dep_schema s ON id = s.flt_id
WHERE s.flt_id IS NULL
OR (s.flt_id IS NOT NULL
AND (s.FLT_IN_TCARD_PROCESS_FLAG ='N'
OR s.FLT_IN_TCARD_PROCESS_FLAG IS NULL))
OR (s.flt_id IS NOT NULL
AND (s.FLT_IN_INIT_PROCESS_FLAG IS NULL
OR s.FLT_IN_INIT_PROCESS_FLAG ='N')) ) f
WHERE 1=1
AND a.SEG_FLIGHT_ID = f.ID
AND a.IS_VALID = 1