A user is complaining that the application is running slowly, or a report someone scheduled is taking too long to finish - where do you look to find out what is going on inside the Oracle database right now? Well if you have a nice GUI based tool such as Enterprise Manager then go there first, because that will have summary screens of what is going on, and also drill downs into the details behind what is going on. Such tools are easier and quicker to use, and mean you don't have to remember the names of internal Oracle dynamic performance views.
But what if you don't have a nice GUI based tool? Or you want to work out for yourself what is really going on in Oracle? Or something has happened to the nice GUI tool and you cannot use it for one reason or another? Then you need to know about Dynamic Performance Views in Oracle, also known as the
V$ tables, and which ones are the most relevant ones.
The Dynamic Performance Views are views defined internal to Oracle that
in fact map onto memory structures inside the SGA (System Global Area).
They enable you to see in real time various data within the SGA using
normal SQL queries.When investigating performance problems you should generally narrow it down to a specific problem and the users being affected by it. In Oracle each connected user has a corresponding "session" inside the Oracle database instance, and information on each session is made available in the
V$SESSION dynamic performance view. This is both an advantage and disadvantage of the Dynamic Performance Views - they are a snapshot of current values in the SGA, and these values can be changing all of the time. Look again and you will get different results. Some views show cumulative values rather than current values, but they are always increasing over time and also constantly changing.
But with that in mind, you can use the
V$SESSION
view to look at what specific sessions are doing at the moment, and
look for any slow, long running SQL statements. We can group the data
columns into the following related groups:- Session Connection Information - User name, Client Application software, Logon time
- Current Activity Information - Status, SQL statement executing
- Delays - both normal Waits and abnormal Blocks i.e. small delays or completely blocked
- Waits are delays that will definitely finish e.g. read a block from disk
- Blocks are delays that are outside the control of the session and can potentially go on forever
- e.g. blocked waiting for a row lock held by another session
V$SESSION
look in the "Oracle Database Reference" Manual under "Dynamic
Performance Views", where you will get a brief description of each
column.All Active Sessions
Want a list of all active sessions and whether they are waiting or not? Try this query from SQL*Plus:-- List the active sessions and break down key data
--
column sid format 999999
column db_user format a8 heading 'USER'
column db_login_time format a6 heading 'LOGON'
column program format a10 heading 'APP_N'
column active_for format 999 heading 'ACTV|FOR'
column state format a5 heading 'STATE'
column waiting_for format 999 heading 'WAIT|FOR'
column event format a12 heading 'WAIT ON'
column sql_id format a13 heading 'SQL ID'
--
select s.sid,
nvl (s.username, 'SYS') db_user,
to_char (s.logon_time, 'hh24:mi') db_login_time,
substr (s.program, 1, 10) program,
s.last_call_et active_for,
decode (s.state,
'WAITING', 'WAIT',
'WAITED KNOWN TIME', 'NW L',
'NW S') state,
decode (s.wait_time, 0, seconds_in_wait, 0) waiting_for,
substr (s.event, 1, 12) event,
nvl (s.sql_id, 'Not Executing') sql_id
FROM v$session s
WHERE s.type = 'USER'
AND s.status = 'ACTIVE'
AND s.wait_class != 'Idle'
/
--
- It restricts the
sessions to only those from real users (not internal background
sessions) that are active and experiencing non-idle waits.
- Idle waits are generally waits external to Oracle, such as waiting for the next SQL statement to execute
- The number of columns of data output is kept low to fit within an 80 column wide terminal screen
- If you have a wider output format then you could add extra data columns to the "select"
- The "
decode" of "state" is meant to encode the following combinations of possibilities:- WAIT = WAITING, NW = NOT WAITING; PREV WAIT:- S = SHORT, L = LONG
- i.e. it combines both whether the session is currently waiting or not, with whether the previous wait was a short or long wait
- It is assumed that users have all connected today, so only the time of connection is shown
- If connections are long lived, then change the date format used in the "
to_char" to include the day
- If connections are long lived, then change the date format used in the "
- How many sessions are active, and how many are currently waiting on something?
- Sessions that are active and not waiting are therefore running on a CPU
- How many sessions are running the same SQL statement? Look at the values of
SQL_ID - How many sessions are experiencing the same kind of wait event?
- Are sessions experiencing long or short waits most of the time?
One Session's Details
If you know theSID
(Session ID) of one session you are interested in and want to drill
down further, then you could run the following set of queries from
within SQL*Plus:--
set newpage none
--
set define on
undefine SESSION_ID
--
accept SESSION_ID prompt 'Enter Session ID > '
--
column sid FORMAT 99999
column username FORMAT a12
column logt FORMAT a12 HEADING 'Logon Time'
column sql_start format a9 heading 'SQL Start'
column last_call_et heading 'Time at Status'
column module FORMAT a16 heading 'Module'
column client_info format a16 heading 'Client Info'
column command format 999 heading 'cmd'
column taddr format a8 heading 'TX Addr'
column server heading 'Server|Type'
column schemaname format a12 heading 'Schema|Name'
column type heading 'Session|Type'
column sql_id heading 'Curr SQL ID'
column prev_sql_id heading 'Prev SQL ID'
column lockwait format a8 heading 'Wait Lock|Addr'
column event format a25 heading 'Waiting For'
column wait_class format a12 heading 'Wait Class'
column row_wait_obj# heading 'Object|Waiting On'
column wait_time heading 'Last Wait Time|(0=Waiting)'
column seconds_in_wait heading 'Elapsed From|Last Wait'
column blocking_session heading 'Blocking|Session ID'
column blocking_session_status heading 'Blocking|Sess Status'
--
select sid,
username,
to_char (logon_time, 'dd/mm hh24:mi') logt,
status,
last_call_et,
to_char (sql_exec_start, 'HH24:MI:SS') sql_start
from v$session
where sid = &&SESSION_ID
/
--
select sid,
substr (module, 1, 24) module,
substr (client_info, 1, 30) client_info,
server, schemaname, type
from v$session
where sid = &&SESSION_ID
/
--
select sid,
sql_id, prev_sql_id,
event,
substr (wait_class, 1, 20) wait_class
from v$session
where sid = &&SESSION_ID
/
--
select sid,
lockwait,
row_wait_obj#,
wait_time, seconds_in_wait,
blocking_session, blocking_session_status
from v$session
where sid = &&SESSION_ID
/
--
column sql_text format a62
--
select sql_id, sql_text from v$sql
where sql_id = (select sql_id from v$session where sid = &&SESSION_ID)
/
--
undefine SESSION_ID
--
set newpage 1
This gives you a lot more information from
V$SESSION for the one session, broken down into related sets of data, as described before. Note that the final query selects from V$SQL
which is probably the other main Dynamic Performance View, which has an
entry for each SQL statement currently in the library cache in the SGA.
Remember that a SQL statement in the library cache can be shared
between many sessions.One Session Detailed Drill Down
If you really want to drill down more into what a single session or a set of sessions is doing over a period of time then you could use Tanel Poder's snapper.sql. This snapshots the contents ofV$SESSION
and other Dynamic Performance views over a period of time and reports
on what it captured. Mainly this will be the wait events experienced by
the sessions, but a lot of other activity statistics are reported too.Snapper is written in PL/SQL and uses two in-memory PL/SQL tables for each snapshot, and then lists out anything that has changed between them. It does not write anything to the database, and only needs minimal permissions -
SELECT_CATALOG_ROLE and execute on DBMS_LOCK.SLEEP. Thus it is safe to run on any database.Conclusion
I've shown how to list all the currently active sessions on a database including the SQL they are executing and what they are waiting on, and how to get more details on what just one session is doing.If you want even more information on what a single session is doing then you can SQL Trace it, which creates a trace file on the database server of all the SQL statements executed by that session and all the waits it experienced while tracing was enabled. Nothing is missed out because every executed SQL statement from that session is traced. You can then use utilities such as "
tkprof" to post process the trace file and report on the SQL statements executed, their elapsed times, and the waits they experienced.If the slow down is not currently happening but was very recent then you can look back at the recent past using ASH (Active Session History), but note that ASH is an extra cost option - you must have the Diagnostic Pack of the Enterprise Edition of Oracle. I'll write something about using ASH in a future post.
