Oracle 10g v$session

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 support
Remote plans
Remote services
Application Server
Applications
Oracle Forms
Oracle Portal
App Upgrades
SQL Server
Oracle Concepts
Software Support
Remote Support
Development
Implementation

Consulting Staff
Consulting Prices
Help Wanted!

Oracle Posters
Oracle Books
Oracle Scripts
Ion
Excel-DB

Don Burleson Blog

Oracle 10g ASH views v$session_event

by Donald Burleson

It will be useful to look at examples of how the v$session_event view might be used in real life. In this example, end users have started complaining that they recently started to experience large delays when running a production application.

In some ERP applications (Oracle Applications, SAP), a single user account is used to connect to the database. In these cases, the DBA can issue the following statement to determine the particular event application for which the sessions are waiting:

< session_waits.sql

select

se.event,

sum(se.total_waits),

sum(se.total_timeouts),

sum(se.time_waited/100) time_waited

from

v$session_event se,

v$session sess

where

sess.username = 'SAPR3'

and

sess.sid = se.sid

group by

se.event

order by 2 DESC;

The output of this script might look like the following:

Waits for user SAPR3

SUM SUM TIME

EVENT WAITS TIMEOUTS WAITED

--------------------------- ------- --------- -----

SQL*Net message to client 7,824 0 .06

SQL*Net message from client 7,812 0 312,969.73

db file sequential read 3,199 0 16.23

SQL*Net more data to client 590 0 .08

SQL*Net break/reset to client 418 0 .2

direct path read 328 0 .01

SQL*Net more data from client 78 0 3.29

latch free 62 10 .08

db file scattered read 56 0 .75

log file sync 47 0 .96

direct path write 32 0 .4

file open 32 0 0

library cache pin 13 0 0

log file switch completion 3 0 .53

From the listing above, the DBA can conclude that end users spend most of their wait time waiting on the event SQL*Net message from client. This may indicate that there is some network-related issue causing clients too much wait time to send data to the database server.

��