_trace_buffer

Found that background process of diag is occupied high pga memory usage in RAC of node 1.
Value of PGA memory usage is captured by “select sid, name, value from v$statname n, v$sesstat s where n.statistic# = s.statistic# and n.name like ‘%memory%’ and s.sid=481order by sid;”

Why occuried high pga memory usage of background process of diag in node 1??

====================================================================================================
SID/Serial : 481,1
Foreground : PID: 14326 – oracle@askmac.cn (DIAG)
Shadow : PID: 14326 – oracle@askmac.cn (DIAG)
Terminal : UNKNOWN/ UNKNOWN
OS User : oracle on askmac.cn
Ora User :
Status Flags: ACTIVE DEDICATED BACKGROUND
Tran Active : NONE
Login Time : Fri 17:10:26
Last Call : Fri 17:10:27 – 8,251.4 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
Previous SQL statement:
Session Waits:
EVENT P2TEXT P2 seconds_in_w
—————————– ———— ———— ————
DIAG idle wait where 1 0
====================================================================================================

RAC-node 1
===========

SID NAME VALUE
———- —————————————————————- ———-
481 session uga memory 180984
481 session uga memory max 180984
481 session pga memory 1647496248
481 session pga memory max 1647496248
481 redo k-bytes read (memory) 0
481 redo k-bytes read (memory) by LNS 0
481 workarea memory allocated 0
481 sorts (memory) 0

RAC-node 2
===========

SID NAME VALUE
———- —————————————————————- ———-
481 session uga memory 180984
481 session uga memory max 180984
481 session pga memory 5950520
481 session pga memory max 5950520
481 redo k-bytes read (memory) 0
481 redo k-bytes read (memory) by LNS 0
481 workarea memory allocated 0
481 sorts (memory) 0

Bug 5092124 : PGA MEMORY FOR DIAG PROCESS LEAKS WHEN DUMPING KST TRACE

1. Please provide the output of the following query:
sql> select a.sid,a.program,b.name,c.value from v$session a,v$sysstat b,v$sesstat c where a.program like ‘%DIAG%’ and a.sid = c.sid and b.name like ‘%pga%’ and b.statistic# = c.statistic#;

2. Provide the output of the following command:
ps -ef | grep diag

3. Perform following test case:

1. Confirm the size of DIAG’s PGA.
.
select a.sid,a.program,b.name,c.value from v$session a,v$sysstat b,v$sesstat c where a.program like ‘%DIAG%’ and a.sid = c.sid and b.name like ‘%pga%’
and b.statistic# = c.statistic#;
.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 798524
169 oracle@jpdel1380 (DIAG) session pga memory max 798524
.
2. Connect 50 sessions via sqlplus.
.
3. Kill one of shadow process.
.
Eg.
% ps -ef | grep rac1022
rac1022 15626 15618 0 20:31 ? 00:00:00 oraclerac10221
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
.
% kill -11 15626
.
4. DIAG dump KST traces under cdmp_xxxxx directory.
.
5. Confirm the size of DIAG’s PGA.
.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 2699068
169 oracle@jpdel1380 (DIAG) session pga memory max 2699068
.
6. Perform the same steps as 2-5.
Confirm the size of DIAG’s PGA.
SID PROGRAM NAME VALUE
—– ———————– ———————- ———-
169 oracle@jpdel1380 (DIAG) session pga memory 3944252
169 oracle@jpdel1380 (DIAG) session pga memory max 3944252

==> PGA for DIAG process increases.

2. Provide the output of the following command:
ps -ef | grep diag

3. Perform following test case:

==> PGA for DIAG process increases.

1. AWR report of one hour from all the instances when the pga usage is high by diag.
2. Database alert.log file from all the instances.
3. init.ora or spfile used in the db.
4. output of the following :
show parameter “_trace_buffer”

DIAG Background process may consume Large PGA Size