oracle中的事務回滾,ORACLE 死事務的回滾

死事務出現在異常關閉數據庫或者事務進程不正常結束，比如KILL -9，shutdown abort的情況下。

當前數據庫里的死事務可以通過查詢內部表x$ktuxe來獲得。

select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL='DEAD';

ADDR?????????????? KTUXEUSN?? KTUXESLT?? KTUXESQN?? KTUXESIZ

---------------- ---------- ---------- ---------- ----------

00002B92FF5D5F68???????? 15???????? 12???? 314961??????43611

KTUXESIZ代表需要回滾的回滾塊數。

死事務的回滾進程數可以通過參數fast_start_parallel_rollback來設置。

show parameter fast

NAME???????????????????????????????? TYPE?????????????????? VALUE

------------------------------------ ---------------------- ------------------------------

fast_start_io_target???????????????? integer??????????????? 0

fast_start_mttr_target?????????????? integer??????????????? 120

fast_start_parallel_rollback???????? string???????????????? LOW

low的設置，會使當產生死事務的時候啟用2*CPU數個并行回滾。

我們來模擬一個事務被kill掉的情況，在delete的過程中，把這個事務的進程kill掉：

delete from test;

ERROR:

ORA-03114: not connected to ORACLE

delete from test

*

ERROR at line 1:

ORA-12152: TNS:unable to send break message

然后觀察數據庫后臺的等待事件，發現啟動了很多的回滾進程。

SID SPID?????? EVENT??????????????????????????????? P1???????? P2???????? P3 SQL_ID???????????? SECON

------- ---------- ------------------------- ------------- ---------- ---------- ------------------ -----

1707 2323?????? SQL*Net message to client??? 1650815232????????? 1????????? 0 3t37hp1cnkuux????? 0

762 2312?????? read by other session???????????????? 3???? 298397???????? 36??????????????????? 0

1327 2286?????? read by other session???????????????? 3???? 298367???????? 36??????????????????? 0

382 2308?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

384 2274?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

573 2276?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

574 2310?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

762 2312?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

763 2278?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

950 2280?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

951 2314?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1139 2282?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1141 2316?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1516 2320?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1517 2284?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1518 2318?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1519 2288?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1705 2290?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

191 2306?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

1892 2258?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

2 2270?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

4 2304?????? wait for a undo record??????????????? 0????????? 0????????? 0??????????????????? 0

top - 21:47:42 up 16 days,? 3:30,? 2 users,? load average: 3.44, 1.94, 1.35

Tasks: 295 total,?? 1 running, 294 sleeping,?? 0 stopped,?? 0 zombie

Cpu(s):? 2.6%us,? 1.2%sy,? 0.0%ni, 79.6%id, 16.3%wa,? 0.0%hi,? 0.2%si,? 0.0%st

Mem:? 24679196k total, 20316832k used,? 4362364k free,?? 666864k buffers

Swap: 20482864k total,???? 3004k used, 20479860k free,? 6074052k cached

PID USER????? PR? NI? VIRT? RES? SHR S %CPU %MEM??? TIME+? COMMAND

23773 oracle??? 16?? 0 1680m 100m? 45m D 11.6? 0.4?? 0:05.95 ora_arc0_xdbsb

2258 oracle??? 16?? 0 1600m? 17m? 14m S? 6.0? 0.1?? 0:01.56 ora_p000_xdbsb

15652 root????? 10? -5???? 0??? 0??? 0 D? 4.6? 0.0?? 0:10.26 [kjournald]

23642 oracle??? 15?? 0 1605m? 31m? 25m S? 2.7? 0.1?? 0:03.51 ora_smon_xdbsb

2264 oracle??? 15?? 0 1600m? 17m? 14m S? 1.7? 0.1?? 0:00.32 ora_p003_xdbsb

2266 oracle??? 15?? 0 1600m? 17m? 14m S? 1.7? 0.1?? 0:00.31 ora_p004_xdbsb

2306 oracle??? 16?? 0 1600m? 17m? 14m S? 1.7? 0.1?? 0:00.33 ora_p024_xdbsb

2262 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p002_xdbsb

2268 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p005_xdbsb

2270 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p006_xdbsb

2274 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p008_xdbsb

2280 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p011_xdbsb

2282 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p012_xdbsb

2286 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p014_xdbsb

2292 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p017_xdbsb

2298 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.32 ora_p020_xdbsb

2310 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.32 ora_p026_xdbsb

2312 oracle??? 16?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.31 ora_p027_xdbsb

2314 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.32 ora_p028_xdbsb

2318 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.30 ora_p030_xdbsb

2320 oracle??? 15?? 0 1600m? 17m? 14m S? 1.3? 0.1?? 0:00.32 ora_p031_xdbsb

2260 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.28 ora_p001_xdbsb

2272 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.30 ora_p007_xdbsb

2276 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.31 ora_p009_xdbsb

2278 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.30 ora_p010_xdbsb

2284 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.30 ora_p013_xdbsb

2288 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.33 ora_p015_xdbsb

2290 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.29 ora_p016_xdbsb

2294 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.33 ora_p018_xdbsb

2296 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.31 ora_p019_xdbsb

2300 oracle??? 16?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.33 ora_p021_xdbsb

2302 oracle??? 16?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.32 ora_p022_xdbsb

2304 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.30 ora_p023_xdbsb

2308 oracle??? 16?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.31 ora_p025_xdbsb

2316 oracle??? 15?? 0 1600m? 17m? 14m S? 1.0? 0.1?? 0:00.31 ora_p029_xdbsb

23634 oracle??? 15?? 0 1609m? 29m? 17m S? 0.7? 0.1?? 0:06.28 ora_dbw0_xdbsb

23636 oracle??? 15?? 0 1607m? 26m? 15m S? 0.7? 0.1?? 0:05.99 ora_dbw1_xdbsb

2321 oracle??? 15?? 0 12872 1252? 816 R? 0.3? 0.0?? 0:00.07 top

由于我的服務器上的CPU數較多，ORACLE啟動了2*CPU數個回滾進程，這些ora_pxxx_xdbsb都是后臺啟動的并行回滾的進程。經常會發現回滾的進程間會產生資源的爭用，例如buffer busy waits等待事件。導致回滾的速度非常慢，我們可以通過調整參數??fast_start_parallel_rollback?為false，這樣回滾的進程就只會有一個，速度有時候反而比并行回滾還快。而且也會解決回滾進程數太多，導致回滾進程占用了太多的系統資源。大事務運行過程中被異常kill掉是一件比較嚴重的事情，死事務的回滾可能會占用掉你很多的系統資源。

回滾過程中，我們通過觀察x$ktuxe.ktuxesiz減少的速度來評估回滾的速度。可以根據以下算法來粗略的估算回滾需要的時間，這里是小時：

declare

l_start number;

l_end?? number;

begin

select ktuxesiz

into l_start

from x$ktuxe

where KTUXEUSN = 10

and KTUXESLT = 39; ---------這里根據實際數字來填寫

dbms_lock.sleep(60);? ---------可以縮小這個時間，但是太小，可能會導致誤差較大

select ktuxesiz

into l_end

from x$ktuxe

where KTUXEUSN = 10

and KTUXESLT = 39; ---------這里根據實際數字來填寫

dbms_output.put_line('time cost Day:' ||

round(l_end / (l_start - l_end) / 60, 2));

end;

/

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/541363.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/541363.shtml
英文地址，請注明出處：http://en.pswp.cn/news/541363.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

大數據數據可視化設計原則_數據可視化設計的8頂帽子

大數據數據可視化設計原則_數據可視化設計的8頂帽子

大數據數據可視化設計原則8 hats of data visualization are basically the important persons and their roles that are basically required to carry out data visualization are as follows: 數據可視化有8個基本要素，而進行數據可視化所需的基本角色如下&#…
閱讀更多...
debian8.8安裝谷歌瀏覽器

debian8.8安裝谷歌瀏覽器

第一步：下載： wget https://dl.google.com/linux/direct/google-chrome-stable_current_i386.deb //32位 wget https://dl.google.com/linux/direct/google-chrome-stable_current_amd64.deb //64位第二步：安裝dpkg -i google-chrome*.deb…
閱讀更多...
MB_LEN_MAX常數，帶C ++示例

MB_LEN_MAX常數，帶C ++示例

C MB_LEN_MAX宏常量 (C MB_LEN_MAX macro constant) MB_LEN_MAX constant is a macro constant which is defied in climits header, it is used to get the maximum number of bytes in a multibyte character, for any locale, it returns maximum number of bytes that a m…
閱讀更多...
php谷歌收錄接口,php實現查詢百度google收錄情況(示例代碼)

php谷歌收錄接口,php實現查詢百度google收錄情況(示例代碼)

PHP$SEOdetail array();$domain !empty($_GET[q]) ? $_GET[q] : www.mycodes.net;baidudetail($domain);googledetail($domain);var_dump($SEOdetail);function baidudetail($domain) {$baidu_site http://www.baidu.com/baidu?wordsite%3A . $domain;$baidu_link http:/…
閱讀更多...
Linux學習第三步(Centos7安裝mysql5.7數據庫)

Linux學習第三步(Centos7安裝mysql5.7數據庫)

版本：mysql-5.7.16-1.el7.x86_64.rpm-bundle.tar 前言：在linux下安裝mysql不如windows下面那么簡單，但是也不是很難。本文向大家講解了如何在Centos7下如何安裝mysql5.7版本,如果有什么問題和錯誤的地方，歡迎大家指出。 注釋&…
閱讀更多...
linux oracle刪除恢復數據恢復,Linux下Oracle誤刪除數據文件恢復操作

linux oracle刪除恢復數據恢復,Linux下Oracle誤刪除數據文件恢復操作

檢查數據文件的位置如下：SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/app/Oracle/oradata/marven/system01.dbf/u01/app/oracle/oradata/marven/undotbs1.dbf/u01/app/oracle/…
閱讀更多...
數據庫如何處理數據庫太大_網絡數據庫中的數據處理

數據庫如何處理數據庫太大_網絡數據庫中的數據處理

數據庫如何處理數據庫太大Before learning the data manipulation in a network model, we are discussing data manipulation language, so what is the data manipulation language? 在學習網絡模型中的數據操作之前，我們正在討論數據操作語言，那么什…
閱讀更多...
鏈接器和加載器 好書

鏈接器和加載器 好書

鏈接器和加載器
閱讀更多...
oracle12537錯誤,ORA-12537:TNS:connection closed錯誤處理方法

oracle12537錯誤,ORA-12537:TNS:connection closed錯誤處理方法

1.ORA-12537:TNS:connection closed錯誤處理過程檢查監聽正常，Oracle服務也是正常啟動的，但是登錄不進去。2.解決方案1. cd $ORACLE_HOME/bin/ 進入bin目錄2. ll oracle-rwxrwxrwx. 1 ora12 dba 323762222 6?. 14 19:12 oracle3.chmod 6571 oracle 更改…
閱讀更多...
操作系統中的死鎖_操作系統中的死鎖介紹

操作系統中的死鎖_操作系統中的死鎖介紹

操作系統中的死鎖1.1究竟什么是僵局？ (1.1 What exactly is a deadlock?) In a multiprogramming environment, there may be several processes with a finite number of resources. A process may request another resource while still holding some of the oth…
閱讀更多...
《云數據管理：挑戰與機遇》2.3　數據庫系統

《云數據管理：挑戰與機遇》2.3　數據庫系統

本節書摘來自華章出版社《云數據管理》一書中的第2章，第3節，作者迪衛艾肯特阿格拉沃爾，更多章節內容可以訪問云棲社區“華章計算機”公眾號查看本節中，我們將為數據庫系統中的一些主要概念提供一個相當抽象、簡潔和高層次的描述。…
閱讀更多...
sql server與oracle的分頁,詳解SQLServer和Oracle的分頁查詢

sql server與oracle的分頁,詳解SQLServer和Oracle的分頁查詢

不管是DRP中的分頁查詢代碼的實現還是面試題中看到的關于分頁查詢的考察，都給我一個提示：分頁查詢是重要的。當數據量大的時候是必須考慮的。之前一直沒有花時間停下來好好總結這里。現在又將Oracle視頻中關于分頁查詢的內容看了一遍，發現很容…
閱讀更多...
java treemap_Java TreeMap lastEntry（）方法與示例

java treemap_Java TreeMap lastEntry（）方法與示例

java treemapTreeMap類的lastEntry()方法 (TreeMap Class lastEntry() method) lastEntry() method is available in java.util package. lastEntry()方法在java.util包中可用。 lastEntry() method is used to return the entry (key-value pairs) that exists with the large…
閱讀更多...
LeetCode OJ 之 Valid Anagram

LeetCode OJ 之 Valid Anagram

題目： Given two strings s and t, write a function to determine if t is an anagram of s. For example,s "anagram", t "nagaram", return true.s "rat", t "car", return false. Note: You may assume the string…
閱讀更多...
oracle光標位置無效,解決在Form表單中光標移動不了問題

oracle光標位置無效,解決在Form表單中光標移動不了問題

apply p8727236_10123 for Developer Suite 10.1.2.3 in Linux首先到oracle的技術支持下載所需補丁,然后1先打補丁7121788，把p7121788_10123_LINUX.zip解壓到/home/oracledev目錄下(ORACLE_HOME為/u01/app/oracledev/OraHome_dev)$cd /home/oracledev/7121788$expo…
閱讀更多...
java treemap_Java TreeMap HigherKey（）方法與示例

java treemap_Java TreeMap HigherKey（）方法與示例

java treemapTreeMap類HigherKey()方法 (TreeMap Class higherKey() method) higherKey() method is available in java.util package. HigherKey()方法在java.util包中可用。 higherKey() method is used to return the lowest key value element higher than the given key e…
閱讀更多...
centos配置ipv6地址

centos配置ipv6地址

首先打開網站注冊一個賬號：http://www.tunnelbroker.net創建一個ipv6的地址：把下面的命令在linux上執行一遍，這個方式是臨時生效，重啟網卡和重啟系統自動失效。把上面的命令保存到一個配置文件中：vi /etc/sysconfig/ne…
閱讀更多...
php oracle 需要libmysql.dll么_,Windows7環境下Apache+PHP+MySQL完美配置

php oracle 需要libmysql.dll么_,Windows7環境下Apache+PHP+MySQL完美配置

寫作此篇文章的目的在于記錄Windows 7環境下成功配置WAMP環境, 初學者在不使用整合好的WAMPServer和XAMPP的情況下徒手配置整合環境貌似有很多意想不到的問題. 這將是我們需要討論的.我將重現幾個經典的問題, 并一一排除. 希望對各位看官有點借鑒作用.一. Apache在整合PHP后無法…
閱讀更多...
stringreader_Java StringReader skip（）方法與示例

stringreader_Java StringReader skip（）方法與示例

stringreaderStringReader類skip()方法 (StringReader Class skip() method) skip() method is available in java.io package. skip()方法在java.io包中可用。 skip() method is used to skip the given number of characters in the stream. skip()方法用于跳過流中給定數量的…
閱讀更多...
NFS部署及優化（一）

NFS部署及優化（一）

NFS部署及優化（一）一、NFS的基本概念NFS network file system 網絡文件系統必然通過網絡通信來實現文件的訪問和寫入，所以做這個實驗的話最好有兩臺虛擬機配置:A：一個192.169.50.201為server端B：一個192.169.50.200為…
閱讀更多...
最新文章

Copyright @ 2022~2023