Início / dba / Perguntas / 61178
Accepted
ceinmart
Asked: 2014-03-19 10:47:49 +0800 CST

Identifique em detalhes o consumo de memória de uma sessão específica

  • 772

Estamos usando o padrão Oracle 11g, sem nenhuma opção (diag/tunning/perf).

Consigo identificar uma sessão com o SQL abaixo que está tendo um alto consumo (1,4 GB) de memória nas últimas horas... (pga e uga)

Existe alguma maneira de obter detalhes sobre esse consumo?
Se é o uso de tabelas temporárias ou outra coisa?

with x as (select s.osuser osuser , s.username
     , s.status
     , se.sid
     , s.serial# serial
     , n.name
     , round(max(se.value)/1024/1024, 2) maxmem_mb
     , max(se.value) as maxmem
  from v$sesstat se , v$statname n , v$session s
 where n.statistic# = se.statistic#
  and n.name in ('session pga memory','session pga memory max', 'session uga memory','session uga memory max')
   and s.sid        = se.sid
 group by s.osuser, s.username, s.status, se.sid, s.serial#, n.name
 order by maxmem desc 
 ) 
 select * from x where rownum < 5
 ;
oracle

2 respostas

  1. Best Answer

    Após algumas pesquisas descobri a view V$PROCESS_MEMORY_DETAIL e como utilizá-la.
    Por padrão está sempre vazio e precisa de alguns comandos para "habilitar" (oudebug ou alterar sessão).

    Referências que encontrei:

    Esta citação do artigo da Oracle diz tudo:

    How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify 
where the memory is growing. 

In Oracle 10.2 and higher exist 2 new views that can be used to find 
where the memory continue to grow. This views can be used instead of 
heap dump to find where the memory is growing: 

 - V$PROCESS_MEMORY: 

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six 
rows for each Oracle process, one row for: 
      - Java 
      - PL/SQL 
      - OLAP 
      - SQL 
      - Freeable 
      - Other 

 - V$PROCESS_MEMORY_DETAIL 
    Contain break down of memory allocation for each component. 
    - To activate this view can one of following commands executed: 
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>'; 
       From ORADEBUG: 
       SQL> ORADEBUG SETMYPID; 
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>; 
    - To remove all rows in the view run following command: 
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>'; 
       From ORADEBUG: 
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

    e um SQL útil:

    SELECT
    s.sid,p.spid,pm.*
FROM 
    v$session s
  , v$process p
  , v$process_memory pm
WHERE
    s.paddr = p.addr
AND p.pid = pm.pid
-- AND p.spid IN (1423)
AND s.sid IN (244)
ORDER BY
    sid
  , category

    Aqui estão as informações detalhadas ...
    Só precisa cavar os nomes misteriosos. 

      1* select * from v$process_memory_detail
SQL> /

       PID    SERIAL# CATEGORY        NAME                       HEAP_NAME            BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR  PARENT_HEAP_DESC
---------- ---------- --------------- -------------------------- --------------- ---------- ---------------- ---------------- ----------------
        15          1 SQL             miscellaneous                                   18880               80 00               00
        15          1 Other           koi korfc                  koh-kghu sessi       24544                2 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           kxsFrame8kPage             session heap         24744                3 0000002A97935BE0 000000000B0BC680
        15          1 PL/SQL          static frame of inst       koh-kghu sessi       26696                3 0000002A97A2BD50 0000002A97935BE0
        15          1 Other           kgsc ht segs               session heap         29824              327 0000002A97935BE0 000000000B0BC680
        15          1 Other           kgh stack                  pga heap             32832                1 000000000B0B6C20 00
        15          1 Other           kopo object                koh-kghu sessi       34728                3 0000002A97A1A1C0 0000002A97935BE0
        15          1 Other           koh-kghu session heap      session heap         35352               15 0000002A97935BE0 000000000B0BC680
        15          1 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        15          1 PL/SQL          miscellaneous                                   64736               47 00               00
        15          1 Other           mark                       Alloc server h      101088               24 0000002A97A99ED0 0000002A9795E090
        15          1 Other           free memory                top call heap       107520                3 000000000B0BC460 00
        15          1 Other           mark                       Alloc environm      111576               18 0000002A9795E090 0000002A97935BE0
        15          1 Other           kxsFrame4kPage             session heap        120408               29 0000002A97935BE0 000000000B0BC680
        15          1 Other           free memory                session heap        345672              213 0000002A97935BE0 000000000B0BC680
        15          1 Other           miscellaneous                                  380264              619 00               00
        15          1 Other           free memory                pga heap            917616             4618 000000000B0B6C20 00
        15          1 Other           permanent memory           koh-kghu call      2618480            32731 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           permanent memory           koh dur heap d     2619072            32731 0000002A97947E40 0000002A97935BE0
        15          1 Other           permanent memory           session heap       7450512              180 0000002A97935BE0 000000000B0BC680
        15          1 Other           permanent memory           pga heap          23983776             2907 000000000B0B6C20 00
        15          1 Other           kol vstring                koh-kghu call     34298216            32727 0000002AAE10E8C0 000000000B0B6C20
        15          1 Other           kadaih image               koh dur heap d   132475264            65452 0000002AAE0E66C8 000000000B0B6C20
        15          1 Other           koh-kghu call heap         pga heap         164949272            65457 000000000B0B6C20 00
        37         67 Other           mark                       Alloc server h        8480                3 0000002A97870860 0000002A978715C8
        37         67 Other           KFKPGA                     KFK_IO_SUBHEAP        9936                1 0000002A97899E10 000000000B0B6C20
        37         67 Other           dbgr entry                 diag pga             12248              218 0000002A976007E0 000000000B0B6C20
        37         67 Other           permanent memory           diag pga             16056                6 0000002A976007E0 000000000B0B6C20
        37         67 PL/SQL          miscellaneous                                   16232               34 00               00
        37         67 PL/SQL          PL/SQL STACK               PLS PGA hp           16432                2 0000002A9788F150 000000000B0B6C20
        37         67 Other           kgh stack                  pga heap             17024                1 000000000B0B6C20 00
        37         67 Other           dbgeInitProcessCtx:InvCtx  diag pga             17088                2 0000002A976007E0 000000000B0B6C20
        37         67 Other           kgsc ht segs               session heap         17320              286 0000002A979467C8 000000000B0BC680
        37         67 Other           kxsc: kkspsc0 2            session heap         20056               23 0000002A979467C8 000000000B0BC680
        37         67 Other           koh-kghu session heap      session heap         22752               10 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                top uga heap         31880                1 000000000B0BC680 00
        37         67 SQL             miscellaneous                                   32728               93 00               00
        37         67 Other           kxsFrame16kPage            session heap         32880                2 0000002A979467C8 000000000B0BC680
        37         67 Other           Fixed Uga                  pga heap             39024                1 000000000B0B6C20 00
        37         67 Other           free memory                pga heap             47192                5 000000000B0B6C20 00
        37         67 PL/SQL          recursive addr reg file    koh-kghu sessi       51112                3 0000002A9810AA50 0000002A979467C8
        37         67 Other           permanent memory           session heap         52672                8 0000002A979467C8 000000000B0BC680
        37         67 Other           free memory                session heap         72944               23 0000002A979467C8 000000000B0BC680
        37         67 Other           miscellaneous                                  101120              178 00               00
        37         67 Other           free memory                top call heap       127936                3 000000000B0BC460 00
        37         67 Other           permanent memory           pga heap            175960               22 000000000B0B6C20 00
        37         67 Other           kxsFrame4kPage             session heap        195144               47 0000002A979467C8 000000000B0BC680

47 rows selected.
    • 3

  2. Você pode verificar qual instrução é executada e o que ela faz. O caminho a percorrer é obter SQL_ID de v$session e juntá-lo com v$sqlstats.

    Você pode usar DBMS_XPLAN para verificar o plano de execução de sua instrução.

    Com esta informação você tem algumas dicas de quais buffers são usados.

    Outra possibilidade é instalar o perfstat, tirar um instantâneo, iniciar sua sessão e tirar outro instantâneo quando a sessão terminar. A saída do perfstat fornece uma imagem bastante detalhada da atividade do banco de dados.

    • 2

relate perguntas