TO KILL FRMWEB PROCESS ON APP NODES
++++++++++++++++++++++++++++++++++++
select s.inst_id,s.sid,s.serial#,s.status,to_char(logon_time,'mm/dd hh:miAM') logged_on,client_identifier,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine,s.module,s.sql_id,s.osuser
from gv$session s, gv$process p
where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.process = '&trgtproc'
order by 5
/
Need to pass trgt pid <-- If there is no DB session we can kill it
CONCURRENT REQ ID FROM SID
+++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$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 =&req_id
AND a.phase_code = 'R';
SID FROM CONCURRENT REQ ID
+++++++++++++++++++++++++++++++++++++++++++++++++++
select b.sid, oracle_session_id, oracle_process_id, os_process_id
from fnd_concurrent_requests a ,
v$session b
where a.request_id=&request_id and
a.ORACLE_SESSION_ID = b.AUDSID
IDENTIFY DATABASE SID BASED ON OS PROCESS ID
+++++++++++++++++++++++++++++++++++++++++++++
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
SESSION DETAILS FROM PID
+++++++++++++++++++++++++++++
set head on
set lin 700
col module format a20
SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);
GET SQL_TEXT WITH SQL_ID
++++++++++++++++++++++++++++++++++
select sql_text from v$sqlarea where sql_id='&SQL_ID';
GET SID RUNNING on which instnace
+++++++++++++++++++++++++++++++++++
select sid,serial#,INST_ID from gv$instance where sid='&SID';
select sid,serial#,username,schemaname,action,program,status,process,inst_id from gv$session where sid=<sid> and serial#=<serial#>;
TO GET SID & SQL_TEXT
+++++++++++++++++++++++++++++++++++
select s.sid, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address
and s.sid = &sid;
TO GET SID,SERIAL#,SQL_ID & SQL_TEXT
+++++++++++++++++++++++++++++++++++
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value and sid='&SID';
select status from v$session where process in(select os_process_id from fnd_concurrent_requests
where request_id in(14639333));
Sql text:
---------
col OSUSER for a8
col PROGRAM for a25
col PROCESS for a10
col MACHINE for a16
col LOGGEDON for a16
select sid,username,STATUS,OSUSER,SERIAL#,INST_ID,process,machine,program,SQL_HASH_VALUE,PREV_HASH_VALUE,to_char(logon_time,'mm/dd/yyyy hh:mi:ssAM') loggedon
from gv$session where sid='&sid';
select sql_text from gv$sql where HASH_VALUE=&HASH_VALUE;
set long 9999
select prev_SQL_ID,SID from gv$session where SID='&SID'
TO GET SPID FROM PID
+++++++++++++++++++++++++++++++++++
select PID,SPID,EXECUTION_TYPE,USERNAME,SERIAL#,PROGRAM from gv$process where pid='&PID';
TP CHECK STATS GATHERING
+++++++++++++++++++++++++++
select to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where table_name='&TABLE_NAME' and OWNER='&OWNER';
TO GET SID WITH SPID
+++++++++++++++++++++++++++++++++++
select s.inst_id,p.spid,s.sid, s.serial#,s.username, s.osuser, s.module, s.program, s.sql_id from gv$session s, gv$process p where s.paddr= p.addr
and p.spid='&spid' order by p.spid;
TO CHECK STATS GATHERING
+++++++++++++++++++++++++++
set lines 300
col TABLE_NAME for a35;
select to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where table_name='&TABLE_NAME' and OWNER='&OWNER_NAME';
11:25:17 SQL> execute dbms_stats.gather_schema_stats('applsys',cascade=>true,degree=>16);
PL/SQL procedure successfully completed.
Elapsed: 00:16:19.28
11:41:48 SQL>
TABLE LEVEL
++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(tabname=>'T_SRC_ITEM_TMPL',ownname=>'XXDEM',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,degree =>16);
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where table_name='&TABLE_NAME' and stale_stats='YES';
++++++++++++++++++++++++++++++
STATS FOR ENTIRE SCHEMA TABLES
++++++++++++++++++++++++++++++
col owner for a15
col "Table Name" for a38;
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT t.owner,
t.table_name AS "Table Name",
t.num_rows AS "Rows",
t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB",
to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed"
FROM dba_tables t,
v$parameter p
WHERE t.owner = Decode(Upper('&&Table_Owner'), 'ALL', t.owner, Upper('&&Table_Owner'))
AND p.name = 'db_block_size'
ORDER by t.owner,t.last_analyzed,t.table_name
/
+++++++++++++++++++++++++++
TO FIND CHILD REQUETS
+++++++++++++++++++++++++++++++++++
To find child requests
set lines 300
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
TO FIND LONG RUNNING REQUESTS
++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++
select s.inst_id,s.sid,s.serial#,s.status,to_char(logon_time,'mm/dd hh:miAM') logged_on,client_identifier,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine,s.module,s.sql_id,s.osuser
from gv$session s, gv$process p
where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.process = '&trgtproc'
order by 5
/
Need to pass trgt pid <-- If there is no DB session we can kill it
CONCURRENT REQ ID FROM SID
+++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$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 =&req_id
AND a.phase_code = 'R';
SID FROM CONCURRENT REQ ID
+++++++++++++++++++++++++++++++++++++++++++++++++++
select b.sid, oracle_session_id, oracle_process_id, os_process_id
from fnd_concurrent_requests a ,
v$session b
where a.request_id=&request_id and
a.ORACLE_SESSION_ID = b.AUDSID
IDENTIFY DATABASE SID BASED ON OS PROCESS ID
+++++++++++++++++++++++++++++++++++++++++++++
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
SESSION DETAILS FROM PID
+++++++++++++++++++++++++++++
set head on
set lin 700
col module format a20
SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);
GET SQL_TEXT WITH SQL_ID
++++++++++++++++++++++++++++++++++
select sql_text from v$sqlarea where sql_id='&SQL_ID';
GET SID RUNNING on which instnace
+++++++++++++++++++++++++++++++++++
select sid,serial#,INST_ID from gv$instance where sid='&SID';
select sid,serial#,username,schemaname,action,program,status,process,inst_id from gv$session where sid=<sid> and serial#=<serial#>;
TO GET SID & SQL_TEXT
+++++++++++++++++++++++++++++++++++
select s.sid, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address
and s.sid = &sid;
TO GET SID,SERIAL#,SQL_ID & SQL_TEXT
+++++++++++++++++++++++++++++++++++
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value and sid='&SID';
select status from v$session where process in(select os_process_id from fnd_concurrent_requests
where request_id in(14639333));
Sql text:
---------
col OSUSER for a8
col PROGRAM for a25
col PROCESS for a10
col MACHINE for a16
col LOGGEDON for a16
select sid,username,STATUS,OSUSER,SERIAL#,INST_ID,process,machine,program,SQL_HASH_VALUE,PREV_HASH_VALUE,to_char(logon_time,'mm/dd/yyyy hh:mi:ssAM') loggedon
from gv$session where sid='&sid';
select sql_text from gv$sql where HASH_VALUE=&HASH_VALUE;
set long 9999
select prev_SQL_ID,SID from gv$session where SID='&SID'
TO GET SPID FROM PID
+++++++++++++++++++++++++++++++++++
select PID,SPID,EXECUTION_TYPE,USERNAME,SERIAL#,PROGRAM from gv$process where pid='&PID';
TP CHECK STATS GATHERING
+++++++++++++++++++++++++++
select to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where table_name='&TABLE_NAME' and OWNER='&OWNER';
TO GET SID WITH SPID
+++++++++++++++++++++++++++++++++++
select s.inst_id,p.spid,s.sid, s.serial#,s.username, s.osuser, s.module, s.program, s.sql_id from gv$session s, gv$process p where s.paddr= p.addr
and p.spid='&spid' order by p.spid;
TO CHECK STATS GATHERING
+++++++++++++++++++++++++++
set lines 300
col TABLE_NAME for a35;
select to_char(last_analyzed, 'DD-MON-YY HH24:MI:SS') from dba_tables where table_name='&TABLE_NAME' and OWNER='&OWNER_NAME';
11:25:17 SQL> execute dbms_stats.gather_schema_stats('applsys',cascade=>true,degree=>16);
PL/SQL procedure successfully completed.
Elapsed: 00:16:19.28
11:41:48 SQL>
TABLE LEVEL
++++++++++++++++++++++++++++++++
exec dbms_stats.gather_table_stats(tabname=>'T_SRC_ITEM_TMPL',ownname=>'XXDEM',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,degree =>16);
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where table_name='&TABLE_NAME' and stale_stats='YES';
++++++++++++++++++++++++++++++
STATS FOR ENTIRE SCHEMA TABLES
++++++++++++++++++++++++++++++
col owner for a15
col "Table Name" for a38;
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SELECT t.owner,
t.table_name AS "Table Name",
t.num_rows AS "Rows",
t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB",
to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed"
FROM dba_tables t,
v$parameter p
WHERE t.owner = Decode(Upper('&&Table_Owner'), 'ALL', t.owner, Upper('&&Table_Owner'))
AND p.name = 'db_block_size'
ORDER by t.owner,t.last_analyzed,t.table_name
/
+++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++
To find child requests
set lines 300
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';
TO FIND LONG RUNNING REQUESTS
++++++++++++++++++++++++++++++++++
col pid format a15
select fcr.request_id req_id,
fcr.phase_code||'/'||fcr.status_code sta,
fcr.pid pid,
sess.sid sid,
sess.inst_id inst_id,
fcr.running_time time,
nvl(t.used_urec,0) u_rec,
substrb(fcr.user_desc,1,8) user_desc,
substrb(decode(fcr.pgm_code,
'FNDRSSUB',fcr.pgm_name||'-'||rs.user_request_set_name,
'FNDRSSTG',fcr.pgm_name||'-'||rs.user_request_set_name
||'-'||rss.user_stage_name,
fcr.pgm_name), 1,48) pgm_name
from gv$session sess,
gv$transaction t,
fnd_request_sets_vl rs,
fnd_request_set_stages_vl rss,
(select /*+ ordered
index (r fnd_concurrent_requests_n7)
index (pt fnd_concurrent_programs_tl_u1) */
r.request_id request_id,
r.program_application_id application_id,
c.os_process_id pid,
r.oracle_session_id audsid,
r.concurrent_program_id concurrent_program_id,
p.concurrent_program_name pgm_code,
u.description user_desc,
decode(p.concurrent_program_name,
'FNDRSSUB','SET',
'FNDRSSTG','Set Stage',
pt.user_concurrent_program_name) pgm_name,
r.phase_code phase_code,
r.status_code status_code,
to_char(r.request_date,'yymmdd hh24:mi:ss') request_date,
to_char(r.actual_completion_date,'yymmdd hh24:mi:ss') actual_completion_date,
to_char(r.requested_start_date,'yymmdd hh24:mi:ss')
requested_start_date,
ceil((nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440)
running_time,
r.actual_start_date actual_start_date_org,
u.user_id user_id,
u.user_name user_name,
r.argument_text arguments,
decode(p.concurrent_program_name,
'FNDRSSUB',r.argument2,
'FNDRSSTG',r.argument2,
-1) request_set_id,
decode(p.concurrent_program_name,
'FNDRSSTG',r.argument3,
-1) request_set_stage_id
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p,
fnd_user u,
fnd_concurrent_processes c
where r.requested_by = u.user_id
and r.program_application_id = pt.application_id
and r.concurrent_program_id = pt.concurrent_program_id
and pt.language = 'US'
and pt.application_id = p.application_id
and pt.concurrent_program_id = p.concurrent_program_id
and r.controlling_manager = c.concurrent_process_id
and r.phase_code = 'R'
and r.status_code = 'R') fcr
where fcr.audsid = sess.audsid(+)
and sess.saddr = t.ses_addr(+)
and fcr.request_set_id = rs.request_set_id(+)
and fcr.request_set_id = rss.request_set_id(+)
and fcr.request_set_stage_id = rss.request_set_stage_id(+)
order by fcr.actual_start_date_org desc, fcr.running_time,
fcr.request_id desc;
TO FIND REQUEST PREVIOUS RUNS AND HISTORY
++++++++++++++++++++++++++++++++++++++++++++++
SELECT to_char(a.request_id) ||'~'||
decode(to_char(parent_request_id),'-1',null,to_char(parent_request_id)) ||'~'||
a.user_concurrent_program_name ||'~'||
to_char(a.requested_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||
to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||
to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS') ||'~'||
round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2) ||'~'||
a.requestor ||'~'||
decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code) ||'~'||
decode(a.status_code,'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W', 'Paused', a.status_code) ||'~'||
a.argument_text
FROM apps.fnd_conc_req_summary_v a
WHERE a.user_concurrent_program_name like ('&1%')
order by a.user_concurrent_program_name,a.actual_start_date,a.phase_code;
decode(to_char(parent_request_id),'-1',null,to_char(parent_request_id)) ||'~'||
a.user_concurrent_program_name ||'~'||
to_char(a.requested_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||
to_char(a.actual_start_date,'DD-MON-RR HH24:MI:SS') ||'~'||
to_char(a.actual_completion_date,'DD-MON-RR HH24:MI:SS') ||'~'||
round(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)+(((actual_completion_date-actual_start_date)*24*60*60)-(trunc(((actual_completion_date-actual_start_date)*24*60*60)/60)*60))/100,2) ||'~'||
a.requestor ||'~'||
decode(a.phase_code,'R','Running','P','Inactive','C','Completed', a.phase_code) ||'~'||
decode(a.status_code,'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W', 'Paused', a.status_code) ||'~'||
a.argument_text
FROM apps.fnd_conc_req_summary_v a
WHERE a.user_concurrent_program_name like ('&1%')
order by a.user_concurrent_program_name,a.actual_start_date,a.phase_code;
TO FIND CURRENT RUNNING REQUESTS
++++++++++++++++++++++++++++++++++++++++++++
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;
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;
No comments:
Post a Comment