V$SESS_IO displays I/O statistics for each user session.
Column
|
Datatype
|
Description
|
SID | NUMBER |
Session identifier
|
BLOCK_GETS | NUMBER |
Block gets for this session
|
CONSISTENT_GETS | NUMBER |
Consistent gets for this session
|
PHYSICAL_READS | NUMBER |
Physical reads for this session
|
BLOCK_CHANGES | NUMBER |
Block changes for this session
|
CONSISTENT_CHANGES | NUMBER |
Consistent changes for this session
|
Note:
- SID (Session identifier) can be used to join to V$SESSION.SID.
- See Oracle Process and Session Data Dictionary Views.
- Use
V$SESS_IOto find sessions generating lots of redo:
V$SESS_IO contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;
Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
|
- Use V$SESS_IO to find out what users are doing and the resources they are using
To determine what is happening in the system at a point in time:
select a.sid, a.username, s.sql_text
from v$session a, v$sqltext s
where a.sql_address = s.address
and a.sql_hash_value = s.hash_value
order by a.username, a.sid, s.piece;
To recognize users that are performing a large numbers of physical disk or memory reads:
select a.username, b.block_gets, b.consistent_gets, b.physical_reads,
b.block_changes, b.consistent_changes
from v$session a , v$sess_io b
where a.sid =b.sid
order by a.username;
|
No comments:
Post a Comment