Statement used to query dead transactions in Oracle

Source: Internet
Author: User

Statement used to query dead transactions in Oracle

Statement used to query dead transactions in Oracle

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
2 KTUXESTA Status, KTUXECFL Flags, KTUXESIZ
3 FROM x $ ktuxe
4 WHERE ktuxesta! = 'Inactivity ';


KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2819919
39 0 502 active none 1
43 45 480 active none 0

 

SQL>/


KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2819919 <---- the value is not reduced.
39 0 502 active none 1
43 45 480 active none 0

 

Query v $ px_session and v $ fast_start_servers. It shows that many parallel processes are in rollback. Based on previous engineering experience:

So change

SQL> alter system set fast_start_parallel_rollback = false scope = both;

Then run again

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
2 KTUXESTA Status, KTUXECFL Flags, KTUXESIZ
3 FROM x $ ktuxe
4 WHERE ktuxesta! = 'Inactivity ';
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2033516
35 29 502 active none 1
 
SQL>/
 
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2033433 <--- the value keeps decreasing.
35 29 502 active none 1

Run the following script to check the Estimated rollback time (in days ):

SQL> set serveroutput on
SQL> declare
2 Rochelle start number;
3 Rochelle end number;
4 begin
5 select ktuxesiz into l_startfrom x $ ktuxe where KTUXEUSN = 13 and KTUXESLT = 5;
6 dbms_lock.sleep (60 );
7 select ktuxesiz into l_endfrom x $ ktuxe where KTUXEUSN = 13 and KTUXESLT = 5;
8 dbms_output.put_line ('time estDay: '| round (l_end/(l_start-l_end)/60/24, 2 ));
9 end;
10/
Time est Day:. 21

24*0.21 = 5.04 hours. That is, the rollback is expected to be completed within 5.04 hours.
 
Note: in other environments, replace KTUXEUSN with KTUXESLT = 5.

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.