Script: Listing Memory Used By All Sessions

Purpose

Sample script for training purposes on finding PGA/UGA memory usage.

Software Requirements/Prerequisites

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”.

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 heading 'Name'
column sid format 999 heading 'SID'
column maxmem format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'

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
/

Sample Output

Thu Aug 26                                                             page    1
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  47 session uga memory              158,768
  45 session uga memory              158,768
  46 session uga memory              158,768
  48 session uga memory              158,768
  31 session uga memory max          158,768
  49 session uga memory              158,768
  46 session uga memory max          158,768
  31 session uga memory              158,768

Thu Aug 26                                                             page    2
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  39 session uga memory max          158,768
  33 session uga memory max          158,768
  45 session uga memory max          158,768
  47 session uga memory max          158,768
  39 session uga memory              158,768
  33 session uga memory              158,768
  48 session uga memory max          158,768
  49 session uga memory max          158,768

Thu Aug 26                                                             page    3
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  42 session uga memory              224,176
  32 session uga memory              224,176
  44 session uga memory              224,176
  44 session uga memory max          224,176
  32 session uga memory max          224,176
  38 session uga memory              289,584
  42 session uga memory max          289,584
  38 session uga memory max          354,992

Thu Aug 26                                                             page    4
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  43 session uga memory              420,400
  43 session uga memory max          420,400
  47 session pga memory max          429,656
  49 session pga memory              429,656
  33 session pga memory              429,656
  33 session pga memory max          429,656
  49 session pga memory max          429,656
  48 session pga memory max          429,656

Thu Aug 26                                                             page    5
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  48 session pga memory              429,656
  47 session pga memory              429,656
  31 session pga memory              495,192
  39 session pga memory max          495,192
  31 session pga memory max          495,192
  39 session pga memory              495,192
  32 session pga memory              560,728
  42 session pga memory              560,728

Thu Aug 26                                                             page    6
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  32 session pga memory max          560,728
  42 session pga memory max          626,264
  44 session pga memory max          640,072
  44 session pga memory              640,072
  38 session pga memory              822,872
  41 session uga memory              878,624
  38 session pga memory max          888,408
  41 session uga memory max        1,074,848

Thu Aug 26                                                             page    7
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  43 session pga memory            1,150,552
  43 session pga memory max        1,150,552
  40 session uga memory            1,271,072
  41 session pga memory            1,395,640
  40 session uga memory max        1,436,120
  40 session pga memory            1,805,912
  41 session pga memory max        2,002,520
  40 session pga memory max        2,657,880

Thu Aug 26                                                             page    8
        PGA = dedicated server processes - UGA = Client machine process

 SID Name                          Max Bytes
---- ------------------------- -------------
  46 session pga memory max        3,558,456
  46 session pga memory            3,558,456
  45 session pga memory           11,684,792
  45 session pga memory max       11,701,848
                               -------------
sum                               61,481,288

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. FAQ Memory Corruption
2. Automatic PGA Memory Management
3. Script to Detect Tablespace Fragmentation
4. ORA-4030 PGA Usage Diagnostic Script
5. Script to show Active Distributed Transactions
6. Script to Collect RAC Diagnostic Information (racdiag.sql)
7. Know More about Oracle PGA Memory
8. Oracle In-Memory Database Cache Oracle TimesTen In-Memory Database
9. 部分行索引使用介绍
10. Script:List SORT ACTIVITY