oracle 11g不同會話產生的事務會使用相同的undo segment嗎--undo系列之一
背景
不同會話產生的事務會不會同時使用相同的undo segment呢，本文我們測試這個問題，加深對於undo概念的理解。結論
1,dba_rollback_segs初始時即資料庫啟動有建立產生11個回滾段，後期隨著資料庫事務的增加，會動態調整增加但是如果已經建立的回滾段，不會因為事務已提交而回收
2,不同的事務會分配不同的undo segment,而不會出現多個事務分配相同的undo segment
3,隱含引數_rollback_segment_count不是控制或限制資料庫建立多少個回滾段
4,對於undo機制的理解仍停留在表層，還要很多工作和知識要補充
測試
資料庫版本
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
1,資料庫目前的回滾段資訊，共計11個
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
11 rows selected.
會話1
SQL> update scott.t_undo set a=3 where rownum=1;
1 row updated.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
會話2
SQL> create table t_oun(a int);
Table created.
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
會話3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
會話4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
會話5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
會話6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
會話7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
會話8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
會話9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
會話10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
會話11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
可見undo segment的數量是隨著資料庫的事務情況動態變化的，不會一成不變
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
13 rows selected.
會話12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
會話13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
回滾所有上述的13個會話的事務，發現已產生的14個回滾段沒有回收
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
14 rows selected.
獲取與udno segment相關的隱含引數
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
transactions_per_rollback_segment 5 number of active transactions per rollback segment
_rollback_segment_count 0 number of undo segments
可見每個undo segment目前配置最大的活動事務個數為5個，而可以建立的undo segment個數為0，即表示不受限制
當前佔用回滾段個數為13，剛好和dba_rollback_segs一致（因為還有有一個system rollback segment）
SQL> select status,count(*) from dba_undo_extents group by status;
STATUS COUNT(*)
------------------ ----------
UNEXPIRED 13
EXPIRED 34
SQL> select count(*) from dba_rollback_segs;
COUNT(*)
----------
14
顯式配置_rollback_segment_count為5個,限制回滾段個數
SQL> alter system set "_rollback_segment_count"=5;
System altered.
會話1
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU2_995037188$
會話2
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU1_2507315526$
會話3
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU27_3263502189$
會話4
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU28_1986380219$
會話5
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU29_193706344$
會話6
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU26_2187890310$
會話7
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU23_2210640156$
會話8
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU22_185849310$
會話9
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU3_2358976981$
會話10
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU30_4054687248$
會話11
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU25_848072715$
會話12
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU24_2463137615$
會話13
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU31_4292867569$
會話14
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU4_2259594378$
會話15
SQL> insert into t_oun values(1);
1 row created.
SQL> select name from v$rollname where usn=(select xidusn from v$transaction where ses_addr=(select saddr from v$session where sid=( select sid from v$mystat where rownum=1)));
NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
可見隱含引數_rollback_segment_count不能控制或限制資料庫建立多少個回滾段
SQL> select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------------------------------------
SYSTEM
_SYSSMU31_4292867569$
_SYSSMU30_4054687248$
_SYSSMU29_193706344$
_SYSSMU28_1986380219$
_SYSSMU27_3263502189$
_SYSSMU26_2187890310$
_SYSSMU25_848072715$
_SYSSMU24_2463137615$
_SYSSMU23_2210640156$
_SYSSMU22_185849310$
SEGMENT_NAME
------------------------------------------------------------
_SYSSMU5_3454406355$
_SYSSMU4_2259594378$
_SYSSMU3_2358976981$
_SYSSMU2_995037188$
_SYSSMU1_2507315526$
16 rows selected.
個人簡介
8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶：
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通4G資料庫效能分析與最佳化
中國聯通4G資料庫效能分析與最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1814233/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle undo segment header 事務表transaction table系列一OracleHeader
- Oracle Undo SegmentOracle
- 不同insert操作產生的undo的測試
- How to Shrink Undo Segment In Oracle DatabaseOracleDatabase
- Oracle中undo 如何產生RedoOracle
- oracle dml產生undo的區別小測試Oracle
- oracle undo系列(三)Oracle
- oracle undo系列(二)Oracle
- 從Undo, Redo, DataFile看Oracle中的事務過程Oracle
- SMON: recover undo segment與並行事務恢復並行
- Oracle 通過undo塊檢視事務資訊Oracle
- UNDO SEGMENT的擴充套件和收縮套件
- oracle undo 使用分析Oracle
- oracle redo和undo系列一Oracle Redo
- 哪種DML操作產生undo多
- undo segment header中記錄的dba是該事務使用的單向連結串列中的頭(end block)!HeaderBloC
- 淺析MySQL事務中的redo與undoMySql
- Oracle Undo 的配置Oracle
- Oracle Undo的作用Oracle
- undo segment的建立、線上以及extent的分配原則。
- 使用oracle 11g bbed dump undo header block報錯之系列六OracleHeaderBloC
- oracle的undo的作用Oracle
- 《Undo, Redo, DataFile看Oracle中的事務過程》學習筆記Oracle筆記
- impdp操作產生大量UNDO的原因及解決方法
- Oracle Undo的學習Oracle
- 事務複製會話 (三)會話
- 事務複製會話 (二)會話
- 事務複製會話 (四)會話
- 事務複製會話 (五)會話
- (轉) oracle 臨時表（事務級、會話級）Oracle會話
- Oracle效能最佳化之Rollback(undo)Segment最佳化（轉）Oracle
- 【會話】Oracle kill session系列會話OracleSession
- undo segment的建立、online以及extent的分配原則
- Oracle Redo and UndoOracle Redo
- Oracle undo 管理Oracle
- oracle undo管理Oracle
- oracle undo一Oracle
- 淺談Oracle的undo管理Oracle