Oracle优化器:星型转换

2010/12/27 by 1 Comment

Oracle 8i中引入了星型转换(star transformation)的优化器新特性以便更有效地处理星型查询。星型查询语句多用于基于星型模型设计的数据仓库应用中。星型模型的称谓源于该种模型以图形化表现时看起来形似一颗海星。这颗星的中央会由一个或多个事实表(fact tables)组成，而各个触角上则分布着多个维度表(dimension tables)，如下图：

星型转换的基本思路是尽量避免直接去扫描星型模式中的事实表，因为这些事实表总会因为存有大量数据而十分庞大，对这些表的全表扫描会引起大量物理读并且效率低下。在典型的星型查询中，事实表总是会和多个与之相比小得多的维度表发生连接(join)操作。典型的事实表针对每一个维度表会存在一个外键(foreign key)，除去这些键值(key)外还会存在一些度量字段譬如销售额度(sales amount)。与之对应的键值(key)在维度表上扮演主键的角色。而事实表与维度表间的连接操作一般都会发生在事实表上的外键和与之对应的维度表的主键间。同时这类查询总是会在维度表的其他列上存在限制十分严格的过滤谓词。充分结合这些维度表上的过滤谓词可以有效减少需要从事实表上访问的数据集合。这也就是星型转换(star transformation)的根本目的，仅访问事实表上相关的、过滤后精简的数据集合。

Oracle在Sample Schema示例模式中就存有星型模型的Schema，譬如SH:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------
www.askmac.cn

SQL> conn maclean/maclean
Connected.

SQL> select table_name,comments
  2    from dba_tab_comments
  3   where owner = 'SH'
  4     and table_name in ('SALES', 'CUSTOMERS', 'CHANNELS', 'TIMES');

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------------------------------------------
CHANNELS                       small dimension table
CUSTOMERS                      dimension table
SALES                          facts table, without a primary key; all rows are uniquely identified by the comb
TIMES                          Time dimension table to support multiple hierarchies and materialized views

可以从以上各表的注释(comment)中看到，SALES表是SH模式下一个没有主键的事实表，而CHANNELS、CUSTOMERS、TIMES三个小表充当维度表的角色。我们试着构建以下星型查询语句，该查询用以检索出从1999年12月至2000年2月间Florida州所有城市直销形式的每月销售额。

SQL> col name for a35
SQL> col description for a45
SQL> col value for a8
SQL> select name,value,description from v$system_parameter where name='star_transformation_enabled';

NAME                                VALUE    DESCRIPTION
----------------------------------- -------- ---------------------------------------------
star_transformation_enabled         FALSE    enable the use of star transformation

/* 初始化参数star_transformation_enabled用以控制如何启用星型转换，
    默认为FALSE，该参数可以动态修改
*/

SELECT c.cust_city,
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
  FROM sh.sales s, sh.times t, sh.customers c, sh.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 = 'FL'
   AND ch.channel_desc = 'Direct Sales'
   AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
 GROUP BY c.cust_city, t.calendar_quarter_desc;

SQL> select * from table(dbms_xplan.display_cursor(format => 'IOSTATS'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

SQL_ID  ddjm7k72b8p2a, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ c.cust_city,
t.calendar_quarter_desc,        SUM(s.amount_sold) sales_amount   FROM
sh.sales s, sh.times t, sh.customers c, sh.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 = 'FL'    AND
ch.channel_desc = 'Direct Sales'    AND t.calendar_quarter_desc IN
('2000-01', '2000-02','1999-12')  GROUP BY c.cust_city,
t.calendar_quarter_desc

Plan hash value: 382868716

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      1 |        |     24 |00:00:00.62 |    1735 |   1726 |
|   1 |  HASH GROUP BY                 |           |      1 |     24 |     24 |00:00:00.62 |    1735 |   1726 |
|*  2 |   HASH JOIN                    |           |      1 |   1580 |   6015 |00:00:00.42 |    1735 |   1726 |
|*  3 |    TABLE ACCESS FULL           | CUSTOMERS |      1 |   2438 |   2438 |00:00:01.73 |    1459 |   1455 |
|*  4 |    HASH JOIN                   |           |      1 |   4575 |  74631 |00:00:00.18 |     276 |    271 |
|   5 |     PART JOIN FILTER CREATE    | :BF0000   |      1 |    227 |    182 |00:00:00.04 |      59 |     60 |
|   6 |      MERGE JOIN CARTESIAN      |           |      1 |    227 |    182 |00:00:00.04 |      59 |     60 |
|*  7 |       TABLE ACCESS FULL        | CHANNELS  |      1 |      1 |      1 |00:00:00.01 |       3 |      6 |
|   8 |       BUFFER SORT              |           |      1 |    227 |    182 |00:00:00.02 |      56 |     54 |
|*  9 |        TABLE ACCESS FULL       | TIMES     |      1 |    227 |    182 |00:00:00.02 |      56 |     54 |
|  10 |     PARTITION RANGE JOIN-FILTER|           |      1 |    117K|    117K|00:00:00.09 |     217 |    211 |
|  11 |      TABLE ACCESS FULL         | SALES     |      2 |    117K|    117K|00:00:00.07 |     217 |    211 |
---------------------------------------------------------------------------------------------------------------

可以看到在以上不使用星型转换的执行计划中对事实表SALES执行了全表扫描，这是我们不希望发生的。因为SALES表中每一行记录都对应于一笔销售记录，因此其可能包含数百万行记录。但实际上这其中仅有极小部分是我们在查询中指定的季度在弗罗里达州直销的纪录。若我们启用星型转换，执行计划是否有所改善？

SQL> alter session set star_transformation_enabled=temp_disable;
Session altered.

SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.

在我们的理想当中星型变化会将原查询语句转换成如下形式:

SELECT c.cust_city,
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
  FROM sh.sales s, sh.times t, sh.customers c
 WHERE s.time_id = t.time_id
   AND s.cust_id = c.cust_id
   AND c.cust_state_province = 'FL'
   AND t.calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12')
   AND s.time_id IN
       (SELECT time_id
          FROM sh.times
         WHERE calendar_quarter_desc IN ('2000-01', '2000-02', '1999-12'))
   AND s.cust_id IN
       (SELECT cust_id FROM sh.customers WHERE cust_state_province = 'FL')
   AND s.channel_id IN
       (SELECT channel_id
          FROM sh.channels
         WHERE channel_desc = 'Direct Sales')
 GROUP BY c.cust_city, t.calendar_quarter_desc;

/* 以添加AND..IN的形式明确了利用组合过滤谓词来减少需要处理的数据集 */

通过10053优化trace我们可以了解Oracle优化器是如何真正产生这部分过度谓词的:

FPD: Considering simple filter push in query block SEL$C3AF6D21 (#1)
"S"."CHANNEL_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "CH"."CHANNEL_ID" FROM "SH"."CHANNELS" "CH")
AND "S"."CUST_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "C"."CUST_ID" FROM "SH"."CUSTOMERS" "C") AND
"S"."TIME_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "T"."TIME_ID
FPD: Considering simple filter push in query block SEL$ACF30367 (#4)
"T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02' OR "T"."CALENDAR_QUARTER_DESC"='1999-12'
try to generate transitive predicate from check constraints for query block SEL$ACF30367 (#4)
finally: "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02' OR "T"."CALENDAR_QUARTER_DESC"='1999-12'

FPD: Considering simple filter push in query block SEL$F6045C7B (#3)
"C"."CUST_STATE_PROVINCE"='FL'
try to generate transitive predicate from check constraints for query block SEL$F6045C7B (#3)
finally: "C"."CUST_STATE_PROVINCE"='FL'

FPD: Considering simple filter push in query block SEL$6EE793B7 (#2)
"CH"."CHANNEL_DESC"='Direct Sales'
try to generate transitive predicate from check constraints for query block SEL$6EE793B7 (#2)
finally: "CH"."CHANNEL_DESC"='Direct Sales'

try to generate transitive predicate from check constraints for query block SEL$C3AF6D21 (#1)
finally: "S"."CHANNEL_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "CH"."CHANNEL_ID" FROM "SH"."CHANNELS" "CH")
AND "S"."CUST_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "C"."CUST_ID" FROM "SH"."CUSTOMERS" "C")
AND "S"."TIME_ID"=ANY (SELECT /*+ SEMIJOIN_DRIVER */ "T"."TIME_ID

Final query after transformations:******* UNPARSED QUERY IS *******

最终转换后的查询语句:

SELECT "C"."CUST_CITY" "CUST_CITY",
       "T"."CALENDAR_QUARTER_DESC" "CALENDAR_QUARTER_DESC",
       SUM("S"."AMOUNT_SOLD") "SALES_AMOUNT"
  FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."CUSTOMERS" "C"
 WHERE "S"."CHANNEL_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         "CH"."CHANNEL_ID" "ITEM_1"
          FROM "SH"."CHANNELS" "CH"
         WHERE "CH"."CHANNEL_DESC" = 'Direct Sales')
   AND "S"."CUST_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER */
         "C"."CUST_ID" "ITEM_1"
          FROM "SH"."CUSTOMERS" "C"
         WHERE "C"."CUST_STATE_PROVINCE" = 'FL')
   AND "S"."TIME_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "T"."TIME_ID" "ITEM_1"
          FROM "SH"."TIMES" "T"
         WHERE "T"."CALENDAR_QUARTER_DESC" = '2000-01'
            OR "T"."CALENDAR_QUARTER_DESC" = '2000-02'
            OR "T"."CALENDAR_QUARTER_DESC" = '1999-12')
   AND "S"."TIME_ID" = "T"."TIME_ID"
   AND "S"."CUST_ID" = "C"."CUST_ID"
   AND "C"."CUST_STATE_PROVINCE" = 'FL'
   AND ("T"."CALENDAR_QUARTER_DESC" = '2000-01' OR
       "T"."CALENDAR_QUARTER_DESC" = '2000-02' OR
       "T"."CALENDAR_QUARTER_DESC" = '1999-12')
 GROUP BY "C"."CUST_CITY", "T"."CALENDAR_QUARTER_DESC"

/* 要比我们想想的复杂一些，子查询将IN语句化解了，
    并且AND...ANY的形式追加了过度谓词条件
*/

------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                              | Name             | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                       |                  |       |       |  1710 |           |       |       |
| 1   |  HASH GROUP BY                         |                  |  1254 |   77K |  1710 |  00:00:21 |       |       |
| 2   |   HASH JOIN                            |                  |  1254 |   77K |  1283 |  00:00:16 |       |       |
| 3   |    HASH JOIN                           |                  |  1254 |   45K |   877 |  00:00:11 |       |       |
| 4   |     TABLE ACCESS FULL                  | TIMES            |   227 |  3632 |    18 |  00:00:01 |       |       |
| 5   |     PARTITION RANGE SUBQUERY           |                  |  1254 |   26K |   858 |  00:00:11 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 6   |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES            |  1254 |   26K |   858 |  00:00:11 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 7   |       BITMAP CONVERSION TO ROWIDS      |                  |       |       |       |           |       |       |
| 8   |        BITMAP AND                      |                  |       |       |       |           |       |       |
| 9   |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 10  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 11  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 12  |            TABLE ACCESS FULL           | CHANNELS         |     1 |    13 |     3 |  00:00:01 |       |       |
| 13  |           BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX|       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 14  |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 15  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 16  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 17  |            TABLE ACCESS FULL           | TIMES            |   227 |  3632 |    18 |  00:00:01 |       |       |
| 18  |           BITMAP INDEX RANGE SCAN      | SALES_TIME_BIX   |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 19  |         BITMAP MERGE                   |                  |       |       |       |           |       |       |
| 20  |          BITMAP KEY ITERATION          |                  |       |       |       |           |       |       |
| 21  |           BUFFER SORT                  |                  |       |       |       |           |       |       |
| 22  |            TABLE ACCESS FULL           | CUSTOMERS        |  2438 |   38K |   406 |  00:00:05 |       |       |
| 23  |           BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX   |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 24  |    TABLE ACCESS FULL                   | CUSTOMERS        |  2438 |   62K |   406 |  00:00:05 |       |       |
------------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - access("S"."CUST_ID"="C"."CUST_ID")
3 - access("S"."TIME_ID"="T"."TIME_ID")
4 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
12 - filter("CH"."CHANNEL_DESC"='Direct Sales')
13 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
17 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
18 - access("S"."TIME_ID"="T"."TIME_ID")
22 - filter("C"."CUST_STATE_PROVINCE"='FL')
23 - access("S"."CUST_ID"="C"."CUST_ID")
24 - filter("C"."CUST_STATE_PROVINCE"='FL')

从以上演示中可以看到，星型转换添加了必要的对应于维度表约束的子查询谓词。这些子查询谓词又被称为位图半连接谓词(bitmap semi-join predicates,见SEMIJOIN_DRIVER hint)。通过迭代来自于子查询的键值，再通过位图(bitmap)的AND、OR操作(这些位图可以源于位图索引bitmap index，但也可以取自普通的B*tree索引)，我们可以做到仅仅访问事实表上的查询相关记录。理想状况下维度表上的过滤谓词可以帮我们过滤掉大量的数据，这样就可以使执行计划效率大大提升。当我们获取到事实表上的相关行后，这部分结果集可能仍需要同维度表使用原谓词重复连接(join back)。某些情况下，重复连接可以被省略，之后我们会提到。

如上演示中列出了星型转换后的查询语句的执行计划。这里可以看到Oracle是使用”TABLE ACCESS BY LOCAL INDEX ROWID”形式访问SALES事实表的，而非全表扫描。这里我们仅关心7-23行的执行计划，服务进程分别在(12,17,22)行从维度表中取得各维度表的相关键值(key value)，同时对部分结果集执行了BUFFER SORT操作;在(13,18,23)行的’bitmap index range scan’操作中服务进程从事实表的三个对应于维度表外键的位图索引上(SALES_CHANNEL_BIX,SALES_TIME_BIX,SALES_CUST_BIX)获取了最原始的位图。位图上的每一个bit都对应于事实表上的一行记录。若从子查询中获取的键值(key values)与事实表上的值一致则bit置为1，否则为0。举例而言位图bitmap:[1][0][1][1][0][0][0]..[0](之后都为零)表示事实表上仅有第一、三、四行匹配于由子查询提供的键值。我们假设以上位图是由times表子查询提供的众多键值中的一个(如’2000-01′)的对应于事实表的位图表达式。

接着在执行计划的(10,15,20)行上的’bitmap key iteration’操作会迭代每一个由子查询提供的键值并获取相应的位图。我们假设times表子查询提供的另外2个键值’2000-02’和’1999-12’分别对应的位图为[0][0][0][0][0][1]..[0]和[0][0][0][0][1][0]…[0]即每键值都只有一行符合。

毫无疑问ITERATION迭代操作会为我们生成众多位图，接下来需要对这些不同键值对应的位图进行位图合并操作(BITMAP MERGE，相当于对位图做OR操作)，可以看到在上例执行计划中为(9,14,19)行；以我们假设的times表子查询位图合并而言，会生产一个简单的位图[1][0][1][1][1][1][0][0]..[0]，这个位图对应事实表上的第一、三、四、五、六行，是对’2000-01′,’2000-02′,’1999-12’三个键值对应位图的合并。

在获得最后位图前我们还需要对来自于三个子查询的位图进一步处理，因为原始查询语句中各约束条件是AND与的形式，因此我们还要对这些已合并的位图执行AND与操作，如执行计划中的第八行”BITMAP AND”，因为是AND与操作所以这步又会过滤掉大量记录。我们假设最终获得的位图是[1][0][1][0]…[0]，即仅有第一、三行。

通过最终bitmap位图Oracle可以极高效地生成事实表的ROWID，此步骤表现为第七行的”BITMAP CONVERSION TO ROWIDS”，我们使用这些ROWID来访问事实表取得少量的”绝对”相关记录。以我们的假设而言最终位图仅有2位为1，只需要用这2行的ROWID从事实表上直接fetch2条记录即可，从而避免了低效的全表扫描。

省略重复连接

因为子查询及位图树只是通过维度表上的过滤条件为事实表过滤掉大量的数据，所以从事实表上获取的相关数据仍可能需要重复一次和维度表的连接。省略重复连接的前提是维度表上所有的谓词都是半连接谓词子查询的一部分，And 由子查询检索到的列均唯一(unique) And 维度表的列不被select或group by涉及。在上例中无需对CHANNELS表再次连接的理由是没有select(或group by)CHANNEL表上的列，且channel_id列是唯一的。

临时表转换

若在已知星型转换中重复连接维度表无法被省略的话，Oracle可以将对维度表的子查询结果集存储到内存中的全局临时表(global temporary table)上以避免重复扫描维度表。此外，因为将子查询的结果集物化了，故而若使用并行查询则每个并行子进程(slave)可以直接从物化结果集的临时表中获得数据，而不需要反复执行子查询。

试看以下示例，了解Oracle是如何利用物化临时表避免反复连接的:

SQL> alter session set star_transformation_enabled=true;
Session altered.

SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.

SELECT "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_0FD9D660E_1DF5D6" "T1"
 WHERE "S"."CUST_ID" = ANY (SELECT /*+ SEMIJOIN_DRIVER CACHE_TEMP_TABLE ("T1") */
         "T1"."C0" "C0"
          FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1")
   AND "S"."CHANNEL_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "CH"."CHANNEL_ID" "ITEM_1"
          FROM "SH"."CHANNELS" "CH"
         WHERE "CH"."CHANNEL_DESC" = 'Direct Sales')
   AND "S"."TIME_ID" = ANY
 (SELECT /*+ SEMIJOIN_DRIVER */
         "T"."TIME_ID" "ITEM_1"
          FROM "SH"."TIMES" "T"
         WHERE "T"."CALENDAR_QUARTER_DESC" = '2000-01'
            OR "T"."CALENDAR_QUARTER_DESC" = '2000-02'
            OR "T"."CALENDAR_QUARTER_DESC" = '1999-12')
   AND "S"."TIME_ID" = "T"."TIME_ID"
   AND "S"."CUST_ID" = "T1"."C0"
   AND ("T"."CALENDAR_QUARTER_DESC" = '2000-01' OR
       "T"."CALENDAR_QUARTER_DESC" = '2000-02' OR
       "T"."CALENDAR_QUARTER_DESC" = '1999-12')
 GROUP BY "T1"."C1", "T"."CALENDAR_QUARTER_DESC"

以上为启用临时表后的星型转换后的查询语句，相应的执行计划如下:
---------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                               | Name                     | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
---------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                        |                          |       |       |   911 |           |       |       |
| 1   |  TEMP TABLE TRANSFORMATION              |                          |       |       |       |           |       |       |
| 2   |   LOAD AS SELECT                        |                          |       |       |       |           |       |       |
| 3   |    TABLE ACCESS FULL                    | CUSTOMERS                |  2438 |   62K |   406 |  00:00:05 |       |       |
| 4   |   HASH GROUP BY                         |                          |  1254 |   64K |   506 |  00:00:07 |       |       |
| 5   |    HASH JOIN                            |                          |  1254 |   64K |   479 |  00:00:06 |       |       |
| 6   |     HASH JOIN                           |                          |  1254 |   45K |   475 |  00:00:06 |       |       |
| 7   |      TABLE ACCESS FULL                  | TIMES                    |   227 |  3632 |    18 |  00:00:01 |       |       |
| 8   |      PARTITION RANGE SUBQUERY           |                          |  1254 |   26K |   456 |  00:00:06 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 9   |       TABLE ACCESS BY LOCAL INDEX ROWID | SALES                    |  1254 |   26K |   456 |  00:00:06 | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 10  |        BITMAP CONVERSION TO ROWIDS      |                          |       |       |       |           |       |       |
| 11  |         BITMAP AND                      |                          |       |       |       |           |       |       |
| 12  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 13  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 14  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 15  |             TABLE ACCESS FULL           | CHANNELS                 |     1 |    13 |     3 |  00:00:01 |       |       |
| 16  |            BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX        |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 17  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 18  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 19  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 20  |             TABLE ACCESS FULL           | TIMES                    |   227 |  3632 |    18 |  00:00:01 |       |       |
| 21  |            BITMAP INDEX RANGE SCAN      | SALES_TIME_BIX           |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 22  |          BITMAP MERGE                   |                          |       |       |       |           |       |       |
| 23  |           BITMAP KEY ITERATION          |                          |       |       |       |           |       |       |
| 24  |            BUFFER SORT                  |                          |       |       |       |           |       |       |
| 25  |             TABLE ACCESS FULL           | SYS_TEMP_0FD9D660E_1DF5D6|  2438 |   12K |     4 |  00:00:01 |       |       |
| 26  |            BITMAP INDEX RANGE SCAN      | SALES_CUST_BIX           |       |       |       |           | KEY(SUBQUERY)| KEY(SUBQUERY)|
| 27  |     TABLE ACCESS FULL                   | SYS_TEMP_0FD9D660E_1DF5D6|  2438 |   36K |     4 |  00:00:01 |       |       |
---------------------------------------------------------------------------+-----------------------------------+---------------+

Predicate Information:
----------------------
3 - filter("C"."CUST_STATE_PROVINCE"='FL')
5 - access("S"."CUST_ID"="C0")
6 - access("S"."TIME_ID"="T"."TIME_ID")
7 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
15 - filter("CH"."CHANNEL_DESC"='Direct Sales')
16 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
20 - filter(("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR "T"."CALENDAR_QUARTER_DESC"='2000-02'))
21 - access("S"."TIME_ID"="T"."TIME_ID")
26 - access("S"."CUST_ID"="C0")

从以上trace中可以看到系统命名的临时表SYS_TEMP_0FD9D660E_1DF5D6缓存CUSTOMERS表，之后原先CUSTOMERS表被SYS_TEMP_0FD9D660E_1DF5D6所取代，原CUSTOMERS表上的cust_id和cust_city列均被替换为别名为T1的临时表的C0和C1列。实际上该临时表也仅需要这2列即可满足计划的需求，所以该临时表以如下查询语句填充:

ST: Subquery text:******* UNPARSED QUERY IS *******
SELECT "C"."CUST_ID" "ITEM_1","C"."CUST_CITY" "ITEM_2" FROM "SH"."CUSTOMERS" "C" WHERE "C"."CUST_STATE_PROVINCE"='FL'
Copy query block qb# -1 () : SELECT /*+ CACHE_TEMP_TABLE(T1) */  "C0" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" T1
ST: Subquery (temp table) text:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "T1"."C0" "C0" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1"
Copy query block qb# -1 () : SELECT /*+ CACHE_TEMP_TABLE(T1) */  "C0", "C1" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" T1
ST: Join back qbc text:******* UNPARSED QUERY IS *******
SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "T1"."C0" "C0","T1"."C1" "C1" FROM "SYS"."SYS_TEMP_0FD9D660E_1DF5D6" "T1"

可以从以上执行计划中看到第一、二、三行的”TEMP TABLE TRANSFORMATION LOAD AS SELECT TABLE ACCESS FULL CUSTOMERS”看到Oracle是如何将子查询物化为临时表的。在第25行，Oracle直接以该临时表替代了子查询来构建我们所需要的位图。到第27行Oracle直接利用该临时表来重复连接，避免再次扫描customers表。因为我们在构建临时表时已经使用谓词条件(如上面的红字语句)，故而我们无需对临时表再次过滤。

如何启用星型查询

星型转换由初始化参数star_transformation_enabled控制，该参数可以有三种选项:

  • TRUE: Oracle优化器自动识别语句中的事实表和约束维度表并进行星型转换。这一切优化尝试都在CBO的藩篱内，优化器需要确定转换后的执行计划成本要低于不转换的执行计划；同时优化器还会尝试利用物化的临时表，如果那样真的好的话。
  • False: 优化器不会考虑星型转换。
  • TEMP_DISABLE:当一个维度表超过100个块时，”如果简单地设置star_transformation_enabled为TRUE来启用星型变换，那么会话会创建一个内存中的全局临时表(global temporary table)来保存已过滤的维度数据，这在过去会造成很多问题;”这里说的100个块其实是隐式参数_temp_tran_block_threshold(number of blocks for a dimension before we temp transform)的默认值，此外隐式参数_temp_tran_cache(determines if temp table is created with cache option，默认为TRUE)决定了这类临时表是否被缓存住；为了避免创建全局临时表可能带来的问题，就可以用到TEMP_DISABLE这个禁用临时表的选项，让优化器不再考虑使用物化的临时表。

默认该参数为False，若要问这是为什么？因为星型转换适用的场景是数据仓库环境中具有星型模型的模式，而且需要事实表的各个连接列上均有良好的索引时才能发挥其优势。如果能确定以上因素，那么我们可以放心的使用星型转换了，把star_transformation_enabled改为true或temp_disable吧！

总结

星型转换可以有效改善大的事实表与多个具有良好选择率的维度表间连接的查询。星型转换有效避免了全表扫描的性能窘境。它只fetch那些事实表上的”绝对”相关行。同时星型转换是基于CBO优化器的，Oracle能很好地认清使用该种转换是否有利。一旦维度表上的过滤无法有效减少需要从事实表上处理的数据集和时，那么可能全表扫描相对而言更为恰当。

以上我们力图通过一些简单的查询和执行计划来诠释星型转换的基本理念，但现实生产环境中实际的查询语句可能要复杂的多；举例而言如果查询涉及星型模型中的多个事实表的话，那么其复杂度就大幅提高了；如何正确构建事实表上的索引，收集相关列上的柱状图信息，在Oracle优化器无法正确判断的情况下循循善诱，都是大型数据仓库环境中DBA所面临的难题。

Filed Under: Oracle, Oracle SQL性能调优 Tagged With: , , , , ,

Latches and Tuning:Redo Log Buffer and Latches

2010/12/26 by Leave a Comment

1. The size of the redo log buffer is determined by the LOG_BUFFER parameter.

a.  Determine the size of the LOG_BUFFER by executing the following query:> select * from v$sga;

b.  View the LOG_BUFFER setting in the init.ora file.

2.  There are three latches associated with the redo log buffer.

a.  Execute the following query to view the latches:> select name from v$latchname where name like ‘redo%’;

There will be more discussion about these latches later in the module.

3.  A number of parameters that effect latch performance have been made obsolete in Oracle8i.
a.  Execute the following query to view the obsolete redo buffer parameters:

> select ksppinm from x$ksppi where ksppinm like ‘_log%’;

Of the parameters listed, _LOG_SIMULTANEOUS_COPIES  and _LOG_IO_SIZE are now obsolete.  LOG_SMALL_ENTRY_MAX_SIZE and LOG_ENTRY_PREBUILD_THRESHOLD are no longer available in Oracle8i.

_LOG_SIMULTANEOUS_COPIES effects the redo copy latch, but since the redo copy is always acquired there is no need to set this parameter.
_LOG_IO_SIZE effects the redo writing latch.

b.  Execute the following query to view the redo related parameters that are still available in Oracle8i:

> select name from v$system_parameter where name like ‘log%’;

You should see the parameters log_buffer, log_checkpoint_interval, and log_checkpoint_timeout.

4. There are 10 wait events associated with the redo log buffer and latches.

a.  View the wait events by executing the following query:> select name from v$event_name where name like ‘log%’;

Of the events that are listed, three are of primary importance:

Log buffer space: a process is waiting for space in the log buffer
Log file sync: the time it takes for LGWR to complete the write then post the requester.
Log file parallel write: the main event waited while writing the redo records to the current log file.

b. You can also gather event information from the V$SYSTEM_EVENT view by executing the following query:

> select event,total_waits,total_timeouts,time_waited from v$system_event where
event in (‘log buffer space’,’log file sync’,’log file parallel write’);

In a well tuned system redo-related waits should be none or minimal.  If one of the events is not listed in the output that means no event has occurred.

5.  There are 14 statistics associated with the redo log buffer and latches.

a.  Execute the following query to view the statistics:> select name from v$sysstat where name like ‘redo%’;

The most important statistics are:

Redo Writes: number of times the log buffer is written.
Redo Blocks Written: total number of blocks written.
Redo Log Space Requests: number of times requested to allocate space in the log file.
Redo Size, Redo Entries: how much redo is generated since instance statrup
Redo Buffer Allocation Retries: total number of retries necessary to allocate space in the redo buffer.

6.  Contention for redo log buffers does not generally cause major performance problems on the database. Waits for
the event log buffer space could indicate contention for the redo log buffers.  However, it may be more useful to find
out the proportion of redo entries which had to wait for space to become available.
a.  Execute the following query to determine the ratio:

> select r.value, s.value, r.value/s.value
from v$sysstat r, v$sysstat s
where r.name = ‘redo buffer allocation retries’ and
s.name = ‘redo entries’;;

The ratio should be near zero.

7. Remember from the first practice that there are three latches associated with the redo log buffer; redo allocation,
redo copy, and redo writing.  The tuning goal is to minimize contention and waits for these latches.

a.  To tune the latches, look at the latch statistics and the ratio of MISSES to GETS by executing the following query:> select n.name, gets, misses, immediate_gets, immediate_misses
from v$latch l, v$latchname n
where n.name in (‘redo allocation’,’redo copy’,’redo writing’)
and n.latch#= l.latch#;

If the ratio of MISSES to GETS exceeds 1%, or the ratio of IMMEDIATE_MISSES to (IMMEDIATE_MISSES+IMMEDIATE_GETS) exceeds 1%, there is latch contention.

To return the ratios you can execute the following query.  However, you may receive a division by zero error.

> select n.name, gets, misses, immediate_gets, immediate_misses, (misses/gets),
(immediate_misses/(immediate_misses+immediate_gets))
from v$latch l, v$latchname n
where n.name in (‘redo allocation’,’redo copy’,’redo writing’)
and n.latch#= l.latch#;

8.  You can increase the number of redo copy latches by setting the parameter LOG_SIMULTANEOUS_COPIES.
This parameter is a hidden parameter and must be set in the init.ora and the database must be restarted for it
to take effect.

a.  One method for determing the number of redo copy latches is to query the V$LATCH_CHILDREN view.  There will be one latch for the value of _LOG_SIMULTANEOUS_COPIES.  If there is only one latch, you will have to query V$LATCH_PARENT as there will be no children.> select name from v$latch_children where name = ‘redo copy’;

b.  If you want to increase or decrease the number of redo copy latches, modify the parameter _LOG_SIMULTANEOUS_COPIES in the init.ora file.  Once you have done this, you can bounce the
database and execute the query in step a.

9.  You can increase the number of redo writing latches by modifying the parameter _LOG_IO_SIZE.  This
parameter is a hidden parameter and must be set in the init.ora and the database must be restarted for it to take
effect.
a.  One method for determing the number of redo copy latches is to query the V$LATCH_CHILDREN view.  There will be one latch for the value of _LOG_IO_SIZE.  If there is only one latch, you will have to query V$LATCH_PARENT as there will be no children.

> select name from v$latch_children where name = ‘redo writing’;

b.  If you want to increase or decrease the number of redo copy latches, modify the parameter
_LOG_IO_SIZE in the init.ora file.  Once you have done this, you can bounce the database and execute the
query in step a.

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , , ,

Latches and Tuning:Buffer Cache

2010/12/26 by Leave a Comment

1.  The buffer pool is structured into pools; RECYCLE, KEEP, and DEFAULT.  Unless otherwise specified at create time or altered, an object will reside in the DEFAULT pool.

a. Suppose you have a table that is frequently updated, but is a relatively small table.  You would want to ensure the blocks for this table are stored in the KEEP pool of the buffer cache.  Execute the following command to create the table TEST1 in the KEEP pool.> create table TEST1 (n number) storage (buffer_pool KEEP);

b.  Suppose you have a large table that is randomly accessed.  You want to ensure the blocks for this table are stored in the RECYCLE pool.  Execute the following command to create the table TEST2 in the RECYCLE pool:

> create table TEST2 (n nmber) storage (buffer_pool RECYCLE);

c.  Suppose you have a table that is subjected to normal access. You want to ensure the blocks for this table are stored in the DEFAULT pool. Execute the following command to create the table TEST3 in the DEFAULT pool:

> create table TEST3 (n number) storage (buffer_pool DEFAULT);

d.  Query the USER_TABLES view to see the table names and the pools to which they are assigned:

> select table_name, buffer_pool from user_tables where table_name like ‘TEST%’;

You can use the ALTER TABLE command to change the buffer pool for a table.

2. Various statistics are incremented during buffer management.
a.  Execute the following query to view the relevants statistics:

> select * from v$sysstat where name in (‘dirty buffers inspected’,’free buffer inspected’);

– dirty buffers inspected: incremented when dirty buffers are read and moved to the LRUW list.
– free buffers inspected: incremented when free buffers are detected during the search for a free buffer on the LRU list.

3. The parameter DB_BLOCK_LRU_LATCHES defines the number of working sets for the instance.

a.   Determine the value of DB_BLOCK_LRU_LATCHES by executing the following query:> select name,value from v$system_parameter where name = ‘db_block_lru_latches’;

4.  There is one working set per DB_BLOCK_LRU_LATCHES.

a.  Query the X$KCBWDS view to determine the number of working sets for the instance.> select count(*) from x$kcbwds;

The returned values should be the same as the value for DB_BLOCK_LRU_LATCHES.

5.  Each working set also has its own checkpoint latch.

a.  Execute the following query to view the checkpoint latch and set id’s for the instance:> select addr,set_id,ckpt_latch,set_latch,dbwr_num from x$kcbwds;

b.  Using one of the ckpt_latch values, execute the following query:

> select * from v$latch_children where addr = <address from query>;

With this you can see that there is one child latch for each checkpoint latch.

6.  The maximum number of DBWR cannot be more than the number of working sets.
a.  Set the number of DBWR processes to a value greater than DB_BLOCK_LRU_LATCHES.  Do this by changing the DB_WRITER_PROCESSES value in the init.ora.

b.  Bounce your database instance.

c.  Execute the following query:

> select dbwr_num from x$kcbwds;

You will see that the number of DBWR processes is equal to the number DB_BLOCK_LRU_LATCHES.

7. The number of DBWR processes are set using the DB_WRITER_PROCESSES parameter.

a.  View your init.ora file and determine if the number of DB_WRITER_PROCESSES has been explicitly set.

8.  Two parameters effect the way DBWR scans for purposes of making free buffers.

a  Execute the following query:

> select ksppinm from x$ksppi where ksppinm like ‘_db%scan%’;

_DB_WRITER_SCAN_DEPTH – Sets the scan depth for DBWR
_DB_BLOCK_MAX_SCAN_CNT – Sets the foregrond scan depth

The maximum number of dirty buffers in the write list, which will not cause DBWR to write is
max(DB_BLOCK_BUFFERS/8, 2*write-batch-size

9. A target value for the buffer cache hit% is 90.

a.  Determine the buffer cache % by executing the following query:> select 1 – (phy.value / (log.value + con.value )) “Buffer Cache Hit%”
from v$sysstat phy, v$sysstat log, v$sysstat con
where phy.name = ‘physical reads’ and
log.name = ‘db block gets’ and
con.name = ‘consistent gets’;

Descriptions of the buffer cache statistics used are:

– db block gets: tracks the number of blocks obtained in CURRENT mode.
– physical reads: stores the number of physical blocks when the OS retrieves a database block from disk
– consistent gets: number of times a consistent read was requested for a block.

Other buffer cache statistics include:

– physical writes
– db block changes
– free buffer requested
– dirty buffers inspected
– free buffer inspected
– CR blocks created

10.  Numerous statistics are associated with the DBWR process.

a.  Execute the following query to view the statistics associated with the DBWR process:> select name from v$sysstat where name like ‘DBWR%’;

Of the statistics listed two of the most important are DBWR checkpoints and DBWR make free requests.

11.  The number of cache buffers chains latches is equal to prime(db_block_buffers/4).  The number of cache buffers
lru chain latches is equal to the value of DB_BLOCK_LRU_LATCHES.

a.  Execute the following query to determine the approximate number of cache buffers chains latches:> select (value/4) from v$system_parameter where name = ‘db_block_buffers’;

b.  The value from the above should be approximately equal to the row count returned from the following
query:

> select count(*) from v$latch_children where name = ‘cache buffers chains’;

c.  Execute the following query to determine the number of cache buffers lru chain latches:

> select count(*) from v$latch_children where name = ‘cache buffers lru chain’;

d.  Check the value of DB_BLOCK_LRU_LATCHES and it should equal the row count returned from the first query.

12.  Wait events are also important for diagnosing buffer cache problems.  It’s the time_waited for each
event that is used for diagnostics.
a.  Execute the following query to view the two primary events associated with tuning the buffer cache:

> select * from v$system_event
where name in (‘buffer busy waits’,’free buffer waits’)
order by time_waited;

You may not see free buffer waits in the result set if there has been no wait time for this event.  It is listed in V$EVENT_NAME.

b.  Additional wait information can be derived from the views V$SESSION_WAIT and V$WAITSTAT views, and the X$KCBFWAIT table.  Query the V$WAITSTAT view to list the different types of
blocks:

> select class from v$waitstat;

Some of the most likely reasons for each block type are:

Segment Header – not enough free lists and high number of inserts
Data Blocks – too many rows in each block
Freelist Blocks – not enough free lists
Undo Header – too many transactions per rollback segment
Undo Blocks – not enough rollback segment blocks

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , ,

Veritas ODM Async and Oracle

2010/12/26 by 1 Comment

简单来说对于Veritas Vxfs和ODM FILE而言filesystemio_options不生效(This parameter is not applicable to VxFS files, ODM files, or Quick I/O files.)，必要检查ODM SO是否正确LINK。

 

ls -l $ORACLE_HOME/lib/libodm*

 

 

对于JFS2而言，一般建议设置filesystemio_options=SETALL

(Since the version 10g, Oracle will open data files located on the JFS2 file system with the O_CIO option if the filesystemio_options initialization parameter is set to either directIO or setall.)

 

 

Symptoms

Disk utility output shows 100% usage for disk continously:

ProcList CPU Rpt Mem Rpt Disk Rpt NextKeys SlctProc Help Exit GlancePlus C.04.50.00 19:15:39 bplita3 ia64 Current Avg High ———————————————————————- CPU Util S SN NU U | 90% 90% 90% Disk Util F F |100% 100% 100% <=== Disk Too heavily loaded
In Statspack or AWR report, high ‘free buffer waits’ seen even after with considerably increasing db_cache_size and db writers.

Statspack Report Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 99.99 Buffer Hit %: 58.92 In-memory Sort %: 99.82 Library Hit %: 89.19 Soft Parse %: 83.51 Execute to Parse %: 50.76 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 81.48 % Non-Parse CPU: 93.21 …. Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time ——————————————– ———— ———– ——– free buffer waits 76,390 74,807 55.82 enqueue 4,071 11,625 8.67
…..

 

Cause
The problem is related to Disk I/O.

Solution

 

1. Use the O/S striping software

Try to use the O/S striping software to distribute database files over as many disks as you can.

2. Use Direct IO

Mount the filesystem with direct IO option.
For example:

% Mount –F vxfs –o remount,nodatainlog,mincache=direct,convosync=direct /dev/vg00/lv_ora /soft/oracle

mincache and convosync

“mincache=direct” => bypass buffer cache on read
“convosync=direct” => force direct I/O for DB writers

Mincache=direct and convosync=direct allow data to be transferred directly from Oracle buffer cache to disk and disk to Oracle buffer cache. This avoids double buffering by bypassing the file system buffer cache and can improve physical read/write performance. However, cases where the disk read could have been avoided because a required block was in file system buffer cache may be negatively impacted.

If your filesystem is mounted with this option, then FILESYSTEMIO_OPTIONS default setting of ASYNCH can be used in order to use DIO.

Parameters in Oracle influencing the use of Direct IO

FILESYSTEMIO_OPTIONS defines the IO operations on filesystem files .This parameter should not normally be set by the user.
The value may be any of the following:
asynch – Set by default on HP. This allows asynchronous IO to be used where supported by the OS.
directIO – This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache.
setall – Enables both ASYNC and DIRECT IO.
none – This disables ASYNC IO and DIRECT IO so that Oracle uses normal synchronous writes, without any direct io options.
See

Document 120697.1 Init.ora Parameter “FILESYSTEMIO_OPTIONS” Reference Note
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES or DB_WRITER_PROCESSES to a value other than its default of zero in order to simulate asynchronous I/O.
DB_WRITER_PROCESSES or DBWR_IO_SLAVES
see comments in DISK_ASYNCH_IO

Again the default setting of ASYNCH can be used when implementing direct I/O on HP-UX / Veritas.

3. Concurrent I/O

An alternative solution to Direct I/O is to use Concurrent I/O. Concurrent I/O is available in OnlineJFS 5.0.1.

To enable Concurrent I/O, the filesystem must be mounted with “-o cio”.
Eg:

mount -F vxfs -o nodatainlog,cio /soevxfs/redos /oracle/mnt/redos

Please note that remount should not be used to enable Concurrent I/O on mounted filesystems.

“-o cio”

Concurrent I/O allows multiple processes to read from or write to the same file without blocking other read(2) or write(2) calls.With Concurrent I/O, the read and write operations are not serialized. This advisory is generally used by applications that require high performance for accessing data and do not perform overlapping writes to the same file. It is the responsibility of the application or the running threads to coordinate the write activities to the same file. It also avoids double buffering by bypassing the filesystem buffer cache and thus improves physical read/write performance significantly. Concurrent I/O performs very close to that of raw logical volumes.

 

The value may be any of the following:
“asynch” – This allows asynchronous IO to be used where
supported by the OS.
“directIO” – This allows directIO to be used where
supported by the OS. Direct IO bypasses any
Unix buffer cache. As of 10.2 most platforms
will try to use “directio” option for NFS
mounted disks (and will also check NFS
attributes are sensible).
“setall” – Enables both ASYNC and DIRECT IO.
“none” – This disables ASYNC IO and DIRECT IO so that
Oracle uses normal synchronous writes, without
any direct io options.

 

We assume Veritas ODM driver is installed, mounted and available.

The following steps to enable/disable ODM for Oracle database (note that with different versions of the Solaris OS, the path may change to the ODM files).

Enable ODM

Log in as oracle user.

i) Shutdown the database

ii) Change directories:
$ cd $ORACLE_HOME/lib

iii) Take a backup of existing original ODM library

PA systems

$ mv $ORACLE_HOME/lib/libodm10.sl $ORACLE_HOME/lib/libodm10.sl.org

IA systems

$ mv $ORACLE_HOME/lib/libodm10.so $ORACLE_HOME/lib/libodm10.so.org

(* Note 9i library name ie libodm9.so,10g is libodm10.so and 11g is libodm11.so)

iv) create a soft link to veritas ODM library

PA systems

ln -s /opt/VRTSodm/lib/libodm.sl $ORACLE_HOME/lib/libodm10.sl

IA systems

ln -s /opt/VRTSodm/lib/libodm.so $ORACLE_HOME/lib/libodm10.so

iv) Start the database and check

Once the database instance is enabled with ODM, the following message is displayed in the Oracle alert log:

Example :-

???￠????????Oracle instance running with ODM: VERITAS 4.1 ODM Library, Version 1.1???￠????????
Disable ODM

Log in as oracle user.

i) Shutdown the database

ii) Change directories:
$ cd $ORACLE_HOME/lib
iii) Remove the softlink

$ cd $ORACLE_HOME/lib

PA systems
$ rm libodm10.sl

IA systems
$ rm libodm10.so
iv) Copy the orginal $ORACLE_HOME/lib/libodm10.sl or libodm10.so file back to disable the ODM library.

PA systems
$ cp $ORACLE_HOME/lib/libodm10.sl.org $ORACLE_HOME/lib/libodm10.sl

IA systems
$ cp $ORACLE_HOME/lib/libodm10.so.oracle $ORACLE_HOME/lib/libodm10.so

v) Start the database

 

 

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.2 to 11.1.0.7 – Release: 10.2 to 11.1
Information in this document applies to any platform.
Database installations using Veritas Storage Foundation for Oracle / Oracle RAC (SFRAC)
and making use of the Veritas ODM (Oracle Disk Manager) library
Symptoms

The Veritas shared library used by Oracle Disk Manager could be lost upon RDBMS patchset
install such as patchset versions 10.2.0.2, 10.2.0.3, 10.2.0.4 or 11.1.0.7.
If this problem is not identified then poor IO performance or a change in IO profile of the
database can result such as cached IO being done to a Veritas filesystem rather than the expected
direct IO as was the case before applying the patchset.
Changes

Applying one of the currently available RDBMS patchsets:

10.2.0.2
10.2.0.3
10.2.0.4
11.1.0.7

on platforms AIX, Linux, HP-UX, Solaris.

Further RDBMS patchset versions which are not yet released as this Note is written can cause the same problem.
Cause

Upon patchset install in the $ORACLE_HOME/lib directory the library libodm<Version>.so is replaced by the the patchset’s own libodm<Version>.so which is created as a soft link to the dummy library libodmd<Version>.so.

In addition, by relinking the oracle binaries during patchset install on Linux, HP-UX or Solaris the dummy library $ORACLE_HOME/rdbms/lib/libodm<Veritas>.a will be statically linked in.
On AIX the make command would link the libodm<version>.so
rather than the static library.
If the previous installed library in $ORACLE_HOME/lib was a soft link or a copy of the Veritas libodm and the library was linked to the oracle binary then the ODM functionality would be lost after patchset install.

If the database is affected by this problem it would be visible from the alert.log file i.e. by the disappearance of a message like below:

“Oracle instance running with ODM: VERITAS 4.1.20.00 ODM Library, Version 1.1 ”

 

 

Solution

If the Veritas libodm has been lost following steps need to be executed to get it back in place:

Example: Oracle RDBMS after 10.2.0.4 patchset install on Solaris

su – <oracle install user>
ldd $ORACLE_HOME/bin/oracle shows no libodm10.so

Create soft link of Veritas ODM library
(below examples are valid for Oracle 10g and Veritas 5.0 installs)
AIX, HP-UX IA64, Linux, Sparc-Solaris:
mv $ORACLE_HOME/lib/libodm10.so $ORACLE_HOME/lib/libodm10.so.10204
HP-UX PA:
mv $ORACLE_HOME/lib/libodm10.sl $ORACLE_HOME/lib/libodm10.sl.10204

And then
AIX:
ln -s /opt/VRTSodm/libodm64.so $ORACLE_HOME/lib/libodm10.so
HP-UX PA:
ln -s /opt/VRTSodm/lib/libodm.sl $ORACLE_HOME/lib/libodm10.sl
HP-UX IA64:
ln -s /opt/VRTSodm/lib/libodm.sl $ORACLE_HOME/lib/libodm10.so
Linux:
ln -s /opt/VRTSodm/lib/libodm64.so $ORACLE_HOME/lib/libodm10.so
Sparc-Solaris:
ln -s /opt/VRTSodm/lib/sparcv9/libodm.so $ORACLE_HOME/lib/libodm10.so
Avoid linking the rdbms/lib static dummy library

mv $ORACLE_HOME/rdbms/lib/libodm10.a $ORACLE_HOME/rdbms/lib/libodm10.a.10204
make -f ins_rdbms.mk ioracle

Confirm libodm10.so is linked to the oracle binary:

ldd $ORACLE_HOME/bin/oracle shows libodm10.so
and starting up the Oracle instance would show a message like:
“Oracle instance running with ODM: VERITAS … ”

References

Bug 7359739: 3X”LOG FILE SYNC” DATAGUARD AFTER UPGRADING DB TO 10.2.0.4 FROM 10.2.0.2

Bug 7010362: STATIC VERSION OF ODM LIBRARY(LIBODM10.A) GETTING LINKED TO ORACLE BINARY

 

In Oracle9 Release 2 (9.2), you can use the filesystemio_optionsinit.ora
parameter to enable or disable asynchronous I/O, direct I/O, or Concurrent I/O
on file system files. This parameter is used on files that reside in non-VxFS
filesystems only. This parameter is not applicable to VxFS files, ODM files, or
Quick I/O files.
See your Oracle documentation for more details.

http://sfdoccentral.symantec.com/sf/5.0MP3/solaris/pdf/sf_ora_admin.pdf

 

 

 

 

Filed Under: Oracle Tagged With: ,

Latches and Tuning:The Library Cache

2010/12/26 by Leave a Comment

1. The shared pool is determined by the value of SHARED_POOL_SIZE as indicated in the init.ora file.  The library
cache is part of the shared pool.

a.  Determine the size of the shared poool by executing the following query:> select name,value from v$system_parameter where name = ‘shared_pool_size’;

The shared pool size is part of the VARIABLE SIZE value returned by querying V$SGA.

2.  The hidden parameter _KGL_BUCKET_COUNT determines the initial size of the hash table.  Typically, you
will accept the default value and not change this parameter.  The maximum value is 8.

a.  Determine the initial size of the hash table by executing the following query:> select ksppinm, ksppity from x$ksppi where ksppinm  = ‘_kgl_bucket_count’;

3. Object types are stored in a namespace.  While there can be 32 different namespaces, objects of the same
type will always be stored in the same namespace.

a.  Determine the namespaces allocated in the library cache by executing the following query:> select namespace from v$librarycache;

The number of namespaces are subject to increase at any time.  Common namespaces are:

CRSR: stores library cache objects of type cursor (shared SQL statements)
TABL/PRCD/TYPE: stores tables, views, sequences, synonyms, and procedure specifications
BODY/TYBD: stores procedure, function, package, and type bodies
INDX: stores librarcy cache objects of type index
TRGR: stores librarcy cache objects of type trigger
CLST: stores librarcy cache objects of type cluster

4.  Object tables are maintained for each object.  While the contents of each X$ table are fairly obscure, you can
query them to derive information about objects.

a.  Describe and/or query one of the following tables.X$KGLDP: (Dependency Table) One entry for each object that this object depends on.  For example, a view would depend on underlying tables or views.
X$KGLTR: (Translation Table) Contains records explaining how names referenced by this object were resolved to base objects.
X$KGLAU: (Authorization Table) Contains entries for each privilege defined on the object.
X$KGLXS: (Access Table) One or more entries for each entry in the dependency table.
X$KGLRD: (Read-only dependency table) Like the dependency table but for read only objects.
X$KGLSN: (Schema Name table) Only cursors have schema name tables and they store the schema names for the objects in the authorization table.
(Child Table) One entry for each object that is a child of this object.

5.  Remember that there are 32 namespaces in the library cache.  Also, each object has three types of flags; public,
status, and special status.
a.  Determine the number of namespaces by querying the OBJ$ table:

> select distinct(namespace) from obj$;

b. You can see the name of objects, their namespace, and flags by executing the following query.  Since there are many objects the query is limited using the psuedo column rownum.

> select name,namespace,flags from obj$ where flags > 1 and rownum < 10;

6. Locks and pins are used to control acccess to library cache objects. The X$KGLLK table, as indicated by the
letters LK, records locks on library cache objects.

a.  Connect as SYS and query the X$KGLLK table using the following query:> select user_name from x$kgllk where user_name = ‘SCOTT’;

Provided SCOTT is not logged in, this query should return no rows.:

b.  Create a second SQL*PLUS session as SCOTT/TIGER.

c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

The user SCOTT acquired object handles on the objects DBMS_APPLICATION_INFO and DBMS_OUTPUT.

d. Switch to SCOTT’s SQL*PLUS session and execute the following update statement:

> update dept set dname = ‘TEST’ where deptno = ’10’;

e. Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

You will see that SCOTT has acquired additional locks as a result of the update statement.

7.  The X$KGLLPN table, as indicated by the letters PN, records pins on library cache objects.  The contents of the
X$KGLPN table are obscure but you may want to take a look at the data.
a.  Describe the X$KGLN table:

> desc X$KGLN

8. Library cache contention can be caused by excessive parsing of SQL statements.

a.  Determine the parse count in the library cache by executing the following query:b.  Create a second SQL*PLUS session as SCOTT/TIGER.c.  Switch to SYS’s SQL*PLUS session and execute the following query:

> select user_name, kglnaobj from x$kgllk where user_name = ‘SCOTT’;

9.  One diagnostic you can use for determing library cache performance is querying the V$LIBRARYCACHE
view.

a.  Execute the following query:> select namespace, gets, gethitratio, pins, pinhitratio, reloads, invalidations
from v$librarycache;

  • NAMESPACE: the different library cache namespaces
  • GETS: the total number of calls to locate and lock an object in the library cache
  • PINS: total number of calls to pin an object heap (to examine and possibly change)
  • GET/PINHITRATIO: ratio of overall requests to successful acquisitions for the GET and PIN calls in the cache
  • RELOADS: object reloads due to being aged out of the library cache
  • INVALIDATIONS: number of times the object was invalidated

    • Tuning Recommendations:

  • Keep the HITRATIOS above 90%
  • Keep the RELOADS to a minimum, ideally close to zero
  • Avoid DDL and minimize user role changes in a busy production environmentto prevent INVALIDATIONS
  • Size the shared pool appropriately so as to avoid objects getting aged out of the library cache
  • Similar SQL statements must be identical to be shared – use bind variables instead of literals
    		•

    10.  By performing a library cache dump you can gather extensive information about the library cache.  The dump will
    show you all of the namespaces, buckets, librarcy cache statistics, and content of the librarcy cache.  Beware, if you have
    a large database this dump file can be quite large and take a long time to generate.  You may want to select the
    appropriate level 1 – 4, depending upon the information you want to see.

    a.  As user SYS, execute the following query:> alter session set events ‘immediate trace name library_cache level 4’;

    The output will be generated in the USER_DUMP_DEST directory.

  • Level 1: dump libracy cache statistics
  • Level 2: include a hash table histogram; each bucket has one asterisk for each included handle
  • Level 3: include the dump of the object handles
  • Level 4: include the dump of the object structures (heap 0 only)
    		•

    11.  The X$KSMLRU fixed table tracks allocations in the shared pool that cause other objects to be aged out.  This
    table can be used to identify what is causing the large allocation.

    a.  Query the X$KSMLRU fixed table:> select * from x$ksmlru where ksmlru > 4000;

    The table contents are deleted after a SELECT.  This is done because the table stores only the largest allocations that have occurred.

    b.  Describe the X$KSMLRU table:

    > desc X$KSMLRU

    KSMLRSIZ: amount of contiguous memory being allocated.  Values over 5KB start to be a problem.
    KSMLRNUM: number of objects that were flushed from the shared pool in order to allocate the memory.
    KSMLRHON: the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.
    KSMLROHV: hash value of object being loaded.
    KSMLRSES: SADDR of the session that loaded the object.

    12.  One way to decrease the load on the library cache latch is to reduce the number of parse calls that are coming into
    the system.

    a.  To identify statements that are receiving a lot of parse calls, execute the following statement:> select sql_text, parse_calls, executions
    from v$sqlarea
    where parse_calls > 100
    and executions< 2 * parse_calls;

    13.  An additional method to tune the library cache is to convert anonymous blocks into packages if possible.
    a.  Find anonymous  blocks by executing the following query (47 is the command for an anonymous PL/SQL block):

    > select sql_text
    from v$sqlarea
    where command_type = 47;

    Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , , ,

    How to set LOGSOURCE PATHMAP on GoldenGate

    2010/12/26 by Leave a Comment 
    Question:
the source machine os is solaris ,the mid machine os is linux ,
the target machine os is solaris,i want extract archive log file and pump trail file
to target on mid machine , i check GoldenGate Reference Guide version 10.4 about TRANLOGOPTIONS,
must set ARCHIVEDLOGONLY and LOGSOURCE PATHMAP , in my environment ,how to set LOGSOURCE PATHMAP ?

Answer:
There two primary things that need to be met when extracting the data remotely.

1. Endian order (How the bytes are stored)
Big endian OS    : AIX, HPUX, MVS, SOLARIS, S290
Little endian OS : LINUX, VMS, WINDOWS

2. Bit (32 or 64 bit).

If your source is SOLARIS(Big Endian) and mid machine where archive logs resides as LINUX(Little Endian),
then you cannot use LOGSOURCE as it violates the ENDIAN order.

For exampel you can either have source and mid machine as solaris solaris or AIX ,HPUX  or vice versa

The general syntax to extract data from transaction logs which resides on a platform other than the one
which hosts the database If you the source and mid machine are Solaris you can use the following syntax.

TRANLOGOPTIONS, LOGSOURCE SOLARIS, PATHMAP

Please refer Oracle GoldenGate Reference guide page 356 for the example
    Filed Under: GoldenGate, Oracle Tagged With: ,

    How GoldenGate process consumes memory

    2010/12/26 by Leave a Comment

    Question:
    We are using Golden Gate to replicate the data from Oracle 9.2.0.8 on Solaris8 SPARC 64 bit (GoldenGate Version 10.4.0.31 Build 001) to Oracle RAC 11.2.0.1 on Solaris10 SPARC 64bit (GoldenGate Version 10.4.0.19 Build 002). Both GoldenGate Extract and Replicat process are working fine. Please refer below information for more easy to understand our goldengate setup.

    Extract Side | Replicat Side
    Hostname: HK8SN020 | Hostname: HK8SP226 (HK8SP227 dont have any goldengate client, all goldengate process are located on HK8SP226)
    Oracle 9.2.0.8 (32bit binary) | Oracle 11.2.0.1 (64bit binary)
    Solaris8 Sparc 64bit Kernel | Solaris10 Sparc 64bit kernel
    GoldenGate Version 10.4.0.31 Build 001 | GoldenGate Version 10.4.0.19 Build 002

    However, on 27-Mar-2010, we found the server memory utilization on Solaris10 HK8SP226 are unexpected continuously rising since around 01:30. At around 3:20, the server memory utilization are up to 100%. At around 5:20, the server memory utilization is suddenly drop and back to normal. We compared the “Sar -r” and Solaris server message logfile. We found that on 05:21:44, goldengate relicate process are terminated with error message ” malloc 2097152 bytes failed”. After that, seem the server memory are suddenly released and back to normal memory utilization level.
    We suspected the abnormal server memory usage are cased by goldengate replicate process. Can you please help to investigate and find out the root cause?

    Answer:
    GoldenGate replicates only committed transactions, it stores the operations of each transaction in a managed virtual-memory pool known as a cache until it receives either a commit or a rollback for that transaction. One global cache operates as a shared resource of an Extract process. The following sub-pools of virtual memory are allocated from the global cache:(1)One sub-pool per log reader thread for most transaction row data. and (2)One sub-pool for BLOB data and possibly other large items.

    Within each sub-pool, individual buffers are allocated from the global cache, each one containing information that is relative to a transaction that is being processed by GoldenGate. The sizes of the initial and incremental buffers are controlled by the CACHEBUFFERSIZE option of CACHEMGR.

    The actual amount of physical memory that is used by any GoldenGate process is controlled by the operating system, not the GoldenGate process. The global cache size is controlled by the CACHESIZE option of CACHEMGR.Cache manager keeps a GoldenGate process working within the soft limit of its global cache size, only allocating virtual memory (not physical memory) on demand. The actual amount of physical memory that is used by any GoldenGate process is controlled by the operating system, not the GoldenGate program.

    GoldenGate cache manager only takes advantage of the memory management functions of the operating system to ensure that GoldenGate processes work in a sustained and efficient manner. Within cache, OGG makes use of all the modern “virtual memory” techniques by allocating and managing active buffers efficiently and recycling old buffers instead of paging to disk, when possible and paging less-used information to disk, when necessary.

    When COM initializes, by default it first determines how much virtual memory the OS has available for it and uses that to determine what CACHESIZE should be. Default for CACHESIZE is 8GB for 64-bit systems and 2GB for 32-bit systems.

    The available virtual memory is reported with the PROCESS VM AVAIL FROM OS value in the report file. The CACHESIZE value will either be rejected or sized down if it is larger than, or sufficiently close to, the amount of virtual memory that is available to the process.

    The CACHESIZE value will always be a power of two, rounded down from the value of PROCESS VM AVAIL FROM OS, unless the latter is itself a power of two, in which case it is halved. After the specified size is consumed by data, the memory manager will try to free up memory by paging data to disk or by reusing aged buffers, before requesting more memory from the system.

    The memory manager generates statistics that can be viewed with the SEND EXTRACT or SEND REPLICAT command when used with the CACHEMANAGER option.The statistics show the size of the memory pool, the paging frequency, the size of the transactions, and other information that creates a system profile. Based on this profile, you might need to make adjustments to the memory cache if you see performance problems that appear to be related to file caching. The first step is to modify the CACHESIZE and CACHEPAGEOUTSIZE parameters. You might need to use a higher or lower cache size, a higher or lower page size, or a combination of both, based on the size and type of transactions that are being generated. You might also need to adjust the initial memory allocation with the CACHEBUFFERSIZE option. It is possible, however, that operating system constraints could limit the effect of modifying any components of the CACHEMGR parameter. In particular, if the operating system has a small per-process virtual memory limit, it will force more file caching, regardless of the CACHEMGR configuration.

    Once the CACHESIZE is set to 1 GB, the GoldenGate process will use up to 1 GB virtual memory and then it will use swap space on disk.
    If the CACHESIZE is explicitly set in process parameter file then the CACHEMGR will use only 1GB. Otherwise it will default to the Memory size depending upon the platform(32 or 64).If a fixed CACHESIZE is set in the parameter file then it will be taken by the process, if no the default will be taken by the process depending upon the platform. If very low virtual memory limit is set or available in the OS then it will force more file caching. There is always a difference between caching in memory buffers and file caching as it involves read and write i/o’s.

    So try to set a default CACHESIZE for the GoldenGate Process (Extract/Replicat). Edit the respective source extract and target replicat parameter files and use the below mentioned CACHEMGR parameter with the options given and restart the processes.

    CACHEMGR CACHEBUFFERSIZE 64KB, CACHESIZE 1GB, CACHEDIRECTORY
    , CACHEDIRECTORY
    Example:
    CACHEMGR CACHEBUFFERSIZE 64KB, CACHESIZE 1GB, CACHEDIRECTORY /ggs/dirtmp, CACHEDIRECTORY /ggs2/temp

    So once the CACHESIZE is set to 1 GB, the GoldenGate process will use up to 1 GB virtual memory only and then after it will use swap space on disk.

    Filed Under: GoldenGate, Oracle Tagged With: , ,

    Will Goldengate use Remote Procedure Call (RPC)?

    2010/12/23 by Leave a Comment

    Question:
    My Customer using Oracle GoldenGate to sync data from 11.2.0.1 RAC (on Solaris 10 SPRAc 64bit) to 9.2.0.8 standalone DB (On solaris Sparc 64bit). My customer recently doing an OS security scan check for node of 11.2.0.1 RAC. The security scan check result saying the RAC node (HK8SP226) has a potential security issue on “rpcstatd: RPC statd remote file creation and removal”. My customer suspect that Goldengate is using RPC for file transfer. Would you please help to check if goldengate need to use RPC or not? if goldengate dont need to use RPC, then i will ask my csutomer to close this function in server

    Supplementory information for security check result.

    ———————————————————————-

    hk8sp226 {172.20.104.178} Solaris
    H
    rpcstatd: RPC statd remote file creation and removal
    Remote Procedure Call (RPC) statd maintains state information in cooperation with RPC lockd to provide crash and recovery
    functionality for file locking across the Network File System (NFS). Statd does not validate information received from a remote lockd.
    By sending to the statd service an RCP or RDIST request including references to the parent directory (“..”), an attacker can provide
    false information to the rpc.statd file, allowing the creation of a file in an arbitrary directory on the host. This can be used to overwrite
    pre-existing files or create new files on the host.

    Answer:
    Oracle GoldenGate transfers trail files over TCP/IP to the remote host which internally uses RPC for transferring the data to remote. You could let customer know about it and could exclude it from hardening.

    Filed Under: GoldenGate, Oracle Tagged With: , ,

    配置GoldenGate意外处理及记录Oracle错误信息(1)

    2010/12/23 by Leave a Comment

    GoldenGate自身不提供异常处理的程序。在默认情况下，若Replicat进程遭遇操作故障都会导致其异常终止(ABEND)，同时将会回滚事务到最近的检查点。在生产环境中这往往并不理想。当我们在做无缝的数据迁移时会用到HANDLECOLLISIONS和NOHANDLECOLLISIONS参数，这2个参数控制了Replicat是否会试图解决重复记录和缺失记录的错误，但这错误真的应该被忽略吗？这个问题只有熟悉应用的维护人员才能解答，我们需要做的是在出现这类错误后及时记录错误的相关信息。

    这就要求我们在Replicat中配置相关的意外处理程序，该程序用以记录是哪个Replicat进程，是那些数据引起了错误的产生。

    接下来我们会尝试建立不同的意外处理程序，用以捕获复制过程中Oracle出现的相关错误，但在这些故障发生后我们允许Replicat继续它的工作:

    1. 首先我们要做的是创建用以记录错误相关信息的错误记录表:
SQL> conn maclean/maclean
Connected.
SQL> create table exception_log
  2  ( replicat_name varchar2(10),
  3    table_name varchar2(100),
  4    errno number,
  5    dberrmsg varchar2(4000),
  6    optype varchar2(20),
  7    errtype varchar2(20),
  8    logrba number,
  9    logposition number,
 10    committimestamp timestamp);

Table created.

SQL> alter table exception_log add primary key (logrba,logposition,committimestamp);
Table altered.

以上意外处理记录表应建立于Goldengate管理员账户名下，可用以记录所有Replicat的意外数据。

2.修改各Replicat的参数文件添加相关的意外处理程序:
GGSCI (rh3.oracle.com) 59> view params rep1

replicat rep1
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/rep1.log,append,megabytes 10
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,ABEND)
map defs.tbc, target defs.tbc;
map defs.tbc, target maclean.exception_log, 
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "rep1"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG") 
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型，即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时，所有MAP映射中未定义Exception部分出现的所有错误

3.停止并重启Replicat 进程
GGSCI (rh3.oracle.com) 60> stop rep1

Sending STOP request to REPLICAT REP1 ...
Request processed.


GGSCI (rh3.oracle.com) 61> start rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

4.以上完成了对单表defs.tbc意外处理的配置，接着我们可以启动相关的应用了

5.通过某些手动篡改数据或不同步操作，可以很容易地触发意外处理而将相关错误信息记录到我们的exception_log表中，我们可以来查看
相关记录:
SQL> col dberrmsg for a1;
SQL> col table_name for a10;
SQL> select * from exception_log;
 
REPLICAT_NAME TABLE_NAME      ERRNO D OPTYPE               ERRTYPE                  LOGRBA LOGPOSITION COMMITTIMESTAMP
------------- ---------- ---------- - -------------------- -------------------- ---------- ----------- ---------
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    59259920 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    59260812 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94620688 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94621580 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94682640 23-DEC-10 1
rep1          DEFS.TBC         1403   PK UPDATE            DB                          231    94683532 23-DEC-10 1
 
6 rows selected

可以看到以上为基于主键更新时出现了1403 "no data found"的Oracle常规错误。

意外处理程序所能记录的相关信息还不于止此，我们还可以用它来记录如Update操作的前后数据镜像，这些信息可以在冲突解决时派上用场。
    Filed Under: GoldenGate, Oracle Tagged With: , ,

    ORA-01652 even though there is sufficient space in RECYCLE BIN

    2010/12/23 by Leave a Comment

    There is a bug 6977045 which may cause ORA-1652 raised even though there is sufficient space in RECYCLE BIN. Version under 11.2 believed to be affected

    
[oracle@rh2 ~]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.


Bug 6977045  ORA-1652 even though there is sufficient space in RECYCLE BIN
 This note gives a brief overview bug 6977045.
 The content was last updated on: 06-DEC-2010
 Click here for details of each of the sections below.
Affects:

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions BELOW 11.2
    Versions confirmed as being affected

        11.1.0.7

    Platforms affected	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in

        11.2.0.1 (Base Release)
        11.1.0.7 Patch 32 on Windows Platforms

Symptoms:

Related To:

    Error May Occur
    Storage Space Usage Affected
    ORA-1652



    Recycle Bin

Description

    Under space pressure an ORA-1652 may be signalled even if there is sufficient
    space in the recyclebin.

    Rediscovery Notes:
     Under space pressure, space allocation fails, even though there
     is sufficient free space in recycle bin.

    Workaround
     Turn off the recycle bin.
     OR
     Purge the recyclebin.

Hdr: 12582291 11.1.0.7 RDBMS 11.1.0.7 SPACE PRODID-5 PORTID-59
Abstract: UPDATING A LOB FAILS WHILE CLEARING RECYCLE BIN EVEN WHEN ENOUGH FREE SPACE IS A

  BUG TYPE CHOSEN
  ===============
  Code

  SubComponent: Recovery
  ======================
  DETAILED PROBLEM DESCRIPTION
  ============================
  An OCI application module tried to update a LOB object, and this operation
  internally & recursively tried to clear off a few segments from the recycle
  bin. As ct. had enabled triggers preventing uncontrolled droppings of
  segments, this apparently prevented the application module from succeeding.
  Further, since this error did not show up on the application module that
  failed, this customer-facing critical application of this large enterprise
  was down for considerable time.

  DIAGNOSTIC ANALYSIS
  ===================
  None. This bug is raised mainly as a Q/A to get clarifications for customer,
  who is demanding an answer and possible action plan so that they can prevent
  such disastrous situation in future.

  WORKAROUND?
  ===========
  Yes

  WORKAROUND INFORMATION
  ======================
  Disable the trigger or not using the recycle bin (Though neither operation
  is acceptable to ct. because of their business reasons).

  TECHNICAL IMPACT
  ================
  Critical application module fails.

  RELATED ISSUES (bugs, forums, RFAs)
  ===================================
  None (MOS Note 978045.1 was referenced by ct.)

Hdr: 6977045 10.2 RDBMS 10.2 RAM DATA PRODID-5 PORTID-23 ORA-1652
Abstract: ORA-1652  LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE

*** 04/16/08 12:57 pm ***
TAR:
----
6880393.992

PROBLEM:
--------
ORA-12801: error signaled in parallel query server P038
ORA-1652: unable to extend temp segment by 320 in tablespace ERROR_TS

After dropping a table in a LMT the space is not properly returned to the
tablespace datafiles .

Only after purge tablespace error_ts; do we see the space returned correctly.
 Subsequently the test plan is successful and the table is created.


DIAGNOSTIC ANALYSIS:
--------------------
See attached test case. test_output.log

WORKAROUND:
-----------
none

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------

TEST CASE:
----------
See attached test case. test_output.log

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 04/16/08 01:29 pm ***
*** 04/16/08 02:04 pm ***
the problem here is that even though the objects are occupying the same space
when they were created, dba_free_space shows one datafile to contain all the
free space reclaimed by the drop table command.
*** 04/16/08 02:35 pm ***
Please confirm this is a duplicate of bug 5083393.
*** 04/17/08 10:56 am ***
*** 04/17/08 05:09 pm ***
*** 04/17/08 05:14 pm *** (CHG: Sta->10)
*** 04/17/08 05:14 pm ***
*** 04/21/08 11:06 am *** (CHG: Sta->16)
*** 04/21/08 11:06 am ***
please review uploaded file ora_test1.log.

Patch 5083393 has been applied to this instance and the test was ran against
this patch.
Notice the query immedatly following the ORA_1652 error.  The temporary
segments seem to be causing the failure and specifically segment 1199.88012  .
*** 04/22/08 01:55 pm ***
Current SQL statement for this session:
create table seckle.my_test2_tb
nologging tablespace error_ts
parallel (degree 6)
as
select * from ecm.E08401AH_GEMINI_CMF_WIDE_TB
        ERROR parallelizer slave or internal
        qbas:54482
        pgakid:2 pgadep:0
        qerpx: error stack: OER(12805)
        qbas_qerpxs: 54482
        dfo_qerpxs: 0x4b7ba89e0 dfo1_qerpxs: 0x4b7ba9178
        ntq_qerpxs: 1 ntqi_qerpxs: 0
        nbfs_qerpxs: 0
        nobj_qerpxs: 2  ngdef_qerpxs: 1
        mflg_qerpxs: 0x2c
        slave set 1 DFO dump:
        kkfdo: (0x4b7ba9178)
        kkfdo->kkfdochi: (0x0)
        kkfdo->kkfdopar: (0x0)
        kkfdo->kkfdonxt: (0x0)
        kkfdo->kkfdotqi: 0
        kkfdo->kkfdontbl: 2
        kkfdo->kkfdongra: 1
        kkfdo->kkfdofigra: 0
        kkfdo->kkfdoflg: 0x2818
        kkfdo->kkfdooct: 1
        kkfdo->kkfdonumeopn: 0
        Output table queue: (0x4b7fab1b8)
          kxfqd     : 0x4b7fa5728
          kxfqdtqi  : 0            TQ id
          kxfqdcc   : 0x14         TQ: from slave set 1 to QC
          kxfqdpty  : 4
          kxfqdsmp  : 0            number of samples
          kxfqdflg  : 0x4
          kxfqdfmt  :              TQ format

          kxfqfnco  : 5            number of TQ columns
          kxfqfnky  : 0            number of key columns
          TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
          kxfqfcol[   0]:  4          23         0x0          4
          kxfqfcol[   1]:  32720      23         0x80         32720
          kxfqfcol[   2]:  1          23         0x0          1
          kxfqfcol[   3]:  76         23         0x0          76
          kxfqfcol[   4]:  32720      23         0x0          32720
        slave set 2 DFO dump:
        np_qerpxm: 6 mflg_qerpxm: 0xa7
        cdfo_qerpxm: 0x4b7ba9178 (tqid 0) sdfo_qerpxm: 0x0 (tqid -1)
        ctqh_qerpxm: 0xffffffff79378ac8 dump:
        kxfqh     : 0xffffffff79378ac8
        kxfqhflg  : 0x15         TQ handle open
        kxfqhmkr  : 0x4          QC
        kxfqhpc   : 2            1:producer 2:consumer 3:ranger
        kxfqepty  : 4
        kxfqhnsam : 6
        kxfqhnth  : 6
        kxfqhdsc  :              TQ descriptor

        kxfqd     : 0x4b7fa5728
        kxfqdtqi  : 0            TQ id
        kxfqdcc   : 0x14         TQ: from slave set 1 to QC
        kxfqdpty  : 4
        kxfqdsmp  : 0            number of samples
        kxfqdflg  : 0x4
        kxfqdfmt  :              TQ format

        kxfqfnco  : 5            number of TQ columns
        kxfqfnky  : 0            number of key columns
        TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
        kxfqfcol[   0]:  4          23         0x0          4
        kxfqfcol[   1]:  32720      23         0x80         32720
        kxfqfcol[   2]:  1          23         0x0          1
        kxfqfcol[   3]:  76         23         0x0          76
        kxfqfcol[   4]:  32720      23         0x0          32720
        dnst_qerpxm[cur,par]: 6,0 dcnt_qerpxm[cur,par]: 0,0
        ppxv_qerpxm[0]: 0xffffffff79377f50 count[np..1]:1 1 1 1 1 1
        pqv1_qerpxm: 0xffffffff79377f38 bits[np..1]: 111111
        pqv2_qerpxm: 0xffffffff79377f40 bits[np..1]: 000000

    If you have enabled recyclebin ,then you should check tablespace free space with dba_free_space and recyclebin space also like:

    create view dba_free_space_pre10g as
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       f.block# BLOCK_ID,
       f.length * ts.blocksize BYTES,
       f.length BLOCKS,
       f.file# RELATIVE_FNO
  from sys.ts$ ts, sys.fet$ f, sys.file$ fi
 where ts.ts# = f.ts#
   and f.ts# = fi.ts#
   and f.file# = fi.relfile#
   and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 f.ktfbfebno BLOCK_ID,
 f.ktfbfeblks * ts.blocksize BYTES,
 f.ktfbfeblks BLOCKS,
 f.ktfbfefno RELATIVE_FNO
  from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
 where ts.ts# = f.ktfbfetsn
   and f.ktfbfetsn = fi.ts#
   and f.ktfbfefno = fi.relfile#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
 /

create view dba_free_space_recyclebin as
select /*+ ordered use_nl(u) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 u.ktfbuebno BLOCK_ID,
 u.ktfbueblks * ts.blocksize BYTES,
 u.ktfbueblks BLOCKS,
 u.ktfbuefno RELATIVE_FNO
  from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
 where ts.ts# = rb.ts#
   and rb.ts# = fi.ts#
   and u.ktfbuefno = fi.relfile#
   and u.ktfbuesegtsn = rb.ts#
   and u.ktfbuesegfno = rb.file#
   and u.ktfbuesegbno = rb.block#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
union all
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       u.block# BLOCK_ID,
       u.length * ts.blocksize BYTES,
       u.length BLOCKS,
       u.file# RELATIVE_FNO
  from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
 where ts.ts# = u.ts#
   and u.ts# = fi.ts#
   and u.segfile# = fi.relfile#
   and u.ts# = rb.ts#
   and u.segfile# = rb.file#
   and u.segblock# = rb.block#
   and ts.bitmapped = 0
/

    dba_free_space_pre10g which shows the real free space like 9i behavior , dba_free_space_recyclebin shows free space resided in recyclebin.

    Filed Under: Oracle Tagged With: , , ,

    沪ICP备14014813号-2

    沪公网安备 31010802001379号