set echo off
--
-- PGA and UGA usage script for trend analysis.
-- Updated: 2019.01.04
--
-- This script is meant to be run via SQL*Plus.
-- The user of this script needs to have the
-- SELECT privilege on the following views:
--
-- - V$SESSION
-- - V$SESSTAT
-- - V$STATNAME
--
set feedback off;
set heading off;
set linesize 262;
set newpage none;
set show off;
set pagesize 55;
set trimspool on;
set verify off;
column "SID AND SERIAL#" FORMAT A19
col SNAP_COLUMN new_value SNAP_TIME
col SNAP_EOF_NAME new_value EOF_NAME
col SNAP_HOST_NAME new_value THE_HOST_NAME
col SNAP_INSTANCE_NAME new_value THE_NAME_OF_THE_INSTANCE
col SNAP_RDBMS_VERSION new_value THE_RDBMS_VERSION
set term off;
select to_char(sysdate,'YYYYMMDD_HH24MISS') "SNAP_COLUMN" from dual;
select trim(host_name) "SNAP_HOST_NAME" from v$instance;
select trim(instance_name) "SNAP_INSTANCE_NAME" from v$instance;
select trim(version) "SNAP_RDBMS_VERSION" from v$instance;
select '&THE_NAME_OF_THE_INSTANCE'||'_'||'&SNAP_TIME'||'.LST' "SNAP_EOF_NAME" from dual;
drop table maxpgauga;
create table maxpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.server,
s2.logon_time
from v$statname n,
v$sesstat s,
v$session s2
where n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory max%';
drop table curpgauga;
create table curpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.server,
s2.logon_time
from v$statname n,
v$sesstat s,
v$session s2
where n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory' and
name not like 'session%memory max%';
drop table wcspgauga;
create table wcspgauga as select *
from (select trim(to_char(sid))||','||trim(to_char(serial#)) "COLUMN_1",
username "COLUMN_2",
sum(value) "COLUMN_3",
server "COLUMN_4",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "COLUMN_5"
from maxpgauga
where server<>'DEDICATED'
group by sid,
serial#,
username,
server,
logon_time
)
union all
(select trim(to_char(sid))||','||trim(to_char(serial#))"COLUMN_1",
username "COLUMN_2",
value "COLUMN_3",
server "COLUMN_4",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "COLUMN_5"
from maxpgauga
where server='DEDICATED' and name like 'session pga memory max%'
);
drop table cwcpgauga;
create table cwcpgauga as select *
from (select trim(to_char(sid))||','||trim(to_char(serial#)) "COLUMN_1",
username "COLUMN_2",
sum(value) "COLUMN_3",
server "COLUMN_4",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "COLUMN_5"
from curpgauga
where server<>'DEDICATED' and
name like 'session%memory' and
name not like 'session%memory max%'
group by sid,
serial#,
username,
server,
logon_time
)
union all
(select trim(to_char(sid))||','||trim(to_char(serial#))"COLUMN_1",
username "COLUMN_2",
value "COLUMN_3",
server "COLUMN_4",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "COLUMN_5"
from curpgauga
where server='DEDICATED' and
name like 'session%memory' and
name not like 'session%memory max%' and
name not like 'session uga memory%'
);
set term on;
spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME
select 'Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session' from dual;
prompt
select 'Host Name....: '||'&THE_HOST_NAME' from dual;
select 'Instance Name: '||'&THE_NAME_OF_THE_INSTANCE' from dual;
select 'RDBMS Version: '||'&THE_RDBMS_VERSION' from dual;
select 'Startup Time.: '||to_char(min(logon_time),'YYYY-MM-DD HH24:MI:SS') from curpgauga;
select 'Current Time.: '||to_char(sysdate,'YYYY.MM.DD-HH24:MI:SS') from dual;
select 'Output File..: ORACLE_MEMORY_USAGE_SNAPSHOT_'||'&EOF_NAME' from dual;
prompt
select 'Section 1 - Worst possible value of PGA + UGA memory usage per session:' from dual;
prompt
select ' For each dedicated server session VALUE = maximum PGA.' from dual;
select ' For each shared server session VALUE = maximum PGA + maximum UGA.' from dual;
select ' Plus, all of the sessions hit their maximum values at the same time.' from dual;
select ' This scenario is unlikely to ever occur, but is still a possibility.' from dual;
prompt
set heading on
select column_1 "SID AND SERIAL#",
column_2 "USERNAME OR PROGRAM",
column_3 "VALUE",
column_4 "SERVER",
column_5 "SESSION START TIME"
from wcspgauga
order by column_3 desc, column_2, column_1;
set heading off
prompt
select 'All Sessions.......: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from wcspgauga;
select 'Background Sessions: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from wcspgauga where column_2 like '%(%)%';
select 'Foreground Sessions: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from wcspgauga where column_2 not like '%(%)%';
prompt
select 'Section 2 - Current value of current PGA + UGA memory usage per session:' from dual;
prompt
select ' For each dedicated server session VALUE = current PGA.' from dual;
select ' For each shared server session VALUE = current PGA + current UGA.' from dual;
prompt
set heading on
select column_1 "SID AND SERIAL#",
column_2 "USERNAME OR PROGRAM",
column_3 "VALUE",
column_4 "SERVER",
column_5 "SESSION START TIME"
from cwcpgauga
order by column_3 desc, column_2, column_1;
set heading off
prompt
select 'All Sessions.......: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from cwcpgauga;
select 'Background Sessions: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from cwcpgauga where column_2 like '%(%)%';
select 'Foreground Sessions: '||sum(column_3)||' bytes (total) and ~'||trunc(avg(column_3))||' bytes (average), for ~'||count(*)||' sessions.' from cwcpgauga where column_2 not like '%(%)%';
prompt
select 'Section 3 - Maximum value of PGA memory usage per session:' from dual;
prompt
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value "VALUE",
server "SERVER",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from maxpgauga
where name like 'session pga memory max%'
order by value desc, sid desc;
set heading off
prompt
select 'All Sessions.......: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%';
select 'Background Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%' and username like '%(%)%';
select 'Foreground Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%' and username not like '%(%)%';
prompt
select 'Section 4 - Maximum value of UGA memory usage per session:' from dual;
prompt
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value "VALUE",
server "SERVER",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from maxpgauga
where name like 'session uga memory max%'
order by value desc, sid desc;
set heading off
prompt
select 'All Sessions.......: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%';
select 'Background Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%' and username like '%(%)%';
select 'Foreground Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%' and username not like '%(%)%';
prompt
select 'Section 5 - Current value of PGA memory usage per session:' from dual;
prompt
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value "VALUE",
server "SERVER",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from curpgauga
where name like 'session pga memory%'
order by value desc, sid desc;
set heading off
prompt
select 'All Sessions.......: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%';
select 'Background Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%' and username like '%(%)%';
select 'Foreground Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%' and username not like '%(%)%';
prompt
select 'Section 6 - Current value of UGA memory usage per session:' from dual;
prompt
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value "VALUE",
server "SERVER",
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from curpgauga
where name like 'session uga memory%'
order by value desc, sid desc;
set heading off
prompt
select 'All Sessions.......: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%';
select 'Background Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%' and username like '%(%)%';
select 'Foreground Sessions: '||sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%' and username not like '%(%)%';
prompt
select 'Section 7 - Some current values of dynamically set initialization parameters:' from dual;
prompt
select 'Parameter: '||trim(a.ksppinm)||' Session: '||trim(b.ksppstvl)||' Instance: '||trim(c.ksppstvl)
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in (
'__cpu_count',
'__db_cache_size',
'__java_pool_size',
'__large_pool_size',
'__memory_max_target',
'__memory_target',
'__olap_page_pool_size',
'__parallel_threads_per_cpu',
'__processes',
'__pga_aggregate_limit',
'__pga_aggregate_target',
'__sessions',
'__sga_max_size',
'__sga_target',
'__shared_pool_size',
'__sort_area_size',
'__streams_pool_size',
'__use_large_pages') order by a.ksppinm;
prompt
select 'Section 8 - Some current values of single underscore parameters:' from dual;
prompt
select 'Parameter: '||trim(a.ksppinm)||' Session: '||trim(b.ksppstvl)||' Instance: '||trim(c.ksppstvl)
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in (
'_pga_limit_target_perc',
'_pga_max_size',
'_realfree_heap_pagesize',
'_realfree_heap_pagesize_hint',
'_use_ism',
'_use_ism_for_pga') order by a.ksppinm;
prompt
select 'Section 9 - Some initialization parameter values at instance startup:' from dual;
prompt
select 'Parameter: '||trim(name)||'='||value
from v$parameter
where name in ('cpu_count',
'db_cache_size',
'java_pool_size',
'job_queue_processes',
'large_pool_size',
'lock_sga',
'memory_max_target',
'memory_target',
'olap_page_pool_size',
'parallel_threads_per_cpu',
'processes',
'pga_aggregate_limit',
'pga_aggregate_target',
'sessions',
'sga_max_size',
'sga_target',
'shared_pool_size',
'sort_area_size',
'streams_pool_size',
'threaded_execution',
'use_large_pages') order by name;
prompt
select 'Current Time: '||TO_CHAR(sysdate,'YYYY.MM.DD-HH24:MI:SS') from dual;
spool off
set feedback on;
set heading on;
set linesize 80;
set pagesize 14;
set verify on;
set echo on;
Comments
Post a Comment