Query to find the request group of a concurrent program
select distinct request_group_name from apps.FND_REQUEST_GROUPS FRG,apps.FND_REQUEST_GROUP_UNITS FRGU,apps.FND_CONCURRENT_PROGRAMS_VL FCP
where FCP.USER_CONCURRENT_PROGRAM_NAME like '&Conc_Program_Name' and FCP.CONCURRENT_PROGRAM_ID=FRGU.REQUEST_UNIT_ID
and FRGU.REQUEST_GROUP_ID=FRG.REQUEST_GROUP_ID
Query to check the middle tier sessions
select machine,
sum(decode(s.status,'ACTIVE',1,0)) totact,
sum(decode(s.status,'INACTIVE',1,0)) totinact,
count(*) totsess,
min(to_char(s.logon_time,'mm/dd hh24:mi')) earliest_logon,
max(to_char(s.logon_time,'mm/dd hh24:mi')) latest_logon
from v$session s
where s.type = 'USER'
group by s.machine
order by 4,1
/
FND_NODES query
select
node_id,
platform_code,
support_db D,
support_cp C,
support_admin A,
support_forms F,
support_web W,
node_name,
server_id,
server_address,
domain,
webhost,
virtual_ip
from
fnd_nodes
order by node_id;
Query to know what changes a patch has made on the instance
Select
J.PATCh_NAME,
H.APPLICATIONS_SYSTEM_NAME Instance_Name,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME appl_top,
D.SUBDIR,
D.FILENAME,
max(F.VERSION) latest,
E.ACTION_CODE action
from
AD_BUGS A,
AD_PATCH_RUN_BUGS B,
AD_PATCH_RUN_BUG_ACTIONS C,
AD_FILES D,
AD_PATCH_COMMON_ACTIONS E,
AD_FILE_VERSIONS F,
AD_PATCH_RUNS G,
AD_APPL_TOPS H,
AD_PATCH_DRIVERS I,
AD_APPLIED_PATCHES J
where
A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and
C.FILE_ID = D.FILE_ID and
E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and
D.FILE_ID = F.FILE_ID and
G.APPL_TOP_ID = H.APPL_TOP_ID and
G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and
B.PATCH_RUN_ID = G.PATCH_RUN_ID and
C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&Patch_Number'))
GROUP BY
J.PATCH_NAME,
H.APPLICATIONS_SYSTEM_NAME,
H.NAME,
I.DRIVER_FILE_NAME,
D.APP_SHORT_NAME,
D.SUBDIR,
D.FILENAME,
E.ACTION_CODE;
Query to know patch details in the instance.
select
D.PATCH_NAME,
B.APPLICATIONS_SYSTEM_NAME,
-- B.INSTANCE_NAME,
B.NAME,
C.DRIVER_FILE_NAME,
A.PATCH_DRIVER_ID,
A.PATCH_RUN_ID,
A.SESSION_ID,
A.PATCH_TOP,
A.START_DATE,
A.END_DATE,
A.SUCCESS_FLAG,
A.FAILURE_COMMENTS
from
AD_PATCH_RUNS A,
AD_APPL_TOPS B,
AD_PATCH_DRIVERS C,
AD_APPLIED_PATCHES D
where
A.APPL_TOP_ID = B.APPL_TOP_ID AND
A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID AND
C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID AND
A.PATCH_DRIVER_ID in
(select PATCH_DRIVER_ID
from AD_PATCH_DRIVERS
where APPLIED_PATCH_ID
in
(select APPLIED_PATCH_ID
from AD_APPLIED_PATCHES
where PATCH_NAME = '&patch_number'))
ORDER BY 3;
Query to show the applications users currently connected based on the sid.
select to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
u.user_name,
u.description ,
s.sid,
p.spid,
u.description,
a.user_form_name
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and
((upper(a.user_form_name) like upper('%&trgtform%')) or (upper('&trgtform') = 'ALL'))
and l.start_time = (select max
(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by
u.user_name, u.description,a.time,a.user_form_name, s.sid,p.spid
order by to_char(max(l.start_time),'mm/dd/yy
hh:mi:ssAM'),a.time;
Query to see the top10 jobs based on total runtime for today
select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name
' (' to_char
(c.concurrent_program_id) ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
from APPLSYS.fnd_Concurrent_requests
a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id =
c.concurrent_program_id
and a.program_application_id = c.application_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and
ctl.language = 'US'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and
actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name ' (' to_char
(c.concurrent_program_id) ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11; Query to get the application user name based on the SID. select /*+ rule */ to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat, a.time, floor (s.last_call_et/3600)':' floor(mod(s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60) "LastCallET", u.user_name, u.description , s.module ' - ' a.user_form_name forminfo from applsys.fnd_logins l, applsys.fnd_user u, apps.fnd_signon_audit_view a, v$process p, v$session s where s.sid = &trgtsid and s.paddr = p.addr and p.pid = l.pid and l.end_time is null and l.spid = s.process and l.start_time is not null -- and l.start_time = u.last_logon_date -- and l.session_number = u.session_number and l.user_id = u.user_id and u.user_id = a.user_id and p.pid = a.pid and l.start_time = (select max(l2.start_time) from applsys.fnd_logins l2 where l2.pid = l.pid) group by to_char (s.logon_time,'mm/dd/yy hh:mi:ssAM'), floor(s.last_call_et/3600)':' floor(mod (s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60), u.user_name, u.description,a.time,s.module ' - ' a.user_form_name order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time; Query to get the server tiers active sessions select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid, to_char(s.logon_time,'mm/dd hh24:mi') logonat, floor(last_call_et/3600)':' floor(mod(last_call_et,3600)/60)':' mod(mod(last_call_et,3600),60) "LastCallET", s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid, decode(h.sid,null,'None',h.name) holdinglatch from v$session s, v$session_wait w, v$process p, v$latchholder h where type = 'USER' and status = 'ACTIVE' and s.sid = w.sid and s.paddr = p.addr and s.osuser is not null and s.sid = h.sid (+) order by 1,4
Script to generate an Automatic workload repository report.
SQL> @E:\R12\11.1.0\RDBMS\ADMIN\awrrpt.sql
Current Instance DB Id DBName Instance
----------- ----------------
402873117 KLONE klone
Enter value for report_type: <---here you can enter 'html' or 'text' based on the output type you want.
Enter value for num_days:<---here enter the number of days will result in the most recent days of snapshots being listed.
Example if we enter 2 here will show two days of snapshots that are automatically stored in the database.
Enter value for begin_snap:<---here enter the begin snapshot number
Enter value for end_snap:<---here enter the end snapshot number
Enter value for report_name:<---awrrpt_1_2579_2580.html
This will generate the html report output for the performance analysis.
How to identify and remove locks on the table
Login to database as SYS or SYSTEM user.
Find out the SESSION_ID by running the following
SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = Table Name;
Use this session id to find SERIAL# by using following
SELECT statment SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS WHERE NAME = Table Name)
Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
Query to check database growth in previous months
select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365
group by creation_time
Query to see the top 10 io datafiles for write activity
select * from (
select substr(f.name,8,(instr(f.name,'/',9,1) -8)) fsmtpt,
substr(f.name,(instr(f.name,'/',-1,1)+1)) fname,
s.phyrds, s.phywrts,s.lstiotim,s.avgiotim
from v$datafile f, v$filestat s
where f.file# = s.file#
order by 4 desc)
where rownum < 11;
Don't hesitate to contact me if you need any technical assistance for Oracle Applications DBA related work. samiora@gmail.com.
