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 FEEDBACK OFF DEFINE schema = '' COLUMN schema_val NOPRINT NEW_VALUE schema SELECT '&&1' schema_val FROM dual; SET LINESIZE 3500 SET PAGESIZE 0 SET VERIFY OFF SET FEEDBACK OFF SET SERVEROUTPUT ON begin dbms_output.put_line(' '); dbms_output.put_line('-- NOTA ------------------------------------------------------------'); dbms_output.put_line('Todos los datos sobre consumo de memoria estan expresados en Kbytes'); dbms_output.put_line('--------------------------------------------------------------------'); end; / 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 A20 HEADING "User name" COLUMN Sid,Serial FORMAT a10 HEADING "Sid,Serial" COLUMN Sid FORMAT 9999 HEADING "Sid" COLUMN Serial FORMAT 99999999 HEADING "Serial#" COLUMN program FORMAT a20 HEADING "program" COLUMN module FORMAT A15 HEADING "module" COLUMN SessPgaMem FORMAT 9G999G999 HEADING "Dedic svr|Curr pga" COLUMN SessPgaMax FORMAT 9G999G999 HEADING "Dedic svr|Max pga" COLUMN SessUgaMem FORMAT 9G999G999 HEADING "Cli proc|Curr uga" COLUMN SessUgaMax FORMAT 9G999G999 HEADING "Cli proc|Max uga" COLUMN WhereClient FORMAT A1 HEADING "C" COLUMN Status FORMAT A1 HEADING "S" COLUMN Call FORMAT 9999 HEADING "Call" COLUMN MemoryType FORMAT A26 HEADING "Memory usage overview" COLUMN MemoryInUse FORMAT 9G999G999G999 HEADING "In use (KBytes)" COLUMN machine FORMAT a20 HEADING "machine" SELECT nvl(sess.username, ( SELECT 'bg: ' || description FROM v$bgprocess bg WHERE bg.paddr = sess.paddr and rownum <= 1)) "UserName", sess.sid||','||sess.serial# "Sid,Serial", sess.program "program", sess.module "module", sess.machine "machine", to_char(sess.logon_time,'dd/mm/yyyy hh24:mi:ss') "logon_time" FROM v$session sess WHERE sess.type = 'USER' AND sess.sid = &schema 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_mem 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 UNDEFINE schema SET FEEDBACK ON CLEAR BREAKS CLEAR COMPUTE