# DCdbpga.ctl:203:Collects Oracle RDBMS Process Global Area Memory Information
# $Id: DCdbpga.ctl,v 1.5 2016/02/18 11:41:54 RDA Exp $
#
# Change History
# 20160210  RSP  bug 22712053 - Remove empty heapdump_addr dump

=head1 NAME

DB:DCdbpga - Collects Oracle RDBMS Process Global Area Memory Information

=head1 DESCRIPTION

This module collects the Oracle RDBMS Process Global Area memory-related
information.

The following reports can be generated and are regrouped under C<Process Global
Area Memory>:

=cut

if !${I_DBC/E}
 return

echo tput('bold'),'Processing DB.DBPGA module ...',tput('off')

# Initialization
var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'

# Load the common macros
run DB:DBinfo()
run RDA:library()

# Get database version
var $DATABASE_VERSION = get_db_version()

pretoc '^1:RDBMS'
pretoc '1+:Process Global Area Memory'

=head2 no_privileges - Insufficient Privileges

This module must be run as a SYSDBA user.

=cut

if !${B_SYSDBA/P}
{report no_privileges
 write 'The RDBMS Process Global Area Memory module requires you to connect as a SYSDBA user. \
        This can be set up during the DB module setup.'
 toc '2:[[',getFile(),'][rda_report][Insufficient Privileges]]'
 toc '-:RDBMS'
 return
}

=head2 pga - Process Global Area Information

Collects information about Process Global Area usage.

=cut

debug ' Inside DBPGA module, gathering Process Global Area information'
report pga
var $TTL = '---+!! RDBMS Process Global Area Memory '
var @TTL = ('',\
  '---+ Total Process Global Area Allocated',\
  '---+ Inactive Total Process Global Area Memory Use',\
  '---+ Inactive Process Global Area Memory Use per Oracle User')
var @HDR = ('',\
  '| *Allocated (MiB)*|',\
  '| *Inactive (MiB)*|',\
  '|*User Name*| *Inactive (MiB)*|')
set $sql
{SELECT '| ' ||
"       ROUND(SUM(pga_alloc_mem)/1048576,2) || '|'
" FROM  v$process p, v$session s
" WHERE p.addr = s.paddr;
"PROMPT ___Macro_separator(2)___
"SELECT '| ' ||
"       NVL(ROUND(SUM(pga_alloc_mem)/1048576,2),0) || '|'
" FROM  v$process p, v$session s
" WHERE p.addr = s.paddr
"   AND s.status = 'INACTIVE';
"PROMPT ___Macro_separator(3)___
"SELECT '|' ||
"       p.username || ' | ' ||
"       ROUND(SUM(pga_alloc_mem)/1048576,2) || '|'
" FROM  v$process p, v$session s
" WHERE p.addr = s.paddr
"   AND s.status = 'INACTIVE'
" GROUP BY p.username
" ORDER BY p.username, SUM(pga_alloc_mem) DESC;
}
call separator(1)
call writeSql($sql)
call separator(0,'Overview')

=head2 uga - User Global Area Information

Collects information about User Global Area usage.

=cut

debug ' Inside DBPGA module, gathering User Global Area cumulative statistics'
report uga
var $TTL = '---+!! RDBMS User Global Area Memory '
var @TTL = ('',\
  '---+ User Global Area Memory Allocation Cumulative Statistics',\
  '---+ User Global Area Maximum Memory Allocation Cumulative Statistics')
var @HDR = ('',\
  '| *Total UGA (All Sessions) (Bytes)*|',\
  '| *Total Max UGA (All Sessions) (Bytes)*|')
set $sql
{SELECT '| ' ||
"       SUM(value) || '|'
" FROM  v$sesstat, v$statname
" WHERE name = 'session uga memory'
"   AND v$sesstat.statistic# = v$statname.statistic#;
"PROMPT ___Macro_separator(2)___
"SELECT '| ' ||
"       SUM(value) || '|'
" FROM  v$sesstat, v$statname
" WHERE name = 'session uga memory max'
"   AND v$sesstat.statistic# = v$statname.statistic#;
}
call separator(1)
call writeSql($sql)
call separator(0,'User Global Area Information')

=head2 ses - Session Memory Information

Collects session memory statistics.

=cut

debug ' Inside DBPGA module, gathering session memory statistics'
report ses
var $TTL = '---+!! RDBMS Session Memory Statistics'
var @TTL = ('',\
  '---+ Cumulative Session Memory Statistics',\
  '---+ Per-Session Memory Statistics')
var @HDR = ('',\
  '|*Statistic Name*| *Value*|',\
  '|*Session ID*|*Statistic Name*| *Value*|')
set $sql
{SELECT '|'||
"       n.name || ' | ' ||
"       s.value || '|'
" FROM (SELECT statistic#, SUM(value) value
"        FROM v$sesstat
"        GROUP BY statistic#) s,
"       v$statname n
" WHERE s.statistic# = n.statistic#
"   AND n.name LIKE '%memory%'
" ORDER BY s.statistic#;
"PROMPT ___Macro_separator(2)___
"SELECT '|'||
"       s.sid || ' |' ||
"       n.name || ' | ' ||
"       s.value || '|'
" FROM v$sesstat s, v$statname n
" WHERE s.statistic# = n.statistic#
"   AND n.name LIKE 'session%memory%'
" ORDER BY s.sid, s.statistic#;
}
call separator(1)
call writeSql($sql)
call separator(0,'Session Memory Statistics')

## RSP remove heapdump_request as this dumps an empty heap
## =head2 headdump_request - Request
##
## Dumps current memory allocations for the Process Global Area with a
## C<heapdump level 536870913>.
##
## =cut
##
## pretoc '2:heapdump level 536870913'
##
## debug ' Inside DBPGA module, requesting heapdump level 536870913'
## report heapdump_request
## title '---+ heapdump level 536870913 Dump Request'
## if match($DATABASE_VERSION,'^(10|11|12)')
## {set $sql
##  {oradebug setmypid
##  "oradebug unlimit
##  "ALTER session SET events 'immediate trace name heapdump level 536870913';
##  "oradebug tracefile_name
##  }
## }
## prefix
##  call beginBlock(true)
## call writeSql($sql,1,'\.trc\b')
## if hasOutput(true)
##  call endBlock()
## if getSqlMessage()
##  write last,'%BR%'
## if isCreated()
##  toc '3:[[',getFile(),'][rda_report][Request]]'
##
## =head2 heapdump_file - Dump File
##
## Retrieves in the user dump destination the dump file generated by the
## C<heapdump level 536870913> request.
##
## =cut
##
## debug ' Inside DBPGA module, retrieving the heapdump level 536870913 dump file'
## if getSqlHits()
## {var ($fil) = first
##  report heapdump_file
##  title '---+ heapdump level 536870913 Dump'
##  title '---## Trace File: ',encode($fil)
##  call writeFile($fil)
##  if isCreated()
##   toc '3:[[',getFile(),'][rda_report][Dump File]]'
## }
## unpretoc

=head2 granuledump_request - Request

Dumps granule statistics with a C<dump_all_comp_granule_addrs level 1> request
(applicable for Oracle 11g and later).

=cut

if match($DATABASE_VERSION,'^(11|12)')
{pretoc '2: dump_all_comp_granule_addrs level 1'

 debug ' Inside DBPGA module, requesting dump_all_comp_granule_addrs level 1'
 report granuledump_request
 title '---+ dump_all_comp_granule_addrs level 1 Dump Request'
 set $sql
 {oradebug setmypid
 "oradebug unlimit
 "ALTER session SET events
 " 'immediate trace name dump_all_comp_granule_addrs level 1';
 "oradebug tracefile_name
 }
 prefix
  call beginBlock(true)
 call writeSql($sql,1,'\.trc\b')
 if hasOutput(true)
  call endBlock()
 if getSqlMessage()
  write last,'%BR%'
 if isCreated()
  toc '3:[[',getFile(),'][rda_report][Request]]'

=head2 granuledump_file - Dump File

Retrieves in the user dump destination the dump file generated by the
C<dump_all_comp_granule_addrs level 1> request.

=cut

 debug ' Inside DBPGA module, retrieving dump_all_comp_granule_addrs level 1'
 if getSqlHits()
 {var ($fil) = first
  report granuledump_file
  title '---+ dump_all_comp_granule_addrs level 1 Dump'
  title '---## Trace File: ',encode($fil)
  call writeFile($fil)
  if isCreated()
   toc '3:[[',getFile(),'][rda_report][Dump File]]'
 }

 unpretoc
}

# Disable the group title in next index
if isTocCreated()
 toc '-:RDBMS'

=head1 SEE ALSO

L<DB:DBinfo|collect::DB:DBinfo>,
L<DB:DCdb|collect::DB:DCdb>,
L<RDA:library|collect::RDA:library>

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut