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)