Почему долго висят сессии в статусе killed в Oracle 12.2.0.1?

Problem Description
-------------------

You did not commit your transactions and the session were accidentally
killed. Your transactions are rolling back and it is taking a long time.
Rollback started hours ago and is still in progress.

You want to know if there is any way to speed up the process such as using
cleanup_rollback_entries in the init.ora and then restarting the database.

You also want to know what will happen if you shutdown the database after 12
hours of rollback. Will the rollback pick up where it left off?


Solution Description
--------------------

There is no way to speed up the rollback process and there is no formula for
determining how long it will take to complete. It depends on what type of
undo the application has generated. Some undo may take little space in an
undo block, but may take awhile to apply.

You can look at used_ublk in V$transaction to estimate how long it is going
to take to complete the rollback.

SQL> SELECT a.used_ublk
FROM v$transaction a, v$session b
WHERE a.addr = b.taddr AND b.sid = ;

For example:

If used_ublk showed 29,900 12 hours ago and is now 22,900, it has
taken 12 hours to rollback 7,000 entries. It will take approximately
another 36 hours to complete depending on the types of transactions
that are rolling back.

CLEANUP_ROLLBACK_ENTRIES determines how long SMON will be holding onto one
transaction's resources. It only affects recovery of transactions in the
background such as after an instance crash. It doesn't affect rollback
by the transaction itself.

Rollback will pick up where it left off if you do shutdown after 12 hours
of rollback.

Solution Explanation
--------------------

You can use V$transaction used_ublk to estimate how long the rollback is
going to take but there is no formula for this. If you shutdown the
database after rollback has started, it will begin where it left off.

For Oracle 9i and onwards ,check :
SQL> SELECT DISTINCT ktuxesiz
FROM x$ktuxe
WHERE ktuxecfl='DEAD';

Solution References
-------------------


Note:30696.1 "PARAMETER: INIT.ORA: CLEANUP_ROLLBACK_ENTRIES"

Related information can be found in Note 231713.1 - Database appears hanging but generates huge amount of redo and
archived redo logs.

APPLIES TO:
Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
PURPOSE
This document briefly describes how to suppress sessions marked killed in v$session.

SCOPE
Killing oracle sessions useful snippet for DBAs.

DETAILS
ALTER SYSTEM KILL Session Marked for Killed Forever:

===================================================

You have a session that you have killed, but it seems as though it will not go away:

SQL> alter system kill session 'sid, serial#';

SQL> select status, username from v$session;



You have issued this several times and it seems it still is marked as killed.

In order to determine which process to kill:

a) On a Unix platform:

SQL> SELECT spid FROM v$process WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr);

or

SQL> SELECT inst_id, status, event, state, blocking_session, sid, serial#, program FROM gv$session WHERE status = 'KILLED';

% kill -9 spid

b) On a Windows platform:

SQL> SELECT spid, osuser, s.program FROM v$process p, v$session s WHERE p.addr=s.paddr;

Then use the orakill utility at the DOS prompt:

c:\> orakill

where = the Oracle instance name (ORACLE_SID) = the thread id of the thread to kill



Explanation:

============

The simplest (and probably most common) reason the session stays around is because the process is still around. The reason the process is still around is because it is waiting on "SQLNet message from client". If it does ever get a message, it will then respond with an ORA-28 "Your session has been killed" error number. At that point the session should go away. The dedicated server process may remain alive until the client disconnects or exits.

PMON may take ownership of the session while it is cleaning up any resources held by the session at the time it was killed. If it cannot clean everything up immediately it will leave the session under the PSEUDO process while performing other tasks.

By finding the spid you can then force the process to be killed. When issuing the 'kill' command be sure that you kill "DEDICATED SERVER PROCESSES", those called:

oracle (local=NO)

where is the ORACLE_SID.

Be sure you do not kill processes such as:

ora_d000_

ora_s000_

ora_pmon_



Related Documents:

=================

Note:100859.1 ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT

Note:1041427.6 KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION ROW FROM V$SESSION

Note:1023442.6 HOW TO HAVE ORACLE CLEAN-UP OLD USER INFO AFTER KILLING SESSION UNDER MTS

Note:387077.1 How to find the process identifier (pid, spid) after the corresponding session is killed?