Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to view the session of the database through the process number of the operating system. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Many novices will encounter this problem at work, that is, the operating system shows that a PID occupies a very high CPU, which causes the whole system to run very slowly.

At the same time, I do not dare to KILL this PID easily, which is a headache.

Today, I will share with you how to find the corresponding database session and statements through the PID of the operating system.

Step 1: through the v$process process that can find the database through the PID of the operating system, the statement is as follows:

Select * from v$process where SPID='&PID'

Step 2: find the corresponding SESSION information

SELECT SID,SERIAL#, USERNAME,MACHINE

FROM v$session b

WHERE b.paddr = (SELECT addr

FROM v$process c

WHERE c.spid ='& pid')

Step 3: through the SID found in step 2, you can further find the SQL statement being executed

Select b.sql_text

From v$session a minute vainsqlarea b

Where a.sql_hash_value=b.hash_value and a. Sideships

In addition, you can find the name of the background process through the following statement

SELECT s.SID SID, p.spid threadid, p.program processname, bg.NAME NAME

FROM v$process p, v$session s, v$bgprocess bg

WHERE p.addr = s.paddr

AND p.addr = bg.paddr

AND bg.paddr '00'

After a query, if you find that there is a problem with the current session, how to determine whether there is a problem, will continue to introduce.

The operation of the killing process of the operating system is very simple, with the following statement: kill-9 pid

Attached: field description of V$PROCESS and V$SESSION

Column

Datatype

Description

ADDR

RAW (4 | 8)

Address of process state object

PID

NUMBER

Oracle process identifier

SPID

VARCHAR2 (12)

Operating system process identifier

USERNAME

VARCHAR2 (15)

Operating system process username. Any two-task user coming across the network has "- T" appended to the username.

SERIAL#

NUMBER

Process serial number

TERMINAL

VARCHAR2 (30)

Operating system terminal identifier

PROGRAM

VARCHAR2 (48)

Program in progress

TRACEID

VARCHAR2 (255)

Trace file identifier

BACKGROUND

VARCHAR2 (1)

1 for a background process; NULL for a normal process

LATCHWAIT

VARCHAR2 (8)

Address of latch the process is waiting for; NULL if none

LATCHSPIN

VARCHAR2 (8)

Address of the latch the process is spinning on; NULL if none

PGA_USED_MEM

NUMBER

PGA memory currently used by the process

PGA_ALLOC_MEM

NUMBER

PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)

PGA_FREEABLE_MEM

NUMBER

Allocated PGA memory which can be freed

PGA_MAX_MEM

NUMBER

Maximum PGA memory ever allocated by the process

Column

Datatype

Description

SADDR

RAW (4 | 8)

Session address

SID

NUMBER

Session identifier

SERIAL#

NUMBER

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.

AUDSID

NUMBER

Auditing session ID

PADDR

RAW (4 | 8)

Address of the process that owns the session

USER#

NUMBER

Oracle user identifier

USERNAME

VARCHAR2 (30)

Oracle username

COMMAND

NUMBER

Command in progress (last statement parsed); for a list of values, see Table 7-5. These values also appear in the AUDIT_ACTIONS table.

OWNERID

NUMBER

The column contents are invalid if the value is 2147483644. Otherwise, this column contains the identifier of the user who owns the migratable session.

For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.

TADDR

VARCHAR2 (8)

Address of transaction state object

LOCKWAIT

VARCHAR2 (8)

Address of lock waiting for; null if none

STATUS

VARCHAR2 (8)

Status of the session:

ACTIVE-Session currently executing SQL

INACTIVE

KILLED-Session marked to be killed

CACHED-Session temporarily cached for use by Oracle*XA

SNIPED-Session inactive, waiting on the client

SERVER

VARCHAR2 (9)

Server type (DEDICATED | SHARED | PSEUDO | NONE)

SCHEMA#

NUMBER

Schema user identifier

SCHEMANAME

VARCHAR2 (30)

Schema user name

OSUSER

VARCHAR2 (30)

Operating system client user name

PROCESS

VARCHAR2 (12)

Operating system client process ID

MACHINE

VARCHAR2 (64)

Operating system machine name

TERMINAL

VARCHAR2 (30)

Operating system terminal name

PROGRAM

VARCHAR2 (48)

Operating system program name

TYPE

VARCHAR2 (10)

Session type

SQL_ADDRESS

RAW (4 | 8)

Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed

SQL_HASH_VALUE

NUMBER

Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

SQL_ID

VARCHAR2 (13)

SQL identifier of the SQL statement that is currently being executed

SQL_CHILD_NUMBER

NUMBER

Child number of the SQL statement that is currently being executed

PREV_SQL_ADDR

RAW (4 | 8)

Used with PREV_HASH_VALUE to identify the last SQL statement executed

PREV_HASH_VALUE

NUMBER

Used with SQL_HASH_VALUE to identify the last SQL statement executed

PREV_SQL_ID

VARCHAR2 (13)

SQL identifier of the last SQL statement executed

PREV_CHILD_NUMBER

NUMBER

Child number of the last SQL statement executed

MODULE

VARCHAR2 (48)

Name of the currently executing module as set by calling theDBMS_APPLICATION_INFO.SET_MODULE procedure

MODULE_HASH

NUMBER

Hash value of the above MODULE

ACTION

VARCHAR2 (32)

Name of the currently executing action as set by calling theDBMS_APPLICATION_INFO.SET_ACTION procedure

ACTION_HASH

NUMBER

Hash value of the above action name

CLIENT_INFO

VARCHAR2 (64)

Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure

FIXED_TABLE_SEQUENCE

NUMBER

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.

ROW_WAIT_OBJ#

NUMBER

Object ID for the table containing the row specified in ROW_WAIT_ROW#

ROW_WAIT_FILE#

NUMBER

Identifier for the datafile containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.

ROW_WAIT_BLOCK#

NUMBER

Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not-1.

ROW_WAIT_ROW#

NUMBER

Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not-1.

LOGON_TIME

DATE

Time of logon

LAST_CALL_ET

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.

PDML_ENABLED

VARCHAR2 (3)

This column has been replaced by column PDML_STATUS

FAILOVER_TYPE

VARCHAR2 (13)

Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:

NONE-Failover is disabled for this session

SESSION-Client is able to fail over its session following a disconnect

SELECT-Client is able to fail over queries in progress as well

See Also:

Oracle Database Concepts for more information on TAF

Oracle Database Net Services Administrator's Guide for information on configuring TAF

FAILOVER_METHOD

VARCHAR2 (10)

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

FAILED_OVER

VARCHAR2 (3)

Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO)

RESOURCE_CONSUMER_GROUP

VARCHAR2 (32)

Name of the session's current resource consumer group

PDML_STATUS

VARCHAR2 (8)

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.

PDDL_STATUS

VARCHAR2 (8)

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.

PQ_STATUS

VARCHAR2 (8)

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.

CURRENT_QUEUE_DURATION

NUMBER

If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0.

CLIENT_IDENTIFIER

VARCHAR2 (64)

Client identifier of the session

BLOCKING_SESSION_STATUS

VARCHAR2 (11)

Blocking session status:

VALID

NO HOLDER

GLOBAL

NOT IN WAIT

UNKNOWN

BLOCKING_INSTANCE

NUMBER

Instance identifier of blocking session

BLOCKING_SESSION

NUMBER

Session identifier of blocking session

SEQ#

NUMBER

Sequence number that uniquely identifies the wait. Incremented for each wait.

EVENT#

NUMBER

Event number

EVENT

VARCHAR2 (64)

Resource or event for which the session is waiting

See Also: Appendix C, "Oracle Wait Events"

P1TEXT

VARCHAR2 (64)

Description of the first additional parameter

P1

NUMBER

First additional parameter

P1RAW

RAW (4)

First additional parameter

P2TEXT

VARCHAR2 (64)

Description of the second additional parameter

P2

NUMBER

Second additional parameter

P2RAW

RAW (4)

Second additional parameter

P3TEXT

VARCHAR2 (64)

Description of the third additional parameter

P3

NUMBER

Third additional parameter

P3RAW

RAW (4)

Third additional parameter

WAIT_CLASS_ID

NUMBER

Identifier of the wait class

WAIT_CLASS#

NUMBER

Number of the wait class

WAIT_CLASS

VARCHAR2 (64)

Name of the wait class

WAIT_TIME

NUMBER

A nonzero value is the session's last wait time. A zero value means the session is currently waiting.

SECONDS_IN_WAIT

NUMBER

If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. IfWAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, andSECONDS_IN_WAIT-WAIT_TIME / 100is the active seconds since the last wait ended.

STATE

VARCHAR2 (19)

Wait state:

0-WAITING (the session is currently waiting)

-2-WAITED UNKNOWN TIME (duration of last wait is unknown)

-1-WAITED SHORT TIME (last wait 0-WAITED KNOWN TIME (WAIT_TIME = duration of last wait)

SERVICE_NAME

VARCHAR2 (64)

Service name of the session

SQL_TRACE

VARCHAR2 (8)

Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED)

SQL_TRACE_WAITS

VARCHAR2 (5)

Indicates whether wait tracing is enabled (TRUE) or not (FALSE)

SQL_TRACE_BINDS

VARCHAR2 (5)

Indicates whether bind tracing is enabled (TRUE) or not (FALSE)

This is the end of this article on "how to view the session of the database through the process number of the operating system". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.