Oracle system table v$session, v$sql field description

In this view, each session that is connected to a database instance has a record. including user session and background process such as DBWR, LGWR, Arcchiver and so on.

Common columns in the V$session

V$session is the basic information view that is used to find the user SID or Saddr. However, it also has some columns that change dynamically and can be used to check the user. As an example:

Sql_hash_value, sql_address: These two columns are used to identify the SQL statement that is executed by the session by default. If NULL or 0, it means that the session did not execute any SQL statements. The Prev_hash_value and prev_address two columns are used to identify the last statement executed by the session.

Note: When selecting with Sql* Plus, make sure you redefine the column width to be no less than 11 to see the full value.

Status: This column is used to determine the session status:

Achtive: Executing SQL statement (waiting for/using a Resource)

Inactive: Wait for operation (that is, wait for the SQL statement to be executed)

Killed: Marked for deletion

The following columns provide information about the session and can be used to find the session when one or more combination are unknown.

Session Information

Sid:session identification, commonly used to connect other columns

serial#: If a SID is used by another session then this value is incremented (when one session ends and the other session starts and the same SID is used).

AUDSID: review session ID uniqueness, confirm that it is usually also used when looking for parallel query patterns

USERNAME: The user name of the current session in Oracle.

Client Information

The database session is initiated by a client process running on a database server or from a server or even desktop through sql* net connection to a database, and the following columns provide information for this client

Osuser: Client operating System User name

Machine: Machines executed by client

TERMINAL: Terminal run by client

Process: The ID of the client processes

Program: client-side programs executed by client

To display the TERMINAL, Osuser of the PC to which the user is connected, Oracle on that PC is required. INI or set keyword TERMINAL in Windows, USERNAME.

Application information

Call the Dbms_application_info package to set some information to differentiate the user. This displays the following columns.

L Client_info:dbms_application_info Set in

L Action:dbms_application_info Set in

L Module:dbms_application_info Set in

The following v$session columns may also be used:

L row_wait_obj#

L row_wait_file#

L row_wait_block#

L row_wait_row#

Connection columns in the V$session

Column View Joined column (s)

Sid V$session_wait,,v$sesstat,,v$lock,v$session_event,v$open_cursor Sid

(Sql_hash_value, sql_address) V$sqltext, V$sqlarea, V$sql (Hash_value, ADDRESS)

(Prev_hash_value, prev_sql_address) V$sqltext, V$sqlarea, V$sql (Hash_value, ADDRESS)

Taddr v$transaction ADDR

Paddr v$process ADDR

Example:

1. Find your session information

SELECT SID, Osuser, USERNAME, Machine, PROCESS

From v$session WHERE audsid = Userenv (' SESSIONID ');

2. Find session when Machine is known

SELECT SID, Osuser, USERNAME, Machine, TERMINAL

From V$session

WHERE terminal = ' pts/tl ' and machine = ' RGMDBS1 ';

3. Find the SQL statement that is currently running by a specified session. Suppose SessionID is 100

Select B.sql_text

From V$session A, V$sqlarea b

where a.sql_hashvalue= B.hash_value and a.sid= 1 00

Or

Select B.sql_text
From V$session A, V$sqlarea b//v$sqlarea includes only 1000 characters of SQL, V$sqltext is all
where A.sql_hash_value = b.hash_value and a.terminal = ' Backham ';//backham is my Computer name

Finding the SQL statement executed by the specified session is a public requirement, and if the session is the main cause of the bottleneck, it can see what the session is doing based on the statement it is currently executing.

V$sqltext

This view includes the full text of the SQL statement in the Shared pool, and an SQL statement may be divided into multiple blocks to be saved in multiple records.

Note: V$sqlarea only includes the first 1000 characters.

Common columns in the V$sqltext

The hash value of the Hash_value:sql statement

Address of the Address:sql statement in the SGA

Sql_text:sql text.

Piece:sql the ordinal of a statement block

Connection columns in the V$sqltext

Column View Joined column (s)

Hash_value, Address V$sql, v$session hash_value, address

Hash_value. ADDRESS v$session Sql_hash_value, sql_address

Example: Known hash_value:3111103299, querying SQL statements:

select* from V$sqltext

where hashvalue= ' 3111103299 '

ORDER BY piece

V$sqlarea

This view keeps track of the shared cursor in all shared pool, with one column for each SQL statement in the shared pool. This view is important in analyzing the use of SQL statement resources.

Information columns in the V$sqlarea

The hash value of the Hash_value:sql statement.

The address of the Address:sql statement in the SGA.

These two columns are used to identify SQL statements, and sometimes two different statements may have the same hash value. At this point, the SQL statement must be confirmed with address together.

PARSING_USER_ID: the user who resolves the first cursor for the statement

Version_count: Number of statement cursor

Kept_versions:

Total shared memory used by Sharable_memory:cursor

Total number of resident memory used by Persistent_memory:cursor

The total number of run-time memory used by Runtime_memory:cursor.

The text of the Sql_text:sql statement (maximum only the first 1000 characters of the statement can be saved).

Module,action: Information when the session parses the first cursor when using Dbms_application_info

Other common columns in the V$sqlarea

Sorts: number of sorts of statements

Cpu_time: The CPU time the statement was parsed and executed

Elapsed_time: The shared time at which statements are parsed and executed

Parse_calls: Number of parsing calls (soft, hard) for statements

Executions: Number of executions of statements

Invalidations: Cursor invalidation number of statements

LOADS: The number of statements loaded (loaded)

Rows_processed: The total number of columns returned by the statement

Connection columns in the V$sqlarea

Column View Joined Column (s)

Hash_value, ADDRESS v$session sql_hash_value, sql_address

Hash_value, Address V$sqltext, V$sql, V$open_cursor Hash_value, address

Sql_text V$db_object_cache NAME

Example:

1. View the SQL that consumes the most resources:

SELECT hash_value, executions, buffer_gets, Disk_reads, Parse_calls

From V$sqlarea

WHERE buffer_gets > 10000000 OR disk_reads > 1000000

ORDER by Buffer_gets + * Disk_reads DESC;

2. View the resource consumption of an SQL statement:

SELECT Hash_value, Buffer_gets, disk_reads, executions, parse_calls

From V$sqlarea

WHERE Hash_value = 228801498 and address = Hextoraw (' cbd8e4b0 ');

3. Find the first 10 poor performance SQL statements
SQL code
SELECT * FROM (select Parsing_user_id,executions,sorts,command_type,disk_reads,sql_text from V$sqlarea
SELECT * FROM (select Parsing_user_id,executions,sorts,command_type,disk_reads,sql_text to V$sqlarea ORDER by Disk_ Reads DESC) where rownum<10; Description:
Executions indicates how many times the same SQL statement was executed, sorts represents the number of sorts, and disk_reads represents the amount of physical reads.



(1) V$sql A statement can map multiple cursor, because the cursor that the object refers to can have different users (such as Example 1). If there is more than one cursor (child cursor) present, the V$sqlarea provides the collection information for all cursors.

Example 1:

Here are the following child cursor

User A:select * from TBL

User B:select * from TBL

People think that these two statements are not the same ah, there may be many people will say is the same, but I tell you not necessarily, then why?

This tbla seems to be the same, but not necessarily oh, one is a user, one is a B user, then their execution plan analysis code differences may be big oh, change the wording of people understand:

SELECT * FROM A.tbl

SELECT * FROM B.tbl

On individual cursor, v$sql can be used. The view contains cursor-level data. Used when attempting to locate a session or user to parse the cursor.

The Plan_hash_value column stores the cursor execution plan as a numeric representation. Can be used to compare execution plans. Plan_hash_value lets you easily identify whether two execution plans are the same without having to compare them in one row.

The column descriptions in V$sql:

Sql_text:sql the first 1000 characters of a text

Sharable_mem: The amount of shared memory occupied (in bytes)

PERSISTENT_MEM: Fixed memory size (in bytes) for the lifetime

RUNTIME_MEM: Fixed memory size during the execution period

Sorts: number of completed sorts

Loaded_versions: Shows whether the context heap is loaded, 1 is 0 no

Open_versions: Shows whether the child cursor is locked, 1 is 0 no

Users_opening: Number of users executing statements

The fetch number of the FETCHES:SQL statement.

Executions: number of executions since it was loaded into the cache library

Users_executing: Number of users executing statements

LOADS: Number of times an object has been loaded

First_load_time: Initial loading time

Invalidations: Invalid number of times

Parse_calls: Number of parse calls

Disk_reads: Number of Read disks

Buffer_gets: Number of Read buffers

rows_processed: Resolves the total number of columns returned by the SQL statement

Command_type: Command Type Code

Optimizer model for OPTIMIZER_MODE:SQL statements

Optimizer_cost: The cost of this query given by the optimizer

PARSING_USER_ID: First resolved user ID

PARSING_SCHEMA_ID: The first resolved plan ID

Kept_versions: Indicates whether the current child cursor is marked as resident memory using the Dbms_shared_pool package

Address: The current cursor parent handle addresses

Type_chk_heap: Current Heap type check description

Hash_value: The hash value of the parent statement in the cache library

Plan_hash_value: A numeric representation of the execution plan.

Child_number: Number of child cursors

MODULE: The first parsing of this statement is done by calling Dbms_application_info. The module name of the Set_module setting.

ACTION: The first parse of this statement is by calling Dbms_application_info. Set_action the name of the action set.

Serializable_aborts: Transaction failed to serialize number of times

Outline_category: If OUTLINE is applied during the interpretation of the cursor, then this column will show OUTLINE categories, otherwise the columns are empty

Cpu_time: CPU Usage time (units, milliseconds) for parse/execute/Get

Elapsed_time: Elapsed time (units, milliseconds) for parsing/executing/acquiring

Outline_sid:outline Session ID

Child_address: Child cursor Address

SqlType: Indicates the SQL language version used by the current statement

Remote: Indicates whether the cursor is an image (y/n)

Object_status: Object state (VALID or INVALID)

Is_obsolete: Indicates whether the cursor is discarded when the number of child cursors is too large (y/n)

Oracle system table v$session, v$sql field description