What is my sesison doing
steps
1. find sid of the session from v$session
2. check v$Session_wait for last wait activity
3. check v$session_event for commuvative waits
4. check v$sesstat for resource usage stats
from where and what
my sid v$mystat
rownum=1
others sid v$session ,v$process
what's up v$sesstat v$statname v$sess_io v$session_wait
CPU used by this session
which segment v$sesion_wait
buffer bust waits db file sequential read db file scattered read free buffer waits
which latch v$session_wait v$latchname
latch free
which sql v$sqltext v$sqlarea v$session
sid
Current State Views
V$SESSION - Sessions currently connected to the instance
v$session_wait - last/current wait
This is a key view for finding bottlenecks. It tells what every session in the
database is currently waiting for (or the last event waited for by the session
if it is not waiting for anything). This view can be used as a starting point
to find which direction to proceed in when a system is experiencing performance
problems.
Since 10g, Oracle displays the v$session_wait information also in the v$session view.
Summary Since Session Startup - cummulative
v$mystat - Resource usage summary for your own session
This view records statistical data about the session that accesses it.
v$session_event - Session-level summary of all the waits for current sessions
This view summarizes wait events for every session. While V$SESSION_WAIT shows
the current waits for a session, V$SESSION_EVENT provides summary of all the
events the session has waited for since it started.
v$sesstat - session-level summary of resource usage since session startup
V$SESSTAT stores session-specific resource usage statistics, beginning at login
and ending at logout.
Includes session logical reads, CPU used by this session, db block changes,
redo size, physical writes, parse count (hard), parse count (total),
sorts (memory), and sorts (disk).
V$SESSTAT can be used to find sessions with the following:
* The highest resource usage
* The highest average resource usage rate (ratio of resource usage to logon time)
* The current resource usage rate (delta between two snapshots)
v$sysstat - Summary of resource usage
V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since
the instance was started.
Similar to V$SESSTAT, this view stores the following types of statistics:
* A count of the number of times an action occurred (user commits)
* A running total of volumes of data generated, accessed, or manipulated (redo size)
* If TIMED_STATISTICS is true, then the cumulative time spent performing some
actions (CPU used by this session)
The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed from V$SYSSTAT data.
v$system_event - cummulative Instance wide summary of resources waited for
This view displays the count (total_waits) of all wait events since startup of the instance.
This view is a summary of waits for an event by an instance. While V$SESSION_WAIT
shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all
the event waits on the instance since it started. It is useful to get a historical
picture of waits on the system. By taking two snapshots and doing the delta on
the waits, you can determine the waits on the system in a given time interval.
v$waitstat - Break down of buffer waits by block class
total_waits where event='buffer busy waits' is equal the sum of count in v$system_event.
This view keeps a summary all buffer waits since instance startup. It is useful
for breaking down the waits by class if you see a large number of buffer busy
waits on the system.
LINK
oracle 9i performance document
oracle 10g performance document
