Tracing other sessions

Tracing other sessions

SQL> SELECT p.spid, s.sid, s.serial#
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid = 24078
SPID SID SERIAL#
———— ———- ———-
24078 18 5
SQL> begin dbms_system.set_ev(18,5, 10046,12,”); end; — trace on
– collect trace information for approximately 15 minutes during the problem
SQL> begin dbms_system.set_ev(18, 5, 10046,0,”); end; — trace off
~

Identify Details about a Oracle OS process

SELECT /*+ ordered */ p.spid, s.sid, s.serial#,s.module,s.action,s.event, s.username, TO_CHAR(s.logon_time, ‘mm-dd-yyyy hh24:mi’) logon_time, s.last_call_et, st.value, s.sql_hash_value, s.sql_address, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = ‘CPU used by this session’
AND p.spid = &osPID
AND s.status = ‘ACTIVE’
ORDER BY st.value desc

TOP 10 BY BUFFER GETS:

SET LINESIZE 100SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
BUFFER_GETS, EXECUTIONS, BUFFER_GETS/EXECUTIONS “GETS/EXEC”,
HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE BUFFER_GETS > 10000
ORDER BY BUFFER_GETS DESC)
WHERE ROWNUM <= 10
;

TOP 10 BY PHYSICAL READS:

SET LINESIZE 100
SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
DISK_READS, EXECUTIONS, DISK_READS/EXECUTIONS “READS/EXEC”,
HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE DISK_READS > 1000
ORDER BY DISK_READS DESC)
WHERE ROWNUM <= 10
;

TOP 10 BY EXECUTIONS:

SET LINESIZE 100
SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
EXECUTIONS, ROWS_PROCESSED, ROWS_PROCESSED/EXECUTIONS “ROWS/EXEC”,
HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE EXECUTIONS > 100
ORDER BY EXECUTIONS DESC)
WHERE ROWNUM <= 10
;

TOP 10 BY PARSE CALLS:

SET LINESIZE 100
SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
PARSE_CALLS, EXECUTIONS, HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE PARSE_CALLS > 1000
ORDER BY PARSE_CALLS DESC)
WHERE ROWNUM <= 10
;

TOP 10 BY SHARABLE MEMORY:

SET LINESIZE 100
SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
SHARABLE_MEM, EXECUTIONS, HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE SHARABLE_MEM > 1048576
ORDER BY SHARABLE_MEM DESC)
WHERE ROWNUM <= 10
;

TOP 10 BY VERSION COUNT:

SET LINESIZE 100
SET PAGESIZE 100
SELECT * FROM
(SELECT SUBSTR(SQL_TEXT,1,40) SQL,
VERSION_COUNT, EXECUTIONS, HASH_VALUE,ADDRESS
FROM V$SQLAREA
WHERE VERSION_COUNT > 20
ORDER BY VERSION_COUNT DESC)
WHERE ROWNUM <= 10

Mapping Concurrent request Id to Sid and serai#

1 select v.request_id,
2 s.sid,
3 s.serial#,
4 s.event,
5 p.spid
6 from apps.fnd_conc_requests_form_v v,
7 v$process p,
8 v$session s
9 where v.request_id = ‘5029302′
10 and v.oracle_process_id = p.spid(+)
11* and p.addr = s.paddr(+)