老熊的三分地-Oracle及数据恢复

某些Oracle错误，并不是总是伴随着产生trace文件，这些错误，对Oracle来说并不是严重的错误，比如像ORA-01555这样的错误。

我们可以设置一个事件，在发生错误时，产生一个Trace文件，事件通常使用下面的命令格式：

    alter system set events '<error_number> trace name errorstack level <level>';
    alter session set events '<error_number> trace name errorstack level <level>';

然而，使用alter system命令设置事件后，只会对新连接的会话有效。比如下面的测试：

会话一：
[oracle@xty scripts]$ sqlplus test/test

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 25 23:25:51 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t1;

Table dropped.

SQL>          
SQL> create table t1 ( a number primary key);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit;

Commit complete.

会话二：

alter system set events '1 trace name errorstack level 1';

会话一：
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C005801) violated

此时检查user_dump_dest目录，没有相应的trace文件产生，如果我们再执行下面的动作：

SQL> conn test/test
Connected.
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C005801) violated

就可以在user_dump_dest发现产生的trace文件。

那么发生错误的会话已经连接到数据库一段时间了，怎么得到这个会话在的信息？比如某个数据库，数据库中频繁地报下面的错误：

select sysdate create_time from dual
ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x09e5.0c3c77b1):
Wed Jul 22 11:17:51 2009
select g.*,m.*  from Tb_Model m right outer join (select t.*,v.table_name from.....

这个ORA-01555错误是非常怪异的，首先是查询DUAL表都会报错，其次，每次报错都是“Query Duration=0 sec, SCN: 0x09e5.0c3c77b1”，这里除了BUG,实在想不到其他的理由 。不过为了查明到底是哪个会话和哪个应用，是不是同一个会话引起，我们需要得到这个引起错误的会话的信息。

这里，我们可以用触发器。以SYS用户执行下面的代码：

Read the rest of this entry

一条看上去很简单的SQL：

SELECT * FROM V_CALL_EVENT_10906 
WHERE to_char(start_date, 'yyyymmdd') in ('20090620', '20090621', '20090622') 

执行时长比较长，以至于出现ORA-01555错误，由于返回的结果数据行数非常大，取1月之内3天的数据，不太适合于使用索引，同时应用结构上决定了，也不能按天分区。

这里如果我们能够把表访问从6次，改为1次，那么性能就能大幅提升，这里修改视图的定义如下：

V_CALL_EVENT_10906视图定义如下：

CREATE VIEW V_CALL_EVENT_10906 
AS
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID1 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE1 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               1 SPLIT_ID
 FROM CALL_EVENT_10906
union all
SELECT ACCT_ID1 ACCT_ID,
               SERV_ID,
               EVENT_TYPE_ID,
               ACCT_ITEM_TYPE_ID2 ACCT_ITEM_TYPE_ID,
               CALLING_AREA_CODE,
               CALLING_NBR,
               CALLED_AREA_CODE,
               CALLED_NBR,
               START_DATE,
               START_DATE + DURATION / 3600 / 24 END_DATE,
               DURATION,
               CHARGE2 CHARGE,
               BILLING_CYCLE_ID,
               TO_DATE(CREATED_DATE) CREATED_DATE,
               TO_DATE(START_DATE) DATA_DATE,
               RESERVED_FIELD1,
               2 SPLIT_ID
 FROM CALL_EVENT_10906
WHERE ACCT_ITEM_TYPE_ID2 != 0
  AND ACCT_ITEM_TYPE_ID2 IS NOT NULL

为节省篇幅，这个视图的定义实际上没有完全列出，视图中实际有5个“UNION ALL”，也就是CALL_EVENT_10906实际访问了6次。

CREATE VIEW V_CALL_EVENT_10906 
AS 
select /*+ no_merge(v) no_push_pred(v) */
v.* FROM 
(SELECT /*+   parallel(a,4) */
          ACCT_ID1 ACCT_ID,
                   SERV_ID,
                   EVENT_TYPE_ID,
                   DECODE(B.SPLIT_ID, 1, ACCT_ITEM_TYPE_ID1, 2, ACCT_ITEM_TYPE_ID2, 3, ACCT_ITEM_TYPE_ID3,   4, ACCT_ITEM_TYPE_ID4, 5, ACCT_ITEM_TYPE_ID5, 6,ACCT_ITEM_TYPE_ID6,0) ACCT_ITEM_TYPE_ID,
                   CALLING_AREA_CODE,
                   CALLING_NBR,
                   CALLED_AREA_CODE,
                   CALLED_NBR,
                   START_DATE,
                   START_DATE + DURATION / 3600 / 24 END_DATE,
                   DURATION,
                   DECODE(B.SPLIT_ID, 1, CHARGE1, 2, CHARGE2, 3, CHARGE3, 4, CHARGE4, 5, CHARGE5, 6,CHARGE6,0) CHARGE,
                   BILLING_CYCLE_ID,
                   TO_DATE(CREATED_DATE) CREATED_DATE,
                   TO_DATE(START_DATE) DATA_DATE,
                   RESERVED_FIELD1,
                   B.SPLIT_ID SPLIT_ID
           FROM CALL_EVENT_10906812 A,
                                   (
                  SELECT 1 SPLIT_ID
                    FROM DUAL
                  UNION ALL
                  SELECT 2
                    FROM DUAL  
                  UNION ALL  
                  SELECT 3
                    FROM DUAL
                  UNION ALL  
                  SELECT 4
                    FROM DUAL
                  UNION ALL  
                  SELECT 5
                    FROM DUAL
                  UNION ALL  
                  SELECT 6 FROM DUAL) B
) v,(select /*+ no_merge */ 0 id from dual) K
where nvl(v.acct_item_type_id,0) !=k.id;

通过UNION DUAL表，得到6行结果，同时与CALL_EVENT_10906表之间没有任何关联条件，这样就会形成笛卡尔连接（cartesian join），CALL_EVENT_10906这个表的每一行数据，将实际产生6行输出。这样就避免了对这个表扫描6次。

为什么这里还要嵌套一层，再加上这样的条件：

where nvl(v.acct_item_type_id,0) !=k.id

这个条件实际上是：

where nvl(v.acct_item_type_id,0) !=0

如果不嵌套一层，那么就会形成CALL_EVENT_10906与DUAL表UNION之后的结果之后的连接关系，就不会使用cartesian join了。