Last four days we getting wait event that is read other by session at daily 9 am when client connect to server at a time.
Also Undo tablespace have 3 GB size on HD.
And before four day we move datafile from HD to SSD drive.
But I can't understand what is happen in inside the system.
I also refer contain of the net but don't getting correct solution.
Can you help me?
SQL> select name from v$event_name
2 where name like 'read %';
NAME
------------------------------------
read by other session
I'm assuming you mean this one ? You know ... when it comes to diagnosing problems...precision sortta matters :-)
"read by other session" means person "A" is reading something from disk into memory. Person "B" also wants to do the same thing... so that wait for "A" to finish.
When this happens, you want to capture what data was being sought, so you can do this:
SELECT p1 , p2
FROM v$session_wait_history
WHERE event = 'read by other session';
and with those returned values
SELECT *
FROM dba_extents
WHERE file_id = "p1"
AND "p2" BETWEEN block_id AND block_id + blocks - 1;
This will tell you what was being competed for. The *cause* of the contention could be various:
1) bad SQL query, so people are going after the same block over and over and over
2) bad I/O, so the time to do that read is very slow
3) an external influence, eg, some *other* process is reading lots of blocks and constantly pushing the block you want out of memory.
See how you go.