自治事务提供了很方便的事务控制功能使得用户可以在不影响当前事务的情况下提交或回滚对数据库的修改那么Oracle为了实现这个功能是否付出了很多的代价呢下面对比一下自治事务和普通事务的统计信息
SQL>CREATEGLOBALTEMPORARYTABLET_SESSION_STAT
(IDNUMBERNAMEVARCHAR()VALUENUMBER)
ONCOMMITPRESERVEROWS;
表已创建
SQL>CREATETABLET_RECORD(IDNUMBERNAMEVARCHAR());
表已创建
SQL>CREATEORREPLACEPROCEDUREP_TESTAS
BEGIN
INSERTINTOT_RECORDVALUES(TEST);
COMMIT;
END;
/
过程已创建
SQL>CREATEORREPLACEPROCEDUREP_TEST_AUTOAS
PRAGMAAUTONOMOUS_TRANSACTION;
BEGIN
INSERTINTOT_RECORDVALUES(TEST);
COMMIT;
END;
/
过程已创建
SQL>SETSERVEROUTOFF
SQL>BEGIN
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST_AUTO;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
FORCIN
(
SELECT*
FROM
(
SELECTANAMECVALUE+AVALUE*BVALUEVALUE
FROM
T_SESSION_STATA
T_SESSION_STATB
T_SESSION_STATC
WHEREANAME=BNAME
ANDANAME=CNAME
ANDAID=
ANDBID=
ANDCID=
)
WHEREABS(VALUE)>
)LOOP
DBMS_OUTPUTPUT_LINE(RPAD(CNAME)||CVALUE);
ENDLOOP;
END;
/
PL/SQL过程已成功完成
SQL>TRUNCATETABLET_SESSION_STAT;
表被截断
SQL>SETSERVEROUTON
SQL>BEGIN
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST_AUTO;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
FORCIN
(
SELECT*
FROM
(
SELECTANAMECVALUE+AVALUE*BVALUEVALUE
FROM
T_SESSION_STATA
T_SESSION_STATB
T_SESSION_STATC
WHEREANAME=BNAME
ANDANAME=CNAME
ANDAID=
ANDBID=
ANDCID=
)
WHEREABS(VALUE)>
)LOOP
DBMS_OUTPUTPUT_LINE(RPAD(CNAME)||CVALUE);
ENDLOOP;
END;
/
recursive cpu usage
session logical reads
CPU used by this session
enqueue releases
db block gets
db block gets from cache
consistent gets
consistent gets from cache
consistent gets examination
db block changes
consistent changes
commit cleanout failures: block lost
commit cleanouts
calls to kcmgcs
calls to get snapshot scn: kcmgss
redo size
undo change vector size
commit txn count during cleanout
IMU commits
IMU Flushes
IMU undo allocation size
PL/SQL 过程已成功完成
由于第一次调用匿名块和过程会产生会导致递归调用信息而这些可能干扰统计信息的结果因此执行两次匿名块取第二次的结果为最终结果
从二者的差异可以看到自治事务产生的redo和undo都比普通事务要大一些这时可以理解的不过现在只是插入一条记录不能说明什么问题关键看这种差异随事务的增大成比例增长还是基本维持原状
下面修改两个存储过程
SQL>CREATEORREPLACEPROCEDUREP_TESTAS
BEGIN
FORIINLOOP
INSERTINTOT_RECORDVALUES(TEST);
ENDLOOP;
COMMIT;
END;
/
过程已创建
SQL>CREATEORREPLACEPROCEDUREP_TEST_AUTOAS
PRAGMAAUTONOMOUS_TRANSACTION;
BEGIN
FORIINLOOP
INSERTINTOT_RECORDVALUES(TEST);
ENDLOOP;
COMMIT;
END;
/
过程已创建
SQL>EXECP_TEST
PL/SQL 过程已成功完成
SQL>EXECP_TEST_AUTO
PL/SQL 过程已成功完成
SQL>TRUNCATETABLET_SESSION_STAT;
表被截断
SQL>DELETET_RECORD;
已删除行
SQL>COMMIT;
提交完成
SQL>BEGIN
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST_AUTO;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
FORCIN
(
SELECT*
FROM
(
SELECTANAMECVALUE+AVALUE*BVALUEVALUE
FROM
T_SESSION_STATA
T_SESSION_STATB
T_SESSION_STATC
WHEREANAME=BNAME
ANDANAME=CNAME
ANDAID=
ANDBID=
ANDCID=
)
WHEREABS(VALUE)>
)LOOP
DBMS_OUTPUTPUT_LINE(RPAD(CNAME)||CVALUE);
ENDLOOP;
END;
/
opened cursors cumulative
recursive calls
recursive cpu usage
session logical reads
CPU used by this session
enqueue requests
enqueue releases
db block gets
db block gets from cache
consistent gets
consistent gets from cache
consistent gets examination
db block changes
consistent changes
change write time
free buffer requested
commit cleanout failures: block lost
commit cleanouts
commit cleanouts successfully completed
calls to kcmgcs
calls to kcmgas
calls to get snapshot scn: kcmgss
redo entries
redo size
redo ordering marks
undo change vector size
no work consistent read gets
deferred (CURRENT) block cleanout applications
commit txn count during cleanout
active txn count during cleanout
cleanout number of ktugct calls
IMU undo allocation size
IMU Redo allocation size
table scans (short tables)
table scan rows gotten
table scan blocks gotten
cluster key scans
cluster key scan block gets
index fetch by key
heap block compress
session cursor cache hits
buffer is not pinned count
parse count (total)
execute count
PL/SQL 过程已成功完成
IMU是g的IN MEMORY UNDO的缩写这个值比较大主要是由于前面进行了DELETE后面又插入相同的数据因此Oracle采用了IMU操作抛去这个因素其他的差异于事务本身代价相比就小得多了
SQL>TRUNCATETABLET_SESSION_STAT;
表被截断
SQL>BEGIN
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
P_TEST_AUTO;
INSERTINTOT_SESSION_STATSELECTNAMEVALUE
FROMV$SESSTATAV$STATNAMEB
WHEREASTATISTIC#=BSTATISTIC#
ANDASID=(SELECTSIDFROMV$MYSTATWHEREROWNUM=);
FORCIN
(
SELECT*
FROM
(
SELECTANAMEBVALUEAVALUEVALUE
FROM
T_SESSION_STATA
T_SESSION_STATB
WHEREANAME=BNAME
ANDAID=
ANDBID=
)
WHEREABS(VALUE)>
)LOOP
DBMS_OUTPUTPUT_LINE(RPAD(CNAME)||CVALUE);
ENDLOOP;
END;
/
recursive calls
session logical reads
db block gets
db block gets from cache
db block changes
redo entries
redo size
undo change vector size
IMU undo allocation size
IMU Redo allocation size
execute count
PL/SQL 过程已成功完成
可见自治事务所带来的额外的代价很小基本上可以不用过多的考虑
