通過作業系統的程式號檢視資料庫的session

wzq609發表於2014-03-10

在工作中很多新手會遇到這種問題,就是作業系統顯示有個PID佔用的CPU很高,導致整個系統執行很慢,

同時又不敢輕易的對這個PID進行KILL操作,很是頭疼。

clip_image001

今天就和大家分享下,怎麼通過作業系統的PID查詢相應的資料庫session和語句。

第一步:通過可以通過作業系統的PID查詢資料庫的v$process程式,語句如下:

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

第二步:查詢相應的SESSION資訊

SELECT SID,SERIAL#, USERNAME,MACHINE
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '&pid');

第三步:通過第二步查詢出來的SID,可以進一步查詢正在執行的SQL語句

select b.sql_text 
from v$session a,v$sqlarea b 
where a.sql_hash_value=b.hash_value and a.sid=‘&SID';

另外可以通過下面這條語句查詢後臺程式的名字

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

經過一番查詢如果發現當前的session有問題,具體怎麼判斷是否有問題,會繼續介紹。

作業系統的殺程式的操作很簡單,語句如下kill -9 pid

附:V$PROCESS和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:

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 / 100 is 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 <1/100th of a second)
  • >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)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12679300/viewspace-1106085/,如需轉載,請註明出處,否則將追究法律責任。

相關文章