Oracle посмотреть активные сессии
I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.
I have the following query :
But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.
But this doesn't seems to be working(In my case it didn't return any rows) So, I have the following questions
1) How do I get the queries executed by each session?
2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?
3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?
Thanking you all in advance,
Хронологические данные более старых сеансов
Представление словаря данных DBA_HIST_ACTIVE_SESSION_HISTORY хранит хронологическую информацию о последнем активном сеансе. Другими словами, это представление — не что иное, как коллекция снимков представления V$ACTIVE_SESSION_HISTORY, которое само по себе является образом данных активного сеанса.
Существуют два способа заполнения DBA_HIST_ACTIVE_SESSION_HISTORY.
- В процессе получения регулярных (по умолчанию — ежечасных) снимков, выполняемых AWR, фоновый процесс MMON передает AWR данные ASH.
- Oracle может понадобиться передать данные в представление DBA_HIST_ACTIVE_SESSION_HISTORY между моментами получения регулярных снимков, если буфер памяти окажется заполненным, и база данных не сможет записать в него данные об активности сеанса. В этом случае новый фоновый процесс MMNL выполнит выталкивание данных из буфера памяти в представление словаря данных.
Данные текущего активного сеанса
Как должно быть известно, представление V$SESSION хранит данные обо всех текущих сеансах. Оно содержит 72 столбца информации, и потому слишком громоздко для анализа данных сеанса. Вот почему ASH упрощает представление V$SESSION и получает из него наиболее важную информацию ожидания. Oracle предлагает новое представление V$ACTIVE_SESSION_HISTORY, которое содержит по одной строке для каждого активного сеанса, откуда ASH делало выборку, и возвращает строку самого последнего сеанса первой.
Представление V$ACTIVE_SESSION_HISTORY — это место, где база данных хранит пример данных всех активных сеансов. В этом представлении имеется столбец по имени SESSION_STATE, который показывает, активен ли сеанс. Столбец SESSION_STATE принимает два значения: ON CPU или WAITING. Сеанс считается активным в следующих случаях:
- состояние сеанса ON CPU, что означает активное использование сеансом процессора для выполнения работы с базой данных;
- состояние сеанса WAITING, но столбец EVENT указывает, что сеанс не ожидает никаких событий в классе IDLE.
Обратите внимание, что ASH — скользящий буфер в SGA; это находящаяся в памяти хронология активного сеанса. Таким образом, в загруженной базе данных старая информация часто перезаписывается, поскольку ASH собирает данные из представления V$SESSION каждую секунду.
На заметку! Использование статистики ASH для настройки производительности экземпляра рассматривается в главе 20.
3 Answers 3
You're probably not going to get the data that you're looking for without doing more configuration (such as enabling auditing) or making some compromises. What is the business problem you're trying to solve? Depending on the problem, we may be able to help you identify the easiest approach to configuring the database to be able to record the information you're after.
Oracle does not attempt to store anywhere how many times a particular user (and particularly not how many times a particular operating system user) executed a particular query. The SQL_ID in V$SESSION only indicates the SQL_ID that the session is currently executing. If, as I'm guessing, this is a client-server application, it is quite likely that this is NULL 99% of the time because the vast majority of the time, the session is not executing any SQL, it's waiting on the user to do something. The PREV_SQL_ID in V$SESSION is the prior SQL statement that was executed-- that at least won't generally be NULL . But it's only going to have one value, it's not going to have a history of the SQL statements executed by that session.
The V$SQL view is a representation of what is in the SQL shared pool. When a SQL statement ages out of the shared pool, it will no longer be in the V$SQL view. How quickly that happens depends on a multitude of factors-- how frequently someone is executing the statement, how frequently new statements are parsed (which generally depends heavily on whether your applications are using bind variables correctly), how big your shared pool is, etc. Generally, that's going to be somewhere between a few minutes and until the database shuts down.
If you are licensed to use the AWR tables and you are interested in approximations rather than perfectly correct answers, you might be able to get the information you're after by looking at some of the AWR tables. For example, V$ACTIVE_SESSION_HISTORY will capture the SQL statement that each session was actively executing each second. Since this is a client-server application, however, that means that the vast majority of the time, the session is going to be inactive, so nothing will be captured. The SQL statements that do happen to get captured for a session, though, will give you some idea about the relative frequency of different SQL statements. Of course, longer-running SQL statements are more likely to be captured as well since they are more likely to be active on a given instant. If query A and B both execute in exactly the same amount of time and a session was captured executing A 5 times and B 10 times in the last hour, you can conclude that B is executed roughly twice as often as A. And if you know the average execution time of a query, the average probability that the query was captured is going to be the number of seconds that the query executes (a query that executes in 0.5 seconds has a 50% chance of getting captured, one that executes in 0.25 seconds has a 25% chance of getting captured) so you can estimate how often a particular session executed a particular query. That is far from an exact number particularly over shorter time-frames and for queries whose actual execution times are more variable.
The data in V$ACTIVE_SESSION_HISTORY view is generally available for a few hours. It then gets sampled down into the DBA_HIST_ACTIVE_SESS_HISTORY table which cuts the amount of data available by an order of magnitude making any estimates much less accurate. But that data is kept for whatever your AWR retention interval is (by default, that's one week though many sites increase it to 30 or 60 days).
V$SESSION displays session information for each current session.
Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
Auditing session ID
- If the database is configured for unified auditing, then this column displays the unified auditing session ID
- If the database is configured for mixed mode auditing, then this column displays the traditional auditing session ID
Address of the process that owns the session
Oracle user identifier
Command in progress (last statement parsed).
You can find the command name for any value n returned in this COMMAND column by running this SQL query:
A value of 0 in this COMMAND column means the command is not recorded in V$SESSION .
Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644
For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator.
Address of the transaction state object
Address of the lock the session is waiting for; NULL if none
Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.
Schema user identifier
Schema user name
Operating system client user name
Operating system client process ID
Operating system machine name
Client port number
Operating system terminal name
Operating system program name
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
SQL identifier of the SQL statement that is currently being executed
Child number of the SQL statement that is currently being executed
Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL
SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR )
Used with PREV_HASH_VALUE to identify the last SQL statement executed
Used with SQL_HASH_VALUE to identify the last SQL statement executed
SQL identifier of the last SQL statement executed
Child number of the last SQL statement executed
SQL execution start of the last executed SQL statement
SQL execution identifier of the last executed SQL statement
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
Hash value of the MODULE column
Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
Hash value of the ACTION column
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.
Oracle top level call number
If the session STATUS is currently ACTIVE , then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE , then the value represents the elapsed time (in seconds) since the session has become inactive.
This column has been replaced by the PDML_STATUS column
Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:
NONE - Failover is disabled for this session
SESSION - Client can fail over its session following a disconnect
SELECT - Client can fail over queries in progress as well
Indicates the transparent application failover method for the session:
NONE - Failover is disabled for this session
BASIC - Client itself reconnects following a disconnect
PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
Indicates whether the session is running in failover mode and failover has occurred ( YES ) or not ( NO )
Name of the session's current resource consumer group
If ENABLED , the session is in a PARALLEL DML enabled mode. If DISABLED , PARALLEL DML enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DML .
If ENABLED , the session is in a PARALLEL DDL enabled mode. If DISABLED , PARALLEL DDL enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DDL .
If ENABLED , the session is in a PARALLEL QUERY enabled mode. If DISABLED , PARALLEL QUERY enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL QUERY .
If queued ( 1 ), the current amount of time the session has been queued. If not currently queued, the value is 0 .
Client identifier of the session
This column provides details on whether there is a blocking session:
VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the blocking session is unknown
Instance identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker.
This column provides details on whether there is a final blocking session:
VALID - there is a final blocking session and it is identified in the FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the final blocking session is unknown
Instance identifier of the final blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
A number that uniquely identifies the current or last wait (incremented for each wait)
If the session is currently waiting, then the number of the resource or event for which the session is waiting. If the session is not in a wait, then the number of the resource or event for which the session most recently waited.
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 .
Service name of the session
Indicates whether SQL tracing is enabled ( ENABLED ) or disabled ( DISABLED )
Indicates whether wait tracing is enabled ( TRUE ) or not ( FALSE )
Indicates whether bind tracing is enabled ( TRUE ) or not ( FALSE )
Frequency at which row source statistics are dumped in the trace files for each cursor:
Shows the value that, in the session, would be reported by sys_context('USERENV', 'SESSION_EDITION_ID')
Address of the creating process or circuit
Serial number of the creating process or circuit
Execution context identifier (sent by Application Server)
Object number of the SQL translation profile
The amount of tunable PGA memory (in bytes).
Untunable memory is PGA_ALLOC_MEM from V$PROCESS minus PGA_TUNABLE_MEM from V$SESSION .
Indicates whether shard DDL is enabled in the current session ( ENABLED ) or not ( DISABLED ).
This value is only relevant for the shard catalog database.
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
External name of the database user. For enterprise users, returns the Oracle Internet Directory DN.
Footnote 1 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 Database Performance Tuning Guide for an example of using V$SESSION to help identify an object that is waiting for buffer busy waits
Oracle Database Performance Tuning Guide for an example of using V$SESSION to determine which sessions are waiting for I/O
Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_APPLICATION_INFO package
V$SESSION displays session information for each current session.
Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
Auditing session ID
- If the database is configured for unified auditing, then this column displays the unified auditing session ID
- If the database is configured for mixed mode auditing, then this column displays the traditional auditing session ID
Address of the process that owns the session
Oracle user identifier
Command in progress (last statement parsed).
You can find the command name for any value n returned in this COMMAND column by running this SQL query:
A value of 0 in this COMMAND column means the command is not recorded in V$SESSION .
Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644
For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator.
Address of the transaction state object
Address of the lock the session is waiting for; NULL if none
Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.
Schema user identifier
Schema user name
Operating system client user name
Operating system client process ID
Operating system machine name
Client port number
Operating system terminal name
Operating system program name
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
SQL identifier of the SQL statement that is currently being executed
Child number of the SQL statement that is currently being executed
Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL
SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR )
Used with PREV_HASH_VALUE to identify the last SQL statement executed
Used with SQL_HASH_VALUE to identify the last SQL statement executed
SQL identifier of the last SQL statement executed
Child number of the last SQL statement executed
SQL execution start of the last executed SQL statement
SQL execution identifier of the last executed SQL statement
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
Hash value of the MODULE column
Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
Hash value of the ACTION column
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.
Oracle top level call number
If the session STATUS is currently ACTIVE , then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE , then the value represents the elapsed time (in seconds) since the session has become inactive.
This column has been replaced by the PDML_STATUS column
Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:
NONE - Failover is disabled for this session
SESSION - Client can fail over its session following a disconnect
SELECT - Client can fail over queries in progress as well
Indicates the transparent application failover method for the session:
NONE - Failover is disabled for this session
BASIC - Client itself reconnects following a disconnect
PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
Indicates whether the session is running in failover mode and failover has occurred ( YES ) or not ( NO )
Name of the session's current resource consumer group
If ENABLED , the session is in a PARALLEL DML enabled mode. If DISABLED , PARALLEL DML enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DML .
If ENABLED , the session is in a PARALLEL DDL enabled mode. If DISABLED , PARALLEL DDL enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DDL .
If ENABLED , the session is in a PARALLEL QUERY enabled mode. If DISABLED , PARALLEL QUERY enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL QUERY .
If queued ( 1 ), the current amount of time the session has been queued. If not currently queued, the value is 0 .
Client identifier of the session
This column provides details on whether there is a blocking session:
VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the blocking session is unknown
Instance identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker.
This column provides details on whether there is a final blocking session:
VALID - there is a final blocking session and it is identified in the FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the final blocking session is unknown
Instance identifier of the final blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
A number that uniquely identifies the current or last wait (incremented for each wait)
If the session is currently waiting, then the number of the resource or event for which the session is waiting. If the session is not in a wait, then the number of the resource or event for which the session most recently waited.
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 .
Service name of the session
Indicates whether SQL tracing is enabled ( ENABLED ) or disabled ( DISABLED )
Indicates whether wait tracing is enabled ( TRUE ) or not ( FALSE )
Indicates whether bind tracing is enabled ( TRUE ) or not ( FALSE )
Frequency at which row source statistics are dumped in the trace files for each cursor:
Shows the value that, in the session, would be reported by sys_context('USERENV', 'SESSION_EDITION_ID')
Address of the creating process or circuit
Serial number of the creating process or circuit
Execution context identifier (sent by Application Server)
Object number of the SQL translation profile
The amount of tunable PGA memory (in bytes).
Untunable memory is PGA_ALLOC_MEM from V$PROCESS minus PGA_TUNABLE_MEM from V$SESSION .
Indicates whether shard DDL is enabled in the current session ( ENABLED ) or not ( DISABLED ).
This value is only relevant for the shard catalog database.
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
External name of the database user. For enterprise users, returns the Oracle Internet Directory DN.
Footnote 1 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 Database Performance Tuning Guide for an example of using V$SESSION to help identify an object that is waiting for buffer busy waits
Oracle Database Performance Tuning Guide for an example of using V$SESSION to determine which sessions are waiting for I/O
Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_APPLICATION_INFO package
V$SESSION displays session information for each current session.
Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.
Auditing session ID
- If the database is configured for unified auditing, then this column displays the unified auditing session ID
- If the database is configured for mixed mode auditing, then this column displays the traditional auditing session ID
Address of the process that owns the session
Oracle user identifier
Command in progress (last statement parsed).
You can find the command name for any value n returned in this COMMAND column by running this SQL query:
A value of 0 in this COMMAND column means the command is not recorded in V$SESSION .
Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644
For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator.
Address of the transaction state object
Address of the lock the session is waiting for; NULL if none
Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.
Schema user identifier
Schema user name
Operating system client user name
Operating system client process ID
Operating system machine name
Client port number
Operating system terminal name
Operating system program name
Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed
Used with SQL_ADDRESS to identify the SQL statement that is currently being executed
SQL identifier of the SQL statement that is currently being executed
Child number of the SQL statement that is currently being executed
Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL
SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR )
Used with PREV_HASH_VALUE to identify the last SQL statement executed
Used with SQL_HASH_VALUE to identify the last SQL statement executed
SQL identifier of the last SQL statement executed
Child number of the last SQL statement executed
SQL execution start of the last executed SQL statement
SQL execution identifier of the last executed SQL statement
Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
Name of the currently executing module as set by calling the DBMS_APPLICATION_INFO.SET_MODULE procedure
Hash value of the MODULE column
Name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure
Hash value of the ACTION column
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.
Oracle top level call number
If the session STATUS is currently ACTIVE , then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE , then the value represents the elapsed time (in seconds) since the session has become inactive.
This column has been replaced by the PDML_STATUS column
Indicates whether failover is enabled for this session:
NONE - Failover is disabled for this session.
SESSION - Transparent application failover (TAF) is enabled for this session and the client can fail over its session following a disconnect.
SELECT - TAF is enabled for this session and the client can fail over queries. The application must not set session state after the initial setup. (Transactions are not included at failover.)
TRANSACTION - Application Continuity (AC) is enabled for this session. This feature is available with the Oracle Real Application Clusters (RAC), Oracle RAC One Node, and Oracle Active Data Guard options. AC hides outages from end users and applications by recovering the database sessions following recoverable outages and planned maintenance.
AUTO - Transparent Application Continuity (TAC) is enabled for this session. This feature is the automated version of Application Continuity (AC) that makes decisions on your behalf. TAC is on by default for Oracle Autonomous Database Cloud and can also be on by default for your applications.
Indicates the transparent application failover method for the session:
NONE - Failover is disabled for this session
BASIC - Client itself reconnects following a disconnect
PRECONNECT - Backup instance can support all connections from every instance for which it is backed up
Indicates whether the session is running in failover mode and failover has occurred ( YES ) or not ( NO )
Name of the session's current resource consumer group
If ENABLED , the session is in a PARALLEL DML enabled mode. If DISABLED , PARALLEL DML enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DML .
If ENABLED , the session is in a PARALLEL DDL enabled mode. If DISABLED , PARALLEL DDL enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL DDL .
If ENABLED , the session is in a PARALLEL QUERY enabled mode. If DISABLED , PARALLEL QUERY enabled mode is not supported for the session. If FORCED , the session has been altered to force PARALLEL QUERY .
If queued ( 1 ), the current amount of time the session has been queued. If not currently queued, the value is 0 .
Client identifier of the session
This column provides details on whether there is a blocking session:
VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the blocking session is unknown
Instance identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID .
The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker.
This column provides details on whether there is a final blocking session:
VALID - there is a final blocking session and it is identified in the FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION columns
NO HOLDER - there is no session blocking this session
NOT IN WAIT - this session is not in a wait
UNKNOWN - the final blocking session is unknown
Instance identifier of the final blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID .
A number that uniquely identifies the current or last wait (incremented for each wait)
If the session is currently waiting, then the number of the resource or event for which the session is waiting. If the session is not in a wait, then the number of the resource or event for which the session most recently waited.
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
Total time for the current wait (in microseconds)
A wait includes code within its beginning and end that can encounter other waits as part of its implementation. For example, a wait might need to acquire a latch to check state to determine when the wait is satisfied, and the latch itself might enter a wait. In this case, when the latch wait ends and returns to the outer wait, the latch time is included in the value of this column, while the value of WAIT_TIME_MICRO is reset to start after the latch wait.
Heuristic wait time (in microseconds)
This value is calculated by attempting to group sequential waits that appear to be the same.
Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0 .
Service name of the session
Indicates whether SQL tracing is enabled ( ENABLED ) or disabled ( DISABLED )
Indicates whether wait tracing is enabled ( TRUE ) or not ( FALSE )
Indicates whether bind tracing is enabled ( TRUE ) or not ( FALSE )
Frequency at which row source statistics are dumped in the trace files for each cursor:
Shows the value that, in the session, would be reported by sys_context('USERENV', 'SESSION_EDITION_ID')
Address of the creating process or circuit
Serial number of the creating process or circuit
Execution context identifier (sent by Application Server)
Object number of the SQL translation profile
The amount of tunable PGA memory (in bytes).
Untunable memory is PGA_ALLOC_MEM from V$PROCESS minus PGA_TUNABLE_MEM from V$SESSION .
Indicates whether shard DDL is enabled in the current session ( ENABLED ) or not ( DISABLED ).
This value is only relevant for the shard catalog database.
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
External name of the database user. For enterprise users, returns the Oracle Internet Directory DN.
Footnote 1 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.
Footnote 2 This column is available starting with Oracle Database 21c.
Oracle Database Performance Tuning Guide for an example of using V$SESSION to help identify an object that is waiting for buffer busy waits
Oracle Database Performance Tuning Guide for an example of using V$SESSION to determine which sessions are waiting for I/O
Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_APPLICATION_INFO package
AWR snapshots (снимки) очень полезны, но Oracle по умолчанию делает их каждые 60 минут. Если вы заинтересованы в анализе проблем с производительностью, которая случилась 10 минут назад, то снимки AWR ничем не помогут. Однако все-таки способ получить эту информацию имеется. Oracle Database собирает статистику Active Session History (состоящую в основном из статистики ожидания для различных событий) для всех активных сеансов каждую секунду, и сохраняет ее в циклическом буфере в SGA. Таким образом, ASH записывает самую свежую активность сеанса (за последние пять или десять минут).
Процесс MMNL (в Oracle его называют manageability monitor light — облегченный монитор управляемости, хотя этот процесс отображается как “manageability monitor process 2”, когда запрашивается представление V$BGPROCESS) выполняет легковесные задачи управляемости, включая метрики и захват хронологической информации сеансов для средства ASH при некоторых обстоятельствах. Например, MMNL сбросит данные ASH на диск, если буфер памяти ASH заполнится до истечения часового интервала, что обычно заставляет MMON выталкивать его.
Анализ ASH предоставляет эффективные данные по производительности, поскольку сосредоточен только на активных сеансах. Анализ текущих активных сеансов выполняется с использованием представления V$ACTIVE_SESSION_HISTORY, а анализ хронологии более старых сеансов — с помощью представления DBA_HIST_ACTIVE_SESSION_HISTORY.
На заметку! Дополнительная статистика в Oracle Database не оказывает заметного влияния на производительность, поскольку поступает в основном прямо из SGA через фоновые процессы. Средство ASH использует около 2 Мбайт памяти в SGA на каждый процессор.
Генерация отчета ASH
Для получения отчета ASH можно воспользоваться сценарием ashrpt.sql, находящимся в каталоге $ORACLE_HOME/rdbms/admin. Применение этого сценария аналогично использованию сценария awrrpt.sql, описанного ранее в этой главе. Этот сценарий генерирует информацию об операторах SQL, которые выполнялись в указанный период времени, и включает детали о блокировках и ожидании. Вот как можно запустить сценарий ashrpt.sql для получения отчета ASH:
Вам будет предложено ввести временные рамки для сбора информации ASH, кроме того, формат вывода отчета — HTML или текстовый, а также имя отчета. В листинге 1 ниже показана часть отчета ASH.
Первый раздел отчета ASH, Top User Events, предоставляет информацию о верхних пользовательских событиях, как показано в листинге 2:
Раздел Top Background Events (Верхние фоновые события), показанный в листинге 3 ниже, демонстрирует события ожидания в базе данных.
Раздел Top Service/Module (Верхняя служба/модуль), показанный в листинге 4, отображает активность, разделенную в соответствии со службами и модулями экземпляра.
В листинге 5 ниже показана информация о важнейших типах команд SQL (раздел Top SQL Command Types), выполненных в базе данных за последний час.
В листинге 6 ниже идентифицируются верхние операторы SQL (раздел Top SQL Statements), выполненные за анализируемый период ASH.
После этого следует раздел под названием Top SQL Using Literals (Верхние операторы SQL, использующие литералы), который поможет идентифицировать SQL-операторы, не использующие переменные привязки.
Следующие два сегмента, показанные в листинге 7, относятся Top Sessions (Ведущие сеансы) и Top Blocking Sessions (Ведущие блокирующие сеансы), основанные на ожиданиях в очереди и статистике ожидания занятого буфера.
Следующие три сегмента подводят итог по ведущим объектам базы данных, ведущим файлам и ведущим защелкам в экземпляре. В конце отчет ASH содержит итоговую информацию о событиях ожидания в базе данных, распределенных по меньшим временным слотам, чем общий период анализа, как показано в листинге 8.
В этом примере часовой период времени разбит на десять шестиминутных интервалов. Данный пример поможет более точно выявить моменты ухудшения производительности.
Читайте также: