Thursday, December 17, 2009

Script: Listing Memory Used By All Sessions


Subject:
Script: Listing Memory Used By All Sessions

Note:1070975.6
Type:
SCRIPT

Last Revision Date:
06-JAN-2003
Status:
PUBLISHED



Abstract
Script to list the Memory used by all sessions connected to the database.
 
Product Name, Product Version
Oracle Server, 8.0.x to 9.x
Platform
Platform Independent
Date Created
13-Jun-2000
 
Instructions
Execution Environment:
     
 
Access Privileges:
     Requires Select privilege on "sys.v$sesstat" and "sys.v$statname". 
 
Usage:
     sqlplus / @session_memory
 
Instructions:
     Copy the script into a file named session_memory.sql.  Execute the script from
     SQL*Plus connected with a user with select privileges on views "sys.v$sesstat"
     and "sys.v$statname".
 
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.
 
Description
This script provides a listing of the UGA and PGA memory for every session 
in the current instance from smallest to largest.  
 
======== 
Example: 
======== 
 
 
Thu May 20          page    1
        PGA = dedicated server processes - UGA = Client machine process
 
       SID NAME   MAXMEM
---------- ------------------------- ----------
         3 session uga memory             10540
      3 session uga memory max         10540
4 session uga memory       11284
         5 session uga memory max         11284
         4 session uga memory max         11284
         5 session uga memory           11284
      1 session uga memory             11388
         1 session uga memory max       11388
 
Thu May 20     page    2
        PGA = dedicated server processes - UGA = Client machine process
 
       SID NAME        MAXMEM
---------- ------------------------- ----------
         2 session uga memory             13656
2 session uga memory max         13656
6 session uga memory  15572
         6 session uga memory max         15572
    12 session uga memory             20768
        14 session uga memory      20768
7 session uga memory             31152
         7 session uga memory max  31152
..
...
....
 
Thu May 20               page    6
PGA = dedicated server processes - UGA = Client machine process
 
       SID NAME                  MAXMEM
---------- ------------------------- ----------
  9 session pga memory            161860
         9 session pga memory max  161860
     3 session pga memory            194696
         3 session pga memory max     194696
                       ----------
sum                   2709744
 
 
References

 
Script
SET ECHO off 
REM NAME:   session_memory.SQL 
REM USAGE:"@path/session_memory" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    Select privilege on sys.v$sesstat and sys.v$statname. 
REM ------------------------------------------------------------------------ 
REM AUTHOR:
REM    Jay Caviness    
REM    
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    This script provides a listing of the uga and pga memory for every session 
REM    in the current instance from smallest to largest.  
REM
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REMYou should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
column name  format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg   format 99,999,999 heading 'Avg Bytes'
column min   format 99,999,999 heading 'Min Bytes'
column max   format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'
 
 
compute sum of minmem on report
compute sum of maxmem on report
break on report
 
select se.sid,n.name, 
       max(se.value) maxmem
from v$sesstat se,
     v$statname n
where n.statistic# = se.statistic#
and   n.name in ('session pga memory','session pga memory max',
                 'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/


No comments:

Post a Comment