[askdba] Re: Malfunctioning Session

  • From: "Pepling, Todd C." <tpepling@xxxxxxx>
  • To: "'sanjay.khangarot@xxxxxxxxx'" <sanjay.khangarot@xxxxxxxxx>, "'askdba@xxxxxxxxxxxxx'" <askdba@xxxxxxxxxxxxx>
  • Date: Thu, 28 Oct 2004 08:53:50 -0400

Another couple SQL scripts that might help:

############################################################
 START top_sql.sql
############################################################
/*
*===========================================================================
=====
* Name:     top_sql.sql
*           Written by Todd Pepling and Andy Eckhardt.
*
* Purpose:  Identifies the most resource intensive query (besides this one).
*
* Usage:    Call from SQLPlus using a DBA account.
*           Ex.
*           |--------------------------------------------------
*           | SQL> top_sql.sql
*           |--------------------------------------------------
*
* Dependencies: 1) Oracle8i.
*
* Revision Log: (most recent at top)
* 08-05-02 tcp: Made pertier.
* 08-02-02 tcp: Excluded this statement, added comments.
* 11-03-00 tcp: Created.
*===========================================================================
===*/
COL RUNTIME  FORMAT 99,999,990
COL OSUSER   FORMAT A20
COL SQL_TEXT FORMAT A70

SET ECHO     ON
SET FEEDBACK ON
SET LINESIZE 110
SET LONG     4000
SET PAGESIZE 4000
SET VERIFY   ON

SELECT
    A.RUNTIME_MEM RUNTIME
    ,SUBSTR(B.USERNAME,1,10)||' ('||B.SID||','||B.SERIAL#||') ' OSUSER
    ,A.SQL_TEXT
  FROM
    V$SQLAREA A
    ,V$SESSION B
  WHERE
    A.ADDRESS = B.SQL_ADDRESS
  AND
    A.RUNTIME_MEM = (
        SELECT
            MAX(C.RUNTIME_MEM) 
        FROM
            V$SQLAREA C
            ,V$SESSION D
        WHERE
            C.ADDRESS = D.SQL_ADDRESS
        AND
            D.USERNAME <> 'SYS'
  )
  AND
    B.AUDSID <> (SELECT SYS_CONTEXT('USERENV','SESSIONID') FROM DUAL)
;

CLEAR BREAKS
CLEAR COLUMNS

SET ECHO     OFF
SET PAGESIZE 20

BTITLE OFF
TTITLE OFF
############################################################
 END top_sql.sql
############################################################




############################################################
 START cpu_stats_per_session.sql
############################################################
rem
============================================================================
rem Name:       cpu_stats_per_session.sql
rem             Obtained from Oracle Resource Stop
rem
rem Purpose:    This script will locate the most CPU intensive sessions.
rem
rem Prepared By:        Oracle Resource Stop
rem
rem Usage Information:  SQLPLUS SYS/pswd
rem                     @heavycpusess.sql
rem
============================================================================
set termout on
set feedback on
set pagesize 132

SELECT sess.username, v.sid, substr(s.name,1,30) "Statistic", v.value 
FROM v$statname s , v$sesstat v , v$session sess
WHERE s.name = 'CPU used by this session' 
and v.statistic#=s.statistic# 
and v.value > 0 
and sess.sid = v.sid
ORDER BY 3 desc;
############################################################
 END cpu_stats_per_session.sql
############################################################
This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you have received this email in error please notify the system manager. This 
message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail.

Other related posts: