通过操作系统的进程号查看数据库的session

简介: 在工作中很多新手会遇到这种问题,就是操作系统显示有个PID占用的CPU很高,导致整个系统运行很慢, 同时又不敢轻易的对这个PID进行KILL操作,很是头疼。 今天就和大家分享下,怎么通过操作系统的PID查找相应的数据库session和语句。

在工作中很多新手会遇到这种问题,就是操作系统显示有个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
  • >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)

相关文章
|
8月前
|
存储 Linux API
【Linux进程概念】—— 操作系统中的“生命体”,计算机里的“多线程”
在计算机系统的底层架构中,操作系统肩负着资源管理与任务调度的重任。当我们启动各类应用程序时,其背后复杂的运作机制便悄然展开。程序,作为静态的指令集合,如何在系统中实现动态执行?本文带你一探究竟!
【Linux进程概念】—— 操作系统中的“生命体”,计算机里的“多线程”
|
11月前
|
算法 调度 Python
深入理解操作系统中的进程调度算法
在操作系统中,进程调度是核心任务之一,它决定了哪个进程将获得CPU的使用权。本文通过浅显易懂的语言和生动的比喻,带领读者了解进程调度算法的重要性及其工作原理,同时提供代码示例帮助理解。
|
10月前
|
算法 Linux 调度
深入理解Linux操作系统的进程管理
本文旨在探讨Linux操作系统中的进程管理机制,包括进程的创建、执行、调度和终止等环节。通过对Linux内核中相关模块的分析,揭示其高效的进程管理策略,为开发者提供优化程序性能和资源利用率的参考。
269 1
|
10月前
|
调度 开发者 Python
深入浅出操作系统:进程与线程的奥秘
在数字世界的底层,操作系统扮演着不可或缺的角色。它如同一位高效的管家,协调和控制着计算机硬件与软件资源。本文将拨开迷雾,深入探索操作系统中两个核心概念——进程与线程。我们将从它们的诞生谈起,逐步剖析它们的本质、区别以及如何影响我们日常使用的应用程序性能。通过简单的比喻,我们将理解这些看似抽象的概念,并学会如何在编程实践中高效利用进程与线程。准备好跟随我一起,揭开操作系统的神秘面纱,让我们的代码运行得更加流畅吧!
|
6月前
|
缓存 运维 前端开发
|
6月前
|
缓存 运维 前端开发
阿里云操作系统控制台:高效解决性能瓶颈与抖动之进程热点追踪
遇到“进程性能瓶颈导致业务异常”等多项业务痛点时,提供高效解决方案,并展示案例。
|
8月前
|
中间件 关系型数据库 数据库
docker快速部署OS web中间件 数据库 编程应用
通过Docker,可以轻松地部署操作系统、Web中间件、数据库和编程应用。本文详细介绍了使用Docker部署这些组件的基本步骤和命令,展示了如何通过Docker Compose编排多容器应用。希望本文能帮助开发者更高效地使用Docker进行应用部署和管理。
204 19
|
9月前
|
监控 搜索推荐 开发工具
2025年1月9日更新Windows操作系统个人使用-禁用掉一下一些不必要的服务-关闭占用资源的进程-禁用服务提升系统运行速度-让电脑不再卡顿-优雅草央千澈-长期更新
2025年1月9日更新Windows操作系统个人使用-禁用掉一下一些不必要的服务-关闭占用资源的进程-禁用服务提升系统运行速度-让电脑不再卡顿-优雅草央千澈-长期更新
680 2
2025年1月9日更新Windows操作系统个人使用-禁用掉一下一些不必要的服务-关闭占用资源的进程-禁用服务提升系统运行速度-让电脑不再卡顿-优雅草央千澈-长期更新
|
10月前
|
C语言 开发者 内存技术
探索操作系统核心:从进程管理到内存分配
本文将深入探讨操作系统的两大核心功能——进程管理和内存分配。通过直观的代码示例,我们将了解如何在操作系统中实现这些基本功能,以及它们如何影响系统性能和稳定性。文章旨在为读者提供一个清晰的操作系统内部工作机制视角,同时强调理解和掌握这些概念对于任何软件开发人员的重要性。
|
10月前
|
Linux 调度 C语言
深入理解操作系统:从进程管理到内存优化
本文旨在为读者提供一次深入浅出的操作系统之旅,从进程管理的基本概念出发,逐步探索到内存管理的高级技巧。我们将通过实际代码示例,揭示操作系统如何高效地调度和优化资源,确保系统稳定运行。无论你是初学者还是有一定基础的开发者,这篇文章都将为你打开一扇了解操作系统深层工作原理的大门。
134 4

热门文章

最新文章

推荐镜像

更多