Scripts

This is a repost of a post I made earlier in March, I just simply could not convince blogspot to properly display the scripts with the fonts etc that I wanted. The first script (current_users.sql) gives you the SID,SERIAL# for every session. So, now got a session that has been active for too long, or using a lot of CPU? Then trace the session remotely.

SQL> exec sys.dbms_system.set_ev(:SID,:SERIAL, 10046,12,'');


I have another script I use that you simply pass a SPID to and it figures everything out for you. I will post it later. You will then have a trace file sitting on the server in the UDUMP location. You can then use tkprof to create an output file you can read. Using the parameter sort=prsela,fchela,exeela sorts the statements from longest running to shortest running, so hopefully the offender is at or near the top. To get the file name (dedicated server) you can run the following:

SELECT PAR.VALUE||'/'|| 'ora_'||P.SPID||'_'||lower(I.INSTANCE_NAME)||'.trc'
FROM V$SESSION S, V$PROCESS P, V$INSTANCE I,
V$PARAMETER PAR
WHERE
1=1
AND S.PADDR = P.ADDR
AND P.SPID IN (&THESPID)
AND PAR.NAME = 'user_dump_dest'
;


You will have to edit the output in take into consideration the case sensitivity of your parameters. Don't want to trace.. or the session is "locked", then I also have a another script in this for you.
current_sql.sql
Simply call this script and pass the SPID to it, or more than one SPID with them comma separated (hence the IN). I choose SPID because in a UNIX environment they are easy to find and relate the session to the OS. I haven't attempted this script in the Windows world.

DEFINE THESPID = &1
SET VERIFY OFF
SET LINES 132
SET PAGES 40
COLUMN SIDSER FORMAT A12
COLUMN SQL_TEXT FORMAT A40
COLUMN STATUS FORMAT A10
TRUNCATE COLUMN SPID FORMAT A10
COLUMN USERNAME FORMAT A10 TRUNCATE
SELECT P.SPID ,S.SID','S.SERIAL# SIDSER
,T.SQL_TEXT ,S.STATUS ,S.USERNAME
FROM V$SESSION S, V$SQLAREA T
, V$PROCESS P, V$SESSION_LONGOPS L
WHERE
1=1
AND S.PADDR = P.ADDR (+)
AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE
AND P.SPID IN (&THESPID)
AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE
GROUP BY
P.SPID,S.SID,S.SERIAL#,T.SQL_TEXT
,S.STATUS,S.USERNAME
/


COLUMN OPNAME FORMAT A10 TRUNC
COLUMN TARGET FORMAT A15 TRUNC
COLUMN MESSAGE FORMAT A40 WRAP
COLUMN SEC_REM FORMAT A10
COLUMN MINS_REM FORMAT A10
COLUMN WAIT NEWLINE FORMAT A60
SELECT DISTINCT L.OPNAME,L.TARGET,L.MESSAGE
,TO_CHAR(TIME_REMAINING) SEC_REM
,TO_CHAR(ROUND((TIME_REMAINING/60),2)) MINS_REM
FROM V$SESSION S, V$SQLAREA T, V$PROCESS P
, V$SESSION_LONGOPS L
WHERE
1=1
AND S.PADDR = P.ADDR (+)
AND S.SQL_HASH_VALUE (+) = T.HASH_VALUE
AND P.SPID IN (&THESPID)
AND L.SQL_HASH_VALUE (+) = S.SQL_HASH_VALUE
;

You get the SQL current executing, as well you get anything in v$session_longops. Very helpful in tracking down problems.