Each and every person who works on the ORACLE Database, they knows the importance of v$session view. It’s the starting point of troubleshooting of the problems if we consider the systematic approach as Tanel poder says. Yes, let’s today see some things which are “unknown and known” to the DBA’s and they skip some of the details from the v$session view which provides a very valuable and vital in life of DBA.
what for we are waiting, let’s dig up some details and understand some important things which its provides..
From Oracle 11g, v$session is very much enhanced and provides a lot of details with respect to user session.
By default Oracle opens the following sessions
SYSMAN – OMS (Program)
DBSNMP - emagent.exe (Program)
21- BACKGROUND process under ORACLE.EXE (considering windows environment) are opened - OS user as “system”.
ORACLE.EXE (LGWR) BACKGROUND
ORACLE.EXE (PSP0) BACKGROUND
ORACLE.EXE (DBRM) BACKGROUND
ORACLE.EXE (DIA0) BACKGROUND
ORACLE.EXE (DBW0) BACKGROUND
ORACLE.EXE (CKPT) BACKGROUND
ORACLE.EXE (RECO) BACKGROUND
ORACLE.EXE (MMNL) BACKGROUND
ORACLE.EXE (Q000) BACKGROUND
ORACLE.EXE (Q001) BACKGROUND
ORACLE.EXE (CJQ0) BACKGROUND
ORACLE.EXE (PMON) BACKGROUND
ORACLE.EXE (GEN0) BACKGROUND
ORACLE.EXE (SMCO) BACKGROUND
ORACLE.EXE (W000) BACKGROUND
ORACLE.EXE (QMNC) BACKGROUND
ORACLE.EXE (MMON) BACKGROUND
ORACLE.EXE (SMON) BACKGROUND
ORACLE.EXE (VKTM) BACKGROUND
ORACLE.EXE (DIAG) BACKGROUND
ORACLE.EXE (MMAN) BACKGROUND
Note:- Even we can get the details of processes from v$process.
module_hash - provides the hash value of the client
SQL*Plus - 3669949024 - hash never changes based on version specific.
The above can be very much utilized in order to block the clients with which the user get’s connected. I have seen some of the questions in forums regarding the “blocking” of specific clients. Perhaps, we can handle these issues in better way by utilizing the above column.
P1RAW, P2RAW, and P3RAW columns display the same values as the P1, P2, and P3 columns, except that the numbers are displayed in hexadecimal, some dba’s thinks both are different gets deviated from the actual details.
If we go through the Oracle documentation
SESSION_EDITIONID - Reserved for future use
CREATOR_ADDR - Address of the creating process or circuit
CREATOR_SERIAL# - Serial number of the creating process or circuit
If we like to see how the dedicated server process works, here the example from the views and how the data is related.
1: C:\Users\Pavan>sqlplus / as sysdba
2: 3: SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 12 13:09:20 2010
4: 5: Copyright (c) 1982, 2010, Oracle. All rights reserved.
6: 7: 8: Connected to:
9: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
10: With the Partitioning, OLAP, Data Mining and Real Application Testing options
11: 12: SQL> select username,sid,serial#,creator_serial#,creator_addr from v$session where module_hash='3669949024';
13: 14: USERNAME SID SERIAL# CREATOR_SERIAL# CREATOR_15: ------------------------------ ---------- ---------- --------------- --------
16: SCOTT 14 2016 35 3E466F0C 17: SCOTT 145 1960 67 3E467A14 18: 19: SQL> select addr,serial#,program,pid from v$process where serial# in (35,67);
20: 21: ADDR SERIAL#22: -------- ----------
23: PROGRAM PID24: ---------------------------------------------------------------- ----------
25: 3E466F0C 35 26: ORACLE.EXE (SHAD) 32 27: 28: 3E467A14 67 29: ORACLE.EXE (SHAD) 33If we disconnected the sessions, automatically respective dedicated server process is given to the PGA.
Column “ECID” – which is not documented.
Let’s see the details of above column in coming posts…!!
0 comments: on "V$session in 11g"
Post a Comment