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

This article mainly introduces the relevant knowledge of "what is v$process in oracle". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "what is v$process in oracle" can help you solve the problem.

Foreword:

Process: this parameter limits the number of operating system processes that can connect to SGA (or the number of threads in Windows systems). This total must be large enough to apply to background processes and all dedicated server processes. In addition, the number of shared server processes and scheduling processes is also counted. In addition, the number of shared server processes and scheduling processes is also counted. Therefore, in a dedicated server environment, this is a way to limit the number of concurrent connections.

Introduction:

Oracle provides the corresponding view v$process to monitor the progress of the entire database.

Introduction to the official document of v$process process: This view has one row for each Oracle process connected to the instance. The columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM can be used to monitor the PGA memory usage of these processes. Used to monitor the memory usage of each process

The detailed V$PROCESS view is as follows:

Column

Datatype

Description

explain

ADDR

RAW (4 | 8)

Address of the process state object

Can be associated with the paddr field of v$session

PID

NUMBER

Oracle process identifier

SPID

VARCHAR2 (24)

Operating system process identifier

Operating system process identifier

PNAME

VARCHAR2 (5)

Name of this process

The name of the process (you can see the foreground and background processes)

USERNAME

VARCHAR2 (15)

Operating system process username

The user name of the operating system running this process

Note: 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

Terminal name is similar to v$session

PROGRAM

VARCHAR2 (48)

Program in progress

The name of the program

TRACEID

VARCHAR2 (255)

Trace file identifier

Track the ID of the file

TRACEFILE

VARCHAR2 (513)

Trace file name of the process

The name and path of the trace file

BACKGROUND

VARCHAR2 (1)

1 for a background process; NULL for a normal process

A value of 1 indicates a background process

LATCHWAIT

VARCHAR2 (8)

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

Waiting for LATCH

LATCHSPIN

VARCHAR2 (8)

This column is obsolete

PGA_USED_MEM

NUMBER

PGA memory currently used by the process

Consumption of PGA

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)

Memory consumed allowed by the current PGA

PGA_FREEABLE_MEM

NUMBER

Allocated PGA memory which can be freed

PGA_MAX_MEM

NUMBER

Maximum PGA memory ever allocated by the process

The largest PGA memory allocation in a process

Extend:

1. The SPID in the v$process process provides the PID of the operating system corresponding to each process, so if there is an exception in the PID of the monitoring operating system, you can use this view to find the corresponding SQL statement.

The script is as follows:

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/

2. Find the problematic session in the database, then drop the corresponding process in the operating system kill, and find the corresponding operating system PID according to the SID of SESSION.

SELECT A. Sid, B.SPID., A. Mach. C.SQLINE text FROM v$SESSION A, v$process B, Vogue SQL C.

Where a.paddr=b.addr

AND A.PREV_SQL_ID=C.SQL_ID

AND A. SIDs

3. The relationship between v$process and v$session

The number of connections (sessions) of oracle is related to the number of processes (process) in its parameter file, and their relationship is as follows: sessions= (1.1*process+5). If resources allow, and the current number of process is too small, then you can appropriately increase the number of processs (the number of session depends on the number of process, generally do not modify the number of session directly).

A Process in a Shared Server corresponds to one or more Session in an Oracle. In Dedicated Server, one session corresponds to one process, but one process does not necessarily correspond to one session.

4. Modify v$process and v$session parameters

The above settings of v$process and v$session parameters are only empirical values, but in the actual operation process, due to changes in business or architecture, the actual number of session and process of the system may exceed the values of v$process and v$session parameters, which directly results in the subsequent connected users not being able to log in.

View current session configuration: show parameter sessions

View current process configuration: show parameter processes

Modify parameter: alter system set processes=1000 scope=spfile;. This parameter takes effect when it is restarted.

This is the end of the content about "what is v$process in oracle". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.