Performance issue -- checking the locks

Take Statspack (SPreport)

If row lock contention is there in top 5 events --> so may be some session is blocking others

check for locks and kill the session after consulting client

select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid;
----------------------------------------------------------------------

check IS_BLOCKING column in dba_locks

col session_id format 9999
col lock_type format 9999999
col blocking_others format 9999999
select session_id,lock_type,blocking_others from dba_locks where blocking_others<>'Not Blocking';

----------------------------------------------------------------------------------------------------------

select sid,sql_id,prev_sql_id,status from v$session where sid=75;

----------------------------------------------------------------------------------------------------------

set lines 155
set pages 9999
select * from table(dbms_xplan.display_cursor(nvl('&sql_id','dummy'),null,'typical +peeked_binds'))
/

&sql_id --> sql_id which got from v$session

------------------------------------------------------------------------------------------------------------

select sid,sql_id,prev_sql_id,status from v$session where sid=75;

check whether sql_id is changing for this session. keep track this command and identify where is get struck for long time

also check if <> 'Not Blocking' is changing

----------------------------------------------------------------------------------------------------------------

select username,program,module from v$session where sid=75;


if Blocking_others <> 'Not Blocking' result is mostly "blocking" means inform the clients about the session and if they ask to kill the session we can kill the particular session with their permission


create index contract(getperioddate(A.contractiid,4,1))