Do you Know These 5 Use of V$session View ? -

Do you Know These 5 Use of V$session View ?

V$session view is the base of all information related to current system status like which user, How many session, Which query and how long query is waiting etc. V$session is the first place when DBA start looking for information related to performance, query execution etc. You might be using this view 100 times in a day but did not realize its worth. By seeing importance of this view I decide to write few common use of V$session view in Oracle.

I am using Oracle 11g for all the queries and test cases, V$session is the view when you have single instance and GV$session is the view for Real Application Cluster instance.

1. List of running session: First thing DBA look into v$session is list of running database session, There are mainly two types of Database sessions Background and User sessions. Background session are used for database basic functionality like dbwr, arch, smon, pmon etc. User session are session which perform user operations.

Whenever you connect to the data a session is created into the database for performing your operations. A DBA can easily see this by using type field of V$session view.

SQL> select count(*),type from v$session group by type;
  COUNT(*) TYPE
---------- ----------
         1 USER
        49 BACKGROUND

Currently there are only one user session running. In case of RAC environment, DBA has to use gv$session instead of v$session.

SQL> select count(*),type,INST_ID from gv$session group by type,inst_id;
 COUNT(*) TYPE          INST_ID
---------- ---------- ----------
        21 USER                1
        48 BACKGROUND          1
        49 BACKGROUND          2
        17 USER                2

This is a two node RAC, Instance 1 has 21 user and 48 background sessions, while Instance 2 has 17 user and 49 background sessions. This information is useful when you see "ORA-00020: maximum number of processes (%s) exceeded" error. You can use below query to identify which use is creating high number of sessions.

SQL> select SID,USERNAME,COMMAND,PROCESS,TERMINAL,PROGRAM from gv$session where type='USER';
       SID USERNAME                          COMMAND PROCESS                  TERMINAL                       PROGRAM
---------- ------------------------------ ---------- ------------------------ ------------------------------ ------------------------------------------------
        16 SYS                                    47 17978                                                   oraagent.bin@dbarm2 (TNS V1-V3)
        19 DBSNMP                                  0 1234                     unknown                        JDBC Thin Client
        25 SYSMAN                                  0 1234                     unknown                        OMS
        26 DBSNMP                                  0 1234                     unknown                        JDBC Thin Client

From the above query SID is session ID, USERNAME is the name of database user, Process is the OS process number, Terminal is the name of system executing this query and program shows name of the program using this query. Terminal and Program is the most important field to find out culprit sessions in ORA-00020 errors.

2. Find Blocking Sessions: A common complain to Database Administrator by user is "my database connection is very slow". In this case DBA should check two things Either whole database is running slow or Only one user session is slow. To check whole database status DBA can use Top Command, OS Watcher and AWR Report.

If you are sure that only single session is running slow then V$session or gv$session is parfect place to start. I have seen many cases where one session is blocking other session due to uncommitted transactions.

SQL> select sid, username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where BLOCKING_SESSION is not null;
       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION WAIT_CLASS
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ----------------- ---------------- ---------------
        47 SCOTT               6 ACTIVE   sqlplus@database.example.com (TNS V1-V3)         2rbwgj3zdsnus                 1               34 Application

In above Case, User scott was not getting any response from his query. I check using above query in which "BLOCKING_SESSION" shows the detail of session which is blocking scott user session.

SID,USERNAME,COMMAND,STATUS,PROGRAM and SQL_ID are scott use detail, BLOCKING_INSTANCE means at which instance blocking session is running, BLOCKING_SESSION is the session ID of blocking session.

Now next action plan for DBA is to check what is causing 34 to block scott session. DBA can use below query to figure out session 34 details.

SQL> select sid,username,command,status,program,sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,WAIT_CLASS from gv$session where sid=34;
       SID USERNAME      COMMAND STATUS   PROGRAM                                          SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION WAIT_CLASS
---------- ---------- ---------- -------- ------------------------------------------------ ------------- ----------------- ---------------- ---------------
        34 SCOTT               3 INACTIVE sqlplus@database.example.com (TNS V1-V3)         17d40vwcct4g6                                    Idle

Though,There could be many reasons for this kind of problem. One of them I have discussed here.

3. Investigate User Session Waits: I discussed a case in above point when one session is blocking other session. Suppose that is not the case then DBA has to look at wait events of the session. There are n number of possibilities for a session is waiting for and Database Administrator has to act accordingly. Using this query you can find out sessions waiting details:

SQL> select INST_ID,SID,USERNAME,COMMAND,WAIT_CLASS,WAIT_TIME,STATE WAIT_TIME_MICRO from gv$session;
   INST_ID        SID USERNAME                          COMMAND WAIT_CLASS                                                        WAIT_TIME WAIT_TIME_MICRO
---------- ---------- ------------------------------ ---------- ---------------------------------------------------------------- ---------- -------------------
         2          1                                         0 Idle                                                                      0 WAITING
         2          2                                         0 Idle                                                                      0 WAITING
         2          3                                         0 Idle                                                                      0 WAITING
         2          4                                         0 Idle                                                                      0 WAITING

For a single instance use v$session and remove INST_ID column. Idle, System I/O, Other, user I/O, Concurrency, Commit, Scheduler, Configuration, Application and Network are the main wait classes.
IF any session is waiting into "Concurrency" then DBA has to investigate immediately, If session is waiting into "Idle" it is not doing any operation.

If you found DB session waiting into any other wait event. Then you can further dig into using V$SESSION_EVENT.

SQL> select SID,EVENT,TIME_WAITED,TOTAL_WAITS from V$SESSION_EVENT where SID=205;
       SID EVENT                                                            TIME_WAITED TOTAL_WAITS
---------- ---------------------------------------------------------------- ----------- -----------
       205 Disk file operations I/O                                                   0           8
       205 latch: cache buffers chains                                                0           3
       205 buffer busy waits                                                        100           6
       205 gc buffer busy acquire                                                     1          15
       205 log file sync                                                            391       11798
       205 db file sequential read                                                    0           2
       205 db file scattered read                                                     1           6
       205 gc cr multi block request                                                  0           6
       205 gc cr block 2-way                                                        780       20897

In above query I am checking wait for a session having id 205. Above result shows all wait details for session 205. DBA can see "log file sync" is the event having highest total_waits. So DBA has to optimize database to reduce this wait event. Here are the ways to reduce log file sync and log file switch (checkpoint incomplete) wait events.

http://www.dbas-oracle.com/2011/08/how-to-resize-andor-add-redo-logs.html
http://www.dbas-oracle.com/2011/08/log-file-switch-checkpoint-incomplete.html

4. Find SQL Running Query with Execution Plan: Some times sql queries start performing poorly because of change in their execution plan. However no change is sql query are done. To investigate, Is it really becuase of chenage execution plan or something else DBA should use V$session view again. GV$session view has SLQ ID and Plan Hash Value of query. Plan Hash Value shows the Plan ID of currently using plan of Sql query. DBA can further look into plan and check either it's a good plan or bad one.

SQL> select b.address,b.hash_value,b.child_number,b.plan_hash_value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=16;

Suppose you want to know query executed by Session Id 16. This query will give you current query executed by session 16. To find it's execution plan use.

SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||

options operation, object_name

FROM (

SELECT id, parent_id, operation, options, object_name

FROM v$sql_plan

WHERE address = '&address'

AND hash_value = &hash_value

AND child_number = &child_number

)

START WITH id = 0

* CONNECT BY PRIOR id = parent_id;

Provide Address, Hash Value and Child number as input from the last query and you will get the execution plan of running query. Here you can see execution plan of currently running query.

5. Kill SQL Session OS Process: Sometimes I have seen if you kill a sql process using below command, Session still not disappears or take a very long time to kill.

SQL> alter system kill session '130,8764';
alter system kill session '130,8764'
*
ERROR at line 1:
ORA-00031: session marked for kill

In that case you can kill that process directly from OS Level. Below is the query to find OS process ID. I am killing a session with SID 16

SQL> select spid from v$process p, v$session s where paddr = addr and sid=16;
SPID
------------------------
18801

SQL> host kill -9 18801

SQL> select spid from v$process p, v$session s where paddr = addr and sid=16;
no rows selected

Session disappears from query immediately. I will suggest not to use this command until very critical, Before executing make sure you are not killing any background process like smon and pmon. If you will do that your instance will terminate immediately.

It is a huge list of usage of V$session view, I have listed few of them here. Please share in comments if you know more.