Showing posts with label processes. Show all posts
Showing posts with label processes. Show all posts

Tuesday, September 22, 2015

Parallel Query Processes - Parent / Child Details

Parallel Process Details including Master/Slave relationships

SQL
set lines 200
set pages 100
col username   format a10
col qcslave    format a10
col slaveset   format a8
col program    format a30
col sid        format a5
col slvinst    format a7
col state      format a8
col waitevent  format a30
col qcsid      format a5
col qcinst     format a6
col reqdop     format 999
col actdop     format 999
col secelapsed format 999,999
SELECT DECODE(px.qcinst_id,NULL,username, ' - '||LOWER(SUBSTR(pp.SERVER_NAME,LENGTH(pp.SERVER_NAME)-4,4) ) ) USERNAME, 
       DECODE(px.qcinst_id,NULL, 'QC', '(Slave)') "QCSLAVE" ,
       TO_CHAR( px.server_set) SLAVESET, 
       s.program PROGRAM, 
       TO_CHAR(s.SID) SID,
       TO_CHAR(px.inst_id) SLVINST, 
       DECODE(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) STATE,
       CASE  sw.state WHEN 'WAITING' THEN SUBSTR(sw.event,1,30) ELSE NULL END WAITEVENT ,
       DECODE(px.qcinst_id, NULL ,TO_CHAR(s.SID) ,px.qcsid) QCSID,
       TO_CHAR(px.qcinst_id) QCINST, 
       px.req_degree REQDOP, 
       px.DEGREE ACTDOP,
       DECODE(px.server_set,'',s.last_call_et,'') SECELAPSED
  FROM gv$px_session px, 
       gv$session s, 
       gv$px_process pp, 
       gv$session_wait sw
 WHERE px.SID=s.SID (+)
   AND px.serial#=s.serial#(+)
   AND px.inst_id = s.inst_id(+)
   AND px.SID = pp.SID (+)
   AND px.serial#=pp.serial#(+)
   AND sw.SID = s.SID
   AND sw.inst_id = s.inst_id
 ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), 
          px.QCSID,
          DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
          px.SERVER_SET, 
          px.INST_ID



Friday, March 15, 2013

Session PGA Memory Usage



A helpful script to find sessions using large amounts of system memory:

SET LINESIZE 140
SET PAGESIZE 100
COL session      HEADING 'SID - User - Client' FORMAT a35
COL current_size HEADING 'Current MB' FORMAT '999,999.99'
COL maximum_size HEADING 'Max MB'     FORMAT '999,999.99'
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF current_size ON REPORT
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",
       se1.value/1024/1024 current_size,
       se2.value/1024/1024 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  (+)
-- AND NVL(ssn.username, NVL(bgp.name, 'background')) = '<SCHEMA>'
ORDER BY 4
/
!free
exit
/

Sample output:
SID - User - Client                 PID/THREAD  Current MB      Max MB
----------------------------------- ---------- ----------- -----------
cut...
  867 - XXXX - server01                   9615        3.47      223.60
  729 - XXXX - server01                   9611        3.47      223.78
  469 - XXXX - server01                   3479        3.16      223.85
  605 - XXXX - server03                   4007       10.97    1,373.78
 1039 - XXXX - server01                  21216       11.16    1,373.97
  448 - XXXX - server02                   3852       10.22    1,378.91
                                               -----------
Total                                               980.91

             total       used       free     shared    buffers     cached
Mem:     132085672  125739556    6346116          0     836680   24065240
-/+ buffers/cache:  100837636   31248036
Swap:      8193140       9968    8183172