Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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 A
20

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.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational