oracle – 第8页 – 但行好事莫问前程

Oracle 12CR2查询转换之星型转换

Posted on 2018年10月25日 by YongJing(敬勇)

星型转换是一种优化转换它用来避免对星型方案中的事实表进行全表扫描。一个星型方案将数据分成事实与维度表。事实是对一个事件比如销售的测量通常是数字。维度是标识事实的分类，比如日期，位置与产品。一个事实表有一个由方案中维度表主键所组成的复合键。维度表实际上充当查找或引用表能让你选择你查询所要请求的值。

星型转换的目的
在连接事实表与维度表时，星型转换可能避免对事实表执行完全扫描。星型转换通过只获取连接到约束维度行记录的相关事实行记录来提高性能。在有些情况下，查询已经在维度表的其它列上有限制性过滤了。过滤组合可以大大减少数据库从事实表中要处理的数据集大小。

星型转换的工作原理
星型转换增加了子查询谓词，叫作位图半连接谓词，关联到约束维度表。当在实际连接列上存在索引时优化器执行转换。通过驱动位图and和or来操作由子查询所提供的键值，数据库只需要从事实表中检索相关行记录。如果维度表上的谓词过滤掉了大量数据，那么星型转换比对事实表完全扫描更有效。

在数据库从事实表中检索相关行记录之后，数据库可能需要使用原始谓词连接这些行记录回维度表。当以下条件满足时数据库可以消除连接回维度表:
.维度表上的所有谓词是半连接子查询谓词的一部分
.从子查询中所选择的列具有唯一性
.维度列不在select列，group by子句中等等

控制星型转换
star_transformation_enabled参数控制着星型转换。这个参数有以下参数值:
.true
优化器通过自动识别事实与约束维度表来执行星型转换。只有转换后的执行计划成本比原始执行计划成本低时优化器才执行星型转换。当物化提高性能时优化器也会尝试临时表转换。

.false(缺省值)
优化器不执行星型转换

.temp_disable
这个值与true相同，只是优化器不会尝试临时表转换

星型转换:应用场景
下面的查询找出1999年Q1和Q2季度在California的所有城市中的总的网络销售额:

SELECT c.cust_city,
t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount
FROM sales s,
times t,
customers c,
channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc = 'Internet'
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;

示例输出如下:

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;

CUST_CITY                      CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 1
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 1865285285

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |       |   957 (100)|          |       |       |     22 |00:00:00.18 |    1812 |       |       |          |
|   1 |  HASH GROUP BY                 |           |      1 |     22 |  1672 |   957   (2)| 00:00:01 |       |       |     22 |00:00:00.18 |    1812 |  1022K|  1022K| 1382K (0)|
|*  2 |   HASH JOIN                    |           |      1 |    138 | 10488 |   956   (2)| 00:00:01 |       |       |    964 |00:00:00.14 |    1812 |  1538K|  1538K| 1588K (0)|
|   3 |    PART JOIN FILTER CREATE     | :BF0000   |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  4 |     TABLE ACCESS FULL          | TIMES     |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |       |       |          |
|*  5 |    HASH JOIN                   |           |      1 |    964 | 57840 |   938   (2)| 00:00:01 |       |       |    964 |00:00:00.11 |    1747 |  1448K|  1448K| 1521K (0)|
|   6 |     MERGE JOIN CARTESIAN       |           |      1 |   3341 |   127K|   426   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1531 |       |       |          |
|*  7 |      TABLE ACCESS FULL         | CHANNELS  |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |       |       |          |
|   8 |      BUFFER SORT               |           |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.02 |    1522 |   178K|   178K|  158K (0)|
|*  9 |       TABLE ACCESS FULL        | CUSTOMERS |      1 |   3341 | 86866 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |       |       |          |
|  10 |     PARTITION RANGE JOIN-FILTER|           |      1 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
|  11 |      TABLE ACCESS FULL         | SALES     |      2 |    819K|    16M|   510   (2)| 00:00:01 |:BF0000|:BF0000|    118K|00:00:00.02 |     216 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

从上面的执行中可以看到，在没有使用星型转换时id=11，对表sales执行的是全表扫描。在这个例子中，sales表是事实表，并且其它的表是维度表。sales表对于每一个销售的产品都有一行记录，因此它可以包含上十亿行销售记录。然而，只有少量产品在指定的季度通过网络销售到了California的客户手中。

使用星形转换

SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> SELECT c.cust_city,
  2  t.calendar_quarter_desc,
  3  SUM(s.amount_sold) sales_amount
  4  FROM sales s,
  5  times t,
  6  customers c,
  7  channels ch
  8  WHERE s.time_id = t.time_id
  9  AND s.cust_id = c.cust_id
 10  AND s.channel_id = ch.channel_id
 11  AND c.cust_state_province = 'CA'
 12  AND ch.channel_desc = 'Internet'
 13  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
 14  GROUP BY c.cust_city, t.calendar_quarter_desc;
Montara                        1999-02      1618.01
Pala                           1999-01      3263.93
Cloverdale                     1999-01        52.64
Cloverdale                     1999-02       266.28
San Francisco                  1999-01      3058.27
San Mateo                      1999-01      8754.59
Los Angeles                    1999-01      1886.19
San Mateo                      1999-02     21399.42
Pala                           1999-02       936.62
El Sobrante                    1999-02      3744.03
El Sobrante                    1999-01      5392.34
Quartzhill                     1999-01        987.3
Legrand                        1999-01        26.32
Pescadero                      1999-01        26.32
Arbuckle                       1999-02        241.2
Quartzhill                     1999-02       412.83
Montara                        1999-01       289.07
Arbuckle                       1999-01       270.08
San Francisco                  1999-02        11257
Los Angeles                    1999-02      2128.59
Pescadero                      1999-02       298.44
Legrand                        1999-02        18.66

22 rows selected.

从10053跟踪文件中找到的星型转换后的语句如下:

ST: Query after star xformation:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE (T1) */
 T1.C1 CUST_CITY,
 T.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC,
 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
  FROM SH.SALES                       S,
       SH.TIMES                       T,
       SYS.SYS_TEMP_0FD9D6684_63D6F82 T1
 WHERE S.CUST_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE (T1) */
         T1.C0 C0
          FROM SYS.SYS_TEMP_0FD9D6684_63D6F82 T1)
   AND S.CHANNEL_ID = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         CH.CHANNEL_ID ITEM_1
          FROM SH.CHANNELS CH
         WHERE CH.CHANNEL_DESC = 'Internet')
   AND S.TIME_ID = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         T.TIME_ID ITEM_1
          FROM SH.TIMES T
         WHERE T.CALENDAR_QUARTER_DESC = '1999-01'
            OR T.CALENDAR_QUARTER_DESC = '1999-02')
   AND S.TIME_ID = T.TIME_ID
   AND S.CUST_ID = T1.C0
   AND (T.CALENDAR_QUARTER_DESC = '1999-01' OR
       T.CALENDAR_QUARTER_DESC = '1999-02')
 GROUP BY T1.C1, T.CALENDAR_QUARTER_DESC

其执行计划如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  a069wzk60bbqd, child number 3
-------------------------------------
SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold)
sales_amount FROM sales s, times t, customers c, channels ch WHERE
s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id =
ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc =
'Internet' AND t.calendar_quarter_desc IN ('1999-01','1999-02') GROUP
BY c.cust_city, t.calendar_quarter_desc

Plan hash value: 2164696140

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                            |      1 |        |       |   573 (100)|          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION         |                            |      1 |        |       |            |          |       |       |     22 |00:00:01.41 |    9083 |     96 |     10 |       |       |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D667F_63D6F82 |      1 |        |       |            |          |       |       |      0 |00:00:01.07 |    1539 |      2 |     10 |  1042K|  1042K|          |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS                  |      1 |    383 |  9958 |   423   (1)| 00:00:01 |       |       |   3341 |00:00:00.01 |    1522 |      0 |      0 |       |       |          |
|   4 |   HASH GROUP BY                    |                            |      1 |    542 | 30894 |   150   (1)| 00:00:01 |       |       |     22 |00:00:00.33 |    7538 |     93 |      0 |  1022K|  1022K| 1346K (0)|
|*  5 |    HASH JOIN                       |                            |      1 |   1681 | 95817 |   149   (0)| 00:00:01 |       |       |    964 |00:00:00.31 |    7538 |     93 |      0 |  1572K|  1572K| 1677K (0)|
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  5745 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |     10 |      0 |       |       |          |
|*  7 |     HASH JOIN                      |                            |      1 |   1681 | 70602 |   147   (0)| 00:00:01 |       |       |    964 |00:00:00.29 |    7520 |     83 |      0 |  1538K|  1538K| 1686K (0)|
|*  8 |      TABLE ACCESS FULL             | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|   9 |      VIEW                          | VW_ST_A3F94988             |      1 |   1685 | 43810 |   129   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  10 |       NESTED LOOPS                 |                            |      1 |   1685 | 96045 |   106   (0)| 00:00:01 |       |       |    964 |00:00:00.23 |    7455 |     83 |      0 |       |       |          |
|  11 |        PARTITION RANGE SUBQUERY    |                            |      1 |   1684 | 47167 |    52   (0)| 00:00:01 |KEY(SQ)|KEY(SQ)|    964 |00:00:00.22 |    7271 |     83 |      0 |       |       |          |
|  12 |         BITMAP CONVERSION TO ROWIDS|                            |      2 |   1684 | 47167 |    52   (0)| 00:00:01 |       |       |    964 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  13 |          BITMAP AND                |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.21 |    7204 |     83 |      0 |       |       |          |
|  14 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.05 |      15 |     10 |      0 |  1024K|   512K| 4096  (0)|
|  15 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.04 |      15 |     10 |      0 |       |       |          |
|  16 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.01 |       9 |      0 |      0 | 73728 | 73728 |          |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                   |      1 |      1 |    13 |     3   (0)| 00:00:01 |       |       |      1 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX          |      2 |        |       |            |          |KEY(SQ)|KEY(SQ)|      2 |00:00:00.04 |       6 |     10 |      0 |       |       |          |
|  19 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.03 |     445 |     10 |      0 |  1024K|   512K|39936  (0)|
|  20 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    181 |00:00:00.02 |     445 |     10 |      0 |       |       |          |
|  21 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |    362 |00:00:00.01 |      65 |      0 |      0 | 73728 | 73728 |          |
|* 22 |              TABLE ACCESS FULL     | TIMES                      |      1 |    183 |  2928 |    18   (0)| 00:00:01 |       |       |    181 |00:00:00.01 |      65 |      0 |      0 |       |       |          |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX             |    362 |        |       |            |          |KEY(SQ)|KEY(SQ)|    181 |00:00:00.01 |     380 |     10 |      0 |       |       |          |
|  24 |           BITMAP MERGE             |                            |      2 |        |       |            |          |       |       |      2 |00:00:00.14 |    6744 |     63 |      0 |  1024K|   512K|45056  (0)|
|  25 |            BITMAP KEY ITERATION    |                            |      2 |        |       |            |          |       |       |    403 |00:00:00.14 |    6744 |     63 |      0 |       |       |          |
|  26 |             BUFFER SORT            |                            |      2 |        |       |            |          |       |       |   6682 |00:00:00.01 |      18 |      0 |      0 |  5512K|   964K|  174K (0)|
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D667F_63D6F82 |      1 |    383 |  1915 |     2   (0)| 00:00:01 |       |       |   3341 |00:00:00.01 |      18 |      0 |      0 |       |       |          |
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX             |   6682 |        |       |            |          |KEY(SQ)|KEY(SQ)|    403 |00:00:00.10 |    6726 |     63 |      0 |       |       |          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                      |    964 |      1 |    29 |    77   (2)| 00:00:01 | ROWID | ROWID |    964 |00:00:00.01 |     184 |      0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$D5EF7599
   2 - SEL$F6045C7B
   3 - SEL$F6045C7B / C@SEL$F6045C7B
   6 - SEL$D5EF7599 / T1@SEL$9C741BEB
   8 - SEL$D5EF7599 / T@SEL$1
   9 - SEL$5E9A798F / VW_ST_A3F94988@SEL$D5EF7599
  10 - SEL$5E9A798F
  12 - SEL$5E9A798F / S@SEL$1
  17 - SEL$6EE793B7 / CH@SEL$6EE793B7
  22 - SEL$ACF30367 / T@SEL$ACF30367
  27 - SEL$E1F9C76C / T1@SEL$E1F9C76C
  29 - SEL$5E9A798F / SYS_CP_S@SEL$5E9A798F

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('star_transformation_enabled' 'true')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$F6045C7B")
      OUTLINE_LEAF(@"SEL$ACF30367")
      OUTLINE_LEAF(@"SEL$6EE793B7")
      OUTLINE_LEAF(@"SEL$E1F9C76C")
      OUTLINE_LEAF(@"SEL$5E9A798F")
      TABLE_LOOKUP_BY_NL(@"SEL$0E028FD0" "S"@"SEL$1")
      OUTLINE_LEAF(@"SEL$D5EF7599")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$0E028FD0")
      OUTLINE(@"SEL$C3AF6D21")
      ELIMINATE_JOIN(@"SEL$1" "CH"@"SEL$1")
      OUTLINE(@"SEL$5208623C")
      STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("T"@"SEL$1") ("CH"@"SEL$1") TEMP_TABLE("C"@"SEL$1")))
      FULL(@"SEL$D5EF7599" "T"@"SEL$1")
      NO_ACCESS(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      FULL(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      LEADING(@"SEL$D5EF7599" "T"@"SEL$1" "VW_ST_A3F94988"@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH(@"SEL$D5EF7599" "VW_ST_A3F94988"@"SEL$D5EF7599")
      USE_HASH(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      SWAP_JOIN_INPUTS(@"SEL$D5EF7599" "T1"@"SEL$9C741BEB")
      USE_HASH_AGGREGATION(@"SEL$D5EF7599")
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CHANNEL_ID") 1)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."TIME_ID") 2)
      BITMAP_AND(@"SEL$5E9A798F" "S"@"SEL$1" ("SALES"."CUST_ID") 3)
      ROWID(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      LEADING(@"SEL$5E9A798F" "S"@"SEL$1" "SYS_CP_S"@"SEL$5E9A798F")
      SUBQUERY_PRUNING(@"SEL$5E9A798F" "S"@"SEL$1" PARTITION)
      USE_NL(@"SEL$5E9A798F" "SYS_CP_S"@"SEL$5E9A798F")
      FULL(@"SEL$E1F9C76C" "T1"@"SEL$E1F9C76C")
      SEMIJOIN_DRIVER(@"SEL$E1F9C76C")
      FULL(@"SEL$6EE793B7" "CH"@"SEL$6EE793B7")
      SEMIJOIN_DRIVER(@"SEL$6EE793B7")
      FULL(@"SEL$ACF30367" "T"@"SEL$ACF30367")
      SEMIJOIN_DRIVER(@"SEL$ACF30367")
      FULL(@"SEL$F6045C7B" "C"@"SEL$F6045C7B")
      SEMIJOIN_DRIVER(@"SEL$F6045C7B")
      END_OUTLINE_DATA
  */

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

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("ITEM_1"="C0")
   7 - access("ITEM_2"="T"."TIME_ID")
   8 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  17 - filter("CH"."CHANNEL_DESC"='Internet')
  18 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  22 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02'))
  23 - access("S"."TIME_ID"="T"."TIME_ID")
  28 - access("S"."CUST_ID"="C0")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[120], SYSDEF[0]
   3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_CITY"[VARCHAR2,30], "C"."CUST_STATE_PROVINCE"[VARCHAR2,40]
   4 - "C1"[VARCHAR2,30], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], SUM("ITEM_3")[22]
   5 - (#keys=1; rowset=256) "C0"[NUMBER,22], "ITEM_1"[NUMBER,22], "C1"[VARCHAR2,30], "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_3"[NUMBER,22]
   6 - (rowset=256) "C0"[NUMBER,22], "C1"[VARCHAR2,30]
   7 - (#keys=1; rowset=256) "T"."TIME_ID"[DATE,7], "ITEM_2"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7], "ITEM_1"[NUMBER,22], "ITEM_3"[NUMBER,22]
   8 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
   9 - "ITEM_1"[NUMBER,22], "ITEM_2"[DATE,7], "ITEM_3"[NUMBER,22]
  10 - ROWID[ROWID,10], ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]
  11 - ROWID[ROWID,10]
  12 - ROWID[ROWID,10]
  13 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  14 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  15 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CHANNEL_ID"[NUMBER,22]
  16 - (#keys=2) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  17 - (rowset=256) "CH"."CHANNEL_ID"[NUMBER,22], "CH"."CHANNEL_DESC"[VARCHAR2,20]
  18 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CHANNEL_ID"[NUMBER,22]
  19 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  20 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."TIME_ID"[DATE,7]
  21 - (#keys=2) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  22 - (rowset=256) "T"."TIME_ID"[DATE,7], "T"."CALENDAR_QUARTER_DESC"[CHARACTER,7]
  23 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."TIME_ID"[DATE,7]
  24 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 32496]
  25 - STRDEF[10], STRDEF[10], STRDEF[7920], "S"."CUST_ID"[NUMBER,22]
  26 - (#keys=1) "C0"[NUMBER,22]
  27 - (rowset=256) "C0"[NUMBER,22]
  28 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S"."CUST_ID"[NUMBER,22]
  29 - ROWID[ROWID,10], "S"."CUST_ID"[NUMBER,22], "S"."TIME_ID"[DATE,7], "S"."AMOUNT_SOLD"[NUMBER,22]

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - cbqt star transformation used for this statement
   - this is an adaptive plan

从Note部分的cbqt star transformation used for this statement信息可知执行了星型转换，从执行计划中的ID=29这个步骤可知对表sales使用了索引扫描而不是全表扫描。对于子查询中的times(第22行),customers(第3行),channels（第17行）表中的每个键值，数据库使用事实表sales(第23,28,18行)上索引检索位图。

Oracle 12CR2查询转换之谓词推送

Posted on 2018年10月16日 by YongJing(敬勇)

在谓词推送中，优化器将包含在查询块中的相关谓词推送到视图查询块中。对于不能合并的视图，这种技术可以提高不能合并视图的执行计划。数据库可以使用推送谓词来访问索引或作为过滤。
例如，假设创建了一个hr.contract_workers表:

SQL> drop table contract_workers;

Table dropped.

SQL> create table contract_workers as (select * from employees where 1=2);

Table created.

SQL> insert into contract_workers values (306, 'bill', 'jones', 'bjones','555.555.2000', '07-jun-02', 'ac_account', 8300, 0,205, 110);

1 row created.

SQL> insert into contract_workers values (406, 'jill', 'ashworth', 'jashworth','555.999.8181', '09-jun-05', 'ac_account', 8300, 0,205, 50);

1 row created.

SQL> insert into contract_workers values (506, 'marcie', 'lunsford', 'mlunsford','555.888.2233', '22-jul-01', 'ac_account', 8300, 0,205, 110);

1 row created.

SQL> commit;

Commit complete.

SQL> create index contract_workers_index on contract_workers(department_id);

Index created.

创建一个视图引用employees与contract_workers表。视图使用了union集合操作:

SQL> create view all_employees_vw as
  2  select employee_id, last_name, job_id, commission_pct, department_id
  3  from employees 
  4  union
  5  select employee_id, last_name, job_id, commission_pct, department_id
  6  from contract_workers;

View created.

然后对视图执行查询:

select last_name from all_employees_vw where department_id = 50;

因为视图是一个union集合操作查询，优化器不能合并视图的查询到主查询块。优化器可以通过推送谓词来转换查询，where子句条件department_id=50，会推送到视图的union集合操作查询中，转换后的等价查询如下:

select last_name
from ( 
select employee_id, last_name, job_id, commission_pct, department_id
from employees
where department_id=50
union
select employee_id, last_name, job_id, commission_pct, department_id
from contract_workers
where department_id=50 );

转换后的查询现在可以考虑对每个查询块使用索引或全表扫描,查询视图语句的执行计划如下:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  265ccrp674n30, child number 0
-------------------------------------
select last_name from all_employees_vw where department_id = 50

Plan hash value: 1422200799

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |      1 |        |       |       |  1018 (100)|          |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   1 |  VIEW                | ALL_EMPLOYEES_VW |      1 |    100K|  2637K|       |  1018   (1)| 00:00:01 |    100K|00:00:01.37 |     955 |    942 |       |       |          |
|   2 |   SORT UNIQUE        |                  |      1 |    100K|  2540K|  3936K|  1018   (1)| 00:00:01 |    100K|00:00:01.18 |     955 |    942 |  8416K|  1135K| 7480K (0)|
|   3 |    UNION-ALL         |                  |      1 |        |       |       |            |          |    100K|00:00:00.76 |     955 |    942 |       |       |          |
|*  4 |     TABLE ACCESS FULL| EMPLOYEES        |      1 |    100K|  2540K|       |   273   (1)| 00:00:01 |    100K|00:00:00.41 |     948 |    942 |       |       |          |
|*  5 |     TABLE ACCESS FULL| CONTRACT_WORKERS |      1 |      1 |    60 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SET$1 / ALL_EMPLOYEES_VW@SEL$1
   2 - SET$1
   4 - SEL$2 / EMPLOYEES@SEL$2
   5 - SEL$3 / CONTRACT_WORKERS@SEL$3

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "ALL_EMPLOYEES_VW"@"SEL$1")
      FULL(@"SEL$3" "CONTRACT_WORKERS"@"SEL$3")
      FULL(@"SEL$2" "EMPLOYEES"@"SEL$2")
      END_OUTLINE_DATA
  */

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

   4 - filter("DEPARTMENT_ID"=50)
   5 - filter("DEPARTMENT_ID"=50)

从执行计划的Predicate Information部分可以看到4,5操作使用了department_id=50来分别对表employees和contract_workers来进行过滤，也证明了可以将谓词推送到了视图中的查询块。

Oracle 12CR2查询转换之视图合并

Posted on 2018年10月16日 by YongJing(敬勇)

这里的测试数据库版本为12.2.0.1,在视图合并中，优化器代表视图的查询块到包含视国的查询块中。视图合并通过让优化器考虑额外的连接顺序，访问方法与其它转换来提高性能。例如，在一个视图被合并后并且在一个查询块中有多个表，内置在视图中的一个表可以允许优化器使用连接消除来删除视图外部的一个表。

对于特定的简单视图执行视图合并总是会生成更好的执行计划，优化器自动合并视图不会考虑成本。另外的优化器使用成本来进行决定。由于许多原因，包括成本或有效的限制优化器可能选择不执行视图合并。

如果optimizer_secure_view_merging设置为true(缺省值)，那么Oracle数据库将执行检查来确保视图合并与谓词推送不会违反视图创建者的安全意图。为了对特定视图禁用这些额外的安全检查，可以给创建视图的用户授予merge view权限。为了对特定的用户的所有视图禁用额外的安全检查，可以给用户授予merge any view权限。

视图合并之查询块
优化器通过单独的查询块来代表每个嵌套子查询或未合并视图。数据库自下而上优化每一个单独的查询块。因此，数据库首先优化最内部的查询块，生成执行计划的一部分，然后为外部的查询块生成执行计划。解析器展开查询中的每个视图成为了一个单独的查询块。查询块的本质代表了视图定义和视国结果。优化器的一个选项是用来分别分析视图查询块，生成一个视图子执行计划，然后通过使用视图子计划来处理查询中的剩余部分来生成整个查询的执行计划。然而，这种技术因为导致了视图被分别优化而可能产生次优的执行计划。视图合并有些可能会提高性能。

简单视图合并
在简单视图合并中，优化器合并select-project-join视图。例如，查询employees表的一个查询包含一个子查询连接departments与locations表。

因为在视图合并后有额外的连接顺序与访问路径可用所以简单视图合并通常会生成更优化的执行计划。对于简单视图合并不生效，因为:
.视图包含了不允许出现在select-project-join视图中的结构，比如:
-group by
-distinct
-Outer join
-MODEL
-connect by
-Set operators
-Aggregation
–

.视图出现在semijoin或antijoin的右边
.在select列表中包含子查询
.外部查询块包含PL/SQL函数
.视图参与外连接并且不满足视图被合并的几个条件中的任何一个

下面的查询连接hr.employees表与dept_locs_v视图，查询将返回每个部门的街道地址。dept_locs_v视图连接departments与locations表。

SELECT e.first_name,
       e.last_name,
       dept_locs_v.street_address,
       dept_locs_v.postal_code
  FROM employees e,
       (SELECT d.department_id,
               d.department_name,
               l.street_address,
               l.postal_code
          FROM departments d, locations l
         WHERE d.location_id = l.location_id) dept_locs_v
 WHERE dept_locs_v.department_id = e.department_id
   AND e.last_name = 'Smith';

数据库执行上面的查询通过连接departments与locations表来为视图生成行记录，然后用这个结果与employees表连接。因为查询包含视图dept_locs_v，并且这个视图包含两个表，优化器必须使用以下一种连接顺序:
.employees,dept_locs_v(departments,locations)
.employees,dept_locs_v(locations,departments)
.dept_locs_v(departments,locations),employees
.dept_locs_v(locations,departments),employees

连接方法也受到约束。对于以employees表开始的连接顺序基于索引的嵌套循环不合适因为对于视图中的列不存在索引。不使用视图合并，优化器生成的执行计划如下:

-----------------------------------------------------------------
| Id | Operation                   | Name        | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT            |             |      7 (15)|
|* 1 |  HASH JOIN                  |             |      7 (15)|
|  2 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      2  (0)|
|* 3 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1  (0)|
|  4 |  VIEW                       |             |      5 (20)|
|* 5 |   HASH JOIN                 |             |      5 (20)|
|  6 |    TABLE ACCESS FULL        | LOCATIONS   |      2  (0)|
|  7 |    TABLE ACCESS FULL        | DEPARTMENTS |      2  (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT_LOCS_V"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
3 - access("E"."LAST_NAME"='Smith')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

视图合并将视图中的表合并到外部查询块中，并删除内部查询块。在视图合并之后，查询语句如下:

SELECT e.first_name, e.last_name, l.street_address, l.postal_code
FROM employees e, departments d, locations l
WHERE d.location_id = l.location_id
AND d.department_id = e.department_id
AND e.last_name = 'Smith';

因为所有三个表都出现在一个查询块，优化器可以从以下6种连接顺序中选择一种:
.employees, departments, locations
.employees, locations, departments
.departments, employees, locations
.departments, locations, employees
.locations, employees, departments
.locations, departments, employees

连接employees与departments表现在可以使用索引，在视图合并之后，优化器将选择更有效的执行计划，使用嵌套循环连接:

-------------------------------------------------------------------
| Id | Operation                      | Name        | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |       4 (0)|
|  1 |  NESTED LOOPS                  |             |            |
|  2 |   NESTED LOOPS                 |             |       4 (0)|
|  3 |    NESTED LOOPS                |             |       3 (0)|
|  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |       2 (0)|
|* 5 |      INDEX RANGE SCAN          | EMP_NAME_IX |       1 (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |       1 (0)|
|* 7 |      INDEX UNIQUE SCAN         | DEPT_ID_PK  |       0 (0)|
|* 8 |    INDEX UNIQUE SCAN           | LOC_ID_PK   |       0 (0)|
|  9 |   TABLE ACCESS BY INDEX ROWID  | LOCATIONS   |       1 (0)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."LAST_NAME"='Smith')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

复杂视图合并
在视图合并中，优化器合并包含group by与distinct操作的视图，像简单视图合燕一样，复杂视图合并能让优化器考虑额外的连接顺序和访问路径。

优化器可能会延迟对group by或distinct操作进行评估直到优化器评估完连接之后。延迟这些操作可能提高或损害性能这依赖于数据的特征。如果连接使用过滤，那么延迟这些操作在连接之后可以减少这些操作将要处理的数据集。尽
早评估可以减少后续连接所要处理的数据量或者连接可能增加这些操作所要处理的数据量。优化器使用成本来评估视图合并并且只有当合并操作之后成本更低才会执行。

除了成本之外，由于以下原因成本可能不会执行复杂视图合并操作:
.外部查询表没有rowid或唯一约束列
.视图出现在connect by查询块中
.视图包含grouping sets,rollup或pivot子句
.视图或外部查询块包含model子句

包含group by子句的复杂视图连接下面的查询使用了group by子句

CREATE VIEW cust_prod_totals_v AS
SELECT SUM(s.quantity_sold) total, s.cust_id, s.prod_id
FROM sales s
GROUP BY s.cust_id, s.prod_id;

下面的查询将找出来那些自United States并且买了至少100件毛衣的所有客户:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p, cust_prod_totals_v
WHERE c.country_id = 52790
AND c.cust_id = cust_prod_totals_v.cust_id
AND cust_prod_totals_v.total > 100
AND cust_prod_totals_v.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

cust_prod_totals_v视图满足复杂视图合并的条件。在合并之后，查询语句如下:

SELECT c.cust_id, cust_first_name, cust_last_name, cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater'
GROUP BY s.cust_id, s.prod_id, p.rowid, c.rowid, c.cust_email, c.cust_last_name,
c.cust_first_name, c.cust_id
HAVING SUM(s.quantity_sold) > 100;

转换后的查询成本比没转换的查询成本要低，因此优化器选择了合并视图。在没有转换的语句中，group by操作是对视图中的整个sales表进行操作。在转换后的查询中，连接products与customers表过滤掉了sales表中的大部分数据，因此group by操作的成本低。连接成本更高因为sales表没有被减少，但它的成本并不会高很多，因为group by操作不会在原始查询中减少太多的行记录。如果之前的特征发生了改变，合并视图后的成本将不会减少。最终的执行计划不包含视图，如下:

--------------------------------------------------------
| Id | Operation             | Name      | Cost (%CPU)|
--------------------------------------------------------
|  0 | SELECT STATEMENT      |           |  2101 (18)|
|* 1 |  FILTER               |           |           |
|  2 |   HASH GROUP BY       |           |  2101 (18)|
|* 3 |    HASH JOIN          |           |  2099 (18)|
|* 4 |     HASH JOIN         |           |  1801 (19)|
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |     96 (5)|
|  6 |      TABLE ACCESS FULL| SALES     |  1620 (15)|
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |   296 (11)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("QUANTITY_SOLD")>100)
3 - access("C"."CUST_ID"="CUST_ID")
4 - access("PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

使用distinct的复杂视图连接
下面的查询对cust_prod_v视图使用了distinct操作:

SELECT c.cust_id, c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p,
( SELECT DISTINCT s.cust_id, s.prod_id
FROM sales s) cust_prod_v
WHERE c.country_id = 52790
AND c.cust_id = cust_prod_v.cust_id
AND cust_prod_v.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater';

在决定视图合并后生成的执行计划成本更低，优化器使用以下等价查询来重写原始查询:

SELECT nwvw.cust_id, nwvw.cust_first_name, nwvw.cust_last_name, nwvw.cust_email
FROM ( SELECT DISTINCT(c.rowid), p.rowid, s.prod_id, s.cust_id,
c.cust_first_name, c.cust_last_name, c.cust_email
FROM customers c, products p, sales s
WHERE c.country_id = 52790
AND c.cust_id = s.cust_id
AND s.prod_id = p.prod_id
AND p.prod_name = 'T3 Faux Fur-Trimmed Sweater' ) nwvw;

上面查询的执行计划如下:

-------------------------------------------
| Id | Operation             | Name      |
-------------------------------------------
|  0 | SELECT STATEMENT      |           |
|  1 |  VIEW                 | VM_NWVW_1 |
|  2 |   HASH UNIQUE         |           |
|* 3 |    HASH JOIN          |           |
|* 4 |     HASH JOIN         |           |
|* 5 |      TABLE ACCESS FULL| PRODUCTS  |
|  6 |      TABLE ACCESS FULL| SALES     |
|* 7 |     TABLE ACCESS FULL | CUSTOMERS |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."CUST_ID"="S"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_NAME"='T3 Faux Fur-Trimmed Sweater')
7 - filter("C"."COUNTRY_ID"='US')

上面的执行计划即使在视图合并后还是包含了一个名叫vm_nwvw_1的视图，也叫projection view。在查询中的distinct视图已经合并后出现了projection视图，或者group by视图被合并到外部查询块并且包含group by,having或聚合操作。在后一种情况下，projection视图包含了group by,having和原始外部查询块中的聚合操作。

在上面的projection视图中，当优化器合并视图时，它将distinct操作移动到外部查询块中，并且增加了几个额外列来维护与原始查询的等价性。在这之后，查询可以只从外部查询块中的select列表中选择所需要的列。优化器保留了视图合并的所有好处:一个查询块中的所有表，优化器可能会在最终的连接顺序中变换它们的顺序，并且distinct操作可能会延迟到所有连接完成之后。

Oracle 12c Automatic Reoptimization

Posted on 2018年10月10日 by YongJing(敬勇)

在自动重优化中，优化器会在初次执行后为后续的执行选择一个执行计划。对于所有类型的计划变化自适应查询计划是不可行的。例如，一个使用效率低的连接顺序的查询可以生成次优的执行计划，但自适应查询计划不支持在执行时调整连接顺序。在一个语句第一次执行之后，优化器使用在执行时所收集到的信息来判断使用自动重优化是否可以降低执行成本。如果收集到的执行信息与优化器所评估的存在显著差异，那么优化器会在下一次执行时查找一个可替代的执行计划。

优化器使用在之前执行时所收集到的信息来帮助决定一个替代执行计划。优化器可能会重新优化一个查询多次，每次收集额外的数据并且将来用于改进执行计划。自动重新优化包含以下两种形式:
统计信息反馈
性能反馈

统计信息反馈
一种重新优化形式叫作统计信息反馈(之前叫作基数反馈)，它能对存在错误基数评估的重复查询进行自动改进。优化器可能因为许多原因而造成基数错误评估，比如丢失统计信息，不正确的统计信息或者复杂的谓词条件。使用统计反馈执行重新优化的基本过程如下:
1.在第一次执行一个SQL语句时，优化器会生成一个执行计划。优化器可能在以下情况下对共享SQL区的统计反馈进行监控:
.没有统计信息的表
.一个表上的多个连接或分离过滤谓词
.包含复杂操作的谓词优化器不能精确的计算选择性

2.在第一次执行结束后，优化器将对初次所评估的基数与在执行时执行计划中每步操作所返回的真实行记录进行比较。
如果评估的基数与真实的基数存在显著差异，那么优化器将存储真实的基数给后续的执行所使用。优化器也会创建一个SQL计划指令因此其它的SQL语句可以从初次执行后所获得的信息中获益。

3.如果查询再次执行，那么优化器使用正确的基数评估来代替它的常用评估。optimizer_adaptive_statistics参数不能控制自动重优化的所有功能。这个参数只在自动重优化上下文中控制着对连接基数的统计信息反馈。例如，将
optimizer_adaptive_statistics参数设置为false时将禁用对连接基数错误评估进行统计信息反馈，但它不会禁用对单表基数错误评估进行统计信息反馈。

下面的例子显示了数据库如何使用统计信息反馈来调整不正的基数评估
1.用户oe来对表orders,order_items与product_information表进行查询:

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

2.查询游标中的执行计划显示评估的行记录(E-Rows)远远小于实际行记录(A-Rows)

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
SQL_ID  5sq2n361a0938, child number 2
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 1906736282

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |      1 |        |    269 |00:00:00.07 |    1339 |       |       |          |
|   1 |  NESTED LOOPS         |                     |      1 |      1 |    269 |00:00:00.07 |    1339 |       |       |          |
|   2 |   MERGE JOIN CARTESIAN|                     |      1 |      4 |   9135 |00:00:00.03 |      34 |       |       |          |
|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |      1 |      1 |     87 |00:00:00.01 |      33 |       |       |          |
|   4 |    BUFFER SORT        |                     |     87 |    105 |   9135 |00:00:00.01 |       1 |  4096 |  4096 | 4096  (0)|
|   5 |     INDEX FULL SCAN   | ORDER_PK            |      1 |    105 |    105 |00:00:00.01 |       1 |       |       |          |
|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |   9135 |      1 |    269 |00:00:00.02 |    1305 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID")

3.用户oe重新执行步骤1中的查询

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

4.查询游标中的执行计划显示对于第二次执行优化器使用了统计信息反馈(Note部分)并且选择了一个不同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------


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

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

Note
-----
   - statistics feedback used for this statement

在上面的输出中，评估的行记录是(269)与实际行记录相匹配。

5.用户oe再执行查询多次(两次以上)

SQL>
SELECT o.order_id, v.product_name
FROM orders o,
( SELECT order_id, product_name
FROM order_items o, product_information p
WHERE p.product_id = o.product_id
AND list_price < 50
AND min_price < 40 ) v
WHERE o.order_id = v.order_id;

6.查询游标中的执行计划显示对于第四次执行优化器使用了与第二次执行相同的执行计划(Note部分)并且选择了一个相同的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last runstats_last peeked_binds'));
SQL_ID  5sq2n361a0938, child number 0
-------------------------------------
SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id,
product_name FROM order_items o, product_information p WHERE
p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v
WHERE o.order_id = v.order_id

Plan hash value: 35479787

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |      1 |        |       |     7 (100)|          |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|   1 |  NESTED LOOPS          |                     |      1 |    269 |  3960 |     7   (0)| 00:00:01 |    269 |00:00:00.03 |      63 |     21 |       |       |          |
|*  2 |   HASH JOIN            |                     |      1 |    313 |  3564 |     7   (0)| 00:00:01 |    269 |00:00:00.02 |      42 |     20 |  1355K|  1355K| 1333K (0)|
|*  3 |    TABLE ACCESS FULL   | PRODUCT_INFORMATION |      1 |     87 |   784 |     5   (0)| 00:00:01 |     87 |00:00:00.01 |      16 |     14 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORDER_ITEMS_UK      |      1 |    665 |  5320 |     2   (0)| 00:00:01 |    665 |00:00:00.01 |      26 |      6 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN    | ORDER_PK            |    269 |      1 |     4 |     0   (0)|          |    269 |00:00:00.01 |      21 |      1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50))
   5 - access("O"."ORDER_ID"="ORDER_ID")

 

Note
-----
   - this is an adaptive plan

从上面的输出的Note部分this is an adaptive plan可知使用了自适应执行计划。

性能反馈
重新优化的另一种形式是性能反馈。这个重新优化当parallel_degree_policy参数设置为adaptive时用来帮助重复执行SQL语句自动选择并行度。使用性能反馈执行重新优化的过程如下:
1.当parallel_degree_policy参数设置为adaptive时，在第一次执行SQL语句时，优化器将决定是否以并行方式来执行SQL语句，如果使用并行，使用什么样的并行度来执行。优化器选择并行度是基于语句的所评估的性能。对于所有语句会启用额外的性能监控。

2.在第一次执行结束后，优化器将比较以下信息:
.优化器所选择的并行度。
.基于在实际执行语句时所收集的性能统计数据所计算出来的并行度。

如果两个并行度存在显著差异，那么数据库将会标记语句重新解析，并且存储每一次执行的统计数据作为反馈。这种反馈将用来对后续的执行更好的计算并行度。

3.如果查询再次执行，优化器将使用第一次执行所收集到的性能统计数据来更好的决定执行语句的并行度。

注意即使parallel_degree_policy没有被设置为adaptive，统计信息反馈也可能影响对语句的并行度的选择。

使用dbms_addm包执行addm性能诊断

Posted on 2018年9月11日 by YongJing(敬勇)

使用dbms_addm包可以以三种模式来执行addm性能诊断
.以数据库模式
.以实例模式
.以部分模式
.显示ADDM报告

以数据库模式语法如下:

begin
dbms_addm.analyze_db (
task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_db(:tname, 3785, 3786);
  4  end;
  5  /

以实例模式语法如下:

begin
dbms_addm.analyze_inst (
task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
instance_number in number := null,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_inst(:tname, 3785, 3786,1);
  4  end;
  5  /

以部分模式语法如下:

begin
dbms_addm.analyze_partial (
task_name in out varchar2,
instance_numbers in varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null);
end;
/

示例:

SQL> var tname varchar2(30);
SQL> begin
  2  :tname := 'addm for 17 to 18';
  3  dbms_addm.analyze_inst(:tname,'1,2',3785,3786);
  4  end;
  5  /

显示ADDM报告

SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
          ADDM Report for Task 'addm_cs for 09 to 10'
          -------------------------------------------

Analysis Period
---------------
AWR snapshot range from 16201 to 16202.
Time period starts at 10-SEP-18 09.00.34 AM
Time period ends at 10-SEP-18 10.00.39 AM

Analysis Target
---------------
Database 'RLZY' with DB ID 1710982568.
Database version 11.2.0.4.0.
Analysis was requested for all instances, but ADDM analyzed instance RLZY2,
numbered 2 and hosted at db2.
See the "Additional Information" section for more information on the requested
instances.

Activity During the Analysis Period
-----------------------------------
Total database time was 22511 seconds.
The average number of active sessions was 6.24.
ADDM analyzed 1 of the requested 1 instances.

Summary of Findings
-------------------
   Description                               Active Sessions      Recommendation
s                                              Percent of Activity
   ----------------------------------------  -------------------  --------------
- 1  Undersized SGA                            1.91 | 30.55         1
2  Hard Parse Due to Literal Usage           1.78 | 28.58         1
3  Top SQL Statements                        1.13 | 18.17         4
4  Top Segments by "User I/O" and "Cluster"  .55 | 8.78           3
5  Global Cache Messaging                    .17 | 2.79           0


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: Undersized SGA
Impact is 1.91 active sessions, 30.55% of total activity.
---------------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "51200 M" during the analysis period.

   Recommendation 1: Database Configuration
   Estimated benefit is 1.91 active sessions, 30.55% of total activity.
   --------------------------------------------------------------------
   Action
      Increase the size of the SGA by setting the parameter "sga_target" to
      76800 M.

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 1.82 active sessions, 29.22% of total activity.
      Wait class "User I/O" was consuming significant database time.
      Impact is 1.21 active sessions, 19.36% of total activity.


Finding 2: Hard Parse Due to Literal Usage
Impact is 1.78 active sessions, 28.58% of total activity.
---------------------------------------------------------
SQL statements were not shared due to the usage of literals. This resulted in
additional hard parses which were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 1.78 active sessions, 28.58% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate application logic for possible use of bind variables instead
      of literals.
   Action
      Alternatively, you may set the parameter "cursor_sharing" to "force".
   Rationale
      At least 106 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 76216136 were found to be using
      literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 104 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 3291498723 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 31 SQL statements with FORCE_MATCHING_SIGNATURE
      1212153288317564648 and PLAN_HASH_VALUE 4105729137 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 30 SQL statements with FORCE_MATCHING_SIGNATURE
      10877138405225238976 and PLAN_HASH_VALUE 4135929742 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.
   Rationale
      At least 27 SQL statements with FORCE_MATCHING_SIGNATURE
      16030230994864437066 and PLAN_HASH_VALUE 2060270506 were found to be
      using literals. Look in V$SQL for examples of such SQL statements.

   Symptoms That Led to the Finding:
   ---------------------------------
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 1.82 active sessions, 29.22% of total activity.


Finding 3: Top SQL Statements
Impact is 1.13 active sessions, 18.17% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .48 active sessions, 7.63% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "awb7dnusv1by2".
      Related Object
         SQL statement with SQL_ID awb7dnusv1by2.
         select distinct a.hospital_id as hospital_id,         (select
         d.hospital_name from bs_hospital d where f.hospital_id =
         d.hospital_id ) as hospital_name,         f.ACCOUNT_ID as ACCOUNT_ID,
         a.indi_id as indi_id,         a.serial_no as
         serial_no,a.treatment_type as treatment_type,         (select
         h.BIZ_NAME from bs_biztype h where h.center_id=a.center_id and
         h.biz_type=a.biz_type) as biz_name,         e.disease as disease,
         a.reg_man as reg_man,         a.begin_date as begin_date,
         a.end_date as end_date,         a.name as name,         a.sex as sex,
         a.IDCARD as idcard,         a.pers_type as pers_type,
         a.finish_flag as finish_flag,         (select g.pers_name from
         bs_person_type g where a.pers_type = g.pers_type and a.center_id =
         g.center_id ) as pers_name,         a.in_area_name as in_area_name,
         a.in_dept_name as in_dept_name,         a.in_bed as in_bed,
         a.bed_type as bed_type,         a.corp_name as corp_name,
         a.fin_date as fin_date,         (select sum(pay_money) from
         pm_account_fund k where  k.account_id = f.account_id) total_pay,
         (select sum(decode(k.FUND_ID,'996',0,'999',0,k.pay_money)) from
         pm_account_fund k where k.account_id = f.account_id ) fund_pay  from
         mt_biz_fin a,         pm_account_biz f,         bs_disease e,
         bs_center center ,bs_hospital bh  where
         decode(a.reimburse_flag,'2',a.rela_hosp_id ,a.hospital_id) =
         f.hospital_id      and f.hospital_id = bh.hospital_id        and
         a.serial_no = f.serial_no         and a.center_id = f.center_id
         and e.icd (+)= f.icd         and center.center_id (+)= f.center_id
         and e.center_id = nvl(center.catalog_center,center.center_id)
         and (bh.hospital_id in ('4307000009') or bh.up_hospital_id in
         ('4307000009')) and exists (select 'X' from bs_center  center where
         center.center_id=a.center_id and center.center_id  in ('430702'))
         and nvl(f.deal_flag,'0') = '0'          and f.valid_flag = '1'
         and to_number(to_char(f.fin_date,'yyyymmdd')) between 20180801 and
         20180831        and a.valid_flag = '1'         and a.lock_flag = '1'
         and a.reimburse_flag in ('0','2')          and
         to_number(to_char(a.fin_date,'yyyymmdd')) between 20180801  and
         20180831
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "awb7dnusv1by2" was executed 2 times and had
      an average elapsed time of 851 seconds.
   Rationale
      At least 2 distinct execution plans were utilized for this SQL statement
      during the analysis period.

   Recommendation 2: SQL Tuning
   Estimated benefit is .31 active sessions, 4.95% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "fw5gmdaxuyk3a".
      Related Object
         SQL statement with SQL_ID fw5gmdaxuyk3a.
         SELECT DISTINCT HOSPITAL_ID,
         SERIAL_NO,PERS_TYPE,RELA_HOSP_ID,A.REIMBURSE_FLAG FROM MT_BIZ_FIN A,
         BS_BIZTYPE B WHERE A.BIZ_TYPE = B.BIZ_TYPE AND (:B8 = 'A' OR
         B.BIZ_STAT = :B8 ) AND B.INSR_NO = :B7 AND B.BIZ_STAT IN ('2', '3')
         AND NVL(A.LOCK_FLAG, '0') = '0' AND A.VALID_FLAG = '1' AND
         (NVL(A.REIMBURSE_FLAG, '0') = '0' OR A.REIMBURSE_FLAG = '2') AND
         (A.HOSPITAL_ID = :B6 OR A.RELA_HOSP_ID = :B6 ) AND (A.CENTER_ID=:B5
         OR (:B5 ='431000' AND A.CENTER_ID IN ('431000','431002','431003')))
         AND ('A' = :B4 OR A.OP_TOWN_ID = :B4 ) AND ('A' = :B3 OR
         A.OP_VILLAGE_ID = :B3 ) AND A.FIN_DATE BETWEEN TO_DATE(:B2 || '
         00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE(:B1 || ' 23:59:59',
         'yyyy-mm-dd hh24:mi:ss')
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "fw5gmdaxuyk3a" was executed 52 times and had
      an average elapsed time of 21 seconds.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8prz8w1bg5714" are responsible for 100% of the database time spent on
      the SELECT statement with SQL_ID "fw5gmdaxuyk3a".
      Related Object
         SQL statement with SQL_ID 8prz8w1bg5714.
         call usp_pay_account_declare(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10
         ,:11 ,:12 ,:13 )

   Recommendation 3: SQL Tuning
   Estimated benefit is .18 active sessions, 2.86% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "f63shdpjy5kgy".
      Related Object
         SQL statement with SQL_ID f63shdpjy5kgy.

   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for TABLE "INSUR_CHANGDE.MT_PAY_RECORD_FIN" with
      object ID 925431 consumed 100% of the database time spent on this SQL
      statement.

   Recommendation 4: SQL Tuning
   Estimated benefit is .17 active sessions, 2.73% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "a81a3tafx4wzb".
      Related Object
         SQL statement with SQL_ID a81a3tafx4wzb.
         select distinct li.pers_type   from lv_indipar li,lv_cropfundpar
         lc,lv_insr_topay lt  where li.indi_id = :1     and li.period = :2
         and lc.money_no = li.money_no    and lt.pay_info_no = lc.pay_info_no
         and lt.insr_detail_code <> 21    and lt.topay_type not in (13) and
         li.pers_type is not null
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "a81a3tafx4wzb" was executed 14866 times and
      had an average elapsed time of 0.046 seconds.
   Rationale
      I/O and Cluster wait for TABLE "INSUR_CHANGDE.LV_INDIPAR" with object ID
      925315 consumed 90% of the database time spent on this SQL statement.


Finding 4: Top Segments by "User I/O" and "Cluster"
Impact is .55 active sessions, 8.78% of total activity.
-------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.

   Recommendation 1: Segment Tuning
   Estimated benefit is .24 active sessions, 3.81% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.MT_PAY_RECORD_FIN" with object ID 925431.
      Related Object
         Database object with ID 925431.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "f63shdpjy5kgy" is responsible for 75% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 438356
      physical reads, 683 physical writes and 0 direct reads.

   Recommendation 2: Segment Tuning
   Estimated benefit is .18 active sessions, 2.87% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.LV_INDIPAR" with object ID 925315.
      Related Object
         Database object with ID 925315.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "a81a3tafx4wzb" is responsible for 85% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 708526
      physical reads, 2431 physical writes and 0 direct reads.

   Recommendation 3: Segment Tuning
   Estimated benefit is .13 active sessions, 2.11% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "INSUR_CHANGDE.MT_BIZ_FIN" with object ID 925378.
      Related Object
         Database object with ID 925378.
   Rationale
      The I/O usage statistics for the object are: 0 full object scans, 275264
      physical reads, 1026 physical writes and 0 direct reads.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is 1.21 active sessions, 19.36% of total activity.


Finding 5: Global Cache Messaging
Impact is .17 active sessions, 2.79% of total activity.
-------------------------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Read and write contention on database blocks was not consuming significant
database time in the cluster.
Global Cache Service Processes (LMSn) in other instances were performing
within acceptable limits of 1 milliseconds.
Waits on "buffer busy" events were not consuming significant database time.

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is .18 active sessions, 2.81% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database
time.

12C Oracle ASM Filter Driver

Posted on 2018年8月31日 by YongJing(敬勇)

Oracle ASM Filter Driver（Oracle ASMFD)消除了在系统每次被重启后Oracle ASM需要重新绑定磁盘设备来简化对磁盘设备的配置与管理。Oracle ASM Filter Driver(Oracle ASMFD)是一种内置在Oracle ASM磁盘的IO路径中的内核模块。Oracle ASM使用filter driver来验证对Oracle ASM磁盘的写IO操作。Oracle ASM Filter Driver会拒绝任何无效的IO请求。这种操作消除了意外覆盖Oracle ASM磁盘而损坏磁盘组中的磁盘与文件。例如，Oracle ASM Filter Driver会过滤掉所有可能意外覆盖磁盘的非Oracle IO操作。从Oracle 12.2开始，Oracle ASM Filter Driver(Oracle ASMFD)在系统安装Oracle ASMLIB的情况下不能被安装，如果你想安装与配置Oracle ASMFD，那么必须首先卸载Oracle ASMLIB。Oracle 12.2的ASMFD不支持扩展分区表。

配置Oracle ASM Filter Driver
可以在安装Oracle Grid Infrastructure时或在安装Oracle Grid Infrastructure后对磁盘设备永久性配置Oracle ASM Filter Driver(Oracle ASMFD)。

在安装Oracle Grid Infrastructure时配置Oracle ASM Filter Driver
在安装Oracle Grid Infrastructure时，可以选择启用自动安装与配置Oracle ASM Filter Driver。如果在安装Oracle Grid Infrastructure所在的系统中没有使用udev，那么可以在在安装Oracle Grid Infrastructure之前执行下面的操作来为Oracle ASMFD准备磁盘。下面的操作必须在Oracle Grid Infrastructure软件包在Oracle Grid Infrastructure home目录中必须解压后，但在配置ASMFD之前执行。

1.为了使用Oracle ASM Filter Driver来配置共享磁盘，以root用户来设置环境变量$ORACLE_HOME为Grid Home目录，设置环境变量$ORACLE_BASE为临时目录

# set ORACLE_HOME=/u01/app/oracle/12.2.0/grid
# set ORACLE_BASE=/tmp

ORACLE_BASE变量被设置为临时目录可以避免在安装Oracle Grid Infrastructure之前在Grid Home目录中创建诊断或跟踪文件。在执行下面的操作之前，确保是在$ORACLE_HOME/bin目录中执行命令。

2.使用ASMCMD afd_label命令来为Oracle ASM Filter Driver来准备磁盘.

#asmcmd afd_label DATA1 /dev/disk1a --init

3.使用ASMCMD afd_lslbl命令来验证磁盘是否已经被标记可以为Oracle ASMFD所使用

#asmcmd afd_lslbl /dev/disk1a

查看某块磁盘

[rootd@cs1 ~]./asmcmd afd_lslbl /dev/asmdisk01
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
CRS2                                  /dev/asmdisk01

列出已经标记可以为Oracle ASMFD所使用的所有磁盘

[grid@jytest1 ~]$ asmcmd afd_lslbl 
--------------------------------------------------------------------------------
Label                     Duplicate  Path
================================================================================
CRS1                                  /dev/asmdisk02
CRS2                                  /dev/asmdisk01
DATA1                                 /dev/asmdisk03
DATA2                                 /dev/asmdisk04
FRA1                                  /dev/asmdisk07
TEST1                                 /dev/asmdisk05
TEST2                                 /dev/asmdisk06

4.当为Oracle ASMFD准备完磁盘后清除变量ORACLE_BASE

# unset ORACLE_BASE

5.运行安装脚本(gridSetup.sh)来安装Oracle Grid Infrastructure并启用Oracle ASM Filter Driver配置。

在安装Oracle Grid Infrastructure后配置Oracle ASM Filter Driver
如果在安装Grid Infrastructure时没有启用配置Oracle ASMFD，那么可以使用Oracle ASMFD来手动配置Oracle ASM设备。

为Oracle Grid Infrastructure Clusterware环境配置Oracle ASM，具体操作如下:
1.以Oracle Grid Infrastructure用户来更新Oracle ASM磁盘发现路径来使Oracle ASMFD来发现磁盘。
首先检查当前Oracle ASM磁盘发现路径并更新

[grid@cs1 ~]$ asmcmd dsget
parameter:/dev/sd*, /dev/asm* 
profile:/dev/sd*,/dev/asm*

将’AFD:*’增加到发现磁盘路径中

[grid@cs1 ~]$ asmcmd dsset 'dev/sd*','/dev/asm*','AFD:*'
[grid@cs1 ~]$ asmcmd dsget
parameter:dev/sd*, /dev/asm*, AFD:*
profile:dev/sd*,/dev/asm*,AFD:*

2.以Oracle Grid Infrastructure用户来获取cluster中的节点列表与角色

[grid@cs1 ~]$ olsnodes -a
cs1     Hub
cs2     Hub

3.在每个Hub与Leaf节点上，可以以回滚或非回滚模式来执行以下操作
3.1以root用户来停止Oracle Grid Infrastructure

# $ORACLE_HOME/bin/crsctl stop crs

如果命令返回错误，那么执行下面的命令来强制停止Oracle Grid Infrastructure

# $ORACLE_HOME/bin/crsctl stop crs -f

3.2在节点层面以root用户来配置Oracle ASMFD

# $ORACLE_HOME/bin/asmcmd afd_configure

3.3以Oracle Grid Infrastructure用户来验证Oracle ASMFD的状态

[grid@cs2 ~]$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'cs2.jy.net'

3.4以root用户来启动Oracle Clusterware stack

# $ORACLE_HOME/bin/crsctl start crs

3.5以Oracle Grid Infrastructure用户来设置Oracle ASMFD发现磁盘路径为步骤3.1中所检索到的原始Oracle ASM磁盘发现路径

[grid@cs1 ~]$ asmcmd dsset 'dev/sd*','/dev/asm*'

迁移不包含OCR或vote文件的磁盘组到Oracle ASMFD
1.以Oracle Grid Infrastructure用户来执行以下操作

2.列出已经存在的磁盘组

[grid@cs2 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40960     1544                0            1544              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960      860                0             860              0             N  DATA/
MOUNTED  NORMAL  N         512             512   4096  4194304     40960    40704                0           20352              0             N  DN/

3.列出相关磁盘

[grid@cs2 ~]$ asmcmd lsdsk -G DN
Path
/dev/asmdisk03
/dev/asmdisk05

从下面的查询可以看到/dev/asmdisk03和/dev/asmdisk05的label字段为空

SQL> select group_number,disk_number,name,label,path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL                                              PATH
------------ ----------- ------------------------------ -------------------------------------------------- --------------------------------------------------
           0           0                                CRS2                                               /dev/asmdisk01
           0           1                                CRS1                                               /dev/asmdisk02
           0           2                                DATA1                                              /dev/asmdisk04
           3           0 DN_0000                                                                           /dev/asmdisk03
           3           1 DN_0001                                                                           /dev/asmdisk05
           1           0 CRS1                           CRS1                                               AFD:CRS1
           2           0 DATA1                          DATA1                                              AFD:DATA1
           1           1 CRS2                           CRS2                                               AFD:CRS2

4.检查Oracle ASM是否是活动状态

[grid@cs2 ~]$ srvctl status asm
ASM is running on cs1,cs2

5.在所有节点上停止数据库与dismount磁盘组

[grid@cs2 ~]$srvctl stop diskgroup -diskgroup DN -f

6.在每个Hub节点上执行以下命令来为磁盘组中的所有已经存在的磁盘进行标记

[grid@cs2 ~]$ asmcmd afd_label DN1 /dev/asmdisk03 --migrate
[grid@cs2 ~]$ asmcmd afd_label DN2 /dev/asmdisk05 --migrate

7.在所有Hub节点上扫描磁盘

[grid@cs1 ~]$ asmcmd afd_scan
[grid@cs2 ~]$ asmcmd afd_scan

8.在所有节点启动数据库并mount磁盘组

[grid@cs2 ~]$ srvctl start diskgroup -diskgroup DN

从下面的查询可以看到/dev/asmdisk03和/dev/asmdisk05的label字段分别显示为DN1和DN2

SQL> select group_number,disk_number,name,label,path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL                                              PATH
------------ ----------- ------------------------------ -------------------------------------------------- --------------------------------------------------
           0           0                                CRS2                                               /dev/asmdisk01
           0           1                                DN2                                                /dev/asmdisk05
           0           2                                DN1                                                /dev/asmdisk03
           0           3                                CRS1                                               /dev/asmdisk02
           0           4                                DATA1                                              /dev/asmdisk04
           1           1 CRS2                           CRS2                                               AFD:CRS2
           2           0 DATA1                          DATA1                                              AFD:DATA1
           1           0 CRS1                           CRS1                                               AFD:CRS1
           3           0 DN_0000                        DN1                                                AFD:DN1
           3           1 DN_0001                        DN2                                                AFD:DN2

现在可以将原先的 udev rules 文件移除。当然，这要在所有节点中都运行。以后如果服务器再次重启，AFD 就会完全接管了。

[root@cs1 bin]# cd /etc/udev/rules.d/
[root@cs1 rules.d]# ls -lrt
total 16
-rw-r--r--. 1 root root  709 Mar  6  2015 70-persistent-ipoib.rules
-rw-r--r--  1 root root 1416 Mar  9 12:23 99-my-asmdevices.rules
-rw-r--r--  1 root root  224 Mar  9 15:52 53-afd.rules
-rw-r--r--  1 root root  190 Mar  9 15:54 55-usm.rules

[root@cs1 rules.d]# mv 99-my-asmdevices.rules 99-my-asmdevices.rules.bak

[root@cs1 rules.d]# cat 53-afd.rules
#
# AFD devices
KERNEL=="oracleafd/.*", OWNER="grid", GROUP="asmadmin", MODE="0775"
KERNEL=="oracleafd/*", OWNER="grid", GROUP="asmadmin", MODE="0775"
KERNEL=="oracleafd/disks/*", OWNER="grid", GROUP="asmadmin", MODE="0664"


[root@cs1 rules.d]# ls -l /dev/oracleafd/disks
total 20
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 CRS1
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 CRS2
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 DATA1
-rwxrwx--- 1 grid oinstall 15 Aug 30 17:42 DN1
-rwxrwx--- 1 grid oinstall 15 Aug 30 17:42 DN2

[root@cs2 bin]# cd /etc/udev/rules.d/
[root@cs2 rules.d]# ls -lrt
total 16
-rw-r--r--. 1 root root  709 Mar  6  2015 70-persistent-ipoib.rules
-rw-r--r--  1 root root 1416 Mar  9 12:23 99-my-asmdevices.rules
-rw-r--r--  1 root root  224 Mar  9 15:52 53-afd.rules
-rw-r--r--  1 root root  190 Mar  9 15:54 55-usm.rules

[root@cs2 rules.d]# mv 99-my-asmdevices.rules 99-my-asmdevices.rules.bak

[root@cs2 rules.d]# cat 53-afd.rules
#
# AFD devices
KERNEL=="oracleafd/.*", OWNER="grid", GROUP="asmadmin", MODE="0775"
KERNEL=="oracleafd/*", OWNER="grid", GROUP="asmadmin", MODE="0775"
KERNEL=="oracleafd/disks/*", OWNER="grid", GROUP="asmadmin", MODE="0664"


[root@cs2 rules.d]# ls -l /dev/oracleafd/disks
total 20
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 CRS1
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 CRS2
-rwxrwx--- 1 grid oinstall 15 Aug 30 14:30 DATA1
-rwxrwx--- 1 grid oinstall 15 Aug 30 17:42 DN1
-rwxrwx--- 1 grid oinstall 15 Aug 30 17:42 DN2

其实，AFD 也在使用 udev

迁移包含OCR或vote文件的磁盘组到Oracle ASMFD
1.以root用户来列出包含OCR和vote文件的磁盘组

[root@cs1 ~]# cd /u01/app/product/12.2.0/crs/bin
[root@cs1 bin]# sh ocrcheck -config
Oracle Cluster Registry configuration is :
         Device/File Name         :       +CRS
         Device/File Name         :      +DATA
[root@cs1 bin]# sh crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   750a78e1ae984fcdbfb4dbf44d337a77 (/dev/asmdisk02) [CRS]
Located 1 voting disk(s).

2.以Oracle Grid Infrastructure用户来列出与磁盘组相关的磁盘

[grid@cs2 ~]$ asmcmd lsdsk -G CRS
Path
/dev/asmdisk01
/dev/asmdisk02

3.以root用户来在所有节点上停止数据库与Oracle Clusterware

[root@cs1 bin]# ./crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'cs2'
CRS-2673: Attempting to stop 'ora.crsd' on 'cs1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'cs2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'cs1'
CRS-2673: Attempting to stop 'ora.cs.db' on 'cs1'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'cs2'
CRS-2673: Attempting to stop 'ora.cs.db' on 'cs2'
CRS-2673: Attempting to stop 'ora.chad' on 'cs2'
CRS-2673: Attempting to stop 'ora.gns' on 'cs2'
CRS-2677: Stop of 'ora.gns' on 'cs2' succeeded
CRS-2677: Stop of 'ora.cs.db' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'cs2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'cs2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'cs2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'cs2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'cs2'
CRS-2673: Attempting to stop 'ora.cvu' on 'cs2'
CRS-2673: Attempting to stop 'ora.gns.vip' on 'cs2'
CRS-2677: Stop of 'ora.cs.db' on 'cs1' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'cs1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'cs1'
CRS-2677: Stop of 'ora.DATA.dg' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs2'
CRS-2677: Stop of 'ora.asm' on 'cs2' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'cs1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'cs2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'cs2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'cs2' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cs2.vip' on 'cs2'
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'cs2'
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'cs2'
CRS-2677: Stop of 'ora.gns.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.cs2.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'cs2' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.chad' on 'cs1'
CRS-2677: Stop of 'ora.cvu' on 'cs2' succeeded
CRS-2677: Stop of 'ora.chad' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'cs2'
CRS-2677: Stop of 'ora.ons' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'cs2'
CRS-2677: Stop of 'ora.net1.network' on 'cs2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'cs2' has completed
CRS-2677: Stop of 'ora.crsd' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'cs2'
CRS-2673: Attempting to stop 'ora.evmd' on 'cs2'
CRS-2673: Attempting to stop 'ora.storage' on 'cs2'
CRS-2677: Stop of 'ora.storage' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs2'
CRS-2677: Stop of 'ora.ctssd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.chad' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'cs1'
CRS-2677: Stop of 'ora.mgmtdb' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'cs1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'cs1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'cs1'
CRS-2677: Stop of 'ora.CRS.dg' on 'cs1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs1'
CRS-2677: Stop of 'ora.MGMTLSNR' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cs1.vip' on 'cs1'
CRS-2677: Stop of 'ora.cs1.vip' on 'cs1' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'cs2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'cs2'
CRS-2677: Stop of 'ora.cssd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs1'
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'cs1'
CRS-2677: Stop of 'ora.ons' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'cs1'
CRS-2677: Stop of 'ora.net1.network' on 'cs1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'cs1' has completed
CRS-2677: Stop of 'ora.crsd' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'cs1'
CRS-2673: Attempting to stop 'ora.evmd' on 'cs1'
CRS-2673: Attempting to stop 'ora.storage' on 'cs1'
CRS-2677: Stop of 'ora.storage' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs1'
CRS-2677: Stop of 'ora.ctssd' on 'cs1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'cs1' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'cs1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'cs1'
CRS-2677: Stop of 'ora.cssd' on 'cs1' succeeded

4.以Oracle Grid Infrastructure用户来执行下面的命令为每个Hub节点上的磁盘组中的磁盘进行标记

[grid@cs2 ~]$ asmcmd afd_label DN1 /dev/asmdisk03
[grid@cs2 ~]$ asmcmd afd_label DN2 /dev/asmdisk05

5.以Oracle Grid Infrastructure用户来执行下面的命令对每个Hub节点进行磁盘重新扫描

[grid@cs1 ~]$ asmcmd afd_scan
[grid@cs2 ~]$ asmcmd afd_scan

6.以root用户来在所有节点上启用Oracle Clusterware stack并mount OCR与vote文件磁盘与数据库

[root@cs1 bin]# ./crsctl start cluster -all
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'cs1'
CRS-2672: Attempting to start 'ora.evmd' on 'cs1'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'cs2'
CRS-2672: Attempting to start 'ora.evmd' on 'cs2'
CRS-2676: Start of 'ora.cssdmonitor' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'cs1'
CRS-2672: Attempting to start 'ora.diskmon' on 'cs1'
CRS-2676: Start of 'ora.diskmon' on 'cs1' succeeded
CRS-2676: Start of 'ora.evmd' on 'cs1' succeeded
CRS-2676: Start of 'ora.cssdmonitor' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'cs2'
CRS-2672: Attempting to start 'ora.diskmon' on 'cs2'
CRS-2676: Start of 'ora.diskmon' on 'cs2' succeeded
CRS-2676: Start of 'ora.evmd' on 'cs2' succeeded
CRS-2676: Start of 'ora.cssd' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'cs1'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'cs1'
CRS-2676: Start of 'ora.cssd' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'cs2'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'cs2'
CRS-2676: Start of 'ora.ctssd' on 'cs1' succeeded
CRS-2676: Start of 'ora.ctssd' on 'cs2' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'cs1'
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'cs2'
CRS-2676: Start of 'ora.asm' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'cs2'
CRS-2676: Start of 'ora.asm' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'cs1'
CRS-2676: Start of 'ora.storage' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'cs1'
CRS-2676: Start of 'ora.crsd' on 'cs1' succeeded
CRS-2676: Start of 'ora.storage' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'cs2'
CRS-2676: Start of 'ora.crsd' on 'cs2' succeeded

判断Oracle ASM Filter Driver是否已经配置
可以通过判断Oracle ASM实例的SYS_ASMFD_PROPERTIES的AFD_STATE参数值来判断Oracle ASMFD是否被配置。也可以使用ASMCMD afd_state命令来检查Oracle ASMFD的状态

[grid@cs1 ~]$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'DISABLED' on host 'cs1.jy.net'

[grid@cs2 ~]$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'cs2.jy.net'

下面的查询如果AFD_STATE参数值等于NOT AVAILABLE就表示Oracle ASMFD没有被配置

SQL> select sys_context('SYS_ASMFD_PROPERTIES', 'AFD_STATE') from dual;

SYS_CONTEXT('SYS_ASMFD_PROPERTIES','AFD_STATE')
--------------------------------------------------------------------------- 
CONFIGURED

设置Oracle ASM Filter Driver的AFD_DISKSTRING参数
AFD_DISKSTRING参数来指定Oracle ASMFD磁盘发现路径来标识由Oracle ASMFD来管理的磁盘。也可以使用ASMCMD afd_dsset和afd_dsget命令来设置和显示AFD_DISKSTRING参数:

[grid@cs1 ~]$ asmcmd afd_dsset '/dev/sd*','/dev/asm*','AFD:*'

[grid@cs2 ~]$ asmcmd afd_dsset '/dev/sd*','/dev/asm*','AFD:*'

[grid@cs1 ~]$ asmcmd dsget
parameter:dev/sd*, /dev/asm*, AFD:*
profile:dev/sd*,/dev/asm*,AFD:*

[grid@cs2 ~]$ asmcmd dsget
parameter:dev/sd*, /dev/asm*, AFD:*
profile:dev/sd*,/dev/asm*,AFD:*

可以使用alter system语句来设置AFD_DISKSTRING。标识已经被创建在磁盘头中通过Oracle ASMFD磁盘发现路径来识别磁盘

SQL> ALTER SYSTEM AFD_DISKSTRING SET 'dev/sd*','/dev/asm*','AFD:*';
System altered.

SQL> SELECT SYS_CONTEXT('SYS_ASMFD_PROPERTIES', 'AFD_DISKSTRING') FROM DUAL;

SYS_CONTEXT('SYS_ASMFD_PROPERTIES','AFD_DISKSTRING')
----------------------------------------------------------------------------------- 
dev/sd*,/dev/asm*,AFD:*

为Oracle ASM Filter Driver磁盘设置Oracle ASM ASM_DISKSTRING参数
可以更新Oracle ASM磁盘发现路径来增加或删除Oracle ASMFD 磁盘标识名到ASM_DISKSTRING参数，可以使用alter system语句或asmcmd dsset命令

SQL> show parameter asm_diskstring

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
asm_diskstring                       string                 dev/sd*, /dev/asm*, AFD:*

[grid@cs1 ~]$  asmcmd dsset 'dev/sd*','/dev/asm*','AFD:*'
[grid@cs2 ~]$ asmcmd dsset 'dev/sd*','/dev/asm*','AFD:*'

测试Filter功能
首先检查filter功能是否开启

[grid@cs1 ~]$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'cs1.jy.net'

[grid@jytest1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk03
DATA2                       ENABLED   /dev/asmdisk04
FRA1                        ENABLED   /dev/asmdisk07
TEST1                       ENABLED   /dev/asmdisk05
TEST2                       ENABLED   /dev/asmdisk06

上面的结果显示filter功能已经开启，如果要禁用filter功能执行asmcmd afd_filter -d

[grid@cs1 ~]$ asmcmd afd_filter -d
[grid@cs1 ~]$ asmcmd afd_state
ASMCMD-9526: The AFD state is 'LOADED' and filtering is 'ENABLED' on host 'jytest1.jydba.net'
[grid@cs1 ~]$ asmcmd afd_lsdsk
There are no labelled devices.

如果要开启filter功能执行asmcmd afd_filter -e

[grid@jytest1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk03
DATA2                       ENABLED   /dev/asmdisk04
FRA1                        ENABLED   /dev/asmdisk07
TEST1                       ENABLED   /dev/asmdisk05
TEST2                       ENABLED   /dev/asmdisk06

先用 KFED 读取一下TEST磁盘组的AFD:TEST1的磁盘头，验证一下确实无误

[grid@jytest1 ~]$ kfed read AFD:TEST1
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3275580027 ; 0x00c: 0xc33d627b
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:    ORCLDISKTEST1 ; 0x000: length=13
kfdhdb.driver.reserved[0]:   1414743380 ; 0x008: 0x54534554
kfdhdb.driver.reserved[1]:           49 ; 0x00c: 0x00000031
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203424000 ; 0x020: 0x0c200100
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                   TEST1 ; 0x028: length=5
kfdhdb.grpname:                    TEST ; 0x048: length=4
kfdhdb.fgname:                    TEST1 ; 0x068: length=5
kfdhdb.fgname:                      DN1 ; 0x068: length=3

下面直接用dd尝试将磁盘头清零。dd 命令本身没有任何错误返回。

[root@cs1 ~]# dd if=/dev/zero of=/dev/asmdisk03 bs=1024 count=10000
10000+0 records in
10000+0 records out
10240000 bytes (10 MB) copied, 1.24936 s, 8.2 MB/s

备份磁盘的前1024字节并清除，普通用户没权限读

[root@jytest1 ~]# dd if=/dev/asmdisk05 of=asmdisk05_header bs=1024 count=1
1+0 records in
1+0 records out
1024 bytes (1.0 kB) copied, 0.000282638 s, 3.6 MB/s
[root@jytest1 ~]# ls -lrt
 
-rw-r--r--  1 root root 1024 Aug 31 01:22 asmdisk05_header


[root@jytest1 ~]# dd if=/dev/zero of=/dev/asmdisk05 bs=1024 count=1
1+0 records in
1+0 records out
1024 bytes (1.0 kB) copied, 0.000318516 s, 3.2 MB/s

再用 KFED 读取一下TEST磁盘组的AFD:TEST1的磁盘头，验证一下确实无误

[grid@jytest1 ~]$ kfed read AFD:TEST1
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3275580027 ; 0x00c: 0xc33d627b
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:    ORCLDISKTEST1 ; 0x000: length=13
kfdhdb.driver.reserved[0]:   1414743380 ; 0x008: 0x54534554
kfdhdb.driver.reserved[1]:           49 ; 0x00c: 0x00000031
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203424000 ; 0x020: 0x0c200100
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                   TEST1 ; 0x028: length=5
kfdhdb.grpname:                    TEST ; 0x048: length=4
kfdhdb.fgname:                    TEST1 ; 0x068: length=5

测试dismount磁盘组TEST，再mount磁盘组TEST都能成功

[grid@jytest1 ~]$ asmcmd umount TEST
[grid@jytest1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40960      264                0             264              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    24732                0           24732              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    18452                0           18452              0             N  FRA/
[grid@jytest1 ~]$ asmcmd mount TEST
[grid@jytest1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40960      264                0             264              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    24732                0           24732              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    18452                0           18452              0             N  FRA/
MOUNTED  NORMAL  N         512             512   4096  4194304     40960    11128                0            5564              0             N  TEST/

现在对磁盘/dev/asmdisk05禁用filter功能

[grid@jytest1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk03
DATA2                       ENABLED   /dev/asmdisk04
FRA1                        ENABLED   /dev/asmdisk07
TEST1                       ENABLED   /dev/asmdisk05
TEST2                       ENABLED   /dev/asmdisk06
[grid@jytest1 ~]$ asmcmd afd_filter -d /dev/asmdisk05
[grid@jytest1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk03
DATA2                       ENABLED   /dev/asmdisk04
FRA1                        ENABLED   /dev/asmdisk07
TEST1                      DISABLED   /dev/asmdisk05
TEST2                       ENABLED   /dev/asmdisk06

清除磁盘的前1024字节

 
[root@jytest1 ~]# dd if=/dev/zero of=/dev/asmdisk05 bs=1024 count=1
1+0 records in
1+0 records out
1024 bytes (1.0 kB) copied, 0.000318516 s, 3.2 MB/s

[grid@jytest1 ~]$ asmcmd umount TEST
[grid@jytest1 ~]$ asmcmd mount TEST
ORA-15032: not all alterations performed
ORA-15017: diskgroup "TEST" cannot be mounted
ORA-15040: diskgroup is incomplete (DBD ERROR: OCIStmtExecute)


[grid@jytest1 ~]$ kfed read AFD:TEST1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
000000000 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

可以看到当filter功能被禁用时就失去了保护功能

使用之前备份的磁盘前1024字节信息来恢复磁盘头

[root@jytest1 ~]# dd if=asmdisk05_header of=/dev/asmdisk05 bs=1024 count=1
1+0 records in
1+0 records out
1024 bytes (1.0 kB) copied, 0.000274822 s, 3.7 MB/s

[grid@jytest1 ~]$ kfed  read /dev/asmdisk05
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  1645917758 ; 0x00c: 0x621ab63e
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:    ORCLDISKTEST1 ; 0x000: length=13
kfdhdb.driver.reserved[0]:   1414743380 ; 0x008: 0x54534554
kfdhdb.driver.reserved[1]:           49 ; 0x00c: 0x00000031
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                203424000 ; 0x020: 0x0c200100
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                   TEST1 ; 0x028: length=5
kfdhdb.grpname:                    TEST ; 0x048: length=4
kfdhdb.fgname:                    TEST1 ; 0x068: length=5

再次mount磁盘组TEST

[grid@jytest1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40960      264                0             264              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    24732                0           24732              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    18452                0           18452              0             N  FRA/
[grid@jytest1 ~]$ asmcmd mount TEST
[grid@jytest1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40960      264                0             264              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    24732                0           24732              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     40960    18452                0           18452              0             N  FRA/
MOUNTED  NORMAL  N         512             512   4096  4194304     40960    11120                0            5560              0             N  TEST/

设置，清除与扫描Oracle ASM Filter Driver Labels
给由Oracle ASMFD管理的磁盘设置一个标识，在标识设置后，指定的磁盘将会由Oracle ASMFD来管理。可以使用ASMCMD afd_label,afd_unlabel与afd_scan来增加，删除和扫描标识查看已经标识过的磁盘可以看到磁盘/dev/asmdisk03和 /dev/asmdisk05没有被标识。

[grid@cs1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk04

SQL> select group_number,disk_number,name,label,path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL                                              PATH
------------ ----------- ------------------------------ -------------------------------------------------- --------------------------------------------------
           0           0                                CRS1                                               AFD:CRS1
           0           1                                                                                   /dev/asmdisk05
           0           2                                DATA1                                              AFD:DATA1
           0           3                                                                                   /dev/asmdisk03
           0           4                                CRS2                                               AFD:CRS2
           1           0 CRS1                           CRS1                                               /dev/asmdisk02
           1           1 CRS2                           CRS2                                               /dev/asmdisk01
           2           0 DATA1                          DATA1                                              /dev/asmdisk04

设置标识

[grid@cs2 ~]$ asmcmd afd_label DN1 /dev/asmdisk03
[grid@cs2 ~]$ asmcmd afd_label DN2 /dev/asmdisk05

查看已经标识过的磁盘可以看到磁盘/dev/asmdisk03和 /dev/asmdisk05已经被标识

[grid@cs1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk04
DN1                         ENABLED   /dev/asmdisk03
DN2                         ENABLED   /dev/asmdisk05

SQL> select group_number,disk_number,name,label,path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL                                                          PATH
------------ ----------- ------------------------------ -------------------------------------------------------------- --------------------------------------------------
           0           0                                CRS1                                                           AFD:CRS1
           0           1                                DN2                                                            /dev/asmdisk05
           0           2                                DN1                                                            AFD:DN1
           0           3                                DATA1                                                          AFD:DATA1
           0           4                                DN1                                                            /dev/asmdisk03
           0           6                                CRS2                                                           AFD:CRS2
           0           5                                DN2                                                            AFD:DN2
           1           1 CRS2                           CRS2                                                           /dev/asmdisk01
           1           0 CRS1                           CRS1                                                           /dev/asmdisk02
           2           0 DATA1                          DATA1                                                          /dev/asmdisk04

清除标识

[grid@cs1 ~]$ asmcmd afd_unlabel 'DN1'
[grid@cs1 ~]$ asmcmd afd_unlabel 'DN2'

注意在清除标识时，如果标识所标记的磁盘已经用来创建磁盘组了那么是不能清除的，例如

[grid@cs1 ~]$ asmcmd afd_unlabel 'TEST1'
disk AFD:TEST1 is already provisioned for ASM
No devices to be unlabeled.
ASMCMD-9514: ASM disk label clear operation failed.

扫描标识

[grid@cs1 ~]$ asmcmd afd_scan

查看已经标识过的磁盘可以看到 /dev/asmdisk03和 /dev/asmdisk05的标识已经清除了

[grid@cs1 ~]$ asmcmd afd_lsdsk
--------------------------------------------------------------------------------
Label                     Filtering   Path
================================================================================
CRS1                        ENABLED   /dev/asmdisk02
CRS2                        ENABLED   /dev/asmdisk01
DATA1                       ENABLED   /dev/asmdisk04

SQL> select group_number,disk_number,name,label,path from v$asm_disk;

GROUP_NUMBER DISK_NUMBER NAME                           LABEL                                                          PATH
------------ ----------- ------------------------------ -------------------------------------------------------------- --------------------------------------------------
           0           0                                CRS1                                                           AFD:CRS1
           0           1                                                                                               /dev/asmdisk05
           0           2                                DATA1                                                          AFD:DATA1
           0           3                                                                                               /dev/asmdisk03
           0           4                                CRS2                                                           AFD:CRS2
           1           1 CRS2                           CRS2                                                           /dev/asmdisk01
           1           0 CRS1                           CRS1                                                           /dev/asmdisk02
           2           0 DATA1                          DATA1                                                          /dev/asmdisk04

Oracle ASM使用asmcmd中的cp命令来执行远程复制

Posted on 2018年8月17日 by YongJing(敬勇)

Oracle ASM使用asmcmd中的cp命令来执行远程复制
cp命令的语法如下:

cp src_file [--target target_type] [--service service_name] [--port port_num] [connect_str:]tgt_file

–target target_type是用来指定asmcmd命令执行复制操作必须要连接到的实例的目标类型。有效选项为ASM,IOS或APX。
–service service_name如果缺省值不是+ASM，用来指定Oracle ASM实例名
–port port_num 缺省值是1521，用来指定监听端口

connect_str用来指定连接到远程实例的连接串。connect_str对于本地实例的复制是不需要指定的。对于远程实例复制，必须指定连接串并且会提示输入密码。它的格式如下:
user@host.SID
user,host和SID都是需要指定的。缺省端口为1521，也可以使用–port选项来修改。连接权限(sysasm或sysdba)是由启动asmcmd命令时由–privilege选项所决定的。

src_file 被复制的源文件名，它必须是一个完整路径文件名或一个Oracle ASM别名。在执行asmcmd复制时，Oracle ASM会创建一个OMF文件例如:
diskgroup/db_unique_name/file_type/file_name.#.#
其中db_unique_name被设置为ASM，#为数字。在复制过程中cp命令会对目标地址创建目录结构并对实际创建的OMF文件创建别名。

tgt_file 复制操作所创建的目标文件名或一个别名目录名的别名。

注意，cp命令不能在两个远程实例之间复制文件。在执行cp命令时本地Oracle ASM实例必须是源地址或目标地址。

使用cp命令可以执行以下三种复制操作:
1.从磁盘组中复制文件到操作系统中
2.从磁盘组中复制文件到磁盘组中
3.从操作系统中复制文件到磁盘组中

注意有些文件是不能执行复制的，比如OCR和SPFILE文件。为了备份，复制或移动Oracle ASM SPFILE文件，可以使用spbackup,spcopy或spmove命令。为了复制OCR备份文件，源地址必须是磁盘组。

如果文件存储在Oracle ASM磁盘组中，复制操作是可以跨字节序的(Little-Endian and Big-Endian)。Orale ASM会自动转换文件格式。在非Oracle ASM文件与Oracle ASM磁盘组之间是可以对不同字节序平台进行复制的，在复制完成后执行命令来对文件进行转换操作即可。

首先显示+data/cs/datafile目录中的所有文件

ASMCMD [+data/cs/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  N    jy01.dbf => +DATA/cs/DATAFILE/JY.331.976296525
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    USERS.275.970601909
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    UNDOTBS2.284.970602381
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    UNDOTBS1.274.970601905
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    TEST.326.976211663
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    SYSTEM.272.970601831
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    SYSAUX.273.970601881
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    JY.331.976296525
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    USERS.261.970598319
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    UNDOTBS1.260.970598319
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSTEM.258.970598233
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSAUX.259.970598293

将+data/cs/datafile/JY.331.976296525文件从磁盘组中复制到操作系统中

ASMCMD [+] > cp +data/cs/datafile/JY.331.976296525 /home/grid/JY.bak
copying +data/cs/datafile/JY.331.976296525 -> /home/grid/JY.bak

将操作系统中的文件复制到磁盘组中

ASMCMD [+] > cp /home/grid/JY.bak +data/cs/datafile/JY.bak
copying /home/grid/JY.bak -> +data/cs/datafile/JY.bak

ASMCMD [+] > ls -lt  +data/cs/datafile/
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  N    jy01.dbf => +DATA/cs/DATAFILE/JY.331.976296525
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    USERS.275.970601909
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    UNDOTBS2.284.970602381
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    UNDOTBS1.274.970601905
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    TEST.326.976211663
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    SYSTEM.272.970601831
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    SYSAUX.273.970601881
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  N    JY.bak => +DATA/ASM/DATAFILE/JY.bak.453.984396007
DATAFILE  UNPROT  COARSE   AUG 17 11:00:00  Y    JY.331.976296525
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    USERS.261.970598319
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    UNDOTBS1.260.970598319
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSTEM.258.970598233
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSAUX.259.970598293

将+data/cs/datafile/JY.331.976296525文件从磁盘组中复制到远程ASM实例的磁盘组中

ASMCMD [+] > cp +data/cs/datafile/JY.331.976296525 sys@10.138.130.175.+ASM1:+TEST/JY.bak
Enter password: ***********
copying +data/cs/datafile/JY.331.976296525 -> 10.138.130.175:+TEST/JY.bak

ASMCMD [+test] > ls -lt
Type      Redund  Striped  Time             Sys  Name
                                            N    rman_backup/
                                            N    arch/
                                            Y    JY/
                                            Y    DUP/
                                            Y    CS_DG/
                                            Y    ASM/
DATAFILE  MIRROR  COARSE   AUG 17 16:00:00  N    JY.bak => +TEST/ASM/DATAFILE/JY.bak.342.984413875

将+data/cs/datafile/JY.331.976296525文件从磁盘组中复制到远程ASM实例所在服务器的操作系统中

ASMCMD [+] > cp +data/cs/datafile/JY.331.976296525 sys@10.138.130.175.+ASM1:/home/grid/JY.bak
Enter password: ***********
copying +data/cs/datafile/JY.331.976296525 -> 10.138.130.175:/home/grid/JY.bak

[grid@jytest1 ~]$ ls -lrt
-rw-r----- 1 grid oinstall 104865792 Aug 17 16:21 JY.bak

使用asmcmd cp命令比使用dbms_file_transfer来方便些。

Oracle 12C ASM asmcmd amdu_extract

Posted on 2018年8月16日 by YongJing(敬勇)

amdu_extract命令用来从磁盘组中抽取文件，其语法如下:

ASMCMD [+DATA/cs/DATAFILE] > help amdu_extract
amdu_extract
        Calls AMDU to extract  from 

Synopsis
        amdu_extract   

Description
        The options for amdu_extract command are described below:
        disk group - Name of the disk group containing the file to be extracted.        
        file       - Name of the file to be extracted. Can be specified as either an absolute or a relative path.
        diskstring - Specifies the value for the discovery diskstring.

Examples
        The following example uses amdu_extract to extract the parameter file from the '+datafile' 
        disk group.

        ASMCMD [+] > amdu_extract datafile +datafile/parameterfile/spfile.ora /devices/disk*

其中diskgroup为被抽取文件所在的磁盘组名字，file_name，被抽取文件的Oracle ASM别名，disk_string为Oracle ASM发现磁盘字符串。而且amdu_extract实际是调用的AMDU工具来抽取文件

查询数据库cs的所有数据文件，这里我们要抽取的文件名是JY.331.976296525

ASMCMD [+DATA/cs/DATAFILE] > ls -lrt
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSAUX.259.970598293
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    SYSTEM.258.970598233
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    UNDOTBS1.260.970598319
DATAFILE  UNPROT  COARSE   MAR 12 18:00:00  Y    USERS.261.970598319
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    JY.331.976296525
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    SYSAUX.273.970601881
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    SYSTEM.272.970601831
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    TEST.326.976211663
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    UNDOTBS1.274.970601905
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    UNDOTBS2.284.970602381
DATAFILE  UNPROT  COARSE   AUG 16 11:00:00  Y    USERS.275.970601909

SQL> select name,file_number from v$asm_alias where file_number<>4294967295 and (name like '%jy%' or name like '%JY%');

NAME                                                                                                                                         FILE_NUMBER
-------------------------------------------------------------------------------------------------------------------------------------------- -----------
JY.331.976296525                                                                                                                                     331

ASMCMD [+DATA/cs/DATAFILE] > amdu_extract DATA +DATA/cs/DATAFILE/JY.331.976296525 /dev/asm*
ASMCMD-8024: file number could not be determined for alias name '+DATA/cs/DATAFILE/JY.331.976296525' 
< ?pre>
可以看到提示信息说通过文件别名不能判断文件号，实际上+DATA/cs/DATAFILE/JY.331.976296525是Oracle在ASM中创建的数据文件名，并不是别名。如是我们来手动为+DATA/cs/DATAFILE/JY.331.976296525增加别名jy01.dbf

SQL> alter diskgroup data add alias '+DATA/cs/DATAFILE/jy01.dbf' for '+DATA/cs/DATAFILE/JY.331.976296525';

Diskgroup altered.


SQL> select name,file_number from v$asm_alias where file_number<>4294967295 and (name like '%jy%' or name like '%JY%');

NAME                                                                                                                                         FILE_NUMBER
-------------------------------------------------------------------------------------------------------------------------------------------- -----------
JY.331.976296525                                                                                                                                     331
jy01.dbf                                                                                                                                             331

ASMCMD [+DATA/cs/DATAFILE] > amdu_extract DATA +DATA/cs/DATAFILE/jy01.dbf /dev/asm*
amdu_2018_08_16_19_17_42/

[grid@cs1 ~]$ ls -lrt

drwxr-xr-x 2 grid oinstall     40 Aug 16 19:17 amdu_2018_08_16_19_17_42
[grid@cs1 ~]$ cd amdu_2018_08_16_19_17_42
[grid@cs1 amdu_2018_08_16_19_17_42]$ ls -lrt
total 102416
-rw-r--r-- 1 grid oinstall      8103 Aug 16 19:17 report.txt
-rw-r--r-- 1 grid oinstall 104865792 Aug 16 19:17 DATA_331.f

使用amdu工具来抽取数据文件是指定Oracle ASM发现磁盘字符串和磁盘组.数据文件序号

[grid@cs2 ~]$ amdu -diskstring '/dev/asm*' -extract 'DATA.331'
amdu_2018_08_16_19_20_59/
AMDU-00204: Disk N0004 is in currently mounted diskgroup DATA.
AMDU-00201: Disk N0004: '/dev/asmdisk04'
[grid@cs2 ~]$ cd amdu_2018_08_16_19_20_59/
[grid@cs2 amdu_2018_08_16_19_20_59]$ ls -lrt
total 102420
-rw-r--r-- 1 grid oinstall      8291 Aug 16 19:21 report.txt
-rw-r--r-- 1 grid oinstall 104865792 Aug 16 19:21 DATA_331.f

可以看到使用amdu与asmcmd的amdu_extract所抽取的同一个文件大小都是104865792。

Oracle 12C Database File Mapping for Oracle ASM Files

Posted on 2018年8月13日 by YongJing(敬勇)

为了理解I/O性能，你必须要详细了解存储文件的存储层次信息。Oracle提供了一组动态性能视国来显示文件到逻辑卷中间层到实际的物理设备之间的映射信息。使用这些动态性能视图，可以找到一个文件的任何数据块所内置的实际物理磁盘。Oracle数据库使用一个名叫FMON的后台进程来管理映射信息。Oracle提供了PL/SQL dbms_storage_map包来将映射操作填充到映射视图中。Oracle数据库文件映射当映射Oracle ASM文件时不需要使用第三方的动态库。另外，Oracle数据库支持在所有操作系统平台上对Oracle ASM文件的映射。

对Oracle ASM文件启用文件映射
为了启用文件映射，需要将参数file_mapping设置为true。数据库实例不必关闭来设置这个参数。可以使用以下alter system语句来设置这个参数:

SQL> alter system set file_mapping=true scope=both sid='*';

System altered.

执行合适的dbms_storage_map映射过程
.在冷启动情况下，Oracle数据库在刚刚启动时没有映射操作被调用。可以执行dbms_storage_map.map_all过程来为数据库相关的整个I/O子系统来构建映射信息。例如，下面的命令构建映射信息并且提供10000事件:

SQL> execute dbms_storage_map.map_all(10000);

PL/SQL procedure successfully completed.

.在暖启动情况下，Oracle数据库已经构建了映射信息，可以选择执行dbms_storage_map.map_save过程来将映射信息保存在数据字典中。缺省情况下这个过程将被dbms_storage_map.map_all过程调用，这将强制SGA中的所有映射信息被刷新到磁盘。缺省情况下dbms_storage_map.map_save过程将被dbms_storage_map.map_all()。在重启数据库后，使用dbms_storage_map.restore()过程来还原映射信息到SGA中。如果需要，dbms_storage_map.map_all()可以用来刷新映射信息。

由dbms_storage_map包生成的映射信息会被捕获到动态性能视图中。这些视图包括v$map_comp_list,v$map_element,v$map_ext_element,v$map_file,v$map_file_extent,v$map_file_io_stack,v$map_library与v$map_subelement。
可以使用v$map_file来查询文件映射信息:

SQL> select file_map_idx, substr(file_name,1,45), file_type, file_structure from v$map_file;

FILE_MAP_IDX SUBSTR(FILE_NAME,1,45)                                                                     FILE_TYPE   FILE_STRU
------------ ------------------------------------------------------------------------------------------ ----------- ---------
           0 +DATA/CS/DATAFILE/system.272.970601831                                                     DATAFILE    ASMFILE
           1 +DATA/CS/DATAFILE/sysaux.273.970601881                                                     DATAFILE    ASMFILE
           2 +DATA/CS/DATAFILE/undotbs1.274.970601905                                                   DATAFILE    ASMFILE
           3 +DATA/CS/4700A987085B3DFAE05387E5E50A8C7B/DAT                                              DATAFILE    ASMFILE
           4 +DATA/CS/4700A987085B3DFAE05387E5E50A8C7B/DAT                                              DATAFILE    ASMFILE
           5 +DATA/CS/DATAFILE/users.275.970601909                                                      DATAFILE    ASMFILE
           6 +DATA/CS/4700A987085B3DFAE05387E5E50A8C7B/DAT                                              DATAFILE    ASMFILE
           7 +DATA/CS/DATAFILE/undotbs2.284.970602381                                                   DATAFILE    ASMFILE
           8 +DATA/CS/DATAFILE/test.326.976211663                                                       DATAFILE    ASMFILE
           9 +DATA/CS/DATAFILE/jy.331.976296525                                                         DATAFILE    ASMFILE
          10 +DATA/CS/6C61AD7B443C2CD2E053BE828A0A2A74/DAT                                              DATAFILE    ASMFILE
          11 +DATA/CS/6C61AD7B443C2CD2E053BE828A0A2A74/DAT                                              DATAFILE    ASMFILE
          12 +DATA/CS/6C61AD7B443C2CD2E053BE828A0A2A74/DAT                                              DATAFILE    ASMFILE
          13 +DATA/CS/ONLINELOG/group_2.277.970601985                                                   LOGFILE     ASMFILE
          14 +DATA/CS/ONLINELOG/group_1.278.970601985                                                   LOGFILE     ASMFILE
          15 +DATA/CS/ONLINELOG/group_3.285.970602759                                                   LOGFILE     ASMFILE
          16 +DATA/CS/ONLINELOG/group_4.286.970602761                                                   LOGFILE     ASMFILE
          17 +DATA/CS/ONLINELOG/redo05.log                                                              LOGFILE     ASMFILE
          18 +DATA/CS/ONLINELOG/redo06.log                                                              LOGFILE     ASMFILE
          19 +DATA/CS/ONLINELOG/redo07.log                                                              LOGFILE     ASMFILE
          20 +DATA/CS/ONLINELOG/redo08.log                                                              LOGFILE     ASMFILE
          21 +DATA/CS/ONLINELOG/redo09.log                                                              LOGFILE     ASMFILE
          22 +DATA/CS/ONLINELOG/redo10.log                                                              LOGFILE     ASMFILE
          23 +DATA/CS/TEMPFILE/temp.279.970602003                                                       TEMPFILE    ASMFILE
          24 +DATA/CS/67369AA1C9AA3E71E053BE828A0A8262/TEM                                              TEMPFILE    ASMFILE
          25 +DATA/CS/6C61AD7B443C2CD2E053BE828A0A2A74/TEM                                              TEMPFILE    ASMFILE
          26 +DATA/arch/1_222_970601983.dbf                                                             ARCHIVEFILE ASMFILE
          27 +DATA/arch/1_223_970601983.dbf                                                             ARCHIVEFILE ASMFILE
          28 +DATA/arch/2_277_970601983.dbf                                                             ARCHIVEFILE ASMFILE
          29 +DATA/arch/2_278_970601983.dbf                                                             ARCHIVEFILE ASMFILE
          30 +DATA/arch/2_279_970601983.dbf                                                             ARCHIVEFILE ASMFILE
          31 +DATA/CS/CONTROLFILE/current.276.970601979                                                 CONTROLFILE ASMFILE

31 rows selected.

可以使用dbms_storage_map PL/SQL包中的过程来控制映射操作。例如，可以使用dbms_storage_map.map_object过程通过指定对象名，所有者与类型来对数据库对象来构建映射信息。在dbms_storage_map.map_object过程运行之后，那么可以通过查询map_object视图来查询映射信息

SQL> execute dbms_storage_map.map_object('T1','C##TEST','TABLE');

PL/SQL procedure successfully completed.

SQL> select io.object_name o_name, io.object_owner o_owner, io.object_type o_type,
  2  mf.file_name, me.elem_name, io.depth,
  3  (sum(io.cu_size * (io.num_cu - decode(io.parity_period, 0, 0,
  4  trunc(io.num_cu / io.parity_period)))) / 2) o_size
  5  from map_object io, v$map_element me, v$map_file mf
  6  where io.object_name = 'T1'
  7  and io.object_owner = 'C##TEST'
and io.object_type = 'TABLE'
  8    9  and me.elem_idx = io.elem_idx
 10  and mf.file_map_idx = io.file_map_idx
 11  group by io.elem_idx, io.file_map_idx, me.elem_name, mf.file_name, io.depth,
 12  io.object_name, io.object_owner, io.object_type
 13  order by io.depth;

O_NAME               O_OWNER              O_TYP FILE_NAME                                          ELEM_NAME                 DEPTH     O_SIZE
-------------------- -------------------- ----- -------------------------------------------------- -------------------- ---------- ----------
T1                   C##TEST              TABLE +DATA/CS/DATAFILE/users.275.970601909              +/dev/asmdisk04               0         64

Oracle Linux 7使用syslog来管理Oracle ASM的审计文件

Posted on 2018年8月1日 by YongJing(敬勇)

使用syslog来管理Oracle ASM的审计文件
如果不对Oracle ASM实例的审计文件目录进行定期维护那么它将会包含大量的审计文件。如果存在大理审计文件可能会造成文件系统耗尽磁盘空间或indoes，或者由于文件系统扩展限制而造成Oracle运行缓慢，还有可能造成Oracle ASM实例在启动时hang住。这里将介绍如何使用Linux syslog工具来管理Oracle ASM审计记录，因此通过使用操作系统的syslog工具来代替单独的audit_dump_dest目录来记录Oracle ASM审计记录。下面将介绍具体的操作，而且这些操作必须对于RAC环境中的每个节点执行。
1.对Oracle ASM实例设置audit_syslog_level与audit_sys_operations参数

SQL> show parameter audit_sys_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string

SQL> alter system set AUDIT_SYSLOG_LEVEL='local0.info' scope=spfile sid='*';

System altered.

由于audit_sys_operations参数默认为启用所以不用进行设置了。

2.为Oracle ASM审计配置/etc/syslog.conf
通过执行以下两处改变来对Oracle ASM审计配置syslog的配置文件/etc/syslog.conf或/etc/rsyslog.conf:
2.1在/etc/syslog.conf或/etc/rsyslog.conf文件中增加以下内容

local0.info   /var/log/oracle_asm_audit.log

2.2在/etc/syslog.conf或/etc/rsyslog.conf文件中的/var/log/messages这一行增加local0.none，修改后的配置如下:

*.info;mail.none;authpriv.none;cron.none;local0.none   /var/log/messages

[root@cs1 ~]# vi /etc/rsyslog.conf
 
 ....省略....

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none;local0.none    /var/log/messages
local0.info                                            /var/log/oracle_asm_audit.log


[root@cs2 ~]# vi /etc/rsyslog.conf
 ....省略....

# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none;local0.none    /var/log/messages
local0.info                                            /var/log/oracle_asm_audit.log

3.配置logrotate来管理syslog日志文件
Linux的logrotate工具被用来管理Oracle ASM审计的syslog日志文件的大小与数量，创建文件/etc/logrotate.d/oracle_asm_audit，并向文件增加以下内容:

/var/log/oracle_asm_audit.log {
  weekly
  rotate 4
  compress
  copytruncate
  delaycompress
  notifempty
}

[root@cs1 ~]# cd /etc/logrotate.d/
[root@cs1 logrotate.d]# pwd
/etc/logrotate.d
[root@cs1 logrotate.d]# vi oracle_asm_audit
/var/log/oracle_asm_audit.log {
  weekly
  rotate 4
  compress
  copytruncate
  delaycompress
  notifempty
}

[root@cs2 ~]# cd /etc/logrotate.d/
[root@cs1 logrotate.d]# pwd
/etc/logrotate.d
[root@cs1 logrotate.d]# vi oracle_asm_audit
/var/log/oracle_asm_audit.log {
  weekly
  rotate 4
  compress
  copytruncate
  delaycompress
  notifempty
}

4.重启Oracle ASM实例与rsyslog服务
为了使用这些改变生效必须重启Oracle ASM实例与rsyslog服务。可以使用crsctl stop cluster -all与crsctl start cluster -all在任何一个RAC节点上执行来重启Oracle ASM实例，这个操作会将数据库实例也关闭。

[root@cs1 bin]# /u01/app/product/12.2.0/crs/bin/crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'cs1'
CRS-2673: Attempting to stop 'ora.crsd' on 'cs2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'cs2'
CRS-2673: Attempting to stop 'ora.chad' on 'cs2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'cs1'
CRS-2673: Attempting to stop 'ora.cs.db' on 'cs2'
CRS-2673: Attempting to stop 'ora.cs.db' on 'cs1'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'cs1'
CRS-2673: Attempting to stop 'ora.gns' on 'cs1'
CRS-2677: Stop of 'ora.gns' on 'cs1' succeeded
CRS-2677: Stop of 'ora.cs.db' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'cs2'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'cs2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'cs2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'cs2'
CRS-2677: Stop of 'ora.CRS.dg' on 'cs2' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs2'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.cs2.vip' on 'cs2'
CRS-2673: Attempting to stop 'ora.chad' on 'cs1'
CRS-2677: Stop of 'ora.chad' on 'cs2' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'cs2'
CRS-2677: Stop of 'ora.cs.db' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'cs1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'cs1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'cs1'
CRS-2673: Attempting to stop 'ora.cvu' on 'cs1'
CRS-2673: Attempting to stop 'ora.gns.vip' on 'cs1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'cs1' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'cs1'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'cs1'
CRS-2677: Stop of 'ora.asm' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs2'
CRS-2677: Stop of 'ora.cs2.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.gns.vip' on 'cs1' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'cs2' succeeded
CRS-2677: Stop of 'ora.scan3.vip' on 'cs1' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'cs2'
CRS-2677: Stop of 'ora.scan2.vip' on 'cs1' succeeded
CRS-2677: Stop of 'ora.ons' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'cs2'
CRS-2677: Stop of 'ora.net1.network' on 'cs2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'cs2' has completed
CRS-2677: Stop of 'ora.chad' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'cs1'
CRS-2677: Stop of 'ora.crsd' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'cs2'
CRS-2673: Attempting to stop 'ora.evmd' on 'cs2'
CRS-2673: Attempting to stop 'ora.storage' on 'cs2'
CRS-2677: Stop of 'ora.cvu' on 'cs1' succeeded
CRS-2677: Stop of 'ora.storage' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs2'
CRS-2677: Stop of 'ora.ctssd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.mgmtdb' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'cs1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'cs1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'cs1'
CRS-2677: Stop of 'ora.CRS.dg' on 'cs1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs1'
CRS-2677: Stop of 'ora.evmd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'cs1' succeeded
CRS-2677: Stop of 'ora.MGMTLSNR' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cs1.vip' on 'cs1'
CRS-2677: Stop of 'ora.cs1.vip' on 'cs1' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'cs2'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'cs2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'cs2'
CRS-2677: Stop of 'ora.cssd' on 'cs2' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs1'
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'cs1'
CRS-2677: Stop of 'ora.ons' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'cs1'
CRS-2677: Stop of 'ora.net1.network' on 'cs1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'cs1' has completed
CRS-2677: Stop of 'ora.crsd' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'cs1'
CRS-2673: Attempting to stop 'ora.evmd' on 'cs1'
CRS-2673: Attempting to stop 'ora.storage' on 'cs1'
CRS-2677: Stop of 'ora.storage' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'cs1'
CRS-2677: Stop of 'ora.evmd' on 'cs1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'cs1' succeeded
CRS-2677: Stop of 'ora.asm' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'cs1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'cs1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'cs1'
CRS-2677: Stop of 'ora.cssd' on 'cs1' succeeded


[root@cs1 bin]# /u01/app/product/12.2.0/crs/bin/crsctl start cluster -all
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'cs1'
CRS-2672: Attempting to start 'ora.evmd' on 'cs1'
CRS-2672: Attempting to start 'ora.evmd' on 'cs2'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'cs2'
CRS-2676: Start of 'ora.cssdmonitor' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'cs2'
CRS-2672: Attempting to start 'ora.diskmon' on 'cs2'
CRS-2676: Start of 'ora.cssdmonitor' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'cs1'
CRS-2672: Attempting to start 'ora.diskmon' on 'cs1'
CRS-2676: Start of 'ora.diskmon' on 'cs1' succeeded
CRS-2676: Start of 'ora.evmd' on 'cs1' succeeded
CRS-2676: Start of 'ora.diskmon' on 'cs2' succeeded
CRS-2676: Start of 'ora.evmd' on 'cs2' succeeded
CRS-2676: Start of 'ora.cssd' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'cs2'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'cs2'
CRS-2676: Start of 'ora.cssd' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'cs1'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'cs1'
CRS-2676: Start of 'ora.ctssd' on 'cs2' succeeded
CRS-2676: Start of 'ora.ctssd' on 'cs1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'cs1'
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'cs2'
CRS-2676: Start of 'ora.asm' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'cs2'
CRS-2676: Start of 'ora.asm' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'cs1'
CRS-2676: Start of 'ora.storage' on 'cs1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'cs1'
CRS-2676: Start of 'ora.crsd' on 'cs1' succeeded
CRS-2676: Start of 'ora.storage' on 'cs2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'cs2'
CRS-2676: Start of 'ora.crsd' on 'cs2' succeeded

执行service rsyslog restart命令来重启rsyslog服务

[root@cs1 bin]# service rsyslog restart
Redirecting to /bin/systemctl restart  rsyslog.service
[root@cs1 bin]# service rsyslog status
Redirecting to /bin/systemctl status  rsyslog.service
rsyslog.service - System Logging Service
   Loaded: loaded (/usr/lib/systemd/system/rsyslog.service; enabled)
   Active: active (running) since Wed 2018-08-01 15:13:22 CST; 12s ago
 Main PID: 23011 (rsyslogd)
   CGroup: /system.slice/rsyslog.service
           鈹斺攢23011 /usr/sbin/rsyslogd -n

Aug 01 15:13:22 cs1.jy.net systemd[1]: Started System Logging Service.

[root@cs2 logrotate.d]#  service rsyslog restart
Redirecting to /bin/systemctl restart  rsyslog.service
[root@cs2 logrotate.d]# service rsyslog status
Redirecting to /bin/systemctl status  rsyslog.service
rsyslog.service - System Logging Service
   Loaded: loaded (/usr/lib/systemd/system/rsyslog.service; enabled)
   Active: active (running) since Wed 2018-08-01 15:13:54 CST; 7s ago
 Main PID: 9809 (rsyslogd)
   CGroup: /system.slice/rsyslog.service
           鈹斺攢9809 /usr/sbin/rsyslogd -n

Aug 01 15:13:54 cs2.jy.net systemd[1]: Started System Logging Service.

5.验证Oracle ASM审计记录是否被记录到/var/log/oracle_asm_audit.log中

[root@cs1 bin]# tail -f /var/log/oracle_asm_audit.log
Aug  1 15:13:46 cs1 journal: Oracle Audit[23601]: LENGTH : '317' ACTION :[80] 'begin dbms_diskgroup.close(:handle); exception when others then   raise;   end;
Aug  1 15:13:48 cs1 journal: Oracle Audit[23610]: LENGTH : '244' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs1.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[3] '100'
Aug  1 15:13:50 cs1 journal: Oracle Audit[23654]: LENGTH : '244' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs1.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[3] '100'
Aug  1 15:13:50 cs1 journal: Oracle Audit[23654]: LENGTH : '494' ACTION :[257] 'select name_kfgrp, number_kfgrp, incarn_kfgrp, compat_kfgrp, dbcompat_kfgrp, state_kfgrp, flags32_kfgrp, type_kfgrp, refcnt_kfgrp, sector_kfgrp, blksize_kfgrp, ausize_kfgrp , totmb_kfgrp, freemb_kfgrp, coldmb_kfgrp, hotmb_kfgrp, minspc_kfgrp, usable_kfgrp, ' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs1.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'
Aug  1 15:13:50 cs1 journal: Oracle Audit[23654]: LENGTH : '308' ACTION :[071] 'offline_kfgrp, lflags_kfgrp  , logical_sector_kfgrp  from x$kfgrp_stat
Aug  1 15:13:55 cs1 journal: Oracle Audit[23681]: LENGTH : '244' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs1.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[3] '100'
Aug  1 15:13:56 cs1 journal: Oracle Audit[23681]: LENGTH : '370' ACTION :[132] 'begin dbms_diskgroup.openpwfile(:NAME,:lblksize,:fsz,:handle,:pblksz,:fmode,:genfname);  exception when others then   raise;   end;
Aug  1 15:13:56 cs1 journal: Oracle Audit[23681]: LENGTH : '355' ACTION :[117] 'begin dbms_diskgroup.read(:handle,:offset,:length,:buffer,:reason,:mirr); exception when others then   raise;   end;
Aug  1 15:13:56 cs1 journal: Oracle Audit[23681]: LENGTH : '355' ACTION :[117] 'begin dbms_diskgroup.read(:handle,:offset,:length,:buffer,:reason,:mirr); exception when others then   raise;   end;
Aug  1 15:13:56 cs1 journal: Oracle Audit[23681]: LENGTH : '317' ACTION :[80] 'begin dbms_diskgroup.close(:handle); exception when others then   raise;   end;


[root@cs2 logrotate.d]# tail -f /var/log/oracle_asm_audit.log
Aug  1 15:14:46 cs2 journal: Oracle Audit[9928]: LENGTH : '299' ACTION :[51] 'BEGIN DBMS_SESSION.USE_DEFAULT_EDITION_ALWAYS; END;' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '47'
Aug  1 15:14:46 cs2 journal: Oracle Audit[9928]: LENGTH : '287' ACTION :[39] 'ALTER SESSION SET "_notify_crs" = false' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '42'
Aug  1 15:14:46 cs2 journal: Oracle Audit[9926]: LENGTH : '287' ACTION :[39] 'ALTER SESSION SET "_notify_crs" = false' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '42'
Aug  1 15:14:47 cs2 journal: Oracle Audit[9928]: LENGTH : '292' ACTION :[45] 'SELECT value FROM v$parameter WHERE name = :1' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'
Aug  1 15:14:47 cs2 journal: Oracle Audit[9928]: LENGTH : '292' ACTION :[45] 'SELECT value FROM v$parameter WHERE name = :1' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'
Aug  1 15:14:47 cs2 journal: Oracle Audit[9928]: LENGTH : '292' ACTION :[45] 'SELECT value FROM v$parameter WHERE name = :1' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'
Aug  1 15:14:47 cs2 journal: Oracle Audit[9928]: LENGTH : '292' ACTION :[45] 'SELECT value FROM v$parameter WHERE name = :1' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSRAC' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[10] '1386528187' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[1] '3'
Aug  1 15:15:01 cs2 journal: Oracle Audit[9944]: LENGTH : '244' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[0] '' STATUS:[1] '0' DBID:[0] '' SESSIONID:[10] '4294967295' USERHOST:[10] 'cs2.jy.net' CLIENT ADDRESS:[0] '' ACTION NUMBER:[3] '100'

可以看到Oracle ASM审计记录已经被记录到了/var/log/oracle_asm_audit.log文件中。