Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Multiple SQLs to Diagnose PGA issues at runtime

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Location
    New Delhi, India
    Posts
    427

    Multiple SQLs to Diagnose PGA issues at runtime

    SET LINESIZE 145
    SET PAGESIZE 9999

    -- This script will provide PGA info with session and OS Process Info.

    COLUMN alme HEADING "Allocated MB" FORMAT 99999D9
    COLUMN usme HEADING "Used MB" FORMAT 99999D9
    COLUMN frme HEADING "Freeable MB" FORMAT 99999D9
    COLUMN mame HEADING "Max MB" FORMAT 99999D9
    COLUMN username FORMAT a15
    COLUMN program FORMAT a22
    COLUMN sid FORMAT a5
    COLUMN spid FORMAT a8
    select * from
    ( SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
    SUBSTR(s.program,1,22) program , s.process pid_remote,
    ROUND(pga_used_mem/1024/1024) usme,
    ROUND(pga_alloc_mem/1024/1024) alme,
    ROUND(pga_freeable_mem/1024/1024) frme,
    ROUND(pga_max_mem/1024/1024) mame
    FROM v$session s,v$process p
    WHERE p.addr=s.paddr
    ORDER BY pga_max_mem desc
    ) where rownum < 8 ;


    -- Total PGA

    SELECT sum(ROUND(pga_used_mem/1024/1024) ) PGAIN_MB
    FROM v$session s,v$process p
    WHERE p.addr=s.paddr;



    -- This query can be used to display both the PGA and UGA memory requirements for all user sessions, including background processes:

    COLUMN sid FORMAT 999 HEADING 'SID'
    COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
    COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
    COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
    COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
    COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
    COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
    COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
    COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'

    SELECT
    s.sid sid
    , lpad(s.username,12) oracle_username
    , lpad(s.osuser,9) os_username
    , s.program session_program
    , lpad(s.machine,8) session_machine
    , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session pga memory') session_pga_memory
    , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session pga memory max') session_pga_memory_max
    , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session uga memory') session_uga_memory
    , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
    sn.name = 'session uga memory max') session_uga_memory_max
    FROM
    v$session s
    ORDER BY session_pga_memory DESC
    /

+ Reply to Thread

Similar Threads

  1. Replies: 1
    Last Post: 11-13-2013, 09:50 AM
  2. How to see the top10 Requests based on total runtime on a given day.
    By dbaANKIT in forum SCRIPTS : Concurrent Requests & Concurrent Request Sessions
    Replies: 0
    Last Post: 08-18-2012, 12:53 AM
  3. admrgpch - Merge multiple Patches
    By Hemant in forum AD Utilities , Autoconfig , FND Utilities
    Replies: 5
    Last Post: 05-08-2012, 12:35 PM
  4. Diagnose RAC Problems
    By devesh_ocp in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 04-25-2012, 03:31 PM
  5. How do I diagnose a slow running Concurrent Request / Program
    By ajaysingh in forum Concepts,Service Management,Apache,OC4J,OPMN,JServ,Forms, Issues,Troubleshooting,
    Replies: 1
    Last Post: 10-19-2011, 11:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics