v$session_wait Tips & Tricks

v$session_wait Tips - Analyzing waits during bottleneck situation

Analyzing real-time physical I/O waits is an important step in improving performance

what are in v$session_wait?

The v$session_wait view displays information about wait events for which active sessions are currently waiting. The following is the description of this view, and it contains some very useful columns, especially the P1 and P2 references to the objects associated with the wait events.

SQL> desc v$session_wait 

Name Null? Type
--------------------------- -------- ------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

Using v$session_wait, it is easy to interpret each wait event parameter using the corresponding descriptive text columns for that parameter. Also, wait class columns were added so that various wait events could be grouped into the related areas of processing such as network, application, idle, concurrency, etc.

This view provides the DBA with a dynamic snapshot of the wait event picture for specific sessions. Each wait event contains other parameters that provide additional information about the event. For example, if a particular session waits for a buffer busy waits event, the database object causing this wait event can easily be determined:

SQL> 
select username, event, p1, p2 from 
v$session_wait 
where sid = 74;

The output of this query for a particular session with SID 74 might look like this:

USERNAME    EVENT            SID P1 P2 
---------- ----------------- --- -- ---
HR         buffer busy waits 74  4  155

Columns P1 and P2 allow the DBA to determine file and block numbers that caused this wait event. The query below retrieves the object name that owns data block 155, the value of P2 above:

SQL> select segment_name,segment_type
from dba_extents
where file_id = 4 
and 155 between block_id and block_id + blocks – 1;

OR

SQL> select segment_name,segment_type
from dba_extents
where file_id = &file_id
and &Block_id between block_id and block_id + blocks – 1;

Note: Here you can enter values

If you are getting below error:
ERROR:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small

Solution:

Please refer my link....

Click Here

SEGMENT_NAME              SEGMENT_TYPE
------------------------------ ---------------
employee                                     TABLE

The above output shows that the table named orders caused this wait event, a very useful clue when tuning the SQL within this session. Also, see my notes on v$session_wait.

The ability to analyze and correct Oracle Database physical read wait events is critical in any tuning project. The majority of activity in a database involves reading data, so this type of tuning can have a huge, positive impact on performance.

System wait tuning has become very popular because it can show you those wait events that are the primary bottleneck for your system. Some experts like the 10046 wait event (level 8 and higher) analysis technique and Oracle MOSC now has an analysis tool called trcanlzr.sql to interpret bottlenecks via 10046 trace dumps. However, some Oracle professionals find dumps cumbersome and prefer to sample real-time wait events.

When doing wait analysis, it is critical to remember that all Oracle databases experience wait events, and that the presence of waits does not always indicate a problem. In fact, all well-tuned databases have some bottleneck. (For example, a computationally intensive database may be CPU-bound and a data warehouse may be bound by disk-read waits.) In theory, any Oracle database will run faster if access to hardware resources associated with waits is increased.

Finding the Contentions:

For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read, but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).

Same P1,P2,P3 means they are waiting on same file, same block and same number of blocks. But could be on different rows or same rows.

You need to query v$lock to find out more info.

The values represent:

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:
SQL>
select p1 "File#",p2 "Block#",p3 "ReasonCode"
from v$session_wait
where event = '&event_name';

You can trace P1 and P2 back to the specific table or index with these scripts:

If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:

SELECT relative_fno, owner, segment_name, segment_type 
FROM dba_extents 
WHERE file_id = &file 
AND &block BETWEEN block_id AND block_id + blocks - 1;

Verification via Event name:

Oracle 10g v $ session view different wait events corresponding p1, p2, p3 of meaning, we can not remember all waiting for an event corresponding p1, p2, p3 of meaning.

The meaning of each wait event corresponds know by querying the V $ EVENT_NAME p1, p2, p3 of
SQL> 
col name format a25; 
col p1 format a10; 
col p2 format a10; 
col p3 format a10; 
SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3 
2 FROM V$EVENT_NAME 
3 WHERE NAME = '&event_name'; 

The event_name input values: db file scattered read 
Original value of 3: WHERE NAME = '& event_name A' 
The new value 3: WHERE NAME = 'db file scattered read' 

The name P1 P2 P3 
-------------------------------------------------- -------- 
db file scattered read file # block # blocks 

file #: data file number 
Block #: starting block number 
blocks: to read the the the number of of the data block 

If you want to trace and analyze the session detail or query details to proceed further, the trace the query or session.

Click here to read more about SQL Trace and Oradebug.