Script To Monitor RDBMS Session PGA and UGA Current And Maximum Usage Over Time (Doc ID 835254.1)
In this Document
| Goal |
| Solution |
| Requirements |
| Configuring |
| Instructions |
| Sample Code |
| Sample Output |
| References |
APPLIES TO:
Oracle Cloud Infrastructure - Database Service - Version N/A and laterOracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.
GOAL
This script is intended to provide trend analysis information in the form of reports which would be used by the DBA to identify any sessions that over time use significantly more UGA and/or PGA memory than their peers by generating a snapshot report of the current values of UGA and PGA memory usage per RDBMS session as well as the maximum values of UGA and PGA memory usage since the time when each of the current RDBMS sessions started. For re-occurring ORA-04030 errors or ORA-04036 errors this script can also be a useful diagnostic tool to find out what RDBMS sessions (if any) use significantly more PGA than their peer RDBMS sessions.
SOLUTION
Requirements
This script would be executed two or more times in sequence from a SQL*Plus session connected to the RDBMS instance of interest and that is logged in under the RDBMS user named SYS. In the case of a multi-tenant environment, this script should be run from the CDB and not from a PDB.
Configuring
None
Instructions
1) Cut and paste the content of the script to a text editor such as notepad, vi, vim and save it under the name of your choice.
2) Log into the target RDBMS instance using SQL*Plus as the RDBMS user named SYS.
3) Run the script from the SQL*Plus prompt at least twice, waiting no less than 1 minute apart between executions of this script. The interval between executions depends on how quickly it is expected that significant changes in memory usage per session will occur.
Assuming that the script was named MEMORY_USAGE_SCRIPT.SQL when it was saved by the text editor of your choice and that it is located within the current working directory of the SQL*Plus RDBMS client utility, then the following command would be used to execute it from within a SQL*Plus session that is connected to the RDBMS instance of interest.
SQL> @MEMORY_USAGE_SCRIPT.SQL
4) Review in order of creation, the generated reports which will be text files in the current working directory of the SQL*Plus session that was used to run this script.
The script when run will indicate the name of the output file that would need to be reviewed and that output file will be found within the current working directory.
For the purposes of this document, "output file" or "report file" or "snapshot file" are all synonyms of the same file generated by the running of the script.
Sample Code
set echo off
--
-- PGA and UGA usage script for trend analysis.
-- Updated: 2023.07.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;
set wrap 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 wrap on;
set echo on;
Sample Output
Sample Output File Contents:
Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session
Host Name....: <HOST NAME>
Instance Name: <INSTANCE NAME>
RDBMS Version: 12.1.0.2.0
Startup Time.: 2019-01-04 09:47:18
Current Time.: 2019.01.04-11:17:10
Output File..: ORACLE_MEMORY_USAGE_SNAPSHOT_VC1020D2_20190104_111709.LST
Section 1 - Worst possible value of PGA + UGA memory usage per session:
For each dedicated server session VALUE = maximum PGA.
For each shared server session VALUE = maximum PGA + maximum UGA.
Plus, all of the sessions hit their maximum values at the same time.
This scenario is unlikely to ever occur, but is still a possibility.
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 12898616 DEDICATED 2019-01-04 09:47:19
128,28254 oracle@<HOST NAME> (CJQ0) 8769848 DEDICATED 2019-01-04 09:47:28
3,25667 oracle@<HOST NAME> (DBW0) 8385320 DEDICATED 2019-01-04 09:47:19
7,63902 oracle@<HOST NAME> (W001) 7450728 DEDICATED 2019-01-04 09:47:27
14,33967 oracle@<HOST NAME> (Q002) 4632680 DEDICATED 2019-01-04 09:47:37
134,36398 oracle@<HOST NAME>(W003) 3125352 DEDICATED 2019-01-04 09:52:48
371,15627 SYS 2797416 DEDICATED 2019-01-04 11:17:09
358,22252 oracle@<HOST NAME> (DIA0) 2526872 DEDICATED 2019-01-04 09:47:19
120,18036 oracle@<HOST NAME> (DBRM) 2347320 DEDICATED 2019-01-04 09:47:19
368,65423 oracle@<HOST NAME> (W005) 2338920 DEDICATED 2019-01-04 09:53:03
361,62612 oracle@<HOST NAME> (W000) 2273384 DEDICATED 2019-01-04 09:47:27
10,34083 oracle@<HOST NAME> (W006) 2273384 DEDICATED 2019-01-04 09:53:06
241,43261 oracle@<HOST NAME> (RECO) 2011240 DEDICATED 2019-01-04 09:47:19
136,50248 oracle@<HOST NAME> (Q003) 1749096 DEDICATED 2019-01-04 09:47:37
137,25040 oracle@<HOST NAME> (W007) 1683560 DEDICATED 2019-01-04 09:53:30
252,1476 oracle@<HOST NAME> (W002) 1618024 DEDICATED 2019-01-04 09:52:33
4,62790 oracle@<HOST NAME> (SMON) 1355880 DEDICATED 2019-01-04 09:47:19
121,36722 oracle@<HOST NAME> (LGWR) 1298744 DEDICATED 2019-01-04 09:47:19
253,40966 oracle@<HOST NAME> (W004) 1159272 DEDICATED 2019-01-04 09:53:00
240,18572 oracle@<HOST NAME> (CKPT) 1127808 DEDICATED 2019-01-04 09:47:19
242,65425 oracle@<HOST NAME> (MMNL) 1028200 DEDICATED 2019-01-04 09:47:19
127,53905 oracle@<HOST NAME> (AQPC) 897128 DEDICATED 2019-01-04 09:47:27
237,3627 oracle@<HOST NAME> (PMON) 790912 DEDICATED 2019-01-04 09:47:18
2,58854 oracle@<HOST NAME> (DIAG) 774456 DEDICATED 2019-01-04 09:47:19
119,49366 oracle@<HOST NAME> (GEN0) 774456 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 766056 DEDICATED 2019-01-04 09:47:19
122,39280 oracle@<HOST NAME> (LG01) 766056 DEDICATED 2019-01-04 09:47:19
360,24397 oracle@<HOST NAME> (LREG) 766056 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 766056 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 766056 DEDICATED 2019-01-04 09:47:18
5,26073 oracle@<HOST NAME> (PXMN) 766056 DEDICATED 2019-01-04 09:47:19
254,46203 oracle@<HOST NAME> (QM02) 766056 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 766056 DEDICATED 2019-01-04 09:47:27
6,11781 oracle@<HOST NAME> (TMON) 766056 DEDICATED 2019-01-04 09:47:26
124,30344 oracle@<HOST NAME> (TT00) 766056 DEDICATED 2019-01-04 09:47:27
239,51882 oracle@<HOST NAME> (VKRM) 766056 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 766056 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 85281288 bytes (total) and ~2304899 bytes (average), for ~37 sessions.
Background Sessions: 82483872 bytes (total) and ~2291218 bytes (average), for ~36 sessions.
Foreground Sessions: 2797416 bytes (total) and ~2797416 bytes (average), for ~1 sessions.
Section 2 - Current value of current PGA + UGA memory usage per session:
For each dedicated server session VALUE = current PGA.
For each shared server session VALUE = current PGA + current UGA.
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 11850040 DEDICATED 2019-01-04 09:47:19
3,25667 oracle@<HOST NAME> (DBW0) 7778440 DEDICATED 2019-01-04 09:47:19
14,33967 oracle@<HOST NAME> (Q002) 4632680 DEDICATED 2019-01-04 09:47:37
128,28254 oracle@<HOST NAME> (CJQ0) 2806072 DEDICATED 2019-01-04 09:47:28
371,15627 SYS 2797416 DEDICATED 2019-01-04 11:17:09
358,22252 oracle@<HOST NAME> (DIA0) 2330264 DEDICATED 2019-01-04 09:47:19
120,18036 oracle@<HOST NAME> (DBRM) 1888568 DEDICATED 2019-01-04 09:47:19
7,63902 oracle@<HOST NAME> (W001) 1552488 DEDICATED 2019-01-04 09:47:27
134,36398 oracle@<HOST NAME> (W003) 1552488 DEDICATED 2019-01-04 09:52:48
361,62612 oracle@<HOST NAME> (W000) 1404360 DEDICATED 2019-01-04 09:47:27
10,34083 oracle@<HOST NAME> (W006) 1404360 DEDICATED 2019-01-04 09:53:06
4,62790 oracle@<HOST NAME> (SMON) 1355880 DEDICATED 2019-01-04 09:47:19
368,65423 oracle@<HOST NAME> (W005) 1355880 DEDICATED 2019-01-04 09:53:03
252,1476 oracle@<HOST NAME> (W002) 1338824 DEDICATED 2019-01-04 09:52:33
121,36722 oracle@<HOST NAME> (LGWR) 1298744 DEDICATED 2019-01-04 09:47:19
136,50248 oracle@<HOST NAME> (Q003) 1159272 DEDICATED 2019-01-04 09:47:37
241,43261 oracle@<HOST NAME> (RECO) 1159272 DEDICATED 2019-01-04 09:47:19
253,40966 oracle@<HOST NAME> (W004) 1159272 DEDICATED 2019-01-04 09:53:00
240,18572 oracle@<HOST NAME> (CKPT) 1127808 DEDICATED 2019-01-04 09:47:19
137,25040 oracle@<HOST NAME> (W007) 1011144 DEDICATED 2019-01-04 09:53:30
242,65425 oracle@<HOST NAME> (MMNL) 962664 DEDICATED 2019-01-04 09:47:19
127,53905 oracle@<HOST NAME> (AQPC) 897128 DEDICATED 2019-01-04 09:47:27
237,3627 oracle@<HOST NAME> (PMON) 790912 DEDICATED 2019-01-04 09:47:18
2,58854 oracle@<HOST NAME> (DIAG) 774456 DEDICATED 2019-01-04 09:47:19
119,49366 oracle@<HOST NAME> (GEN0) 774456 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 766056 DEDICATED 2019-01-04 09:47:19
122,39280 oracle@<HOST NAME> (LG01) 766056 DEDICATED 2019-01-04 09:47:19
360,24397 oracle@<HOST NAME> (LREG) 766056 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 766056 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 766056 DEDICATED 2019-01-04 09:47:18
5,26073 oracle@<HOST NAME> (PXMN) 766056 DEDICATED 2019-01-04 09:47:19
254,46203 oracle@<HOST NAME> (QM02) 766056 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 766056 DEDICATED 2019-01-04 09:47:27
6,11781 oracle@<HOST NAME> (TMON) 766056 DEDICATED 2019-01-04 09:47:26
124,30344 oracle@<HOST NAME> (TT00) 766056 DEDICATED 2019-01-04 09:47:27
239,51882 oracle@<HOST NAME> (VKRM) 766056 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 766056 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 64355560 bytes (total) and ~1739339 bytes (average), for ~37 sessions.
Background Sessions: 61558144 bytes (total) and ~1709948 bytes (average), for ~36 sessions.
Foreground Sessions: 2797416 bytes (total) and ~2797416 bytes (average), for ~1 sessions.
Section 3 - Maximum value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 12898616 DEDICATED 2019-01-04 09:47:19
128,28254 oracle@<HOST NAME><host_name> (CJQ0) 8769848 DEDICATED 2019-01-04 09:47:28
3,25667 oracle@<HOST NAME> (DBW0) 8385320 DEDICATED 2019-01-04 09:47:19
7,63902 oracle@<HOST NAME> (W001) 7450728 DEDICATED 2019-01-04 09:47:27
14,33967 oracle@<HOST NAME> (Q002) 4632680 DEDICATED 2019-01-04 09:47:37
134,36398 oracle@<HOST NAME> (W003) 3125352 DEDICATED 2019-01-04 09:52:48
371,15627 SYS 2797416 DEDICATED 2019-01-04 11:17:09
358,22252 oracle@<HOST NAME> (DIA0) 2526872 DEDICATED 2019-01-04 09:47:19
120,18036 oracle@<HOST NAME> (DBRM) 2347320 DEDICATED 2019-01-04 09:47:19
368,65423 oracle@<HOST NAME> (W005) 2338920 DEDICATED 2019-01-04 09:53:03
361,62612 oracle@<HOST NAME> (W000) 2273384 DEDICATED 2019-01-04 09:47:27
10,34083 oracle@<HOST NAME> (W006) 2273384 DEDICATED 2019-01-04 09:53:06
241,43261 oracle@<HOST NAME> (RECO) 2011240 DEDICATED 2019-01-04 09:47:19
136,50248 oracle@<HOST NAME> (Q003) 1749096 DEDICATED 2019-01-04 09:47:37
137,25040 oracle@<HOST NAME> (W007) 1683560 DEDICATED 2019-01-04 09:53:30
252,1476 oracle@<HOST NAME> (W002) 1618024 DEDICATED 2019-01-04 09:52:33
4,62790 oracle@<HOST NAME> (SMON) 1355880 DEDICATED 2019-01-04 09:47:19
121,36722 oracle@<HOST NAME> (LGWR) 1298744 DEDICATED 2019-01-04 09:47:19
253,40966 oracle@<HOST NAME> (W004) 1159272 DEDICATED 2019-01-04 09:53:00
240,18572 oracle@<HOST NAME> (CKPT) 1127808 DEDICATED 2019-01-04 09:47:19
242,65425 oracle@<HOST NAME> (MMNL) 1028200 DEDICATED 2019-01-04 09:47:19
127,53905 oracle@<HOST NAME> (AQPC) 897128 DEDICATED 2019-01-04 09:47:27
237,3627 oracle@<HOST NAME> (PMON) 790912 DEDICATED 2019-01-04 09:47:18
119,49366 oracle@<HOST NAME> (GEN0) 774456 DEDICATED 2019-01-04 09:47:19
2,58854 oracle@<HOST NAME> (DIAG) 774456 DEDICATED 2019-01-04 09:47:19
360,24397 oracle@<HOST NAME> (LREG) 766056 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 766056 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 766056 DEDICATED 2019-01-04 09:47:18
254,46203 oracle@<HOST NAME> (QM02) 766056 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 766056 DEDICATED 2019-01-04 09:47:27
239,51882 oracle@<HOST NAME> (VKRM) 766056 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 766056 DEDICATED 2019-01-04 09:47:19
124,30344 oracle@<HOST NAME> (TT00) 766056 DEDICATED 2019-01-04 09:47:27
122,39280 oracle@<HOST NAME> (LG01) 766056 DEDICATED 2019-01-04 09:47:19
6,11781 oracle@<HOST NAME> (TMON) 766056 DEDICATED 2019-01-04 09:47:26
5,26073 oracle@<HOST NAME>(PXMN) 766056 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 766056 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 85281288 bytes (total) and ~2304899 bytes (average), for ~37 sessions.
Background Sessions: 82483872 bytes (total) and ~2291218 bytes (average), for ~36 sessions.
Foreground Sessions: 2797416 bytes (total) and ~2797416 bytes (average), for ~1 sessions.
Section 4 - Maximum value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 10694896 DEDICATED 2019-01-04 09:47:19
128,28254 oracle@<HOST NAME> (CJQ0) 2585152 DEDICATED 2019-01-04 09:47:28
134,36398 oracle@<HOST NAME> (W003) 1504672 DEDICATED 2019-01-04 09:52:48
14,33967 oracle@<HOST NAME> (Q002) 1461432 DEDICATED 2019-01-04 09:47:37
368,65423 oracle@<HOST NAME> (W005) 1439184 DEDICATED 2019-01-04 09:53:03
120,18036 oracle@<HOST NAME> (DBRM) 1396040 DEDICATED 2019-01-04 09:47:19
10,34083 oracle@<HOST NAME> (W006) 1366208 DEDICATED 2019-01-04 09:53:06
7,63902 oracle@<HOST NAME> (W001) 1300720 DEDICATED 2019-01-04 09:47:27
136,50248 oracle@<HOST NAME> (Q003) 1053696 DEDICATED 2019-01-04 09:47:37
361,62612 oracle@<HOST NAME> (W000) 922720 DEDICATED 2019-01-04 09:47:27
371,15627 SYS 872136 DEDICATED 2019-01-04 11:17:09
137,25040 oracle@<HOST NAME> (W007) 857232 DEDICATED 2019-01-04 09:53:30
252,1476 oracle@<HOST NAME> (W002) 733720 DEDICATED 2019-01-04 09:52:33
241,43261 oracle@<HOST NAME> (RECO) 733720 DEDICATED 2019-01-04 09:47:19
4,62790 oracle@<HOST NAME> (SMON) 413720 DEDICATED 2019-01-04 09:47:19
253,40966 oracle@<HOST NAME> (W004) 348232 DEDICATED 2019-01-04 09:53:00
242,65425 oracle@<HOST NAME> (MMNL) 282744 DEDICATED 2019-01-04 09:47:19
360,24397 oracle@<HOST NAME> (LREG) 217256 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 217256 DEDICATED 2019-01-04 09:47:19
358,22252 oracle@<HOST NAME> (DIA0) 217256 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 217256 DEDICATED 2019-01-04 09:47:18
254,46203 oracle@<HOST NAME> (QM02) 217256 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 217256 DEDICATED 2019-01-04 09:47:27
240,18572 oracle@<HOST NAME> (CKPT) 217256 DEDICATED 2019-01-04 09:47:19
239,51882 oracle@<HOST NAME> (VKRM) 217256 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 217256 DEDICATED 2019-01-04 09:47:19
237,3627 oracle@<HOST NAME> (PMON) 217256 DEDICATED 2019-01-04 09:47:18
127,53905 oracle@<HOST NAME> (AQPC) 217256 DEDICATED 2019-01-04 09:47:27
124,30344 oracle@<HOST NAME> (TT00) 217256 DEDICATED 2019-01-04 09:47:27
122,39280 oracle@<HOST NAME> (LG01) 217256 DEDICATED 2019-01-04 09:47:19
121,36722 oracle@<HOST NAME> (LGWR) 217256 DEDICATED 2019-01-04 09:47:19
119,49366 oracle@<HOST NAME> (GEN0) 217256 DEDICATED 2019-01-04 09:47:19
6,11781 oracle@<HOST NAME> (TMON) 217256 DEDICATED 2019-01-04 09:47:26
5,26073 oracle@<HOST NAME> (PXMN) 217256 DEDICATED 2019-01-04 09:47:19
3,25667 oracle@<HOST NAME> (DBW0) 217256 DEDICATED 2019-01-04 09:47:19
2,58854 oracle@<HOST NAME> (DIAG) 217256 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 217256 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 32311344 bytes (total) and ~873279 bytes (average), for ~37 sessions.
Background Sessions: 31439208 bytes (total) and ~873311 bytes (average), for ~36 sessions.
Foreground Sessions: 872136 bytes (total) and ~872136 bytes (average), for ~1 sessions.
Section 5 - Current value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 11850040 DEDICATED 2019-01-04 09:47:19
3,25667 oracle@<HOST NAME> (DBW0) 7778440 DEDICATED 2019-01-04 09:47:19
14,33967 oracle@<HOST NAME> (Q002) 4632680 DEDICATED 2019-01-04 09:47:37
128,28254 oracle@<HOST NAME> (CJQ0) 2806072 DEDICATED 2019-01-04 09:47:28
371,15627 SYS 2797416 DEDICATED 2019-01-04 11:17:09
358,22252 oracle@<HOST NAME> (DIA0) 2330264 DEDICATED 2019-01-04 09:47:19
120,18036 oracle@<HOST NAME> (DBRM) 1888568 DEDICATED 2019-01-04 09:47:19
134,36398 oracle@<HOST NAME> (W003) 1552488 DEDICATED 2019-01-04 09:52:48
7,63902 oracle@<HOST NAME> (W001) 1552488 DEDICATED 2019-01-04 09:47:27
361,62612 oracle@<HOST NAME> (W000) 1404360 DEDICATED 2019-01-04 09:47:27
10,34083 oracle@<HOST NAME> (W006) 1404360 DEDICATED 2019-01-04 09:53:06
368,65423 oracle@<HOST NAME> (W005) 1355880 DEDICATED 2019-01-04 09:53:03
4,62790 oracle@<HOST NAME> (SMON) 1355880 DEDICATED 2019-01-04 09:47:19
252,1476 oracle@<HOST NAME> (W002) 1338824 DEDICATED 2019-01-04 09:52:33
121,36722 oracle@<HOST NAME> (LGWR) 1298744 DEDICATED 2019-01-04 09:47:19
253,40966 oracle@<HOST NAME> (W004) 1159272 DEDICATED 2019-01-04 09:53:00
241,43261 oracle@<HOST NAME> (RECO) 1159272 DEDICATED 2019-01-04 09:47:19
136,50248 oracle@<HOST NAME> (Q003) 1159272 DEDICATED 2019-01-04 09:47:37
240,18572 oracle@<HOST NAME> (CKPT) 1127808 DEDICATED 2019-01-04 09:47:19
137,25040 oracle@<HOST NAME> (W007) 1011144 DEDICATED 2019-01-04 09:53:30
242,65425 oracle@<HOST NAME> (MMNL) 962664 DEDICATED 2019-01-04 09:47:19
127,53905 oracle@<HOST NAME> (AQPC) 897128 DEDICATED 2019-01-04 09:47:27
237,3627 oracle@<HOST NAME> (PMON) 790912 DEDICATED 2019-01-04 09:47:18
119,49366 oracle@<HOST NAME> (GEN0) 774456 DEDICATED 2019-01-04 09:47:19
2,58854 oracle@<HOST NAME> (DIAG) 774456 DEDICATED 2019-01-04 09:47:19
360,24397 oracle@<HOST NAME> (LREG) 766056 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 766056 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 766056 DEDICATED 2019-01-04 09:47:18
254,46203 oracle@<HOST NAME> (QM02) 766056 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 766056 DEDICATED 2019-01-04 09:47:27
239,51882 oracle@<HOST NAME> (VKRM) 766056 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 766056 DEDICATED 2019-01-04 09:47:19
124,30344 oracle@<HOST NAME> (TT00) 766056 DEDICATED 2019-01-04 09:47:27
122,39280 oracle@<HOST NAME> (LG01) 766056 DEDICATED 2019-01-04 09:47:19
6,11781 oracle@<HOST NAME> (TMON) 766056 DEDICATED 2019-01-04 09:47:26
5,26073 oracle@<HOST NAME> (PXMN) 766056 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 766056 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 64355560 bytes (total) and ~1739339 bytes (average), for ~37 sessions.
Background Sessions: 61558144 bytes (total) and ~1709948 bytes (average), for ~36 sessions.
Foreground Sessions: 2797416 bytes (total) and ~2797416 bytes (average), for ~1 sessions.
Section 6 - Current value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SERVER SESSION START TIME
------------------- ------------------------------------------------ ---------- --------- -------------------
123,4671 oracle@<HOST NAME> (MMON) 2306304 DEDICATED 2019-01-04 09:47:19
128,28254 oracle@<HOST NAME> (CJQ0) 1912552 DEDICATED 2019-01-04 09:47:28
14,33967 oracle@<HOST NAME> (Q002) 1461432 DEDICATED 2019-01-04 09:47:37
120,18036 oracle@<HOST NAME> (DBRM) 1265064 DEDICATED 2019-01-04 09:47:19
371,15627 SYS 872136 DEDICATED 2019-01-04 11:17:09
134,36398 oracle@<HOST NAME> (W003) 806648 DEDICATED 2019-01-04 09:52:48
368,65423 oracle@<HOST NAME> (W005) 700464 DEDICATED 2019-01-04 09:53:03
10,34083 oracle@<HOST NAME> (W006) 634976 DEDICATED 2019-01-04 09:53:06
361,62612 oracle@<HOST NAME> (W000) 610184 DEDICATED 2019-01-04 09:47:27
136,50248 oracle@<HOST NAME> (Q003) 544696 DEDICATED 2019-01-04 09:47:37
7,63902 oracle@<HOST NAME> (W001) 504008 DEDICATED 2019-01-04 09:47:27
252,1476 oracle@<HOST NAME> (W002) 438512 DEDICATED 2019-01-04 09:52:33
241,43261 oracle@<HOST NAME> (RECO) 373032 DEDICATED 2019-01-04 09:47:19
4,62790 oracle@<HOST NAME> (SMON) 348232 DEDICATED 2019-01-04 09:47:19
137,25040 oracle@<HOST NAME> (W007) 307544 DEDICATED 2019-01-04 09:53:30
253,40966 oracle@<HOST NAME> (W004) 282744 DEDICATED 2019-01-04 09:53:00
360,24397 oracle@<HOST NAME> (LREG) 217256 DEDICATED 2019-01-04 09:47:19
359,30285 oracle@<HOST NAME> (LG00) 217256 DEDICATED 2019-01-04 09:47:19
358,22252 oracle@<HOST NAME> (DIA0) 217256 DEDICATED 2019-01-04 09:47:19
356,8454 oracle@<HOST NAME> (PSP0) 217256 DEDICATED 2019-01-04 09:47:18
254,46203 oracle@<HOST NAME> (QM02) 217256 DEDICATED 2019-01-04 09:47:37
243,40712 oracle@<HOST NAME> (SMCO) 217256 DEDICATED 2019-01-04 09:47:27
242,65425 oracle@<HOST NAME> (MMNL) 217256 DEDICATED 2019-01-04 09:47:19
240,18572 oracle@<HOST NAME> (CKPT) 217256 DEDICATED 2019-01-04 09:47:19
239,51882 oracle@<HOST NAME> (VKRM) 217256 DEDICATED 2019-01-04 09:47:19
238,12470 oracle@<HOST NAME> (MMAN) 217256 DEDICATED 2019-01-04 09:47:19
237,3627 oracle@<HOST NAME> (PMON) 217256 DEDICATED 2019-01-04 09:47:18
127,53905 oracle@<HOST NAME> (AQPC) 217256 DEDICATED 2019-01-04 09:47:27
124,30344 oracle@<HOST NAME> (TT00) 217256 DEDICATED 2019-01-04 09:47:27
122,39280 oracle@<HOST NAME> (LG01) 217256 DEDICATED 2019-01-04 09:47:19
121,36722 oracle@<HOST NAME> (LGWR) 217256 DEDICATED 2019-01-04 09:47:19
119,49366 oracle@<HOST NAME> (GEN0) 217256 DEDICATED 2019-01-04 09:47:19
6,11781 oracle@<HOST NAME> (TMON) 217256 DEDICATED 2019-01-04 09:47:26
5,26073 oracle@<HOST NAME> (PXMN) 217256 DEDICATED 2019-01-04 09:47:19
3,25667 oracle@<HOST NAME> (DBW0) 217256 DEDICATED 2019-01-04 09:47:19
2,58854 oracle@<HOST NAME> (DIAG) 217256 DEDICATED 2019-01-04 09:47:19
1,10647 oracle@<HOST NAME> (VKTM) 217256 DEDICATED 2019-01-04 09:47:19
All Sessions.......: 17930904 bytes (total) and ~484619 bytes (average), for ~37 sessions.
Background Sessions: 17058768 bytes (total) and ~473854 bytes (average), for ~36 sessions.
Foreground Sessions: 872136 bytes (total) and ~872136 bytes (average), for ~1 sessions.
Section 7 - Some current values of dynamically set initialization parameters:
Parameter: __db_cache_size Session: 734003200 Instance: 734003200
Parameter: __java_pool_size Session: 4194304 Instance: 4194304
Parameter: __large_pool_size Session: 20971520 Instance: 20971520
Parameter: __pga_aggregate_target Session: 58720256 Instance: 58720256
Parameter: __sga_target Session: 1073741824 Instance: 1073741824
Parameter: __shared_pool_size Session: 251658240 Instance: 251658240
Parameter: __streams_pool_size Session: 8388608 Instance: 8388608
Section 8 - Some current values of single underscore parameters:
Parameter: _pga_limit_target_perc Session: 200 Instance: 200
Parameter: _pga_max_size Session: 209715200 Instance: 209715200
Parameter: _use_ism Session: TRUE Instance: TRUE
Parameter: _use_ism_for_pga Session: TRUE Instance: TRUE
Section 9 - Some initialization parameter values at instance startup:
Parameter: cpu_count=4
Parameter: db_cache_size=0
Parameter: java_pool_size=0
Parameter: job_queue_processes=1000
Parameter: large_pool_size=0
Parameter: lock_sga=FALSE
Parameter: memory_max_target=0
Parameter: memory_target=0
Parameter: olap_page_pool_size=0
Parameter: parallel_threads_per_cpu=2
Parameter: pga_aggregate_limit=2147483648
Parameter: pga_aggregate_target=58720256
Parameter: processes=300
Parameter: sessions=472
Parameter: sga_max_size=1073741824
Parameter: sga_target=1073741824
Parameter: shared_pool_size=0
Parameter: sort_area_size=65536
Parameter: streams_pool_size=0
Parameter: threaded_execution=FALSE
Parameter: use_large_pages=TRUE
Current Time: 2019.01.04-11:17:10
Comments
Post a Comment