[applprod@lupin-db-apps-server ~]$ top
top - 14:58:52 up 2165 days, 6:23, 8 users, load average: 3.17, 3.21, 3.19
Tasks: 532 total, 3 running, 529 sleeping, 0 stopped, 0 zombie
Cpu(s): 75.2%us, 0.2%sy, 0.0%ni, 24.5%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 15512804k total, 15071228k used, 441576k free, 67764k buffers
Swap: 20971440k total, 41348k used, 20930092k free, 6643980k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
10732 applprod 15 0 2795m 2.6g 8768 S 100.0 17.6 341606:01 eggcups
18795 applprod 25 0 67516 45m 9884 R 100.0 0.3 31236:42 f60webmx
8740 applprod 25 0 61440 38m 9004 R 99.6 0.3 29591:56 f60webmx
ACCU (PROD)> select * from v$session where process = 8740 and status= 'INACTIVE' and ACTION like '%FRM%';
no rows selected
ACCU (PROD)> select * from v$session where process = 18795 and status= 'INACTIVE' and ACTION like '%FRM%';
SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME
-------- ---------- ---------- ---------- -------- ---------- ------------------------------ ---------- ---------- -------- -------- -------- --------- ---------- ------------------------------
OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE
------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ----------
SQL_ADDR SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER PREV_SQL PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID
-------- -------------- ------------- ---------------- -------- --------------- ------------- ----------------- --------------------- ------------------------- --------------- -------------------
MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE
------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- --------------------
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
------------- -------------- --------------- ------------- --------- ------------ --- ------------- ---------- --- -------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION SEQ# EVENT# EVENT
---------------------------------------------------------------- ----------- ----------------- ---------------- ---------- ---------- ----------------------------------------------------------------
P1TEXT P1 P1RAW P2TEXT P2 P2RAW
---------------------------------------------------------------- ---------- -------- ---------------------------------------------------------------- ---------- --------
P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME
---------------------------------------------------------------- ---------- -------- ------------- ----------- ---------------------------------------------------------------- ----------
SECONDS_IN_WAIT STATE SERVICE_NAME SQL_TRAC SQL_T SQL_T
--------------- ------------------- ---------------------------------------------------------------- -------- ----- -----
9D420870 911 622 28365727 9FC137B8 235 APPS 3 2147483644 INACTIVE DEDICATED 235 APPS
applprod 18795 lupin-db-apps-server.itciss.com USER
8A5E283C 1342714831 ft85h9980hcyg 0 8A5E283C 1342714831 ft85h9980hcyg 0
POXPOEPO 3265560539 FRM:DL36409:PO BUYER CNA-ACCU 542061818 180 0 1141150032
-1 0 0 0 26-APR-21 1882524 NO NONE NONE NO DISABLED ENABLED ENABLED 0
NO HOLDER 1764 259 SQL*Net message from client
driver id 1952673792 74637000 #bytes 1 00000001
0 00 2723168908 6 Idle 0
1882524 WAITING ACCU DISABLED FALSE FALSE
9D490260 1001 48452 28364796 9FC137B8 235 APPS 0 2147483644 INACTIVE DEDICATED 235 APPS
applprod 18795 lupin-db-apps-server.itciss.com USER
00 0 9E62E14C 3174209965 326up1aym56dd 0
POXPOVPO 2399349501 FRM:DL36409:PO BUYER CNA-ACCU 542061818 180 0 1141068994
79599 452 763748 0 26-APR-21 1882910 NO NONE NONE NO DISABLED ENABLED ENABLED 0
NO HOLDER 5837 259 SQL*Net message from client
driver id 1952673792 74637000 #bytes 1 00000001
0 00 2723168908 6 Idle -1
1882910 WAITED SHORT TIME ACCU DISABLED FALSE FALSE
ACCU (PROD)> select sid,serial# from v$session where process = 18795 and status= 'INACTIVE' and ACTION like '%FRM%';
SID SERIAL#
---------- ----------
911 622
1001 48452
ACCU (PROD)>
######################we have to check with TOP 100% cpu consuming process id's wit the bellow sql,it will give how many sid is are running what is running################
set echo off
set serveroutput on size 999999
set verify off
set feedback off
accept uxproc prompt 'Enter Unix process id: '
DECLARE
v_sid number;
vs_cnt number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.v_$process p, sys.v_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc');
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.v_$process p, sys.v_$session s where p.addr = s.paddr and (p.spid = &uxproc or s.process = '&uxproc');
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('=====================================================================');
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.sql_hash_value order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext where HASH_VALUE = s.prev_hash_value order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid)
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.lmode) ) lmode,decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.request) ) lrequest,decode(l.type, 'MR', o.name,'TD', o.name,'TM', o.name,'RW', 'FILE#='||substr(l.id1,1,3)||' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,'WL', 'REDO LOG FILE#='||l.id1,'RT', 'THREAD='||l.id1,'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),'ID1='||l.id1||' ID2='||l.id2) objname from sys.v_$lock l, sys.obj$ o where sid = s.sid and l.id1 = o.obj#(+) ) loop dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;
dbms_output.put_line(to_char(vs_cnt)||' sessions were found with '||'&uxproc'||' as their unix process id.');
dbms_output.put_line('Please scroll up to see details of all the sessions.');
dbms_output.put_line('=====================================================================');
close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&uxproc!!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef uxproc
set heading on
set verify on
set feedback on
set echo on
####################################after firing this script we came to know it is running from 20days###########
ACCU (PROD)> @unix.sql
Enter Unix process id: 8740
=====================================================================
1 sessions were found with 8740 as their unix process id.
=====================================================================
SID/Serial : 856,62035
Foreground : PID: 8740 -
Shadow : PID: 8752 - oracle@lupin-db-apps-server.itciss.com
Terminal : / UNKNOWN
OS User : applprod on lupin-db-apps-server.itciss.com
Ora User : APPS
Details : PO BUYER CNA-ACCU - FNDSCSGN
Status Flags: INACTIVE DEDICATED USER
Tran Active : NONE
Login Time : Tue 23:20:04
Last Call : Tue 23:20:07 - 29761.7 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL WHERE
ROWNUM=1
Session Waits:
WAITED SHORT TIME: SQL*Net message from client
Locks:
=====================================================================
1 sessions were found with 8740 as their unix process id.
Please scroll up to see details of all the sessions.
=====================================================================
ACCU (PROD)>
ACCU (PROD)>
ACCU (PROD)> !date
Tue May 18 15:21:58 PDT 2021
Enter Unix process id: 18795
=====================================================================
1 sessions were found with 18795 as their unix process id.
=====================================================================
SID/Serial : 933,54316
Foreground : PID: 18795 -
Shadow : PID: 23187 - oracle@lupin-db-apps-server.itciss.com
Terminal : / UNKNOWN
OS User : applprod on lupin-db-apps-server.itciss.com
Ora User : APPS
Details : PO BUYER CNA-ACCU - FNDSCSGN
Status Flags: INACTIVE DEDICATED USER
Tran Active : NONE
Login Time : Mon 17:29:23
Last Call : Mon 17:29:27 - 31555.1 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL WHERE
ROWNUM=1
Session Waits:
WAITED SHORT TIME: SQL*Net message from client
Locks:
=====================================================================
1 sessions were found with 18795 as their unix process id.
Please scroll up to see details of all the sessions.
=====================================================================
###################VALIDATED WITH SHADO PROCESS######################
[applprod@lupin-db-apps-server ~]$ ps -ef|grep 23187
oraprod 23187 1 0 Apr26 ? 00:00:15 oracleACCU (LOCAL=NO)
applprod 25968 16828 0 15:31 pts/3 00:00:00 grep 23187
[applprod@lupin-db-apps-server ~]$ ps -ef|grep 8752
oraprod 8752 1 0 Apr27 ? 00:00:00 oracleACCU (LOCAL=NO)
applprod 26566 16828 0 15:32 pts/3 00:00:00 grep 8752
[applprod@lupin-db-apps-server ~]$
[applprod@lupin-db-apps-server ~]$ ps -ef|grep 8740
applprod 8740 26618 99 Apr27 ? 20-13:59:50 f60webmx webfile=5,33381,lupin-db-apps-server_9000_ACCU
applprod 31871 16828 0 15:47 pts/3 00:00:00 grep 8740
[applprod@lupin-db-apps-server ~]$
[applprod@lupin-db-apps-server ~]$ ps -ef|grep 18795
applprod 18795 26618 99 Apr26 ? 21-17:24:58 f60webmx webfile=5,33287,lupin-db-apps-server_9000_ACCU
applprod 31967 16828 0 15:47 pts/3 00:00:00 grep 18795
[applprod@lupin-db-apps-server ~]$
No comments:
Post a Comment