ROLLBACK相關的統計信息
2024-07-21 02:34:19
供稿：網友
與ROLLBACK相關的統計信息有如下幾個，我們關注什么情況下這些統計信息會增加。 SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%'; NAME VALUE -------------------------------------------------- --------- user rollbacks 0 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 0 transaction rollbacks 0 初始情況下： SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%'; NAME VALUE -------------------------------------------------- --------- user rollbacks 0 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 0 transaction rollbacks 0 6 rows selected. 未作任何改動，執行rollback SQL> rollback; Rollback complete. SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and name like '%rollback%'; NAME VALUE ---------------------------------------------------------------- user rollbacks 1 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 0 transaction rollbacks 0 6 rows selected. 統計信息user rollbacks 增加，即使沒有做任何改動（沒有什么可以rollback的），user rollbacks也會增加。 SQL> connect / SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%'; NAME VALUE ---------------------------------------------------------------- user rollbacks 0 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 0 transaction rollbacks 0 6 rows selected. 插入一行紀錄，然后回滾 SQL> insert into t values(1); 1 row created. SQL> rollback; Rollback complete. SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%'; NAME VALUE ---------------------------------------------------------------- - user rollbacks 1 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0
cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 2 transaction rollbacks 1 6 rows selected. 除了user rollbacks增加外，當有活動的事務回滾時候，因為有實際工作可做，transaction rollbacks增加，且因為回滾記錄的應用，rollback changes - undo records applied增加。 SQL> insert into t values(1); 1 row created. SQL> insert into t values(1); 1 row created. SQL> commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (Oracle.SYS_C0020256) violated SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%'; NAME VALUE ---------------------------------------------------------------- ---- user rollbacks 1 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 8 transaction rollbacks 2 6 rows selected. 如上，因為發生unique constraint violated錯誤，Statement自動回滾（到默認的savepoint），有回滾工作可做，則transaction rollbacks 和rollback changes - undo records applied都增加。 SQL> select * from t; no rows selected SQL> rollback; Rollback complete. SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%'; NAME VALUE ---------------------------------------------------------------- - user rollbacks 2 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 8 transaction rollbacks 2 6 rows selected. 因為Statement已經回滾，沒有實際的回滾工作量，因此只有user rollbacks增加。 SQL> connect / 如下 測試因為Write consistent導致的mini-rollback SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.sta NAME VALUE -------------------------------------------------------------- user rollbacks 0 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 0 rollback changes - undo records applied 0 transaction rollbacks 0 6 rows selected. SQL> update t set x=x*10 where x in (1,5,10); 2 rows updated. 之前該session被另外的一個session在更新x=5處被阻塞； SQL> select s.name,m.value from v$mystat m,v$statname s where s.statistic#=m.statistic# and upper(name) like '%ROLLBACK%'; NAME VALUE ---------------------------------------------------------------- -
user rollbacks 0 transaction tables consistent read rollbacks 0 rollbacks only - consistent read gets 0 cleanouts and rollbacks - consistent read gets 1 rollback changes - undo records applied 3 transaction rollbacks 0 6 rows selected. 如上的mini-rollback 會導致cleanouts and rollbacks - consistent read gets 增加且帶往返滾（rollback changes - undo records applied）。 結論是： 1) 'user rollbacks' 當用戶執行rollback的時候每次遞增，而不管是否有實際的回滾工作量要做。 2) 'transaction rollbacks'在如下3種情況下會遞增 當用戶執行 'rollback' 回滾了之前的DML操作,因此有實際的回滾工作要做。 進程中斷 terminated or killed, PMON 代替執行回滾。 DML 發生錯誤，如. unique constraint violation，執行Statement級別的語句回滾 3) 'user rollbacks' 和'transaction rollbacks' 不會因為"write consistency" mini-rollback 的原因增加。 4)mini-rollback會導致cleanouts and rollbacks - consistent read gets增加。當發生mini-rollback的時候，update/delete語句會釋放掉所有已更新的block的鎖（cleanout），然后再以select for update(v$lock.mode=2) 先“悲觀”鎖住要更新的紀錄，在執行update/delete.(v$lock.mode=3)
