Concurrent request scripts
Overview of Concurrent Processing
Concurrent Requests, Programs, and Processes
Concurrent Managers start concurrent programs
Every time your users request a concurrent program to be run, their request is inserted into a database table, and is uniquely identified by a request ID. Concurrent managers read requests from this table.Part of a manager's definition is how many operating system processes it can devote to running requests. This number is referred to as the manager's number of target processes.
Running concurrent programs
A concurrent program actually starts running based on:- When it is scheduled to start
- Whether it is placed on hold,
- Whether it is incompatible (cannot run) with other programs
- Its request priority
Concurrent Request Priorities
The priority of a concurrent request is determined by application username, and is set by the System Administrator using the Concurrent:Priority user profile option.The first available concurrent manager compares the request's priority to other requests it is eligible to process, and runs the request with the highest priority.
When choosing between requests of equal priority, the concurrent manager runs the oldest request first.
Parent requests and Child requests
Often, several programs may be grouped together, as in a request set. Submitting the request set as a whole generates a request ID, and as each member of the set is submitted it receives its own request ID. The set's request ID identifies the Parent request, and each of the individual programs' request ID identifies a Child request.locks in concurrent jobs
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,id1, id2, lmode, request, type FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1,request;
concurrent programs running currently with Details of Processed time-- and Start Date
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND status_code='R' order by Process_time desc;
last run of a Concurrent Program along with Processed time
Useful to find the Details of Concurrent programs which run daily and comparison purpose
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,To_Char(a.request_date,'DD-MON-YY HH24:MI:SS'),To_Char(a.actual_start_date,'DD-MON-YY HH24:MI:SS'),
To_Char(a.actual_completion_date,'DD-MON-YY HH24:MI:SS'), (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
-- trunc(a.actual_completion_date) = '24-AUG-2005'
c.USER_CONCURRENT_PROGRAM_NAME='Incentive Compensation Analytics - ODI' -- and argument_text like '%, , , , ,%';
-- and status_code!='C'
For Checking the last run of concurrent Program.
- Use below query to check all the concurrent request running which may refer given package
-- This is very useful check before compiling any package on given instance.
-- The query can be modified as per requirement.
-- Remove FND_CONCURRENT_REQUESTS table and joins to check all program dependent on given package.
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
-- Query 5:The following query will display the time taken to execute the concurrent Programs
--for a particular user with the latest concurrent programs sorted in least time taken
-- to complete the request.
SELECT
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--Query 6: By using the below Query we can get sid,serial#,spid of the concurrent Request..
SELECT a.request_id, d.sid, d.serial# , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
By using below Concurrent Manager and Program rules...
Gives Detail of the Concurrent_queue_name and User_concurrent_program_name
SELECT b.concurrent_queue_name, c.user_concurrent_program_name
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b, fnd_concurrent_programs_vl c
WHERE a.queue_application_id = 283
and a.concurrent_queue_id = b.concurrent_queue_id
and a.type_id = c.concurrent_program_id
order by decode(INCLUDE_FLAG, 'I', 1, 2), type_code;
Gives Details of Running Concurrent jobs
SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
(a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
(a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id=b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by=d.user_id AND
status_code='R' order by Process_time desc;
Gives detail of Concurrent job completed and pending
SELECT
FCR.REQUEST_ID
,FCPV.USER_CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_NAME
,FCPV.CONCURRENT_PROGRAM_ID
,FCR.STATUS_CODE
,FCR.PHASE_CODE
FROM FND_CONCURRENT_PROGRAMS_VL FCPV
,FND_EXECUTABLES FE
,SYS.DBA_DEPENDENCIES DD
,FND_CONCURRENT_REQUESTS FCR
WHERE FCPV.EXECUTABLE_ID = FE.EXECUTABLE_ID
AND FE.EXECUTION_METHOD_CODE = 'I'
AND SUBSTR(FE.EXECUTION_FILE_NAME,1,INSTR(FE.EXECUTION_FILE_NAME, '.', 1, 1) - 1) = UPPER(DD.NAME)
AND DD.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'TRIGGER', 'PACKAGE') -- add as required
--AND referenced_owner = 'XXCUS'
AND DD.REFERENCED_NAME = UPPER('&Package_name')
AND FCR.CONCURRENT_PROGRAM_ID = FCPV.CONCURRENT_PROGRAM_ID
AND fcr.phase_code NOT IN ( 'C','P');
Gives Detail of Running and Completed Concurrent jobs with Start date and end date
Latest one at the top
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
f.actual_start_date start_on,
f.actual_completion_date end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
f.status_code
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
order by
f.actual_start_date desc;
--- Query 11 wait events details related with Concurrent programs
SELECT s.saddr, s.SID, s.serial#, s.audsid, s.paddr, s.user#, s.username,
s.command, s.ownerid, s.taddr, s.lockwait, s.status, s.server,
s.schema#, s.schemaname, s.osuser, s.process, s.machine, s.terminal,
UPPER (s.program) program, s.TYPE, s.sql_address, s.sql_hash_value,
s.sql_id, s.sql_child_number, s.sql_exec_start, s.sql_exec_id,
s.prev_sql_addr, s.prev_hash_value, s.prev_sql_id,
s.prev_child_number, s.prev_exec_start, s.prev_exec_id,
s.plsql_entry_object_id, s.plsql_entry_subprogram_id,
s.plsql_object_id, s.plsql_subprogram_id, s.module, s.module_hash,
s.action, s.action_hash, s.client_info, s.fixed_table_sequence,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#, s.logon_time, s.last_call_et, s.pdml_enabled,
s.failover_type, s.failover_method, s.failed_over,
s.resource_consumer_group, s.pdml_status, s.pddl_status, s.pq_status,
s.current_queue_duration, s.client_identifier,
s.blocking_session_status, s.blocking_instance, s.blocking_session,
s.seq#, s.event#, s.event, s.p1text, s.p1, s.p1raw, s.p2text, s.p2,
s.p2raw, s.p3text, s.p3, s.p3raw, s.wait_class_id, s.wait_class#,
s.wait_class, s.wait_time, s.seconds_in_wait, s.state,
s.wait_time_micro, s.time_remaining_micro,
s.time_since_last_wait_micro, s.service_name, s.sql_trace,
s.sql_trace_waits, s.sql_trace_binds, s.sql_trace_plan_stats,
s.session_edition_id, s.creator_addr, s.creator_serial#
FROM v$session s
WHERE ( (s.username IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND') AND STATUS='ACTIVE'
)
ORDER BY "PROGRAM";
-- Query 12 To find the pid of the Concurrent job and kill it.
Select a.inst_id, sid, b.spid
from gv$session a, gv$process b,apps.fnd_concurrent_requests c where a.paddr = b.addr and request_ID ='31689665'
AND a.inst_id = b.inst_id and c.os_process_id = a.process;
-- Query 13:To find the Database SID of the Concurrent job
-- We need our concurrent request ID as an input.
-- c.SPID= is the operating system process id
-- d.sid= is the Oracle process id
SQL> column process heading "FNDLIBR PID"
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';
-- Query 14:To find the currently running SQL after finding SID from Query 12
SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN
(SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID=11710);
-- Query 15:To find what exactly the program,module and the SQL it is executing
--P2 column should change that means Concurrent program is changing blocks and moving
select sid,serial#,p2,program,module from gv$session where sid=11710;
-- Query 16:To find any blocking is there in RAC or non-RAC Database
select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;
-- Query17:To check the SID and concurrent program details, where 5991=SID
select sid,serial#,program,module,p2 from gv$session where SID=5991;
-- Query 18: To check time remaining in minutes and elapsed in minutes for long running job
Select round(sofar*100/totalwork,2)"finished(%)",
Sid,
Serial#,
Opname,
Target,
Sofar,
Totalwork,
Units,
(Time_Remaining/60) Time_Rem_Mins,
(Elapsed_Seconds/60) Elapsed_Time_Mins
From gV$session_Longops
where TIME_REMAINING>0;
--Query 19:To get the location of the trace file for Concurrent request id,eg:The Request ID --(205979614)
select sid,serial#,p2,program,module from gv$session where sid=11710;
-- Query 16:To find any blocking is there in RAC or non-RAC Database
select process,sid,blocking_session,BLOCKING_INSTANCE from gv$session
where blocking_session is not null;
-- Query17:To check the SID and concurrent program details, where 5991=SID
select sid,serial#,program,module,p2 from gv$session where SID=5991;
-- Query 18: To check time remaining in minutes and elapsed in minutes for long running job
Select round(sofar*100/totalwork,2)"finished(%)",
Sid,
Serial#,
Opname,
Target,
Sofar,
Totalwork,
Units,
(Time_Remaining/60) Time_Rem_Mins,
(Elapsed_Seconds/60) Elapsed_Time_Mins
From gV$session_Longops
where TIME_REMAINING>0;
--Query 19:To get the location of the trace file for Concurrent request id,eg:The Request ID --(205979614)
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
--- and prog.application_id = execname.application_id
and prog.executable_application_id = execname.application_id
and prog.executable_id=execname.executable_id;
Note:We can use the above query to diagnose the Concurrent job failure by enabling trace on that job first and than running that concurrent job again.
--Query 20:To list the concurrent programs that run today,Giving details about status and logs
SELECT DISTINCT fcp.user_concurrent_program_name,
fcp.concurrent_program_name,
fcr.request_id,
fcr.request_date,
flv.meaning status,
fcr.status_code,
fcr.completion_text,
fcr.logfile_name,
fcr.outfile_name,
fcr.argument_text
FROM apps.fnd_concurrent_programs_vl fcp,
apps.fnd_concurrent_requests fcr,
apps.fnd_lookup_values flv
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND trunc(fcr.last_update_date) = trunc(SYSDATE)
AND flv.lookup_code = fcr.status_code
AND flv.lookup_type = 'CP_STATUS_CODE'
AND flv.language = 'US'
ORDER BY fcr.request_date,
fcr.request_id DESC;
************************************************************
How to find out blocking locks & sessions.
There are many ways to find out blocking session in Oracle or blocking locks in Oracle.You can use following SQL query to find out Blocking locks, User ID, Concurrent Request Name and Object name in oracle applications environment.
SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
You can also schedule using a crontab entry. Use following shell script and schedule it using crontab. This sheel script will create a html file and mail it to the users.
#------------------Start of script----------------------- #
#!/bin/sh
# Check Blocking Locks
#
. /u01/oracle/ORCL/orcldb/10.2.0/ORCL1_orcl-1.env
echo "Content-Type: text/html" >> Blocking_Session.html
sqlplus -s apps/apps EOF
SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv\$session s,
gv\$lock m,
gv\$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv\$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
spool off
set markup html off spool off
EOF
cat blocking_session.html >> Blocking_Session.html
(echo "Importance: High"; echo "Subject: ORCL: Blocking Session Locks"; cat Blocking_Session.html)
| /usr/sbin/sendmail -F Oracle abc@test.com
rm Blocking_Session.html
rm blocking_session.html
#--------------------End of script----------------------- #
Use following SQL to get more detail data.
SELECT s.inst_id, NVL (s.username, 'Internal') database_user, s.SID,
s.event, s.p1, s.serial#, p.spid, m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) lock_type,
m.TYPE
|| ' - '
|| DECODE (m.TYPE,
'BL', 'Buffer hash table instance lock',
'CF', ' Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CS', 'Control file schema global enqueue lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'Direct loader parallel index create',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FI', 'SGA open-file information lock',
'FS', 'File set lock',
'HW', 'Space management on a specific segment lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'MB', 'Master buffer hash table instance lock',
'MM', 'Mount definition gloabal enqueue lock',
'MR', 'Media recovery lock',
'PF', 'Password file lock',
'PI', 'Parallel operation lock',
'PR', 'Process startup lock',
'PS', 'Parallel operation lock',
'RE', 'USE_ROW_ENQUEUE enforcement lock',
'RT', 'Redo thread global enqueue lock',
'RW', 'Row wait enqueue lock',
'SC', 'System commit number instance lock',
'SH', 'System commit high water mark enqueue lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment lock',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TD', 'DDL enqueue lock',
'TE', 'Extend-segment enqueue lock',
'TM', 'DML enqueue lock',
'TO', 'Temporary Table Object Enqueue',
'TT', 'Temporary table enqueue lock',
'TX', 'Transaction enqueue lock',
'UL', 'User supplied lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
'WS', 'Write-atomic-log-switch global enqueue lock',
'TS', DECODE (m.id2,
0, 'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'
),
'LA', 'Library cache lock instance lock (A=namespace)',
'LB', 'Library cache lock instance lock (B=namespace)',
'LC', 'Library cache lock instance lock (C=namespace)',
'LD', 'Library cache lock instance lock (D=namespace)',
'LE', 'Library cache lock instance lock (E=namespace)',
'LF', 'Library cache lock instance lock (F=namespace)',
'LG', 'Library cache lock instance lock (G=namespace)',
'LH', 'Library cache lock instance lock (H=namespace)',
'LI', 'Library cache lock instance lock (I=namespace)',
'LJ', 'Library cache lock instance lock (J=namespace)',
'LK', 'Library cache lock instance lock (K=namespace)',
'LL', 'Library cache lock instance lock (L=namespace)',
'LM', 'Library cache lock instance lock (M=namespace)',
'LN', 'Library cache lock instance lock (N=namespace)',
'LO', 'Library cache lock instance lock (O=namespace)',
'LP', 'Library cache lock instance lock (P=namespace)',
'LS', 'Log start/log switch enqueue lock',
'PA', 'Library cache pin instance lock (A=namespace)',
'PB', 'Library cache pin instance lock (B=namespace)',
'PC', 'Library cache pin instance lock (C=namespace)',
'PD', 'Library cache pin instance lock (D=namespace)',
'PE', 'Library cache pin instance lock (E=namespace)',
'PF', 'Library cache pin instance lock (F=namespace)',
'PG', 'Library cache pin instance lock (G=namespace)',
'PH', 'Library cache pin instance lock (H=namespace)',
'PI', 'Library cache pin instance lock (I=namespace)',
'PJ', 'Library cache pin instance lock (J=namespace)',
'PL', 'Library cache pin instance lock (K=namespace)',
'PK', 'Library cache pin instance lock (L=namespace)',
'PM', 'Library cache pin instance lock (M=namespace)',
'PN', 'Library cache pin instance lock (N=namespace)',
'PO', 'Library cache pin instance lock (O=namespace)',
'PP', 'Library cache pin instance lock (P=namespace)',
'PQ', 'Library cache pin instance lock (Q=namespace)',
'PR', 'Library cache pin instance lock (R=namespace)',
'PS', 'Library cache pin instance lock (S=namespace)',
'PT', 'Library cache pin instance lock (T=namespace)',
'PU', 'Library cache pin instance lock (U=namespace)',
'PV', 'Library cache pin instance lock (V=namespace)',
'PW', 'Library cache pin instance lock (W=namespace)',
'PX', 'Library cache pin instance lock (X=namespace)',
'PY', 'Library cache pin instance lock (Y=namespace)',
'PZ', 'Library cache pin instance lock (Z=namespace)',
'QA', 'Row cache instance lock (A=cache)',
'QB', 'Row cache instance lock (B=cache)',
'QC', 'Row cache instance lock (C=cache)',
'QD', 'Row cache instance lock (D=cache)',
'QE', 'Row cache instance lock (E=cache)',
'QF', 'Row cache instance lock (F=cache)',
'QG', 'Row cache instance lock (G=cache)',
'QH', 'Row cache instance lock (H=cache)',
'QI', 'Row cache instance lock (I=cache)',
'QJ', 'Row cache instance lock (J=cache)',
'QL', 'Row cache instance lock (K=cache)',
'QK', 'Row cache instance lock (L=cache)',
'QM', 'Row cache instance lock (M=cache)',
'QN', 'Row cache instance lock (N=cache)',
'QO', 'Row cache instance lock (O=cache)',
'QP', 'Row cache instance lock (P=cache)',
'QQ', 'Row cache instance lock (Q=cache)',
'QR', 'Row cache instance lock (R=cache)',
'QS', 'Row cache instance lock (S=cache)',
'QT', 'Row cache instance lock (T=cache)',
'QU', 'Row cache instance lock (U=cache)',
'QV', 'Row cache instance lock (V=cache)',
'QW', 'Row cache instance lock (W=cache)',
'QX', 'Row cache instance lock (X=cache)',
'QY', 'Row cache instance lock (Y=cache)',
'QZ', 'Row cache instance lock (Z=cache)',
'????'
) lock_type_detail,
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) lock_request,
DECODE (command,
0, 'BACKGROUND',
1, 'Create Table',
2, 'INSERT',
3, 'SELECT',
4, 'CREATE CLUSTER',
5, 'ALTER CLUSTER',
6, 'UPDATE',
7, 'DELETE',
8, 'DROP',
9, 'CREATE INDEX',
10, 'DROP INDEX',
11, 'ALTER INDEX',
12, 'DROP TABLE',
13, 'CREATE SEQUENCE',
14, 'ALTER SEQUENCE',
15, 'ALTER TABLE',
16, 'DROP SEQUENCE',
17, 'GRANT',
18, 'REVOKE',
19, 'CREATE SYNONYM',
20, 'DROP SYNONYM',
21, 'CREATE VIEW',
22, 'DROP VIEW',
23, 'VALIDATE INDEX',
24, 'CREATE PROCEDURE',
25, 'ALTER PROCEDURE',
26, 'LOCK TABLE',
27, 'NO OPERATION',
28, 'RENAME',
29, 'COMMENT',
30, 'AUDIT',
31, 'NOAUDIT',
32, 'CREATE EXTERNAL DATABASE',
33, 'DROP EXTERNAL DATABASE',
34, 'CREATE DATABASE',
35, 'ALTER DATABASE',
36, 'CREATE ROLLBACK SEGMENT',
37, 'ALTER ROLLBACK SEGMENT',
38, 'DROP ROLLBACK SEGMENT',
39, 'CREATE TABLESPACE',
40, 'ALTER TABLESPACE',
41, 'DROP TABLESPACE',
42, 'ALTER SESSION',
43, 'ALTER USER',
44, 'COMMIT',
45, 'ROLLBACK',
46, 'SAVEPOINT',
47, 'PL/SQL EXECUTE',
48, 'SET TRANSACTION',
49, 'ALTER SYSTEM SWITCH LOG',
50, 'EXPLAIN',
51, 'CREATE USER',
52, 'CREATE ROLE',
53, 'DROP USER',
54, 'DROP ROLE',
55, 'SET ROLE',
56, 'CREATE SCHEMA',
57, 'CREATE CONTROL FILE',
58, 'ALTER TRACING',
59, 'CREATE TRIGGER',
60, 'ALTER TRIGGER',
61, 'DROP TRIGGER',
62, 'ANALYZE TABLE',
63, 'ANALYZE INDEX',
64, 'ANALYZE CLUSTER',
65, 'CREATE PROFILE',
66, 'DROP PROFILE',
67, 'ALTER PROFILE',
68, 'DROP PROCEDURE',
69, 'DROP PROCEDURE',
70, 'ALTER RESOURCE COST',
71, 'CREATE SNAPSHOT LOG',
72, 'ALTER SNAPSHOT LOG',
73, 'DROP SNAPSHOT LOG',
74, 'CREATE SNAPSHOT',
75, 'ALTER SNAPSHOT',
76, 'DROP SNAPSHOT',
79, 'ALTER ROLE',
85, 'TRUNCATE TABLE',
86, 'TRUNCATE CLUSTER',
87, '-',
88, 'ALTER VIEW',
89, '-',
90, '-',
91, 'CREATE FUNCTION',
92, 'ALTER FUNCTION',
93, 'DROP FUNCTION',
94, 'CREATE PACKAGE',
95, 'ALTER PACKAGE',
96, 'DROP PACKAGE',
97, 'CREATE PACKAGE BODY',
98, 'ALTER PACKAGE BODY',
99, 'DROP PACKAGE BODY',
command || ' - ???'
) command,
(CASE
WHEN m.TYPE = 'UL'
THEN 'None '
ELSE DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username
|| '.'
|| SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
)
END
) OBJECT,
s.machine, s.process, m.ctime, s.program, SQL.sql_text, s.sql_id,
(SELECT 'select * from '
|| owner
|| '.'
|| object_name
|| ' where rowid=dbms_rowid.rowid_create( 1, '
|| row_wait_obj#
|| ','
|| row_wait_file#
|| ','
|| row_wait_block#
|| ','
|| row_wait_row#
|| ' );'
FROM dba_objects db
WHERE db.object_id = s.row_wait_obj# AND db.object_type = 'TABLE') row_wait
FROM gv$session s,
gv$lock m,
gv$process p,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND m.inst_id = s.inst_id
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
***********************************************************************
If any Concurrent request is slow or takes more than normal time to complete,follow the below steps first to verify ..
HighLevel Steps..
SQL >select * from v$session where process in(select os_process_id from fnd_concurrent_requests where request_id='
Get SID from above sql.
Take that sid and find object_id's from below command
SQL >select * from v$locked_objects where session_id='SID'
Now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
SQL >select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource
SQL >alter system kill session 'SID,SERIAL#';
########################################################################
Source: copied from http://sujeetdba.blogspot.in/
Thanks for sharing useful scripts.
ReplyDeleteI appreciate your article. You truly share relevant and extraordinary knowledge. Thank you for keep sharing these valuable thoughts.
ReplyDelete500GB DISK DRIVE
LINE INTERFACE PRNCD
DEXCS UNIT MANAGER