Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]Information in this document applies to any platform.
PURPOSE
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.
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 SYSTEM, or any RDBMS user with privileges to select from v$parameter, v$session, v$sesstat and v$statname and space quota available for the default tablespace of the RDBMS user that will be used to run this script.
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 SYSTEM.
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 snapshot report files will have names that follow the naming pattern shown below, where sid reprsents the Oracle RDBMS system identifier of the instance from which statistics were gathered and yyyymmdd_hhmiss represents the date and time that the report was generated.
ORACLE_MEMORY_USAGE_SNAPSHOT_sid_yyyymmdd_hhmiss.LST
CAUTION
This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
SAMPLE CODE
set echo off;
set feedback off;
set heading off;
set linesize 128;
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.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.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%';
set term on;
spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME
select 'Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session' from dual;
select 'Host........: '||'&THE_HOST_NAME' from dual;
select 'Name........: '||'&THE_NAME_OF_THE_INSTANCE' from dual;
select '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 'Worst possible value of concurrent PGA + UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
sum(value),
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from maxpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select 'Worst possible total and average values of concurrent PGA + UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from maxpgauga;
select 'Approximate value of current PGA + UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
sum(value),
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from curpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select 'Current total and average values of concurrent PGA + UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from curpgauga;
select 'Maximum value of PGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Worst possible total and average values of concurrent PGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%';
select 'Maximum value of UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Worst possible total and average values of concurrent UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%';
select 'Current value of PGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Current total and average values of concurrent PGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%';
select 'Current value of UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Current total and average values of concurrent UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%';
select 'Current SGA structure sizings:' from dual;
show sga
select 'Some initialization parameter values at instance startup:' from dual;
select trim(name)||'='||value
from v$parameter
where name in ('java_pool_size',
'large_pool_size',
'olap_page_pool_size',
'pga_aggregate_target',
'sga_max_size',
'sga_target',
'shared_pool_size',
'sort_area_size',
'streams_pool_size') order by name;
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;
set feedback off;
set heading off;
set linesize 128;
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.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.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%';
set term on;
spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME
select 'Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session' from dual;
select 'Host........: '||'&THE_HOST_NAME' from dual;
select 'Name........: '||'&THE_NAME_OF_THE_INSTANCE' from dual;
select '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 'Worst possible value of concurrent PGA + UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
sum(value),
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from maxpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select 'Worst possible total and average values of concurrent PGA + UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from maxpgauga;
select 'Approximate value of current PGA + UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
sum(value),
to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') "SESSION START TIME"
from curpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select 'Current total and average values of concurrent PGA + UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||trunc(count(*)/2)||' sessions.' from curpgauga;
select 'Maximum value of PGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Worst possible total and average values of concurrent PGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session pga memory max%';
select 'Maximum value of UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Worst possible total and average values of concurrent UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from maxpgauga where name like 'session uga memory max%';
select 'Current value of PGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Current total and average values of concurrent PGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session pga memory%';
select 'Current value of UGA memory usage per session:' from dual;
set heading on
select trim(to_char(sid))||','||trim(to_char(serial#)) "SID AND SERIAL#",
username "USERNAME OR PROGRAM",
value,
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
select 'Current total and average values of concurrent UGA memory usage:' from dual;
select sum(value)||' bytes (total) and ~'||trunc(avg(value))||' bytes (average), for ~'||count(*)||' sessions.' from curpgauga where name like 'session uga memory%';
select 'Current SGA structure sizings:' from dual;
show sga
select 'Some initialization parameter values at instance startup:' from dual;
select trim(name)||'='||value
from v$parameter
where name in ('java_pool_size',
'large_pool_size',
'olap_page_pool_size',
'pga_aggregate_target',
'sga_max_size',
'sga_target',
'shared_pool_size',
'sort_area_size',
'streams_pool_size') order by name;
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;
SAMPLE OUTPUT
Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session
Host........: suppnw2
Name........: VB107UTF
Version.....: 11.1.0.7.0
Startup Time: 2009-05-29 16:19:41
Current Time: 2009.05.29-18:27:45
Worst possible value of concurrent PGA + UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11785268 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 7627152 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 6662124 2009-05-29 16:24:47
159,1 oracle@suppnw2 (SMON) 5755684 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 5378576 2009-05-29 16:19:41
139,451 SYS 4801104 2009-05-29 18:16:31
151,1 oracle@suppnw2 (FBDA) 4543392 2009-05-29 16:19:46
146,3 oracle@suppnw2 (q000) 4325628 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 3693188 2009-05-29 16:19:59
158,1 oracle@suppnw2 (RECO) 2012136 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 1954868 2009-05-29 18:27:44
152,2 oracle@suppnw2 (QMNC) 1553600 2009-05-29 16:19:46
160,1 oracle@suppnw2 (CKPT) 1175748 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 840756 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 775148 2009-05-29 18:24:57
156,1 oracle@suppnw2 (MMNL) 775148 2009-05-29 16:19:41
166,1 oracle@suppnw2 (DBRM) 709612 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 513076 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 513076 2009-05-29 16:24:47
169,1 oracle@suppnw2 (PMON) 513076 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 513076 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 513076 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 513076 2009-05-29 16:19:41
Worst possible total and average values of concurrent PGA + UGA memory usage:
67447588 bytes (total) and ~1466251 bytes (average), for ~23 sessions.
Approximate value of current PGA + UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11654196 2009-05-29 16:19:41
151,1 oracle@suppnw2 (FBDA) 3641272 2009-05-29 16:19:46
162,1 oracle@suppnw2 (DBW0) 3478032 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 3247408 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2602236 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 2511944 2009-05-29 16:24:47
139,451 SYS 2485608 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 2034996 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 1830900 2009-05-29 16:19:59
158,1 oracle@suppnw2 (RECO) 1233612 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 1175748 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 840612 2009-05-29 16:19:46
165,1 oracle@suppnw2 (DIA0) 823728 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 775148 2009-05-29 18:24:57
156,1 oracle@suppnw2 (MMNL) 775148 2009-05-29 16:19:41
166,1 oracle@suppnw2 (DBRM) 709612 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 644148 2009-05-29 18:27:44
163,1 oracle@suppnw2 (MMAN) 513076 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 513076 2009-05-29 16:24:47
169,1 oracle@suppnw2 (PMON) 513076 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 513076 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 513076 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 513076 2009-05-29 16:19:41
Current total and average values of concurrent PGA + UGA memory usage:
43542804 bytes (total) and ~946582 bytes (average), for ~23 sessions.
Maximum value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11680128 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 5273436 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 4781820 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 4536704 2009-05-29 16:24:47
151,1 oracle@suppnw2 (FBDA) 3602172 2009-05-29 16:19:46
159,1 oracle@suppnw2 (SMON) 3225984 2009-05-29 16:19:41
139,451 SYS 3045992 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 2505088 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 2308480 2009-05-29 16:19:59
141,120 oracle@suppnw2 (J000) 1849728 2009-05-29 18:27:44
158,1 oracle@suppnw2 (RECO) 1194368 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 1070608 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 932224 2009-05-29 16:19:46
165,1 oracle@suppnw2 (DIA0) 735616 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 604544 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 604544 2009-05-29 18:24:57
166,1 oracle@suppnw2 (DBRM) 539008 2009-05-29 16:19:41
169,1 oracle@suppnw2 (PMON) 407936 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 407936 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 407936 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 407936 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 407936 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 407936 2009-05-29 16:24:47
Worst possible total and average values of concurrent PGA memory usage:
50938060 bytes (total) and ~2214698 bytes (average), for ~23 sessions.
Maximum value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
157,1 oracle@suppnw2 (MMON) 2845332 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2529700 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 2125420 2009-05-29 16:24:47
146,3 oracle@suppnw2 (q000) 1820540 2009-05-29 16:19:56
139,451 SYS 1755112 2009-05-29 18:16:31
148,6 oracle@suppnw2 (q001) 1384708 2009-05-29 16:19:59
151,1 oracle@suppnw2 (FBDA) 941220 2009-05-29 16:19:46
158,1 oracle@suppnw2 (RECO) 817768 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 621376 2009-05-29 16:19:46
166,1 oracle@suppnw2 (DBRM) 170604 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 170604 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 170604 2009-05-29 18:24:57
169,1 oracle@suppnw2 (PMON) 105140 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 105140 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 105140 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 105140 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 105140 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 105140 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 105140 2009-05-29 16:19:41
161,1 oracle@suppnw2 (LGWR) 105140 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 105140 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 105140 2009-05-29 16:24:47
141,120 oracle@suppnw2 (J000) 105140 2009-05-29 18:27:44
Worst possible total and average values of concurrent UGA memory usage:
16509528 bytes (total) and ~717805 bytes (average), for ~23 sessions.
Current value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11549056 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 3372892 2009-05-29 16:19:41
151,1 oracle@suppnw2 (FBDA) 3077884 2009-05-29 16:19:46
157,1 oracle@suppnw2 (MMON) 2094844 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2046336 2009-05-29 16:19:41
139,451 SYS 1718244 2009-05-29 18:16:31
145,5 oracle@suppnw2 (CJQ0) 1636092 2009-05-29 16:24:47
146,3 oracle@suppnw2 (q000) 1390976 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 1194368 2009-05-29 16:19:59
160,1 oracle@suppnw2 (CKPT) 1070608 2009-05-29 16:19:41
158,1 oracle@suppnw2 (RECO) 801152 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 718588 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 604544 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 604544 2009-05-29 16:19:46
142,18 oracle@suppnw2 (W000) 604544 2009-05-29 18:24:57
166,1 oracle@suppnw2 (DBRM) 539008 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 539008 2009-05-29 18:27:44
169,1 oracle@suppnw2 (PMON) 407936 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 407936 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 407936 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 407936 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 407936 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 407936 2009-05-29 16:24:47
Current total and average values of concurrent PGA memory usage:
36010304 bytes (total) and ~1565665 bytes (average), for ~23 sessions.
Current value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
157,1 oracle@suppnw2 (MMON) 1152564 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 875852 2009-05-29 16:24:47
139,451 SYS 767364 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 644020 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 636532 2009-05-29 16:19:59
151,1 oracle@suppnw2 (FBDA) 563388 2009-05-29 16:19:46
159,1 oracle@suppnw2 (SMON) 555900 2009-05-29 16:19:41
158,1 oracle@suppnw2 (RECO) 432460 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 236068 2009-05-29 16:19:46
166,1 oracle@suppnw2 (DBRM) 170604 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 170604 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 170604 2009-05-29 18:24:57
169,1 oracle@suppnw2 (PMON) 105140 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 105140 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 105140 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 105140 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 105140 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 105140 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 105140 2009-05-29 16:19:41
161,1 oracle@suppnw2 (LGWR) 105140 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 105140 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 105140 2009-05-29 16:24:47
141,120 oracle@suppnw2 (J000) 105140 2009-05-29 18:27:44
Current total and average values of concurrent UGA memory usage:
7532500 bytes (total) and ~327500 bytes (average), for ~23 sessions.
Current SGA structure sizings:
Total System Global Area 209235968 bytes
Fixed Size 1312388 bytes
Variable Size 163580284 bytes
Database Buffers 41943040 bytes
Redo Buffers 2400256 bytes
Some initialization parameter values at instance startup:
java_pool_size=0
large_pool_size=0
olap_page_pool_size=0
large_pool_size=0
pga_aggregate_target=58720256
sga_max_size=276824064
sga_target=209715200
shared_pool_size=0
sort_area_size=65536
streams_pool_size=0
Current Time: 2009.05.29-18:27:46
(文件 ID 835254.1)
Host........: suppnw2
Name........: VB107UTF
Version.....: 11.1.0.7.0
Startup Time: 2009-05-29 16:19:41
Current Time: 2009.05.29-18:27:45
Worst possible value of concurrent PGA + UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11785268 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 7627152 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 6662124 2009-05-29 16:24:47
159,1 oracle@suppnw2 (SMON) 5755684 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 5378576 2009-05-29 16:19:41
139,451 SYS 4801104 2009-05-29 18:16:31
151,1 oracle@suppnw2 (FBDA) 4543392 2009-05-29 16:19:46
146,3 oracle@suppnw2 (q000) 4325628 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 3693188 2009-05-29 16:19:59
158,1 oracle@suppnw2 (RECO) 2012136 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 1954868 2009-05-29 18:27:44
152,2 oracle@suppnw2 (QMNC) 1553600 2009-05-29 16:19:46
160,1 oracle@suppnw2 (CKPT) 1175748 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 840756 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 775148 2009-05-29 18:24:57
156,1 oracle@suppnw2 (MMNL) 775148 2009-05-29 16:19:41
166,1 oracle@suppnw2 (DBRM) 709612 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 513076 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 513076 2009-05-29 16:24:47
169,1 oracle@suppnw2 (PMON) 513076 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 513076 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 513076 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 513076 2009-05-29 16:19:41
Worst possible total and average values of concurrent PGA + UGA memory usage:
67447588 bytes (total) and ~1466251 bytes (average), for ~23 sessions.
Approximate value of current PGA + UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11654196 2009-05-29 16:19:41
151,1 oracle@suppnw2 (FBDA) 3641272 2009-05-29 16:19:46
162,1 oracle@suppnw2 (DBW0) 3478032 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 3247408 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2602236 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 2511944 2009-05-29 16:24:47
139,451 SYS 2485608 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 2034996 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 1830900 2009-05-29 16:19:59
158,1 oracle@suppnw2 (RECO) 1233612 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 1175748 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 840612 2009-05-29 16:19:46
165,1 oracle@suppnw2 (DIA0) 823728 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 775148 2009-05-29 18:24:57
156,1 oracle@suppnw2 (MMNL) 775148 2009-05-29 16:19:41
166,1 oracle@suppnw2 (DBRM) 709612 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 644148 2009-05-29 18:27:44
163,1 oracle@suppnw2 (MMAN) 513076 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 513076 2009-05-29 16:24:47
169,1 oracle@suppnw2 (PMON) 513076 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 513076 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 513076 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 513076 2009-05-29 16:19:41
Current total and average values of concurrent PGA + UGA memory usage:
43542804 bytes (total) and ~946582 bytes (average), for ~23 sessions.
Maximum value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11680128 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 5273436 2009-05-29 16:19:41
157,1 oracle@suppnw2 (MMON) 4781820 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 4536704 2009-05-29 16:24:47
151,1 oracle@suppnw2 (FBDA) 3602172 2009-05-29 16:19:46
159,1 oracle@suppnw2 (SMON) 3225984 2009-05-29 16:19:41
139,451 SYS 3045992 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 2505088 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 2308480 2009-05-29 16:19:59
141,120 oracle@suppnw2 (J000) 1849728 2009-05-29 18:27:44
158,1 oracle@suppnw2 (RECO) 1194368 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 1070608 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 932224 2009-05-29 16:19:46
165,1 oracle@suppnw2 (DIA0) 735616 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 604544 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 604544 2009-05-29 18:24:57
166,1 oracle@suppnw2 (DBRM) 539008 2009-05-29 16:19:41
169,1 oracle@suppnw2 (PMON) 407936 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 407936 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 407936 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 407936 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 407936 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 407936 2009-05-29 16:24:47
Worst possible total and average values of concurrent PGA memory usage:
50938060 bytes (total) and ~2214698 bytes (average), for ~23 sessions.
Maximum value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
157,1 oracle@suppnw2 (MMON) 2845332 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2529700 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 2125420 2009-05-29 16:24:47
146,3 oracle@suppnw2 (q000) 1820540 2009-05-29 16:19:56
139,451 SYS 1755112 2009-05-29 18:16:31
148,6 oracle@suppnw2 (q001) 1384708 2009-05-29 16:19:59
151,1 oracle@suppnw2 (FBDA) 941220 2009-05-29 16:19:46
158,1 oracle@suppnw2 (RECO) 817768 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 621376 2009-05-29 16:19:46
166,1 oracle@suppnw2 (DBRM) 170604 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 170604 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 170604 2009-05-29 18:24:57
169,1 oracle@suppnw2 (PMON) 105140 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 105140 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 105140 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 105140 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 105140 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 105140 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 105140 2009-05-29 16:19:41
161,1 oracle@suppnw2 (LGWR) 105140 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 105140 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 105140 2009-05-29 16:24:47
141,120 oracle@suppnw2 (J000) 105140 2009-05-29 18:27:44
Worst possible total and average values of concurrent UGA memory usage:
16509528 bytes (total) and ~717805 bytes (average), for ~23 sessions.
Current value of PGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
161,1 oracle@suppnw2 (LGWR) 11549056 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 3372892 2009-05-29 16:19:41
151,1 oracle@suppnw2 (FBDA) 3077884 2009-05-29 16:19:46
157,1 oracle@suppnw2 (MMON) 2094844 2009-05-29 16:19:41
159,1 oracle@suppnw2 (SMON) 2046336 2009-05-29 16:19:41
139,451 SYS 1718244 2009-05-29 18:16:31
145,5 oracle@suppnw2 (CJQ0) 1636092 2009-05-29 16:24:47
146,3 oracle@suppnw2 (q000) 1390976 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 1194368 2009-05-29 16:19:59
160,1 oracle@suppnw2 (CKPT) 1070608 2009-05-29 16:19:41
158,1 oracle@suppnw2 (RECO) 801152 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 718588 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 604544 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 604544 2009-05-29 16:19:46
142,18 oracle@suppnw2 (W000) 604544 2009-05-29 18:24:57
166,1 oracle@suppnw2 (DBRM) 539008 2009-05-29 16:19:41
141,120 oracle@suppnw2 (J000) 539008 2009-05-29 18:27:44
169,1 oracle@suppnw2 (PMON) 407936 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 407936 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 407936 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 407936 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 407936 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 407936 2009-05-29 16:24:47
Current total and average values of concurrent PGA memory usage:
36010304 bytes (total) and ~1565665 bytes (average), for ~23 sessions.
Current value of UGA memory usage per session:
SID AND SERIAL# USERNAME OR PROGRAM VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
157,1 oracle@suppnw2 (MMON) 1152564 2009-05-29 16:19:41
145,5 oracle@suppnw2 (CJQ0) 875852 2009-05-29 16:24:47
139,451 SYS 767364 2009-05-29 18:16:31
146,3 oracle@suppnw2 (q000) 644020 2009-05-29 16:19:56
148,6 oracle@suppnw2 (q001) 636532 2009-05-29 16:19:59
151,1 oracle@suppnw2 (FBDA) 563388 2009-05-29 16:19:46
159,1 oracle@suppnw2 (SMON) 555900 2009-05-29 16:19:41
158,1 oracle@suppnw2 (RECO) 432460 2009-05-29 16:19:41
152,2 oracle@suppnw2 (QMNC) 236068 2009-05-29 16:19:46
166,1 oracle@suppnw2 (DBRM) 170604 2009-05-29 16:19:41
156,1 oracle@suppnw2 (MMNL) 170604 2009-05-29 16:19:41
142,18 oracle@suppnw2 (W000) 170604 2009-05-29 18:24:57
169,1 oracle@suppnw2 (PMON) 105140 2009-05-29 16:19:41
168,1 oracle@suppnw2 (VKTM) 105140 2009-05-29 16:19:41
167,1 oracle@suppnw2 (DIAG) 105140 2009-05-29 16:19:41
165,1 oracle@suppnw2 (DIA0) 105140 2009-05-29 16:19:41
164,1 oracle@suppnw2 (PSP0) 105140 2009-05-29 16:19:41
163,1 oracle@suppnw2 (MMAN) 105140 2009-05-29 16:19:41
162,1 oracle@suppnw2 (DBW0) 105140 2009-05-29 16:19:41
161,1 oracle@suppnw2 (LGWR) 105140 2009-05-29 16:19:41
160,1 oracle@suppnw2 (CKPT) 105140 2009-05-29 16:19:41
143,7 oracle@suppnw2 (SMCO) 105140 2009-05-29 16:24:47
141,120 oracle@suppnw2 (J000) 105140 2009-05-29 18:27:44
Current total and average values of concurrent UGA memory usage:
7532500 bytes (total) and ~327500 bytes (average), for ~23 sessions.
Current SGA structure sizings:
Total System Global Area 209235968 bytes
Fixed Size 1312388 bytes
Variable Size 163580284 bytes
Database Buffers 41943040 bytes
Redo Buffers 2400256 bytes
Some initialization parameter values at instance startup:
java_pool_size=0
large_pool_size=0
olap_page_pool_size=0
large_pool_size=0
pga_aggregate_target=58720256
sga_max_size=276824064
sga_target=209715200
shared_pool_size=0
sort_area_size=65536
streams_pool_size=0
Current Time: 2009.05.29-18:27:46
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24585765/viewspace-1193175/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- How to Monitor UGA, PGA and Cursor Usage Per SessionSession
- 獲取指定pid的session的pga/uga使用情況Session
- Oracle RDBMS History Over the YearsOracle
- PGA/UGA、SGA的一點知識
- [Shell] Linux monitor tablespace usageLinux
- 4.Linux monitor tablespace usageLinux
- [Shell] Monitor filesystem usage & delete expire filedelete
- makes the current sessionSession
- 5.Monitor filesystem usage & delete expire filedelete
- gc current block pin time gc current block flush time 疑惑GCBloC
- mysql CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMPMySql
- oracle event heapdump_sga dump_shared pool_pga_ugaOracle
- Monitor Current SQL Running(10g)SQL
- [Oracle Script] Undo Usage Per sessionOracleSession
- Maximum No. Of SAP Session Per UserSession
- AUDIT IN SESSION CURRENT無法NOAUDITSession
- Oracle Real Time SQL MonitorOracleSQL
- Procedure PqStat to monitor Current PX Queries (Doc ID 240762.1)
- 1.Monitor Current SQL Running(10g)SQL
- 關於華碩主機板“USB Devices Over Current Status Detected!”dev
- MySQL中的CURRENT_TIMESTAMPMySql
- 【SESSION】v$session and v$license 中sessions_current 的區別Session
- hibernate.current_session_context_classSessionContext
- AUTHID DEFINER\authid current_user與alter session set current_schemaSession
- [Shell] Linux monitor OS process and DB sessionLinuxSession
- 【故障-ORACLE】rdbms ipc message timeout解釋Oracle
- [20210315]acknowledge over PGA limit.txtMIT
- 6.Monitor Linux OS process and DB sessionLinuxSession
- ORA-13639: The CURRENT operation was interrupted because it timed OUT
- 【Mysql】為MySQL增加執行緒記憶體監控 (MySQL Thread Memory Usage Monitor)MySql執行緒記憶體thread
- 教你如何使用MySQL中CURRENT_TIMESTAMP時間戳MySql時間戳
- Job: TO_CHAR(CURRENT_TIMESTAMP, 'HH24MISSFF') errorError
- SpringMVC @Transactional的陷井大坑引發No Session found for current threadSpringMVCSessionthread
- [轉]Setup Session Timeout In Oracle BIEESessionOracle
- TimesTen記憶體庫monitor表引數說明記憶體
- 使用dbms_monitor.session_trace_enable跟蹤一個會話Session會話
- 異常資訊解決：Could not obtain transaction-synchronized Session for current threadAIsynchronizedSessionthread
- Could not obtain transaction-synchronized Session for current thread原因及解決方案AIsynchronizedSessionthread