LOCK, performance realated scripts

To find details(including appln user name) of a lock on given object or all objects locked in database

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

To fine locks on table query
v$locked_object, some other tables dba_objecs, tab , dba-tables, v$session, v$process, v$loc, dba_locks ,dba-waiters
select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_NAME='FA_MASS_ADDITIONS';
select * from v$locked_object where OBJECT_ID=79706; if this return any rows then lock is present on that object
rdbms/admin/utllockt.sql to get lock tree
Also check in v$access to see if any session is holding the opbject
select SESSION_ID,OBJECT_ID,LOCKED_MODE,PROCESS from v$locked_object where OBJECT_ID=79706;
select SESSION_ID,OBJECT_ID,LOCKED_MODE,PROCESS,ORACLE_USERNAME,OS_USER_NAME from v$locked_object where OBJECT_ID=79706;
select LOCKWAIT,STATUS,SCHEMANAME,OSUSER from v$session where SID=105;

query to find a lock
select sid,username,serial#,status from v$session where sid in
(select session_id from dba_locks where blocking_others='Blocking');


To find locks on a module
SELECT a.object_id, a.session_id, SUBSTR(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner IN ('&M')
ORDER BY a.session_id;

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40),owner,status
FROM v$locked_object a, dba_objects b WHERE a.object_id = b.object_id AND b.owner in ('&module');


To find and terminate all the sessions held by an object
SQL> select sid, serial#, status, module from v$session where sid in (select distinct sid from v$access where object = 'XXART_RCV_TRANSACTION_PK');

SID SERIAL# STATUS MODULE
---------- ---------- -------- ------------------------------------------------
3434 53145 ACTIVE XXPOURCVW
3854 9742 ACTIVE XXPOURCVW
3949 6531 INACTIVE XXPOURCVW
3955 14566 INACTIVE XXPOURCVW
3991 50125 INACTIVE XXPOURCVW
4143 58949 ACTIVE XXPOURCVW
4294 39052 ACTIVE XXPOURCVW
4347 19960 INACTIVE XXPOURCVW
4770 65507 INACTIVE XXPOURCVW
4841 59140 INACTIVE XXPOURCVW
4895 3632 INACTIVE XXPOURCVW"


Some times JVM processes keeps hogging on the db server, to check who all the users connected from that jvm prcoess we can do the below to track those users along with the action they are doing and report to client.

set linesize 1000
set pagesize 1000
column module format a40
column program format a20
column address format 9
column appsuser format a8
column username format a4
column SID format 99999
column sql_text format a80
column user_name format a15
col action for a10
SELECT s.sid,s.serial#,p.pid,p.spid,s.status,s.action,s.last_call_et/60 wait_mins,f.user_name,s.module
FROM gv$process p, gv$session s,fnd_signon_audit_view f
WHERE p.inst_id = s.inst_id
and p.addr = s.paddr
and f.pid=p.pid
and s.process = '12649'
-- and s.sid = 9606;
/



To find forms session details which are existing from several hours"select b.sid ""sid"", b.serial# ""ser#"", c.spid ""OS Prc."", b.status, b.username, b.module, b.action, to_char(b.logon_time, 'DD-MON-YY HH:MI AM') ""Logon Time""
from v$session b, v$process c
where b.paddr = c.addr
and b.action like 'FRM%'
and b.logon_time < sysdate - (1/2) --logged in over 16 hours ago
order by logon_time asc
/"

select b.sid "sid", b.serial# "ser#", c.spid "OS Prc.", a.event, b.status, b.username, b.module, b.action,
to_char(b.logon_time, 'DD-MON-YY HH:MI AM') "Logon Time"
from v$session_wait a, v$session b, v$process c
where a.sid = b.sid
and b.paddr = c.addr
and b.action like 'FRM%'
and b.logon_time < (sysdate - (1/4)) -- logged in over 6 hours ago
order by logon_time asc

To find number of sessions held by application users
select count(*), fu.user_name
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name IN ('VLEE', 'TNGUYEN', 'DBREIG' ) group by fu.user_name
/"

To Kill all sessions of an user ( eg: DBREIG)

select 'alter system kill session ' || '''' ||sid || ',' || s.serial# || '''' || ';'
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='DBREIG'
order by f.start_time
/

TO get session informations of an application user

set linesize 1000 pagesize 1000 feedback off

column module format a15

column program format a20

column address format 9

column appsuser format a8

column username format a4

column SID format a10

column sql_text format a80

column user_name format a30

column "Time (m)" for a9

SELECT s.sid||','||s.serial# sid,p.pid,p.spid,s.status,s.action,f.time "Time (m)",u.email_address user_name

FROM v$process p, v$session s,fnd_signon_audit_view f, fnd_user u

WHERE p.addr = s.paddr

and f.pid=p.pid

and f.user_id = u.user_id

and u.user_name like '&Application_User%';

To find sql test from sid
"select sql_text from v$sqlarea where address in (select sql_address from v$session where sid =928);

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------
begin DP.DATA_LOAD.EP_LOAD_SALES; end;

SQL> select SID,EVENT,p1,p2 from v$session_wait where sid=928;

SID EVENT P1 P2
---------- ---------------------------------------------------------------- ---------- ----------
928 db file sequential read 53 223359


To get Top 5 time-consuming requets
*****************************

select f1.conc_prog,f1.req_id,f1.oracle_id from
(select f.concurrent_program conc_prog,f.request_id req_id,f.oracle_process_id oracle_id from
(select t.user_concurrent_program_name concurrent_program,
r.request_id request_id,r.oracle_process_id oracle_process_id
from fnd_concurrent_requests r,
fnd_concurrent_processes p,
fnd_concurrent_programs_tl t
where r.controlling_manager = p.concurrent_process_id
and r.program_application_id = t.application_id
and r.concurrent_program_id = t.concurrent_program_id
and to_char(sysdate-r.actual_start_date)*1440 > 60
and r.Phase_Code = 'R' and r.Status_Code = 'R'
order by to_char(sysdate-r.actual_start_date)*1440 ) f
where rownum < 6) f1

To get sqls of Top 5 time-consuming concurrent requests
***********************************************

set pages 5000
set lines 132
break on cy
spoo /tmp/cy
select f1.conc_prog||' '||f1.req_id||' '||f1.oracle_id cy,st.sql_text
FROM
(select f.concurrent_program conc_prog,f.request_id req_id,f.oracle_process_id oracle_id from
(select t.user_concurrent_program_name concurrent_program,
r.request_id request_id,r.oracle_process_id oracle_process_id
from fnd_concurrent_requests r,
fnd_concurrent_processes p,
fnd_concurrent_programs_tl t
where r.controlling_manager = p.concurrent_process_id
and r.program_application_id = t.application_id
and r.concurrent_program_id = t.concurrent_program_id
and to_char(sysdate-r.actual_start_date)*1440 > 60
and r.Phase_Code = 'R' and r.Status_Code = 'R'
order by to_char(sysdate-r.actual_start_date)*1440 ) f where rownum < 6) f1,
v$session s,
v$process p,
v$sqltext st
WHERE
p.spid = f1.oracle_id
and p.addr = s.paddr
and s.sql_address = st.address
and s.sql_hash_value = st.hash_value
order by cy,st.piece
/

Monitors for excessive JDBC connections and alerts if the connection count breaches the defined threshold.

Use the following query:
select count(*) from v$session where program like 'JDBC Thin%'

How to check JDBC Connection ?
connect using apps, sys or system & issue
select count(*), module from v$session where program like '%JDBC%' group by module;

Command to kill a session I.e also to release a lock heldby a table
alter system kill session 'sid,serial#';

REQUEST ID TAKING LONG TIME
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from FND_CONCURRENT_REQUESTS where REQUEST_ID=10779300;

REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
--------------------------------------------------------------------------------
10779300 900 15875 32829458
29985

SQL> select addr from v$process where spid=15875;

ADDR
----------------
C0000000513FEAF0



SQL> select sid,status,serial# from v$session where paddr='C0000000513FEAF0';

SID STATUS SERIAL#
---------- -------- ----------
178 ACTIVE 48878

SQL> select SOFAR,TOTALWORK from v$session_longops where sid=178;

SOFAR TOTALWORK
---------- ----------
16576 16576
1116 12668

SQL> /

SOFAR TOTALWORK
---------- ----------
16576 16576
1131 12668

SQL> select sql_hash_value from v$session_longops where sid=178;

SQL_HASH_VALUE
--------------
2468084395
2468084395


SQL> select sql_text from v$sql where hash_value='2468084395';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT CRRID.* FROM CNC_RS_RES_INT_DELTA CRRID WHERE CRRID.PROCESS_FLAG IN ('PRO
CESSED','UNPROCESSED','ERRORED') AND (CRRID.PROCESS_TYPE = 'DELETE' OR ( CRRID.P
ROCESS_TYPE = 'NEW' AND CRRID.END_DATE_ACTIVE IS NOT NULL) ) AND NOT EXISTS ( SE
LECT 1 FROM CNC_RS_RG_INT_DELTA CRRID1 WHERE CRRID1.SOURCE_ID = CRRID.SOURCE_ID
AND CRRID1.PROCESS_FLAG IN ('G','ERRORED','UNPROCESSED') ) ORDER BY CRRID.START_
DATE_ACTIVE


SQL> select last_analyzed from dba_tables where table_name='CNC_RS_RES_INT_DELTA';

LAST_ANAL
---------
22-Dec-05


This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.
The column named ""module"" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';