The purpose of view V$sql in Oracle

1. Gets the SQL statement being executed, the execution time of the SQL statement, and the wait event for the SQL statement:

Select A.sql_text,b.status,b.last_call_et,b.machine,b.event,b.program from V$sql a,v$session b where a.sql_id=b.sql_ Id

2. Get SQL statement execution time:

Select sql_text,cpu_time/1000/1000 T_cpu,trunc (elapsed_time/1000/1000) T_elapse from V$sql

However, not all SQL statements can be obtained from V$sql because Oracle dynamically updates the information of the shared pool, removing some old SQL statements from the shared pool to provide a shared pool space for the new SQL statement

To manually empty the shared pool information:

Alter system flush Shared_pool

The column descriptions in V$sql:

L SQL_TEXT:SQL the first 1000 characters of a text

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

L PERSISTENT_MEM: Fixed memory size during lifetime in bytes

L RUNTIME_MEM: Fixed memory size during the execution period

L Sorts: number of completed sorts

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

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

L users_opening: Number of users executing statements

L FETCHES:SQL the number of fetch for the statement.

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

L users_executing: Number of users executing statements

L LOADS: Number of times an object has been loaded

L First_load_time: Initial loading time

L Invalidations: Invalid number of times

L Parse_calls: Parse call count

L Disk_reads: Read disk Count

L Buffer_gets: Number of Read buffers

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

L Command_type: Command type Code

The optimizer model of the L-optimizer_mode:sql statement

L Optimizer_cost: The cost of this query given by the optimizer

L PARSING_USER_ID: The first resolved user ID

L PARSING_SCHEMA_ID: The first resolved plan ID

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

L Address: Current cursor parent handle addresses

L Type_chk_heap: Current heap type check description

L Hash_value: Hash value of parent statement in Cache library

L Plan_hash_value: A numerical representation of the execution plan.

L Child_number: Number of child cursors

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

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

L Serializable_aborts: Transaction failed to serialize number of times

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

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

L Elapsed_time: Elapsed time (units, milliseconds) for parsing/execution/acquisition

L Outline_sid:outline Session ID

L Child_address: Child cursor Address

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

L Remote: Indicates if the cursor is a remote image (y/n)

L Object_status: Object state (VALID or INVALID)

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

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 that you redefine the column widths not less than 11 to see the full value.

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

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

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

L 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

L sid:session identification, commonly used to connect other columns

L 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).

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

L 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 connected to a database through Sql*net, and the following columns provide information for this client

L Osuser: Client operating System User name

L Machine: Machines executed by client

L TERMINAL: Terminal run by client

L Process: The ID of the client process

L 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#

The purpose of view V$sql in Oracle