Oracle когда ушла сессия
Перейти к содержимому

Oracle когда ушла сессия

  • автор:

Oracle когда ушла сессия

V$SESSION_WAIT displays the current or last wait for each session.

Session identifier; maps to V$SESSION.SID

A number that uniquely identifies the current or last wait (incremented for each wait)

If the session is currently waiting, then the resource or event for which the session is waiting. If the session is not in a wait, then the resource or event for which the session most recently waited.

Description of the first wait event parameter

First wait event parameter (in decimal)

First wait event parameter (in hexadecimal) Foot 1

Description of the second wait event parameter

Second wait event parameter (in decimal)

Second wait event parameter (in hexadecimal) Foot 1

Description of the third wait event parameter

Third wait event parameter (in decimal)

Third wait event parameter (in hexadecimal) Foot 1

Identifier of the class of the wait event

Number of the class of the wait event

Name of the class of the wait event

If the session is currently waiting, then the value is 0 . If the session is not in a wait, then the value is as follows:

> 0 — Value is the duration of the last wait in hundredths of a second

-1 — Duration of the last wait was less than a hundredth of a second

-2 — Parameter TIMED_STATISTICS was set to false

This column has been deprecated in favor of the columns WAIT_TIME_MICRO and STATE .

If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait.

This column has been deprecated in favor of the columns WAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO .

WAITING — Session is currently waiting

WAITED UNKNOWN TIME — Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false

WAITED SHORT TIME — Last wait was less than a hundredth of a second

WAITED KNOWN TIME — Duration of the last wait is specified in the WAIT_TIME column

Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait.

Value is interpreted as follows:

> 0 — Amount of time remaining for the current wait (in microseconds)

0 — Current wait has timed out

-1 — Session can indefinitely wait in the current wait

NULL — Session is not currently waiting

Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0 .

The ID of the container to which the data pertains. Possible values include:

0 : This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

1 : This value is used for rows containing data that pertain to only the root

n : Where n is the applicable container ID for the rows containing data

The P1RAW , P2RAW , and P3RAW columns display the same values as the P1 , P2 , and P3 columns, except that the numbers are displayed in hexadecimal.

Oracle когда ушла сессия

For a long time DBAs have been encouraged to use variations on the YAPP method of performance tuning, which focuses on wait event monitoring, rather than hit ratios. Tools like Statspack, AWR, ADDM and SQL Trace are all very useful for gathering wait event information during tuning, but they tend to focus on looking back at what has happened, rather than what is currently happening. The [G]V$ dynamic performance views provide masses of real-time information, but it can be difficult for beginners and experienced people alike to make good use of this information.

Oracle 10g introduced the Active Session History (ASH) as part of the Diagnostics and Tuning Pack. It samples information from the [G]V$ views allowing you to see current and historical information about active sessions on the database.

Being part of the Diagnostics and Tuning Pack means ASH is only available as a paid option on top of Oracle Database Enterprise Edition.


Samples of wait event information are taken once per second and made available using the V$ACTIVE_SESSION_HISTORY view. An active session is one that is waiting on CPU or any event that does not belong to the «Idle» wait class at the time of the sample. The sample information is written to a circular buffer in the SGA, so the greater the database activity, the less time the information will remain available for.

The V$ACTIVE_SESSION_HISTORY view is essentially a fact table, which can be linked to a number of dimensions to provide statistics specific to a variety of things including SQL statements, execution plans, objects, wait events, sessions, modules, actions, client identifiers, services and consumer groups etc. This makes it an incredibly flexible way of identifying what active sessions are doing, or have done. For example, if I wanted to see the main activity on the database for the last 5 minutes, I could use the following query.

Notice how the count of the samples is used to determine the time waited, not the WAIT_TIME or TIME_WAITED columns. Why is this done? Remember, this is sample data, so wait times are accumulating with each sample. Merely summing them will give a falsely high value. To explain this, imagine simplified case where a single session is waiting on «db file sequential read» for 5 seconds. That means we would have 5 samples, that may look like this.

We know the total wait time is 5 seconds. If we count the number of samples, we get 5, which we equate to 5 seconds. If we sum the time for all 5 samples get 15 seconds.

The time columns in the ASH data are a little more complicated than they first appear, so check the documentation when using them. Incorrect usage of these columns is probably the most common mistake people make when using ASH views.

The fact ASH uses samples can of course mean certain bits of information slip through the net, but this is not really a problem. The chances are your main concerns will be those sessions or statements that are taking lots of time. The longer things take to run, the more samples they are likely to be picked up in. It is unlikely that anything of major concern will completely fail to register in the ASH views, but that doesn’t mean you can ignore its limitations.


To allow for historical access to the ASH data, one in ten samples are persisted to disk and made available using the DBA_HIST_ACTIVE_SESS_HISTORY view. So this is a sample of a sample. Using this view is similar to using the V$ACTIVE_SESSION_HISTORY view, but remember the sample time is now 10 seconds, so use (count*10) to measure time, rather than just the count.

Enterprise Manager Performance Pages

Accessing the ASH information directly can be very useful, but there are a number of more efficient ways to get to some of this information. The performance pages of Enterprise Manager (Grid Control and Cloud Control) are fantastic. They are based on the ASH information, giving you easy access to real-time and historical performance information.

The following picture is an example of the Enterprise Manager performance home page.

Performance Home

An example of the Top activity page is shown below.

Top Activity

ASH Report

ASH reports can be displayed using Enterprise Manager, or generated from SQL*Plus. To manually generate them using SQL*Plus, run the following script, while logged in as a privileged user.

The script prompts you for the following details:

  • Report Type: [ html | text ]
  • Instance number: [all | n ] — On single instance databases, this defaults to «1». On RAC databases you can report on a specific instance number or «all» instances.
  • Begin Time: The script describes the formats for this value. It can be an explicit date string or an offset of the current datetime. The default is -15 minutes.
  • Duration: The number of minutes to report on. The default duration is (SYSDATE — begin_time).
  • Report Name: A default name is provided. Change this if required.

The script produces either text or HTML output as requested. Examples of these are shown below.

Depending on the options selected, the ASH report scripts call one of several table functions from the DBMS_WORKLOAD_REPOSITORY package.


SQL Developer and ASH Reports

If you are using SQL Developer 4 onward, you can view ASH reports directly from SQL Developer. If it is not already showing, open the DBA pane «View > DBA», expand the connection of interest, then expand the «Performance» node. The ASH reports are available from the «ASH Reports Viewer» node.

SQL Developer - ASH Report

ASH Viewer

The ASH Viewer tool gives a graphical view of active session history data within the Oracle instance. Interestingly, it is supports Oracle 8i onward. In releases prior to Oracle 10g, or if you don’t have a Diagnostic and Tuning Pack license, you can connect using the «Standard» connection and the tool will mimic the functionality of ASH. If you have the necessary licenses, you can make «Enterprise» connections, which use ASH to provide the data.

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

  • Вопрос задан более трёх лет назад
  • 1269 просмотров
  • Facebook
  • Вконтакте
  • Twitter


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?

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 = ;

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.

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 :
FROM x$ktuxe
WHERE ktuxecfl=’DEAD’;


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

Oracle Database — Enterprise Edition — Version and later
Information in this document applies to any platform.
This document briefly describes how to suppress sessions marked killed in v$session.

Killing oracle sessions useful snippet for DBAs.

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);

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

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:

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

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:

where is the ORACLE_SID.

Be sure you do not kill processes such as:

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



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

Oracle – My KILL’ed Session Continues To Stay On In KILLED Status, Why?

If you do not know already, to check who is connected and since when, what their connection status is etc., you would use dynamic view v$session .

The view has the following columns:

You would kill the required session by issuing

Note: The IMMEDIATE option of the above command just returns control to you immediately. There is no difference in the underlying KILL operation and its after-effects!

2 Reasons why a session stays in KILLED status

The view has a lot of useful information. In this post, we are not so much concerned with the view itself but the status displayed by the view for an underlying session.

In particular, when a session is killed by a DBA, the session’s STATUS changes to KILLED. When you query v$session for your SID & SERIAL# (which uniquely constitute a session), the status may remain in “KILLED” status. There are a couple of reasons why this is happens

  1. The client (e.g., SQL*Plus) was not closed or did not issue anything to reconnect with the database, at which point Oracle would have said “ORA-00028: your session has been killed”
  2. The session is KILLED but PMON (Oracle background process – Process Monitor), has to cleanup and the work might involve rolling back changes

If you are trying to perform operations that involve getting an exclusive lock on the object(s) involved, you may get this error:
ORA-00054: resource busy and acquire with NOWAIT specified

More about why a session stays in KILLED status

So, how do you get around the error or make the entry in v$session with the “KILLED” status disappear? It depends on which of the two situations is causing the entry to stay on.

  • If the reason for session “hanging around” in “KILLED” status is because of #1 above:
  • Close the client software that you requested your DBA to kill. Alternatively, try issuing another command that tries to make a connection to the database. This should result in the error – ORA-00028: your session has been killed .
  • If the client S/W is not on your PC but on a server for example, close or kill the OS process associated with the session.
  • After doing the above, if the session is still showing up in v$session with “KILLED” status, it is because of #2 above
  • You have to wait until Oracle finishes the rollback and cleanup necessary

If the reason is #2, the following query, which shows UNDO usage by session should show the usage by the session in KILLED status.

What if I restart the instance?

I can tell you right away that you are making the problem worse by restarting. It still has to ROLLBACK and bring the objects involved to a consistent state before letting them go. By restarting, you will be interfering and interrupting that and adding more time delays!

Can you do anything about it? No, just sit tight and monitor!

As PMON cleanup/rollback continues, the size of UNDO used by the session should keep going down. When it gets to zero, yes, you guessed it – your session in KILLED status will disappear from v$session. Use this in coordination with the other tip from before that showed how to query the overall UNDO usage at the system level.

The serial# is also a key part of the query here when you monitor. Oracle reuses SID values. For example, session with SID 17 that a few minutes ago, was running a query and then disconnected, can have the SID value be used by another session but with a different Serial#. Until the next database restart, SID+Serial# combination will remain unique.

Can I do something to be proactive? Absolutely

Yes, the DBA can tell you what statement is being executed or where the connection is made from etc, but he may not know what application the session belongs to. You could help your DBA by using DBMS_APPLICATION_INFO.SET_CLIENT_INFO in your entry-point procedure of your application to set the CLIENT_INFO so that it shows up as part of the v$Session.client_info column and the DBA can then tell you that such and such an application is the one whose ROLLBACK is in progress. There are also additional instrumentation options available in the package that should be fully taken advantage of.

Assuming you here have instrumented your code, v$session.client_info column has the name of the application. To find all the sessions for an application(s), simple issue this query:

In my organization, I instituted the instrumentation part so that everyone knows exactly what is running.

When you request us to kill a session, you can be more knowledgeable about your request by first looking the session status and then giving the DBA’s the SID/Serial#. Once killed, you can monitor when the session has been completely flushed out of the system or if it is not, you now know why!

Как узнать, чего ждала сессия?

Если у нас есть сессия БД, которая почему-то выполняется дольше обычного, будь то sql-запрос или хранимая процедура, то понять чем она занята очень просто — в том случае, когда она продолжает свою работу. Представление V$SESSION_WAIT показывет все ожидания активных сессий, причем в большинстве популярных инструментов есть GUI для работы с ним. Проблемы начинаются при появлении вопросов «Четыре дня назад в 12.05 процедура xxx отработала за 15 секунд, хотя вызов её же в 12.06 того же дня занял всего 0.5 секунды. В чем дело?». К этому моменту не то, что данные о текущем ожидании отсутствует, но и самой сессии легко может не быть. К счастью, часть данных о текущих ожиданиях сессий сохраняется и доступна для последующего анализа. Доступ к архивным данным можно получить через представление DBA_HIST_ACTIVE_SESS_HISTORY.

Как правило, анализ начинается с выборки по SAMPLE_TIME и PROGRAM/MODULE/MACHINE потенциально интересных записей. После этого по TOP_LEVEL_SQL_ID можно проверить, что это то, что требуется. Дальше остается только внимательно и вдумчиво изучать SQL_EXEC_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, EVENT, P1-P3, SESSION_STATE, TIME_WAITED и BLOCKING_SESSION в попытках понять, что просходило и чем была занята база. Это процесс творческий и плохо формализуемый, а без хотя бы базовых представлений об архитектуре Oracle еще и очень медленный. Также надо учесть, что данные по сессиям сохраняются раз в 10 секунд, поэтому если ожидания разовые и не очень длительные, полезной информации в представлении может и не найтись.
Подробное описание столбцов DBA_HIST_ACTIVE_SESS_HISTORY.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *