Rem Fichero : allmem.sql Rem Rem Parametros : Niniguno Rem Rem RDBMS : Oracle V8.x, V9.x, V10.x Rem Rem Rem =========================================================================================================== Rem Rem Descripcion : Listado de procesos cliente y servidor activos y consumo de memoria. Rem Rem Usuario : Nombre de usurio oracle conectado. Rem Procesos background estan etiquetados con el prefijo "bg:". Rem Sid : SID de la sesion Oracle. Rem Rem Servidor dedicado : Tamaņo actual de la PGA para la sesion. Rem Memoria actual Memoria destinada por el proceso servidor. Rem Rem Servidor dedicado : Tamaņo maximo alcanzado en la PGA para la sesion. Rem Memoria maxima Memoria destinada por el proceso servidor. Rem Rem Proceso cliente : Tamaņo actual de la UGA para la sesion. Rem Memoria actual Memoria destinada por el proceso cliente. Rem Rem Proceso cliente : Tamaņo maximo alcanzado en la UGA para la sesion. Rem Memoria maxima Memoria destinada por el proceso cliente. Rem Rem Cliente : Local = el cliente esta corriendo en el mismo servidor. Rem Donde? Remoto = el cliente esta corriendo en un nodo remoto. Rem Rem Rem Known issues : la localizacion de los procesos backgroud job queue processes (si existen) se muestran incorrectamente Rem como procesos remotos cuando deberian ser locales. Rem Rem Sample output Rem ------------- Rem Rem ----------------------------------------------------------------------------------------------------------- Rem BBDD : PDB1 on server epicsmo2 Rem Current date and time : 13-JUN-2005 17:08:14 Rem ----------------------------------------------------------------------------------------------------------- Rem Rem Serv. dedicado Serv. dedicado Proc. cliente Proc. cliente Cliente Rem User name Sid Memoria actual Memoria maxima Memoria actual Memoria maxima Donde? Rem ------------------------------ ----- ---------------- ---------------- -------------- -------------- ------ Rem ... Rem MXXXXXXX 13 1.704.168 201.216 378.688 734.976 Remote Rem bg: process cleanup 1 210.120 210.120 78.464 78.464 Local Rem bg: db writer process 0 2 1.949.280 1.949.280 78.464 78.464 Local Rem bg: db writer process 1 3 1.914.416 1.914.416 78.464 78.464 Local Rem bg: Redo etc. 4 4.522.832 4.522.832 78.464 78.464 Local Rem bg: checkpoint 5 337.520 337.520 78.464 78.464 Local Rem bg: System Monitor Process 6 1.227.688 1.227.688 143.872 143.872 Local Rem bg: Network Server 0 14 10.704.536 10.704.536 78.464 78.464 Local Rem ... Rem Rem Memory usage overview In use (Bytes) Rem -------------------------- -------------- Rem PGA 32.675.192 Rem UGA (local processes only) 1.268.608 Rem SGA Fixed Size 737.320 Rem SGA Variable Size 150.994.944 Rem SGA Database Buffers 67.108.864 Rem SGA Redo Buffers 798.720 Rem -------------- Rem sum 253.583.648 Rem Rem =========================================================================================================== SET LINESIZE 3500 SET PAGESIZE 0 SET VERIFY OFF SET FEEDBACK OFF alter session set nls_numeric_characters = ',.'; @header_dbinfo 107 DC SET TERMOUT OFF DEFINE pga_mem = 0 DEFINE pga_max = 0 DEFINE uga_mem = 0 DEFINE uga_max = 0 DEFINE ServerName = '?' COLUMN statistic# NOPRINT NEW_VALUE pga_mem SELECT statistic# FROM v$statname WHERE name = 'session pga memory'; COLUMN statistic# NOPRINT NEW_VALUE pga_max SELECT statistic# FROM v$statname WHERE name = 'session pga memory max'; COLUMN statistic# NOPRINT NEW_VALUE uga_mem SELECT statistic# FROM v$statname WHERE name = 'session uga memory'; COLUMN statistic# NOPRINT NEW_VALUE uga_max SELECT statistic# FROM v$statname WHERE name = 'session uga memory max'; COLUMN host_name NOPRINT NEW_VALUE ServerName SELECT host_name FROM v$instance; SET PAGESIZE 9999 SET TERMOUT ON CLEAR COLUMNS COLUMN UserName FORMAT A35 HEADING "User name" COLUMN Sid FORMAT 9999 HEADING "Sid" COLUMN Serial FORMAT 99999999 HEADING "Serial#" COLUMN Sqlhashval FORMAT 999999999999999 HEADING "SqlHashVal" COLUMN SessPgaMem FORMAT 9G999G999 HEADING "Dedic svr|Curr mem" COLUMN SessPgaMax FORMAT 9G999G999 HEADING "Dedic svr|Max mem" COLUMN SessUgaMem FORMAT 9G999G999 HEADING "Cli proc|Curr mem" COLUMN SessUgaMax FORMAT 9G999G999 HEADING "Cli proc|Max mem" COLUMN WhereClient FORMAT A6 HEADING "Client|Where?" COLUMN MemoryType FORMAT A26 HEADING "Memory usage overview" COLUMN MemoryInUse FORMAT 9G999G999G999 HEADING "In use (KBytes)" COLUMN status FORMAT A10 HEADING "status" COLUMN last_call_et FORMAT 9G999G999G999 HEADING "last_call_et" SELECT nvl(sess.username, ( SELECT 'bg: ' || description FROM v$bgprocess bg WHERE bg.paddr = sess.paddr and rownum <= 1 )) "UserName", sess.sid "Sid", sess.serial# "Serial", sess.sql_hash_value "Sqlhashval", sess_pga_mem.value/1024 "SessPgaMem", sess_pga_max.value/1024 "SessPgaMax", sess_uga_mem.value/1024 "SessUgaMem", sess_uga_max.value/1024 "SessUgaMax", decode( sess.machine, '&ServerName', 'Local', 'Remote' ) "WhereClient", sess.status,sess.last_call_et FROM v$session sess, ( SELECT sid, value FROM v$sesstat WHERE statistic# = &pga_mem ) sess_pga_mem, ( SELECT sid, value FROM v$sesstat WHERE statistic# = &pga_max ) sess_pga_max, ( SELECT sid, value FROM v$sesstat WHERE statistic# = &uga_mem ) sess_uga_mem, ( SELECT sid, value FROM v$sesstat WHERE statistic# = &uga_max ) sess_uga_max WHERE sess_pga_mem.sid = sess.sid AND sess_pga_max.sid = sess.sid AND sess_uga_mem.sid = sess.sid AND sess_uga_max.sid = sess.sid --and sess.status = 'ACTIVE' --and sess.username = 'ODS' ORDER BY sess.username; PROMPT COMPUTE SUM OF MemoryInUse ON REPORT BREAK ON REPORT SELECT 'PGA' "MemoryType", sum(value)/1024 "MemoryInUse" FROM v$sesstat WHERE statistic# = &pga_max union all SELECT 'UGA (local processes only)', sum(value)/1024 FROM v$sesstat, v$session WHERE statistic# = &uga_mem AND v$sesstat.sid = v$session.sid AND v$session.machine = '&ServerName' union all SELECT 'SGA ' || name, value/1024 FROM v$sga; UNDEFINE pga_mem UNDEFINE pga_max UNDEFINE uga_mem UNDEFINE uga_max UNDEFINE ServerName SET FEEDBACK ON CLEAR BREAKS CLEAR COMPUTE