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.
- 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
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.
No comments:
Post a Comment