在OEM 12C上显示，在一个时间段，latch free严重。

经过观察是如下SQL导致：

SQL> SELECT A.ORD_STS_CD,
  2                 (SELECT C.CODE_NM
  3                    FROM CMM_CODE_D C
  4                   WHERE C.CODE_GRP_ID = 'ORD017'
  5                     AND C.CODE = A.ORD_STS_CD) ORD_STS_CD_NM
  6            FROM (SELECT T.ORD_STS_CD
  7                    FROM ORD_ORD_DTL_D T
  8                    WHERE T.CST_ID = 6222483 
  9                   ORDER BY T.INST_DTM DESC) A
 10           WHERE ROWNUM < 2
 11  ;

这条SQL同时运行此时较多，以下是SQL的执行计划。

Elapsed: 00:01:55.53

Execution Plan
----------------------------------------------------------
Plan hash value: 3401062463

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |     5 |   716K  (1)| 02:23:19 |       |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| CMM_CODE_D    |     1 |    27 |     2   (0)| 00:00:01 |       |       |
|*  2 |   INDEX UNIQUE SCAN         | PK_CMM_CODE_D |     1 |       |     1   (0)| 00:00:01 |       |       |
|*  3 |  COUNT STOPKEY              |               |       |       |            |          |       |       |
|   4 |   VIEW                      |               |     6 |    30 |   716K  (1)| 02:23:19 |       |       |
|*  5 |    SORT ORDER BY STOPKEY    |               |     6 |   120 |   716K  (1)| 02:23:19 |       |       |
|   6 |     PARTITION RANGE ALL     |               |     6 |   120 |   716K  (1)| 02:23:19 |     1 |1048575|
|*  7 |      TABLE ACCESS FULL      | ORD_ORD_DTL_D |     6 |   120 |   716K  (1)| 02:23:19 |     1 |1048575|

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    2101472  consistent gets
    2081518  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

经检测，ORD_ORD_DTL_D表的CST_ID字段存在LOCAL索引，可执行计划并没有用到索引，这样就产生了大量的物理读和逻辑读，大量的SQL同时运行就导致了LATCH征用。

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
IX_ORD_ORD_DTL_D_14            CST_ID

索引状态也是正常的，为什么不走索引呢？经过查看ORD_ORD_DTL_D表的描述，CST_ID字段竟然是VARCHAR2类型的，而非NUMBER类型。

Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
CST_ID                                             VARCHAR2(40)

将SQL中的数字用单引号引起来就走索引了！！！

SQL> SELECT A.ORD_STS_CD,
  2      (SELECT C.CODE_NM
  3       FROM CMM_CODE_D C
  4      WHERE C.CODE_GRP_ID = 'ORD017'
  5       AND C.CODE = A.ORD_STS_CD) ORD_STS_CD_NM
  6       FROM (SELECT T.ORD_STS_CD
  7         FROM ORD_ORD_DTL_D T
  8           WHERE T.CST_ID = '6222483' 
  9         ORDER BY T.INST_DTM DESC) A
 10     WHERE ROWNUM < 2
 11  ;

ORD_STS			ORD_STS_CD_NM
-------			---------------
90				订购取消

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1829833084

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

   2 - access("C"."CODE_GRP_ID"='ORD017' AND "C"."CODE"=:B1)
   3 - filter(ROWNUM<2)
   5 - filter(ROWNUM<2)
   8 - access("T"."CST_ID"='6222483')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1425  consistent gets
          0  physical reads
          0  redo size
        616  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

让开发人员修改了程序后，latch free事件明显下降。

本文固定链接: http://www.dbdream.com.cn/2015/08/%e6%95%b0%e5%ad%97%e5%92%8c%e5%ad%97%e7%ac%a6%e8%bd%ac%e6%8d%a2%e5%af%bc%e8%87%b4%e5%a4%a7%e9%87%8f%e7%9a%84latch-free%e7%ad%89%e5%be%85/ | 信春哥，系统稳，闭眼上线不回滚！