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@oracledatabase12g.com (DIAG) Shadow : PID: 14326 - oracle@oracledatabase12g.com (DIAG) Terminal : UNKNOWN/ UNKNOWN OS User : oracle on oracledatabase12g.com 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. 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. 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"
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277501
