Learning Dynamic Performance Table (7)--v$process

Learning Dynamic Performance Table

Seventh Article --v$process 2007.5.30

This view contains all the process information for the current system Oracle operation. is often used to establish a connection between the operating system process ID of an Oracle or service process and the database session. This is useful in some cases:

1. If the database bottleneck is a system resource (such as CPU, memory), and the user who consumes the most resources is always stuck in a few service processes, the following items are done:

• Identify resource processes
• To find their session, you must associate the session with the session.
• Find out why the session is taking up so much resources

1. The SQL Trace file name is the operating system process ID based on the service process. To find the trace file for the session, you must link the session to the service process.
2. Certain events, such as the RDBMS IPC reply, identify the Oracle process ID of the session process. To find out what these processes are doing, you must find their session.
3. The background processes (Dbwr,lgwr,pmon, etc.) on the server you see are service processes. To know what they are doing, you have to find their session.

v$process the common columns in

• ADDR: Process Object Address
• Pid:oracle Process ID
• SPID: Operating system process ID

v$process the connection column in

Column View Joined column (s)

ADDR v$session paddr

Example:

1. Find the session information and process ID of the specified system user in Oracle, assuming the operating system user is: Junsansi

Select s.sid,s.serial#, S.username,p.spid

From V$session S, v$process p

where S.osuser = ' Junsansi '

and s.paddr = P.addr

1. View Locks and waits

SELECT /*+ rule * /

Lpad (", Decode (L.XIDUSN, 0, 3, 0)) | | L.oracle_username user_name,

O.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid

From V$locked_object L, dba_objects O, v$session S, v$process p

WHERE l.object_id = o.object_id

and l.session_id = S.sid and s.paddr = p.addr

ORDER by o.object_id, Xidusn DESC

Note:

In the Linux environment can be through the PS view process information including Pid,windows in the Task Manager PID and v$process PID can not be mapped, this block in Oracledocument also found no introduction, later Google a bit, There is information about the fact that Windows is a multithreaded server, and each process contains a series of threads. This is different from UNIX, where each oralce process exists independently and all threads on NT are derived from the oralce process.

To display the Oracle-related process PID in Windows, we can do so with a simple SQL statement.

SELECT S.sid, P.pid, P.spid signaled, S.osuser, S.program

From V$process p, v$session s

WHERE p.addr = s.paddr;

Sid	Pid	Signaled	Osuser	Program
1	2	2452	SYSTEM	ORACLE. Exe
2	3	2460	SYSTEM	ORACLE. Exe
3	4	2472	SYSTEM	ORACLE. Exe
4	5	2492	SYSTEM	ORACLE. Exe
5	6	2496	SYSTEM	ORACLE. Exe
6	7	2508	SYSTEM	ORACLE. Exe
7	8	2520	SYSTEM	ORACLE. Exe
8	9	2524	SYSTEM	ORACLE. Exe
10	12	1316	Jss\junsansi	PlSqlDev.exe
9	13	3420	Jss\junsansi	PlSqlDev.exe
13	14	660	Jss\junsansi	PlSqlDev.exe

You can also query the name of the background process through the v$bgprocess connection:

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

Sid	THREADID	PROCESSNAME	NAME
1	2452	ORACLE. Exe	Pmon
2	2460	ORACLE. Exe	DBW0
3	2472	ORACLE. Exe	Lgwr
4	2492	ORACLE. Exe	CKPT
5	2496	ORACLE. Exe	Smon
6	2508	ORACLE. Exe	RECO
7	2520	ORACLE. Exe	CJQ0
8	2524	ORACLE. Exe	QMN0

Eygle wrote a SQL script getsql.sql to get the SQL statement that the specified PID is executing, which is also a note.

REM Getsql.sql

REM author Eygle

REM on Windows, known process ID, gets the statement that is currently executing

REM on Windows, the process ID is 16 binary and needs to be converted in UNIX directly to 10 binary

SELECT/*+ ORDERED */

Sql_text

From V$sqltext A

WHERE (A.hash_value, a.address) in (

SELECT DECODE (Sql_hash_value,

0, Prev_hash_value,

Sql_hash_value

),

DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

From V$session b

WHERE b.paddr = (SELECT addr

From v$process C

WHERE c.spid = to_number (' &pid ', ' xxxx '))

ORDER by Piece ASC

Learning Dynamic Performance Table (7)--v$process