ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 小议自治事务(六)

小议自治事务(六)

原创 Linux操作系统 作者:yangtingkun 时间:2008-08-05 23:00:11 0 删除 编辑

这两天看了11g的CONCEPT文档的事务部分,发现自治事务还有一些以前没有注意到的地方,这里简单总结一下。

对比自治事务与普通事务的代价差别。

小议自治事务(一):http://yangtingkun.itpub.net/post/468/467765

小议自治事务(二):http://yangtingkun.itpub.net/post/468/467840

小议自治事务(三):http://yangtingkun.itpub.net/post/468/467933

小议自治事务(四):http://yangtingkun.itpub.net/post/468/468031

小议自治事务(五):http://yangtingkun.itpub.net/post/468/468180

 

 

自治事务提供了很方便的事务控制功能,使得用户可以在不影响当前事务的情况下,提交或回滚对数据库的修改。那么Oracle为了实现这个功能是否付出了很多的代价呢,下面对比一下自治事务和普通事务的统计信息:

SQL> CREATE GLOBAL TEMPORARY TABLE T_SESSION_STAT
  2  (ID NUMBER, NAME VARCHAR2(100), VALUE NUMBER)
  3  ON COMMIT PRESERVE ROWS;

表已创建。

SQL> CREATE TABLE T_RECORD (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
  2  BEGIN
  3   INSERT INTO T_RECORD VALUES (1, 'TEST');
  4   COMMIT;
  5  END;
  6  /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE P_TEST_AUTO AS
  2   PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4   INSERT INTO T_RECORD VALUES (2, 'TEST');
  5   COMMIT;
  6  END;
  7  /

过程已创建。

SQL> SET SERVEROUT OFF
SQL> BEGIN
  2  
  3   INSERT INTO T_SESSION_STAT SELECT 1, NAME, VALUE
  4   FROM V$SESSTAT A, V$STATNAME B
  5   WHERE A.STATISTIC# = B.STATISTIC#
  6   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
  7  
  8   P_TEST;
  9  
 10   INSERT INTO T_SESSION_STAT SELECT 2, NAME, VALUE
 11   FROM V$SESSTAT A, V$STATNAME B
 12   WHERE A.STATISTIC# = B.STATISTIC#
 13   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 14 
 15   P_TEST_AUTO;
 16  
 17   INSERT INTO T_SESSION_STAT SELECT 3, NAME, VALUE
 18   FROM V$SESSTAT A, V$STATNAME B
 19   WHERE A.STATISTIC# = B.STATISTIC#
 20   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 21 
 22   FOR C IN
 23   (
 24    SELECT *
 25    FROM
 26     (
 27      SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE
 28      FROM
 29       T_SESSION_STAT A,
 30       T_SESSION_STAT B,
 31       T_SESSION_STAT C
 32      WHERE A.NAME = B.NAME
 33      AND A.NAME = C.NAME
 34      AND A.ID = 1
 35      AND B.ID = 2
 36      AND C.ID = 3
 37     )
 38    WHERE ABS(VALUE) > 0
 39   ) LOOP
 40    DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
 41   END LOOP;
 42  
 43  END;
 44  /

PL/SQL 过程已成功完成。

SQL> TRUNCATE TABLE T_SESSION_STAT;

表被截断。

SQL> SET SERVEROUT ON
SQL> BEGIN
  2  
  3   INSERT INTO T_SESSION_STAT SELECT 1, NAME, VALUE
  4   FROM V$SESSTAT A, V$STATNAME B
  5   WHERE A.STATISTIC# = B.STATISTIC#
  6   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
  7  
  8   P_TEST;
  9  
 10   INSERT INTO T_SESSION_STAT SELECT 2, NAME, VALUE
 11   FROM V$SESSTAT A, V$STATNAME B
 12   WHERE A.STATISTIC# = B.STATISTIC#
 13   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 14 
 15   P_TEST_AUTO;
 16  
 17   INSERT INTO T_SESSION_STAT SELECT 3, NAME, VALUE
 18   FROM V$SESSTAT A, V$STATNAME B
 19   WHERE A.STATISTIC# = B.STATISTIC#
 20   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 21 
 22   FOR C IN
 23   (
 24    SELECT *
 25    FROM
 26     (
 27      SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE
 28      FROM
 29       T_SESSION_STAT A,
 30       T_SESSION_STAT B,
 31       T_SESSION_STAT C
 32      WHERE A.NAME = B.NAME
 33      AND A.NAME = C.NAME
 34      AND A.ID = 1
 35      AND B.ID = 2
 36      AND C.ID = 3
 37     )
 38    WHERE ABS(VALUE) > 0
 39   ) LOOP
 40    DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
 41   END LOOP;
 42  
 43  END;
 44  /
recursive cpu usage                               2
session logical reads                             6
CPU used by this session                          2
enqueue releases                                  -1
db block gets                                     4
db block gets from cache                          4
consistent gets                                   2
consistent gets from cache                        2
consistent gets - examination                     1
db block changes                                  6
consistent changes                                4
commit cleanout failures: block lost              -2
commit cleanouts                                  -2
calls to kcmgcs                                   1
calls to get snapshot scn: kcmgss                 1
redo size                                         168
undo change vector size                           108
commit txn count during cleanout                  1
IMU commits                                       1
IMU Flushes                                       -1
IMU undo allocation size                          316

PL/SQL 过程已成功完成。

由于第一次调用匿名块和过程会产生会导致递归调用信息,而这些可能干扰统计信息的结果,因此执行两次匿名块,取第二次的结果为最终结果。

从二者的差异可以看到,自治事务产生的redo和undo都比普通事务要大一些,这时可以理解的,不过现在只是插入一条记录,不能说明什么问题,关键看这种差异随事务的增大成比例增长还是基本维持原状。

下面修改两个存储过程:

SQL> CREATE OR REPLACE PROCEDURE P_TEST AS
  2  BEGIN
  3   FOR I IN 1..10000 LOOP
  4    INSERT INTO T_RECORD VALUES (1, 'TEST');
  5   END LOOP;
  6   COMMIT;
  7  END;
  8  /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE P_TEST_AUTO AS
  2   PRAGMA AUTONOMOUS_TRANSACTION;
  3  BEGIN
  4   FOR I IN 1..10000 LOOP
  5    INSERT INTO T_RECORD VALUES (2, 'TEST');
  6   END LOOP;
  7   COMMIT;
  8  END;
  9  /

过程已创建。

SQL> EXEC P_TEST

PL/SQL 过程已成功完成。

SQL> EXEC P_TEST_AUTO

PL/SQL 过程已成功完成。

SQL> TRUNCATE TABLE T_SESSION_STAT;

表被截断。

SQL> DELETE T_RECORD;

已删除20000行。

SQL> COMMIT;

提交完成。

SQL> BEGIN
  2  
  3   INSERT INTO T_SESSION_STAT SELECT 1, NAME, VALUE
  4   FROM V$SESSTAT A, V$STATNAME B
  5   WHERE A.STATISTIC# = B.STATISTIC#
  6   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
  7  
  8   P_TEST;
  9  
 10   INSERT INTO T_SESSION_STAT SELECT 2, NAME, VALUE
 11   FROM V$SESSTAT A, V$STATNAME B
 12   WHERE A.STATISTIC# = B.STATISTIC#
 13   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 14 
 15   P_TEST_AUTO;
 16  
 17   INSERT INTO T_SESSION_STAT SELECT 3, NAME, VALUE
 18   FROM V$SESSTAT A, V$STATNAME B
 19   WHERE A.STATISTIC# = B.STATISTIC#
 20   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 21 
 22   FOR C IN
 23   (
 24    SELECT *
 25    FROM
 26     (
 27      SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE
 28      FROM
 29       T_SESSION_STAT A,
 30       T_SESSION_STAT B,
 31       T_SESSION_STAT C
 32      WHERE A.NAME = B.NAME
 33      AND A.NAME = C.NAME
 34      AND A.ID = 1
 35      AND B.ID = 2
 36      AND C.ID = 3
 37     )
 38    WHERE ABS(VALUE) > 0
 39   ) LOOP
 40    DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
 41   END LOOP;
 42  
 43  END;
 44  /
opened cursors cumulative                         -2
recursive calls                                   -32
recursive cpu usage                               5
session logical reads                             -7
CPU used by this session                          1
enqueue requests                                  -2
enqueue releases                                  -3
db block gets                                     -3
db block gets from cache                          -3
consistent gets                                   -4
consistent gets from cache                        -4
consistent gets - examination                     1
db block changes                                  -3
consistent changes                                4
change write time                                 -2
free buffer requested                             -23
commit cleanout failures: block lost              -2
commit cleanouts                                  -1
commit cleanouts successfully completed           1
calls to kcmgcs                                   -5
calls to kcmgas                                   -16
calls to get snapshot scn: kcmgss                 -4
redo entries                                      -84
redo size                                         -2464
redo ordering marks                               -16
undo change vector size                           -104
no work - consistent read gets                    -3
deferred (CURRENT) block cleanout applications    -5
commit txn count during cleanout                  1
active txn count during cleanout                  2
cleanout - number of ktugct calls                 2
IMU undo allocation size                          24648
IMU Redo allocation size                          31200
table scans (short tables)                        -1
table scan rows gotten                            -16
table scan blocks gotten                          -2
cluster key scans                                 -1
cluster key scan block gets                       -1
index fetch by key                                -1
heap block compress                               -7
session cursor cache hits                         -1
buffer is not pinned count                        -1
parse count (total)                               -2
execute count                                     -2

PL/SQL 过程已成功完成。

IMU是10g的IN MEMORY UNDO的缩写,这个值比较大主要是由于前面进行了DELETE,后面又插入相同的数据,因此Oracle采用了IMU操作。抛去这个因素,其他的差异于事务本身代价相比就小得多了:

SQL> TRUNCATE TABLE T_SESSION_STAT;

表被截断。

SQL> BEGIN
  2  
  3   INSERT INTO T_SESSION_STAT SELECT 1, NAME, VALUE
  4   FROM V$SESSTAT A, V$STATNAME B
  5   WHERE A.STATISTIC# = B.STATISTIC#
  6   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
  7  
  8   P_TEST_AUTO;
  9  
 10   INSERT INTO T_SESSION_STAT SELECT 2, NAME, VALUE
 11   FROM V$SESSTAT A, V$STATNAME B
 12   WHERE A.STATISTIC# = B.STATISTIC#
 13   AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1);
 14 
 15   FOR C IN
 16   (
 17    SELECT *
 18    FROM
 19     (
 20      SELECT A.NAME, B.VALUE - A.VALUE VALUE
 21      FROM
 22       T_SESSION_STAT A,
 23       T_SESSION_STAT B
 24      WHERE A.NAME = B.NAME
 25      AND A.ID = 1
 26      AND B.ID = 2
 27     )
 28    WHERE ABS(VALUE) > 1000
 29   ) LOOP
 30    DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
 31   END LOOP;
 32  
 33  END;
 34  /
recursive calls                                   10182
session logical reads                             10602
db block gets                                     10492
db block gets from cache                          10492
db block changes                                  20293
redo entries                                      10124
redo size                                         2461664
undo change vector size                           642896
IMU undo allocation size                          12060
IMU Redo allocation size                          15200
execute count                                     10017

PL/SQL 过程已成功完成。

可见,自治事务所带来的额外的代价很小,基本上可以不用过多的考虑。

 

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-420426/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1964
  • 访问量
    11085795