国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 開發 > 綜合 > 正文

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)

上一篇:數據庫恢復實例

下一篇:Index Range Scan

發表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發表
主站蜘蛛池模板: 聂荣县| 义马市| 本溪市| 新巴尔虎左旗| 威宁| 吴旗县| 余庆县| 芮城县| 吉水县| 黄陵县| 阳泉市| 固镇县| 九龙坡区| 石楼县| 青铜峡市| 东宁县| 左权县| 黔西| 湛江市| 霞浦县| 蒲城县| 双流县| 溧阳市| 乌拉特前旗| 安溪县| 吉木萨尔县| 清涧县| 淮北市| 吴江市| 呼伦贝尔市| 东山县| 鹤壁市| 平阴县| 湖北省| 忻城县| 达孜县| 澎湖县| 泗水县| 隆昌县| 历史| 安国市|