Oracle Apps DBA Blog - Important Apps DBA performance sql queries

Date Added: 03 Mar.2019 Date Updated: 15 Nov.2019 Oracle Apps DBA Full Blog

Important Apps DBA sql queries

How to Put Concurrent Requests in Hold Status?

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity. This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.

1) create a table apps.conc_req_on_hold as select 1 from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';

2) select count(*) from apps.conc_req_on_hold

3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

6)Commit the changes

commit;

Inactive form sessions more than 1 hour:

select p.spid, s.sid,s.CLIENT_IDENTIFIER, s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL" from gv$session s, gv$sqlarea t,gv$process p where s.sql_address =t.address and s.sql_hash_value =t.hash_value and p.addr=s.paddr and s.status='INACTIVE' And s.module like '%frm%' and S.LAST_CALL_ET > 3600*1 order by last_call_et;

The below SQL is currently "ACTIVE" session:-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE' and s.username <> 'SYSTEM' order by s.sid,t.piece;

This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:

select

object_name,

object_type,

session_id,

type, -- Type or system/user lock

lmode, -- lock mode in which session holds lock

request,

block,

ctime -- Time since current mode was granted

from

v$locked_object, all_objects, v$lock

where

v$locked_object.object_id = all_objects.object_id AND

v$lock.id1 = all_objects.object_id AND

v$lock.sid = v$locked_object.session_id

order by

session_id, ctime desc, object_name;

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99

SELECT sid, to_char(start_time,'hh24:mi:ss') stime,

message,( sofar/totalwork)* 100 percent

FROM v$session_longops

WHERE sofar/totalwork < 1;

Oracle Apps DBA - Important Apps DBA performance sql queries