小议自治事务（六）

最新推荐文章于 2021-04-03 08:29:08 发布

ckawt40802

最新推荐文章于 2021-04-03 08:29:08 发布

阅读量84

点赞数

这两天看了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