REM =============================================================================REM ************ SCRIPT TO MONITOR MEMORY USAGE BY DATABASE SESSIONS ************REM =============================================================================REM Created: 21/march/2003REM Last update: 28/may/2003REMREM NAMEREM ====REM MEMORY.sqlREMREM AUTHORREM ======REM Mauricio BuissaREMREM DISCLAIMERREM ==========REM This script is provided for educational purposes only. It is NOT supported byREM Oracle World Wide Technical Support. The script has been tested and appearsREM to work as intended. However, you should always test any script beforeREM relying on it.REMREM PURPOSEREM =======REM Retrieves PGA and UGA statistics for users and background processes sessions.REMREM EXECUTION ENVIRONMENTREM =====================REM SQL*PlusREMREM ACCESS PRIVILEGESREM =================REM Select on V$SESSTAT, V$SESSION, V$BGPROCESS, V$PROCESS and V$INSTANCE.REMREM USAGEREM =====REM $ sqlplus "/ as sysdba" @MEMORYREMREM INSTRUCTIONSREM ============REM Call MEMORY.sql from SQL*Plus, connected as any DBA user.REM Press <ENTER> whenever you want to refresh information.REM You can change the ordered column and the statistics shown by choosing from the menu.REM Spool files named MEMORY_YYYYMMDD_HH24MISS.lst will be generated in the current directory.REM Every time you refresh screen, a new spool file is created, with a snapshot of the statistics shown.REM These snapshot files may be uploaded to Oracle Support Services for future reference, if needed.REMREM REFERENCESREM ==========REM "Oracle Reference" - Online DocumentationREMREM SAMPLE OUTPUTREM =============REM :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::REMREM SESSION PID/THREAD CURRENT SIZE MAXIMUM SIZEREM -------------------------------------------------- ---------- ------------------ ------------------REM 9 - SYS: myworkstation 2258 10.59 MB 10.59 MBREM 3 - LGWR: testserver 2246 5.71 MB 5.71 MBREM 2 - DBW0: testserver 2244 2.67 MB 2.67 MBREM ...REMREM :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::REMREM SESSION PID/THREAD CURRENT SIZE MAXIMUM SIZEREM -------------------------------------------------- ---------- ------------------ ------------------REM 9 - SYS: myworkstation 2258 0.29 MB 0.30 MBREM 5 - SMON: testserver 2250 0.06 MB 0.06 MBREM 4 - CKPT: testserver 2248 0.05 MB 0.05 MBREM ...REMREM SCRIPT BODYREM ===========REM Starting script executionCLE SCRPROMPT .PROMPT . ======== SCRIPT TO MONITOR MEMORY USAGE BY DATABASE SESSIONS ========PROMPT .REM Setting environment variablesSET LINESIZE 200SET PAGESIZE 500SET FEEDBACK OFFSET VERIFY OFFSET SERVEROUTPUT ONSET TRIMSPOOL ONCOL "SESSION" FORMAT A50COL "PID/THREAD" FORMAT A10COL " CURRENT SIZE" FORMAT A18COL " MAXIMUM SIZE" FORMAT A18REM Setting user variables valuesSET TERMOUT OFFDEFINE sort_order = 3DEFINE show_pga = 'ON'DEFINE show_uga = 'ON'COL sort_column NEW_VALUE sort_orderCOL pga_column NEW_VALUE show_pgaCOL uga_column NEW_VALUE show_ugaCOL snap_column NEW_VALUE snap_timeSELECT nvl(:sort_choice, 3) "SORT_COLUMN"FROM dual/SELECT nvl(:pga_choice, 'ON') "PGA_COLUMN"FROM dual/SELECT nvl(:uga_choice, 'ON') "UGA_COLUMN"FROM dual/SELECT to_char(sysdate, 'YYYYMMDD_HH24MISS') "SNAP_COLUMN"FROM dual/REM Creating new snapshot spool fileSPOOL MEMORY_&snap_timeREM Showing PGA statistics for each session and background processSET TERMOUT &show_pgaPROMPTPROMPT :::::::::::::::::::::::::::::::::: PROGRAM GLOBAL AREA statistics :::::::::::::::::::::::::::::::::SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "SESSION", to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE", to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE" FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins, v$statname stat1, v$statname stat2 WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory' AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max' AND se1.sid = ssn.sid AND se2.sid = ssn.sid AND ssn.paddr = bgp.paddr (+) AND ssn.paddr = prc.addr (+)ORDER BY &sort_order DESC/REM Showing UGA statistics for each session and background processSET TERMOUT &show_ugaPROMPTPROMPT :::::::::::::::::::::::::::::::::::: USER GLOBAL AREA statistics ::::::::::::::::::::::::::::::::::SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "SESSION", to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE", to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE" FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins, v$statname stat1, v$statname stat2 WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session uga memory' AND se2.statistic# = stat2.statistic# and stat2.name = 'session uga memory max' AND se1.sid = ssn.sid AND se2.sid = ssn.sid AND ssn.paddr = bgp.paddr (+) AND ssn.paddr = prc.addr (+)ORDER BY &sort_order DESC/REM Showing sort informationSET TERMOUT ONPROMPTBEGIN IF (&sort_order = 1) THEN dbms_output.put_line('Ordered by SESSION'); ELSIF (&sort_order = 2) THEN dbms_output.put_line('Ordered by PID/THREAD'); ELSIF (&sort_order = 3) THEN dbms_output.put_line('Ordered by CURRENT SIZE'); ELSIF (&sort_order = 4) THEN dbms_output.put_line('Ordered by MAXIMUM SIZE'); END IF;END;/REM Closing current snapshot spool fileSPOOL OFFREM Showing the menu and getting sort order and information viewing choicePROMPTPROMPT Choose the column you want to sort: == OR == You can choose which information to see: PROMPT ... 1. Order by SESSION ... 5. PGA and UGA statistics (default)PROMPT ... 2. Order by PID/THREAD ... 6. PGA statistics onlyPROMPT ... 3. Order by CURRENT SIZE (default) ... 7. UGA statistics onlyPROMPT ... 4. Order by MAXIMUM SIZEPROMPTACCEPT choice NUMBER PROMPT 'Enter the number of your choice or press <ENTER> to refresh information: 'VAR sort_choice NUMBERVAR pga_choice CHAR(3)VAR uga_choice CHAR(3)BEGIN IF (&choice = 1 OR &choice = 2 OR &choice = 3 OR &choice = 4) THEN :sort_choice := &choice; :pga_choice := '&show_pga'; :uga_choice := '&show_uga'; ELSIF (&choice = 5) THEN :sort_choice := &sort_order; :pga_choice := 'ON'; :uga_choice := 'ON'; ELSIF (&choice = 6) THEN :sort_choice := &sort_order; :pga_choice := 'ON'; :uga_choice := 'OFF'; ELSIF (&choice = 7) THEN :sort_choice := &sort_order; :pga_choice := 'OFF'; :uga_choice := 'ON'; ELSE :sort_choice := &sort_order; :pga_choice := '&show_pga'; :uga_choice := '&show_uga'; END IF;END;/REM Finishing script executionPROMPT Type "@MEMORY" and press <ENTER>SET FEEDBACK ONSET VERIFY ONSET SERVEROUTPUT OFFSET TRIMSPOOL OFFREM =============REM END OF SCRIPTREM ============= | 
