Perform Waiting 10g

How can we determine what's make Database slowly? or have the problem. We can find session waiting (V$SESSION) without Idle event:

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle';

SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME
---------- ------------------------------ -----------------------------------------------
53 HR enq: TX - row lock contention 29 129 0

We'll see "HR" user, that's waiting on "enq: TX - row lock contention" event, that's blocked by session id 29.
After that, we can find SQL statements (on V$SQL):

select sid, sql_text
from v$session s, v$sql q
where sid in (53,29)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

SID SQL_TEXT
---- ----------------------------------------------------------------------------------
29 select * from BIG_TABLE for update
53 SELECT DECODE('A','A','1','2') FROM DUAL
53 SELECT DECODE('A','A','1','2') FROM DUAL
53 delete from big_table

A session id 29 is blocking session id 53; If session id 29 commits or roll back,that session id 53 will continue to wait for the lock.
Anyway We can kill session id 29.

After commit or roll back on session id 29:

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle';

no rows selected

In the Other Case:

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle';

SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME
---------- ------------------------------ -----------------------------------------
613 MGR db file sequential read 0 0

That's no blocking, But session id 613 is waiting for "db file sequential read" event.

Find sql statements:

select sid, sql_text
from v$session s, v$sql q
where sid = 613
and (q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

SID SQL_TEXT
---------- ----------------------------------------------------------------------------------------------------------------------------------
613 SELECT "A1"."CUSID","A1"."LOGIN","A1"."DOMAIN",... FROM ...
613 SELECT "A1"."GID","A1"."ASSETNUM","A1"."BCYCLE",... FROM ...

Anyway we can focus on specific class (use V$SESSION_WAIT_CLASS view):

select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 613;

WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- ----------- -----------
1893977003 Other 1224 15
3875070507 Concurrency 729 120
2723168908 Idle 2118450 121891
2000153315 Network 2118520 273
1740759767 User I/O 1484880 955565
3871361733 Cluster 975991 264267

6 rows selected.

Example: we focus on "User I/O". Find timed wait on V$SYSTEM_EVENT view:

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and e.wait_class_id = 1740759767 ;

EVENT TOTAL_WAITS TIME_WAITED
-------------------- ----------- -----------
Data file init write 43 9
local write wait 352 99
read by other session 236087 69939
db file sequential read 41818354 26016340
db file scattered read 18110759 6760862
db file single write 8156 2716
db file parallel read 3039583 2261496
direct path read 248340 8595
direct path read temp 2054769 125373
direct path write 109045 857
direct path write temp 113683 22748

We see "db file sequential read " event:
We can enable trace file on session id (613), after that investigate where the database is waiting?

Enjoy!