 |
|
Oracle session views
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall.
Information about the performance of an Oracle
instance and its sessions is provided by the dynamic performance
views. These read-only views are created by the catalog.sql script
and provide an internal view of the disk and memory structures for the
instance. The dynamic performance views can be identified by
searching for views with a "V_$" prefix.
SELECT
view_name
FROM dba_views
WHERE view_name LIKE 'V_$%'
ORDER BY view_name;
VIEW_NAME
------------------------------
V_$ACCESS
V_$ACTIVE_INSTANCES
V_$ACTIVE_SERVICES
.
.
.
V_$WAITSTAT
V_$_LOCK
347 rows
selected.
Each view has a public synonym defined with a
"v$" prefix, which should be used in preference to the real name to
access the view.
Nearly every v_$ view as an associated gv_$
view, in which the "G" stands for global. These views are used in a
Real Application Clusters (RAC) environment to provide a view of
performance data for all instances that make up a specific database.
The view structures are the same as their equivalent v_$ views, with
the addition of an INST_ID column that indicates the owning instance
of the particular data row.
The complexity and number of the v_$ views
make a full explanation of them well beyond the scope of this book.
Instead, the rest of this sections contains example queries using some
of the v_$ views that I use to help to identify problem sessions and
SQL statements. These scripts, along with many more, are freely
available for download from the DBA Scripts page at http://www.oracle-base.com.
sessions.sql
The sessions.sql script uses the v$session
view to provide information about all the sessions currently connected
to the database instance. In addition, the v$process view is used to
provide the associated operating system process id (SPID) for each
session.
If you don't want to write your own v$ scripts, you
can download over 600 scripts in the
Oracle scripts
collection.
sessions.sql
SET LINESIZE
500
SET PAGESIZE 1000
COLUMN
username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE
see code depot
for full script
s.paddr = p.addr
ORDER BY s.username, s.osuser;
SET PAGESIZE
14
The typical
output for this script is show below.
SQL>
@sessions
USERNAME
OSUSER SID SERIAL# SPID LOCKWAIT STATUS MODULE MACHINE
PROGRAM LOGON_TIME
---------- --------- ---- -------- ----- -------- ------- ---------
-------- ------------------ TEST tim_hall 147 4
2228 ACTIVE SQL*Plus BART sqlplusw.exe
22-AUG-2005 12:01:53
(oracle) SYSTEM 170 1 1016 ACTIVE
BART ORACLE.EXE (PMON) 22-AUG-2005 11:50:51
(oracle) SYSTEM 169 1 2492 ACTIVE
BART ORACLE.EXE (MMAN) 22-AUG-2005 11:50:51
(oracle) SYSTEM 168 1 2212 ACTIVE
BART ORACLE.EXE (DBW0) 22-AUG-2005 11:50:51
(oracle) SYSTEM 167 1 3612 ACTIVE
BART ORACLE.EXE (LGWR) 22-AUG-2005 11:50:51
(oracle) SYSTEM 166 1 3828 ACTIVE
BART ORACLE.EXE (CKPT) 22-AUG-2005 11:50:51
(oracle) SYSTEM 157 1 3048 ACTIVE
BART ORACLE.EXE (MMNL) 22-AUG-2005 11:51:06
(oracle) SYSTEM 160 7 308 ACTIVE
BART ORACLE.EXE (MMON) 22-AUG-2005 11:51:06
(oracle) SYSTEM 165 1 2208 ACTIVE
BART ORACLE.EXE (SMON) 22-AUG-2005 11:50:51
(oracle) SYSTEM 164 1 2572 ACTIVE
BART ORACLE.EXE (RECO) 22-AUG-2005 11:50:51
(oracle) SYSTEM 163 1 3280 ACTIVE
BART ORACLE.EXE (CJQ0) 22-AUG-2005 11:50:51
(oracle) SYSTEM 159 1 3020 ACTIVE
BART ORACLE.EXE (QMNC) 22-AUG-2005 11:51:02
(oracle) 155 27 2152
ACTIVE 22-AUG-2005 12:01:35
(oracle) 153 3 2296
ACTIVE 22-AUG-2005 11:51:32
(oracle) 145 3 3392
ACTIVE 22-AUG-2005 11:51:30
15 rows
selected.
Previously, we discussed the use of the
dbms_application_info package, which allows an application to write
information into the v$session view. The session.sql script displays
the module column from the v$session view, making the identification
of sessions associated to registered modules extremely simple. The
script could easily be modified to include both the action and
client_info columns.
The sid and serial# columns are used together
when trying to kill sessions using the ALTER SYSTEM KILL SESSION
command, but the sid is often used on its own as a parameter for
subsequent scripts.
top_sessions.sql
The top_session.sql script is a variation of
the session.sql script, which lists all database sessions ordered by
one of three criteria specified by a parameter:
-
READS - The number of reads performed
by each session.
-
EXECS - The number of executions
performed by each session.
-
CPU - The amount of CPU used by the
session.
This helps to identify the database sessions
that are using the most resources, allowing your efforts to be focused
on the code being run by these sessions.
The statistics are retrieved from the
v$sesstat view, with the v$statname view being used to identify the
statistic name.
top_sessions.sql
SET LINESIZE
500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN
username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE
see code depot
for full script
a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical
reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this
session',
'CPU used by this
session')
ORDER BY c.value DESC;
SET PAGESIZE 14
This scripts output is listed from largest to
smallest, as shown below.
SQL> @top_sessions
reads
USERNAME
OSUSER SID SERIAL# READS LOCKWAIT STATUS MODULE MACHINE
PROGRAM LOGON_TIME
--------- --------- ---- -------- ------ -------- ------- ----------
-------- -----------------(oracle) SYSTEM 160 7
8542 ACTIVE BART ORACLE.EXE (MMON)
23-AUG-2005 13:53:41
(oracle) SYSTEM 165 1 7132 ACTIVE
BART ORACLE.EXE (SMON) 23-AUG-2005 13:53:18
(oracle) SYSTEM 163 1 2451 ACTIVE
BART ORACLE.EXE (CJQ0) 23-AUG-2005 13:53:18
TEST tim_hall 162 5 594 ACTIVE SQL*Plus
BART sqlplusw.exe 23-AUG-2005 14:21:38
(oracle) SYSTEM 164 1 70 ACTIVE
BART ORACLE.EXE (RECO) 23-AUG-2005 13:53:18
(oracle) SYSTEM 157 1 25 ACTIVE
BART ORACLE.EXE (MMNL) 23-AUG-2005 13:53:41
(oracle) SYSTEM 149 11 11 ACTIVE
BART ORACLE.EXE (q000) 23-AUG-2005 14:14:50
(oracle) SYSTEM 159 1 6 ACTIVE
BART ORACLE.EXE (QMNC) 23-AUG-2005 13:53:35
(oracle) SYSTEM 170 1 0 ACTIVE
BART ORACLE.EXE (PMON) 23-AUG-2005 13:53:18
(oracle) 161 63 0 ACTIVE
23-AUG-2005 14:21:29
(oracle) 150 3 0 ACTIVE
23-AUG-2005 14:00:20
(oracle) SYSTEM 166 1 0 ACTIVE
BART ORACLE.EXE (CKPT) 23-AUG-2005 13:53:18
(oracle) SYSTEM 167 1 0 ACTIVE
BART ORACLE.EXE (LGWR)
23-AUG-2005 13:53:18
(oracle) SYSTEM 168 1 0 ACTIVE
BART ORACLE.EXE (DBW0) 23-AUG-2005 13:53:18
(oracle) SYSTEM 169 1 0 ACTIVE
BART ORACLE.EXE (MMAN) 23-AUG-2005 13:53:18
15 rows
selected.
SQL>
top_sql.sql
Focusing your efforts on tuning the worst
performing statements will generally yield the biggest returns. The
top_sql.sql script returns the SQL statements that have caused the
most disk reads per execution since the instance was last started.
The statistics displayed by this script are retrieved from the
v$sqlarea view and the number of rows displayed is limited by an input
parameter.
top_sql.sql
SET LINESIZE
500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions))
reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
see code depot
for full script
ORDER BY 2 DESC)
WHERE rownum <= &&1;
SET PAGESIZE
14
The output below shows the top five SQL
statements based on the reads per execution criteria.
SQL> @top_sql
5
SQL_TEXT
READS_PER_EXECUTION BUFFER_GETS DISK_READS EXECUTIONS SORTS
ADDRESS
-------------------------------------------------- -------------------
----------- ---------- -insert into "SYS"."ALERT_QT" (q_name, msgid,
corr 3 1540 30 8 0
69D5378C
SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE
1 19 1 1 0 6A74C7A0
select last_sample_date_num, last_sample_date, tot
1 32 1 1 0 6AB8265C
insert into sys.col_usage$ values ( :objn, :coln
0 25 2 6 0 69E1A060
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.t
0 3598 38 424 1208 6A595690
5 rows
selected.
SQL>
longops.sql
The use of the dbms_application_info package
to register long operations was discussed in a previous section. The
longops.sql scripts uses the v$session_longops view to identify and
monitor sessions performing long operations, including those
registered using the dbms_application_info package.
longops.sql
COLUMN sid
FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' ||
MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60)
remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE
see code depot
for full script
s.sid = sl.sid
AND s.serial# = sl.serial#;
When the instrumentation is switched off in production
environments, it is often difficult to make estimations of the
progress of batch processes. The registration of PL/SQL batch
processes as long operations takes the guesswork out of these
estimations, making it very valuable.
session_waits.sql
The session_waits.sql script uses the
v$session_wait view to display a list of the events currently being
waited on by active sessions. This is useful when trying to discover
what each active session is currently doing.
session_waits.sql
SET LINESIZE
200
SET PAGESIZE 1000
COLUMN
username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE see
code depot for full script
s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
The output
from this script is shown below.
SQL> @session_waits
USERNAME
SID SERIAL# EVENT WAIT_CLASS WAIT_TIME
SECONDS_IN_WAIT STATE
--------- ---- -------- -------------------------- -----------
---------- --------------- -----(oracle) 170 1 pmon timer
Idle 0 1195 WAITING
(oracle) 157 1 rdbms ipc message
Idle 0 1185 WAITING
(oracle) 145 3 jobq slave wait
Idle 0 1164 WAITING
(oracle) 169 1 rdbms ipc message
Idle 0 1164 WAITING
(oracle) 153 3 jobq slave wait
Idle 0 1162 WAITING
(oracle) 164 1 rdbms ipc message Idle
0 1139 WAITING
(oracle) 163 1 rdbms ipc message
Idle 0 653 WAITING
(oracle) 146 13 wakeup time manager
Idle 0 531 WAITING
(oracle) 159 1 Queue Monitor Wait
Idle 0 531 WAITING
(oracle) 160 7 rdbms ipc message
Idle 0 282 WAITING
(oracle) 165 1 smon timer
Idle 0 219 WAITING
(oracle) 168 1 rdbms ipc message
Idle 0 70 WAITING
(oracle) 155 45 jobq slave wait
Idle 0 6 WAITING
(oracle) 167 1 rdbms ipc message
Idle 0 6 WAITING
TEST 147 4 SQL*Net message to client Network
-1 0 WAITED SHORT TIME
(oracle) 166 1 rdbms ipc message
Idle 0 0 WAITING
16 rows
selected.
The meaning of the wait_time and
seconds_in_wait columns varies depending on their values follows:
-
wait_time - A non-zero value
represents the session’s last wait time, while a zero value
indicates that the session is currently waiting.
-
seconds_in_wait - When the wait_time
is zero, the seconds_in_wait value represents the seconds spent in
the current wait condition. When the wait_time is greater than
zero, the seconds_in_wait value represents the seconds since the
start of the last wait, and (seconds_in_wait - wait_time / 100) is
the active seconds since the last wait ended.
The wait_class column is only available in
Oracle 10g, so this should be commented out for use against previous
versions.
session_events_by_sid.sql and system_events.sql
Once a session of interest has been
identified, the session_events_by_sid.sql script can be used to
display the history of events associated with that session. It does
this by retrieving the event information from the v$session_event
view.
session_events_by_sid.sql
SET LINESIZE
200
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN
username FORMAT A20
COLUMN event FORMAT A40
SELECT
NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
se.max_wait,
se.time_waited_micro
FROM v$session_event se,
v$session s
WHERE
see code depot
for full script
s.sid = se.sid
AND s.sid = &1
ORDER BY se.time_waited DESC;
The times displayed by this script are all
based on hundredths of seconds, as seen below.
SQL> @session_events_by_sid
147
USERNAME
SID SERIAL# EVENT TOTAL_WAITS TOTAL_TIMEOUTS
TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
--------- ---- -------- ------------------------------ -----------
-------------- ----------- -TEST 147 4 SQL*Net message
from client 39 0 32745
840 21864 327450526
TEST 147 4 db file sequential read
27 0 15 1 4
149683
TEST 147 4 db file scattered read
16 0 3 0 1
29984
TEST 147 4 SQL*Net message to client
40 0 0 0 0
274
TEST 147 4 SQL*Net more data to client
1 0 0 0 0 157
TEST 147 4 SQL*Net break/reset to client
2 0 0 0 0 206
6 rows
selected.
The session_events_by_sid.sql script is a
useful tool for identifying events having the greatest impact on the
performance of a session, thus allowing your efforts to be focused on
those events that will give the best performance returns.
The system_events.sql script is similar to the
session_events.sql script, except the statistics are sourced from the
v$system_event view, representing the values for the whole instance
rather than for individual sessions.
system_events.sql
SELECT
event,
total_waits,
total_timeouts,
time_waited,
average_wait,
time_waited_micro
FROM v$system_event
ORDER BY event;
An example of the output expected from the script is listed
below.
SQL> @system_events
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
AVERAGE_WAIT TIME_WAITED_MICRO
------------------------------ ----------- -------------- -----------
------------ ------------LGWR wait for redo copy
9 0 0 0 523
Queue Monitor Slave Wait 27 27
111552 4132 1115524830
Queue Monitor Task Wait 21 0
0 0 213
Queue Monitor Wait 56 56
167191 2986 1671909935
.
.
.
wakeup time manager 5 0
141580 28316 1415799209
54 rows
selected.
|
and include the URL for the page.
