Logo Background

Database Session Memory Usage

  • By on November 14, 2008 | No Comments

    Memory value set and used by database session was one the important factor that decides the performance of your database.

    SQL> 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;

    This SQL script will list all the session with the memory consumption details for your database PGA memory

    You may need to know the number of Disk Sorts and Memory Sorts set in your database system. The 2 SQL script below will help you to get the answer.

    SQL> SELECT 'INIT.ORA sort_area_size: 'VALUE
    FROM v$parameter
    WHERE name LIKE 'sort_area_size';
    SQL> SELECT a.name, VALUE
    FROM v$statname a, v$sysstat
    WHERE a.statistic# = v$sysstat.statistic#
    AND a.name IN ('sorts (disk)', 'sorts (memory)', 'sorts (rows)');
    » Monitor Oracle Session Waits
Leave a Comment