关于QB_NAME的hint

近期,在优化一个SQL时用到了QB_NAME的HINT,此HINT我经常使用的原因目的是让子查询单独作为一个整体,与其他表关联时可以进行调整驱动顺序,举例:

SELECT b.owner,
       b.object_name,
       b.created,
       b.status
FROM albert.fc b
WHERE 1=1
  AND b.object_id IN
    (SELECT c.object_id
     FROM albert.P c
     WHERE c.owner='NFS_SN')
执行计划如下:
Plan hash value: 3977992979

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows | Cost (%CPU)| E-Time	 | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |   569 (100)| 	 |    119 |00:00:00.05 |    1445 |
|*  1 |  HASH JOIN RIGHT SEMI	     |	       |      1 |  64830 |   569  (11)| 00:00:07 |    119 |00:00:00.05 |    1445 |
|   2 |   TABLE ACCESS BY INDEX ROWID| P       |      1 |   2192 |   116   (0)| 00:00:02 |    119 |00:00:00.01 |      17 |
|*  3 |    INDEX RANGE SCAN	     | P_IDX01 |      1 |   2192 |     6   (0)| 00:00:01 |    119 |00:00:00.01 |       2 |
|   4 |   TABLE ACCESS FULL	     | FC      |      1 |   3000K|   439  (11)| 00:00:06 |    100K|00:00:00.02 |    1428 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / C@SEL$2
   3 - SEL$5DA710D3 / C@SEL$2
   4 - SEL$5DA710D3 / B@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "B"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5DA710D3" "C"@"SEL$2" ("P"."OWNER"))
      LEADING(@"SEL$5DA710D3" "B"@"SEL$1" "C"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "C"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "C"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
   3 - access("C"."OWNER"='NFS_SN')
继续阅读

诡异的ORA-27090: Unable to reserve kernel resources for asynchronous disk I/O

今年以来,公司多套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),

但是很诡异的是,我们的数据库早在2016年左右(第一次触发此问题)时,已经将该参数调整至4194304,理论上是不应该触发异步IO不足的问题。并且此报错风险性较高,ASM实例异步IO失败会导致数据库面临crash问风险,例如DBWR将脏块落盘(数据文件)时,如果此时DBWR进程无法进行时,数据库崩溃等其他异常行为会有极大可能性的。虽然可以将systcl.conf中的aio-max-nr一直调大,但是总归不是长久之计,并且在某年某月时会再次“爆发”。

最可怕的是,通过分析alert中的trace文件时,并没有发现任何有价值信息,所有的线索就是异步IO请求超过最大限制,导致某些IO请求失败抛出错误。。。

通过查看cat /proc/sys/fs/aio-nr查看当前的异步IO请求数量,此值偶尔下降但总量一直处于增长态势, 此时严重怀疑是否数据库中有某些进程执行异步IO请求后并没有释放IO描述符?造成异步IO描述符泄露?

我们数据库集群后发现异步IO请求变回归到正常范围内,但是态势仍然处于上升趋势:

ora11g@travelskydba-rac$ cat /proc/sys/fs/aio-nr 
22784
继续阅读