了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support，N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群，空位多多。欢迎有一定基础的Oracle骨友加入，现在入群需要经过Maclean的技术面试，欢迎面试，请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试

在Oracle中如何让SELECT查询绕过UNDO

是否有想过如何在Oracle中实现脏读(dirty read)，在Oracle官方文档或者Asktom的时候显然会提到Oracle是不实现脏读的， 总是有undo来提供数据块的前镜像(before image)以维护一致性Consistent， 通过正常途径我们几乎不可能破坏Oracle中查询的一致性来实现脏读。   是否真的无计可施？ 非也，非也，Oracle作为一个高度复杂而又可控的RDBMS，仍是有很多空子可以钻的。   我们先来介绍以下的2个隐藏参数： _offline_rollback_segments or _corrupted_rollback_segments 这2个隐藏参数对于熟悉Oracle数据库异常恢复或者解决ORA-600[4XXX]问题有经验的同学来说肯定不陌生，因为这2个参数是针对Undo存在Corruption讹误时忽略问题的有力工具，而大家对这2个参数实际起到的作用有多少认识呢？   我们可能在以下几种场景中用到_offline_rollback_segments 和 _corrupted_rollback_segments 这2个隐藏参数：
  • 强制打开数据库(FORCE OPEN DATABASE)
  • 控制一致性读和延迟块清除(consistent read & delayed block cleanout)
  • 强制删除某个rollback segment回滚段
  但是请注意：千万不要在没有Oracle专家建议的情况下，在产品环境设置以上这2个隐藏参数，可能造成数据逻辑讹误等影响！！   _offline_rollback_segments 和 _corrupted_rollback_segments 均会造成的实例行为变化：
  • 以上2个参数所列出的Undo Segments(撤销段/回滚段)将不会被在线使用online
  • 在UNDO$数据字典基表中将体现为OFFLINE的记录
  • 在实例instance的生命周期中将不会再给新的事务分配使用
  • 参数所列出的Undo Segments列表上的活跃事务active transaction将即不被回滚亦不被标记为dead以便SMON去回滚(了解你所不知道的SMON功能(五):Recover Dead transaction)
  _OFFLINE_ROLLBACK_SEGMENTS(offline undo segment list)隐藏参数(hidden parameter)的独有作用：
  • 在实例startup启动并open database的阶段仍将读取_OFFLINE_ROLLBACK_SEGMENTS所列出的Undo segments(撤销段/回滚段)，若访问这些undo segments出现了问题则将在alert.log和其他TRACE中体现出来，但不影响实际的startup进程
    • 若查询数据块发现活跃的事务，并ITL指向对应的undo segments则：
    • 若读取undo segments的transaction table事务表发现事务已提交则做数据块的清除
    • 若读取发现事务仍活动未commit，则生成一个CR块拷贝
    • 若读取该undo segments存在问题(可能是corrupted讹误，可能是missed丢失)则产生一个错误并写出到alert.log，查询将异常终止
  • 若DML更新相关的数据块会导致服务进程为了恢复活跃事务而进入死循环消耗大量CPU，解决方法是通过可以进行的查询工作重建相关表
  _CORRUPTED_ROLLBACK_SEGMENTS(corrupted undo segment list)隐藏参数所独有的功能:
  • 在实例启动startup并open database的阶段_CORRUPTED_ROLLBACK_SEGMENTS所列出的undo segments(撤销段/回滚段)将不会被访问读取
  • 所有指向这些被_CORRUPTED_ROLLBACK_SEGMENTS列出的undo segments的事务都被认为已经提交了commit，和这个undo segments已经被drop时类似
  • 衷心地建议用_CORRUPTED_ROLLBACK_SEGMENTS这个参数打开数据库后导出数据并重建数据库，这个参数使用的后遗症可能很顽固
  • Oracle公司内部有叫做TXChecker的工具可以检查问题事务
  好了了解了以上2个参数之后，你将不难明白下面演示如何通过_CORRUPTED_ROLLBACK_SEGMENTS隐藏参数让SELECT查询绕过UNDO实现脏读的过程：   
SQL> alter system set event= '10513 trace name context forever, level 2' scope=spfile;

System altered.

SQL> alter system set "_in_memory_undo"=false scope=spfile;

System altered.

10513 level 2 event可以禁止SMON 回滚rollback 死事务 dead transaction
_in_memory_undo 禁用 in memory undo 特性

SQL> startup force;
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2232472 bytes
Variable Size            1795166056 bytes
Database Buffers         1325400064 bytes
Redo Buffers               17227776 bytes
Database mounted.
Database opened.

session A:

SQL> conn maclean/maclean
Connected.

SQL> create table maclean tablespace users as select 1 t1 from dual connect by level exec dbms_stats.gather_table_stats('','MACLEAN');

PL/SQL procedure successfully completed.

SQL> set autotrace on;   

SQL> select sum(t1) from maclean;

   SUM(T1)
----------
       501

Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN |   501 |  1503 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

在没有活跃事务的情况下，直接读取current block，
全表扫描一致性读，consistent gets只要3次                  

SQL> update maclean set t1=0;

501 rows updated.

SQL> alter system checkpoint;

System altered.

这里session A不commit;

另开一个 session:

SQL> conn maclean/maclean
Connected.
SQL> 
SQL> set autotrace on;
SQL>  select sum(t1) from maclean;

   SUM(T1)
----------
       501

Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN |   501 |  1503 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

为了一致性读 上面的查询需要通过undo构造CR块，这导致consistent gets上升到 505

[oracle@vrh8 ~]$ ps -ef|grep LOCAL=YES |grep -v grep
oracle    5841  5839  0 09:17 ?        00:00:00 oracleG10R25 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

[oracle@vrh8 ~]$ kill -9 5841

杀掉session A对应的Server Process服务进程，这导致dead transaction 但是不被smon回滚

     select ktuxeusn,
               to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time",
               ktuxesiz,
               ktuxesta
          from x$ktuxe
         where ktuxecfl = 'DEAD';

  KTUXEUSN Time                   KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
         2 06-AUG-2012 09:20:45          7 ACTIVE

此时有1个active rollback segment 

SQL> conn maclean/maclean
Connected.

SQL> set autotrace on;

SQL> select sum(t1) from maclean;

   SUM(T1)
----------
       501

Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN |   501 |  1503 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

到上面为止 虽然通过kill进程 和禁止smon 回滚dead transaction ，
形成了一个不回滚的死事务 但是仍通过undo实现了一致性读

找出当前active的rollback segment的名字

SQL> select segment_name from dba_rollback_segs where segment_id=2;

SEGMENT_NAME
------------------------------
_SYSSMU2$

SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU2$'  scope=spfile;

System altered.

用 _corrupted_rollback_segments 废掉 上面的2个rollback segment， 这将导致无法提供undo 


SQL> startup force;
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2232472 bytes
Variable Size            1795166056 bytes
Database Buffers         1325400064 bytes
Redo Buffers               17227776 bytes
Database mounted.
Database opened.

SQL> conn maclean/maclean
Connected.

SQL> set autotrace on;

SQL> select sum(t1) from maclean;

   SUM(T1)
----------
        94

Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN |   501 |  1503 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        228  recursive calls
          0  db block gets
         29  consistent gets
          5  physical reads
        116  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

   SUM(T1)
----------
        94

Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN |   501 |  1503 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
    以上可以看到 consistent gets下降到3，服务进程读取数据块发现存在活跃事务，但是ITL指向的UNDO SEGMENTS在_corrupted_rollback_segments的列表中，所以直接认为该事务已经COMMIT提交，以便绕过UNDO。 这里实现了脏读，虽然通过上述方法去实现脏读在产品环境中没有实际收益(有部分数据库软件允许脏读来做统计信息收集)，破坏了一致性且需要设置需重启实例的 隐藏参数 ， 仅供参考。

posted on 2013-03-19 00:51  Oracle和MySQL  阅读(410)  评论(0)  编辑  收藏  举报

导航