Probe how does your PGA consume

2011/04/21 by 5 Comments

前2天有客户报一套10.2.0.3的数据库个别服务进程PGA使用量暴涨，疑似内存泄露(memory leak)；遂提供on-site service，赶到用户现场时问题进程已经消失，系统内存使用量恢复正常，客户之前除了保留了v$process动态性能视图的信息外未抓取其他有用的诊断信息。

查看保存的v$process视图信息可以看到进程991714的PGA内存使用量达到13个G：

select spid,program,PGA_USED_MEM,PGA_ALLOC_MEM from v$process;
SPID                     PROGRAM                                          PGA_USED_MEM PGA_ALLOC_MEM
------------------------ ------------------------------------------------ ------------ -------------
991714                         oracleBTS@oam_app_a             14427510986 14432001786

oracle@oam_app_a@/oracle/product/10.2.0/dbs $ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        4194304
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) unlimited

SQL> select x.ksppinm name,y.ksppstvl value
  2  from sys.x$ksppi x, sys.x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id = USERENV('Instance')
  5  and x.indx = y.indx
  6  and x.ksppinm like '%pga%';

pga_aggregate_target
200715200

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_pga_max_size
209715200

SQL> select x.ksppinm name,y.ksppstvl value
  2  from sys.x$ksppi x, sys.x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id = USERENV('Instance')
  5  and x.indx = y.indx
  6  and x.ksppinm like '%hash_join%';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_hash_join_enabled
TRUE

SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fileio_network_adapters              string
workarea_size_policy                 string      AUTO

可以看到该系统使用自动PGA管理且pga_aggregate_target参数值为较小的191M，查询隐藏参数_pga_max_size可发现该参数值也为191M。

从告警日志alert.log中找不到任何信息，单个服务进程PGA使用量达到13G居然没有报ORA-04030错误!

到实例的user_dump目录下ls -ltr了一把有意外收获，找到了该991714进程最近的trace文件:

Dump file /oracle/product/10.2.0/admin/BTS/udump/bts_ora_991714.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2.0
System name:    AIX
Node name:      oam_app_a
Release:        3
Version:        5
Machine:        0000E5DBD600
Instance name: BTS
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 991714, image: oracleBTS@oam_app_a

*** 2011-04-19 18:27:07.766
*** SERVICE NAME:(SYS$USERS) 2011-04-19 18:27:07.733
*** SESSION ID:(248.45987) 2011-04-19 18:27:07.733
WARNING: out of private memory [1]

以上trace文件中唯一有用的信息就是”WARNING: out of private memory [1]”了，但在metalink上搜索”out of private memory”找不到任何有用的信息；因为未发生ORA-04030错误，而用户也没有手动去收集过PGA Heap的堆使用情况，所以未产生任何对该进程PGA内存使用细节描述的转储信息文件，难以从dump信息中获取线索继续探索，线索断裂。

通过查询PGA状态历史记录视图可以发现在之前的2个快照时间窗口内，inused PGA总量为13G，之后PGA内存使用量又恢复正常:

select * from dba_hist_pgastat where name='total PGA inuse' order by value asc
 SNAP_ID       DBID INSTANCE_NUMBER NAME                                                                  VALUE
---------- ---------- --------------- ---------------------------------------------------------------- ----------
     16048 3731271451               1 total PGA inuse                                                  1.4883E+10
     16047 3731271451               1 total PGA inuse                                                  1.4888E+10

查询在以上时间窗口中991714的活动会话记录，可以发现该进程一直处于”cursor: pin S wait on X”中；Oracle 10.2.0.2以后利用mutex来替代latch保护SQL游标，当硬解析发生时会出现”cursor: pin s wait on X”等待。这里可以看到需要硬解析的SQL的ID为”bp7y9fvtqra1h”，而且此处SQL_CHILD_NUMBER为-1，说明Oracle共享池中之前没有缓存该SQL游标，该问题进程是首次为该SQL语句执行硬解析。

     	SNAP_ID	DBID	INSTANCE_NUMBER	SAMPLE_ID	SAMPLE_TIME	SESSION_ID	SESSION_SERIAL#	USER_ID	SQL_ID	SQL_CHILD_NUMBER	SQL_PLAN_HASH_VALUE	FORCE_MATCHING_SIGNATURE	SQL_OPCODE	PLSQL_ENTRY_OBJECT_ID	PLSQL_ENTRY_SUBPROGRAM_ID	PLSQL_OBJECT_ID	PLSQL_SUBPROGRAM_ID	SERVICE_HASH	SESSION_TYPE	SESSION_STATE	QC_SESSION_ID	QC_INSTANCE_ID	BLOCKING_SESSION	BLOCKING_SESSION_STATUS	BLOCKING_SESSION_SERIAL#	EVENT	EVENT_ID	SEQ#	P1TEXT	P1	P2TEXT	P2	P3TEXT	P3	WAIT_CLASS	WAIT_CLASS_ID	WAIT_TIME	TIME_WAITED	XID	CURRENT_OBJ#	CURRENT_FILE#	CURRENT_BLOCK#	PROGRAM	MODULE	ACTION	CLIENT_ID
1	16047	3731271451	1	57716200	19-4月 -11 04.00.35.625 下午	248	45987	25	bp7y9fvtqra1h	-1	0	0	0					3427055676	FOREGROUND	WAITING				UNKNOWN		cursor: pin S wait on X	1729366244	1140	idn	4083918896	value	1.14676E+12	where|sleeps	21474836525	Concurrency	3875070507	0	9772		-1	0	0
2	16047	3731271451	1	57716210	19-4月 -11 04.00.45.629 下午	248	45987	25	bp7y9fvtqra1h	-1	0	0	0					3427055676	FOREGROUND	WAITING				UNKNOWN		cursor: pin S wait on X	1729366244	2137	idn	4083918896	value	1.14676E+12	where|sleeps	21474837522	Concurrency	3875070507	0	9773		-1	0	0
3	16047	3731271451	1	57716220	19-4月 -11 04.00.55.633 下午	248	45987	25	bp7y9fvtqra1h	-1	0	0	0					3427055676	FOREGROUND	WAITING				UNKNOWN		cursor: pin S wait on X	1729366244	3134	idn	4083918896	value	1.14676E+12	where|sleeps	21474838519	Concurrency	3875070507	0	9775		-1	0	0
4	16047	3731271451	1	57716230	19-4月 -11 04.01.05.637 下午	248	45987	25	bp7y9fvtqra1h	-1	0	0	0					3427055676	FOREGROUND	WAITING				UNKNOWN		cursor: pin S wait on X	1729366244	4126	idn	4083918896	value	1.14676E+12	where|sleeps	21474839511	Concurrency	3875070507	0	9772		-1	0	0

但无论是从SQL历史记录视图(Workload Repository)还是v$SQL中都无法找到该条语句，可以判断该问题进程最后也没能成功完成对该’bp7y9fvtqra1h’语句的解析！因为没有该SQL的记录，所以也无法了解该SQL的执行计划和workarea工作区的使用状况，线索再次断裂！

SQL> select * from dba_hist_sqltext where sql_id='bp7y9fvtqra1h';

no rows selected

SQL> select * From v$sql where sql_id='bp7y9fvtqra1h';

no rows selected

通过查询进程内存使用总结历史视图dba_hist_process_mem_summary可以发现占用主要内存的分类是Other，猜测这些Other内存是用来解析游标使用的临时内存(call heap).

SQL> select snap_id,category,num_processes,non_zero_allocs,used_total,allocated_total,allocated_max from dba_hist_process_mem_summary where snap_id in (16047,16048,16049) order by allocated_total desc ,snap_id ;

   SNAP_ID CATEGORY        NUM_PROCESSES NON_ZERO_ALLOCS USED_TOTAL ALLOCATED_TOTAL ALLOCATED_MAX
---------- --------------- ------------- --------------- ---------- --------------- -------------
     16047 Other                      95              95                 1.5062E+10    1.4665E+10
     16048 Other                      96              96                 1.5053E+10    1.4664E+10
     16049 Other                      94              94                  386117660      53794202
     16047 Freeable                   36              36          0        36372480       3670016
     16048 Freeable                   33              33          0        32112640       3670016
     16047 SQL                        78              31   27346656        29785568      28016616
     16048 SQL                        79              34   26812992        29240400      27885544
     16049 Freeable                   27              27          0        26738688       3670016
     16049 SQL                        77              31     591528         1242168        125816
     16048 PL/SQL                     96              96     272224          601040         68944
     16049 PL/SQL                     94              94     218104          536592         68944
     16047 PL/SQL                     95              95     212816          506536         68800

因为证据断裂，无法根据现有的信息为该服务进程过量使用PGA内存来定位Bug。到MOS上搜索找不到类似的Bug，但即使有也很难定论，因为没有heapdump的话即便提交SR，Oracle GCS(Global Customer Service)也不太愿定位到Bug。

那么如果出现以上类似的PGA内存泄露的问题，我们因当如何第一时间收集有用的信息，以供后续诊断呢？我在这里提供一些可选的方案:

  1. 定期收集系统内PGA/UGA的使用情况，具体可以使用脚本<Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage>
  2. 防御式地在系统级别设置4030 dump heapdump 536870917级别的event dump事件，虽然此案例中未发生ORA-04030错误，但不代表下一次也不发生
  3. 在问题发生时，第一时间使用oradebug PGA_DETAIL_GET命令填充v$process_memory_detail视图，以便了解问题进程PGA内存的使用细节
  4. 若觉得v$process_memory_detail动态性能视图的信息还不够丰富的话，也可以做systemstate 266和heapdump 536870917级别的dump

虽然以上我们介绍了一些针对PGA内存泄露问题发生时的诊断方法，但可能你还是不了解具体要如何实践，接下来我们通过实践来体会一下,首先我们特意构建一个PGA内存泄露的假象(测试说明，不要用于生产环境!!):

[oracle@rh2 ~]$ wc -l very_large.sql 
18526 very_large.sql

/* 这里very_large.sql是我们"精心"构造的一条万分复杂的SQL语句，解析该SQL语句需要消耗大量的PGA内存 !!* /

SQL> @very_large

/* 执行very_large SQL语句开始模拟内存泄露，将持续较长时间  */

SQL> @MEMORY_USAGE_SCRIPT.SQL

Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session

Host........: rh2.oracle.com

Name........: PROD1

Version.....: 11.2.0.2.0

Startup Time: 2011-04-20 19:41:32

Current Time: 2011.04.21-19:57:16

Worst possible value of concurrent PGA + UGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                              SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
29,465              SYS                                               180444688 2011-04-21 19:52:16
152,987             SYS                                                67781616 2011-04-21 18:40:59
146,1               oracle@rh2.oracle.com (ARC3)                       37598184 2011-04-20 19:41:43
19,5                oracle@rh2.oracle.com (ARC2)                       36484072 2011-04-20 19:41:43
17,7                oracle@rh2.oracle.com (ARC0)                       33141736 2011-04-20 19:41:42
145,1               oracle@rh2.oracle.com (ARC1)                       19837928 2011-04-20 19:41:43
125,7               oracle@rh2.oracle.com (CJQ0)                       15826432 2011-04-20 19:41:50
135,1               oracle@rh2.oracle.com (LGWR)                       13480936 2011-04-20 19:41:33
131,1               oracle@rh2.oracle.com (LMS0)                       11973608 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                       11973608 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                       11842536 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                       10580296 2011-04-20 19:41:33
25,57               SYS                                                 9854112 2011-04-21 19:56:59
10,1                oracle@rh2.oracle.com (DBW0)                        9105992 2011-04-20 19:41:33
136,1               oracle@rh2.oracle.com (SMON)                        8777056 2011-04-20 19:41:33
140,1               oracle@rh2.oracle.com (MARK)                        8565736 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                        8238120 2011-04-20 19:41:33
138,1               oracle@rh2.oracle.com (MMON)                        7215184 2011-04-20 19:41:33
31,1                oracle@rh2.oracle.com (SMCO)                        7123896 2011-04-20 19:43:52
3,1                 oracle@rh2.oracle.com (DIAG)                        6730728 2011-04-20 19:41:33
16,1                oracle@rh2.oracle.com (RSMN)                        5420008 2011-04-20 19:41:35
150,5               oracle@rh2.oracle.com (Q000)                        5001608 2011-04-20 19:41:46
23,1                oracle@rh2.oracle.com (Q001)                        3445984 2011-04-20 19:41:46
22,1                oracle@rh2.oracle.com (QMNC)                        3314960 2011-04-20 19:41:45
12,1                oracle@rh2.oracle.com (RECO)                        3249448 2011-04-20 19:41:33
11,1                oracle@rh2.oracle.com (CKPT)                        3086120 2011-04-20 19:41:33
128,1               oracle@rh2.oracle.com (DBRM)                        2667304 2011-04-20 19:41:33
14,1                oracle@rh2.oracle.com (MMNL)                        2143208 2011-04-20 19:41:33
127,1               oracle@rh2.oracle.com (GEN0)                        2012136 2011-04-20 19:41:33
158,183             SYS                                                 1758344 2011-04-21 07:44:57
143,23              SYS                                                 1692808 2011-04-21 07:45:01
142,1               oracle@rh2.oracle.com (LCK0)                        1299288 2011-04-20 19:41:34
149,1               oracle@rh2.oracle.com (RCBG)                        1160120 2011-04-20 19:41:45
33,59               oracle@rh2.oracle.com (W000)                         963512 2011-04-21 19:55:14
4,1                 oracle@rh2.oracle.com (PING)                         898024 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         832488 2011-04-20 19:41:32
13,1                oracle@rh2.oracle.com (ASMB)                         832488 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         832488 2011-04-20 19:41:33
144,1               oracle@rh2.oracle.com (O000)                         832488 2011-04-20 19:41:36
129,1               oracle@rh2.oracle.com (ACMS)                         832488 2011-04-20 19:41:33
133,1               oracle@rh2.oracle.com (RMS0)                         832488 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         832488 2011-04-20 19:41:32
9,1                 oracle@rh2.oracle.com (LMHB)                         832488 2011-04-20 19:41:33
21,1                oracle@rh2.oracle.com (GTX0)                         832488 2011-04-20 19:41:45
18,3                oracle@rh2.oracle.com (O001)                         832488 2011-04-20 19:41:37
137,1               oracle@rh2.oracle.com (RBAL)                         832488 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         832488 2011-04-20 19:41:33

Worst possible total and average values of concurrent PGA + UGA memory usage:

564679192 bytes (total) and ~6007225 bytes (average), for ~47 sessions.

Approximate value of current PGA + UGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                              SUM(VALUE) SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
29,465              SYS                                               178083824 2011-04-21 19:52:16
146,1               oracle@rh2.oracle.com (ARC3)                       36484072 2011-04-20 19:41:43
19,5                oracle@rh2.oracle.com (ARC2)                       35369960 2011-04-20 19:41:43
17,7                oracle@rh2.oracle.com (ARC0)                       33141736 2011-04-20 19:41:42
145,1               oracle@rh2.oracle.com (ARC1)                       19837928 2011-04-20 19:41:43
135,1               oracle@rh2.oracle.com (LGWR)                       13480936 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                       11973608 2011-04-20 19:41:33
131,1               oracle@rh2.oracle.com (LMS0)                       11973608 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                       11842536 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                       10580296 2011-04-20 19:41:33
10,1                oracle@rh2.oracle.com (DBW0)                        8712776 2011-04-20 19:41:33
140,1               oracle@rh2.oracle.com (MARK)                        8565736 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                        8238120 2011-04-20 19:41:33
3,1                 oracle@rh2.oracle.com (DIAG)                        6730728 2011-04-20 19:41:33
152,987             SYS                                                 6224040 2011-04-21 18:40:59
16,1                oracle@rh2.oracle.com (RSMN)                        5420008 2011-04-20 19:41:35
125,7               oracle@rh2.oracle.com (CJQ0)                        4854824 2011-04-20 19:41:50
25,57               SYS                                                 4738504 2011-04-21 19:56:59
138,1               oracle@rh2.oracle.com (MMON)                        4165448 2011-04-20 19:41:33
136,1               oracle@rh2.oracle.com (SMON)                        3863504 2011-04-20 19:41:33
150,5               oracle@rh2.oracle.com (Q000)                        3108848 2011-04-20 19:41:46
11,1                oracle@rh2.oracle.com (CKPT)                        2561832 2011-04-20 19:41:33
12,1                oracle@rh2.oracle.com (RECO)                        2538120 2011-04-20 19:41:33
31,1                oracle@rh2.oracle.com (SMCO)                        2536376 2011-04-20 19:43:52
128,1               oracle@rh2.oracle.com (DBRM)                        2339768 2011-04-20 19:41:33
23,1                oracle@rh2.oracle.com (Q001)                        2339672 2011-04-20 19:41:46
22,1                oracle@rh2.oracle.com (QMNC)                        2242336 2011-04-20 19:41:45
127,1               oracle@rh2.oracle.com (GEN0)                        2012136 2011-04-20 19:41:33
14,1                oracle@rh2.oracle.com (MMNL)                        1946600 2011-04-20 19:41:33
158,183             SYS                                                 1692856 2011-04-21 07:44:57
143,23              SYS                                                 1561784 2011-04-21 07:45:01
142,1               oracle@rh2.oracle.com (LCK0)                        1299288 2011-04-20 19:41:34
149,1               oracle@rh2.oracle.com (RCBG)                        1160120 2011-04-20 19:41:45
33,59               oracle@rh2.oracle.com (W000)                         963512 2011-04-21 19:55:14
4,1                 oracle@rh2.oracle.com (PING)                         898024 2011-04-20 19:41:33
13,1                oracle@rh2.oracle.com (ASMB)                         832488 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         832488 2011-04-20 19:41:33
129,1               oracle@rh2.oracle.com (ACMS)                         832488 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         832488 2011-04-20 19:41:32
9,1                 oracle@rh2.oracle.com (LMHB)                         832488 2011-04-20 19:41:33
21,1                oracle@rh2.oracle.com (GTX0)                         832488 2011-04-20 19:41:45
18,3                oracle@rh2.oracle.com (O001)                         832488 2011-04-20 19:41:37
137,1               oracle@rh2.oracle.com (RBAL)                         832488 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         832488 2011-04-20 19:41:32
133,1               oracle@rh2.oracle.com (RMS0)                         832488 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         832488 2011-04-20 19:41:33
144,1               oracle@rh2.oracle.com (O000)                         832488 2011-04-20 19:41:36

Current total and average values of concurrent PGA + UGA memory usage:

463473320 bytes (total) and ~4930567 bytes (average), for ~47 sessions.

Maximum value of PGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                                   VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
29,465              SYS                                               177212856 2011-04-21 19:52:16
152,987             SYS                                                57208040 2011-04-21 18:40:59
146,1               oracle@rh2.oracle.com (ARC3)                       37416168 2011-04-20 19:41:43
19,5                oracle@rh2.oracle.com (ARC2)                       36302056 2011-04-20 19:41:43
17,7                oracle@rh2.oracle.com (ARC0)                       32959720 2011-04-20 19:41:42
145,1               oracle@rh2.oracle.com (ARC1)                       19655912 2011-04-20 19:41:43
135,1               oracle@rh2.oracle.com (LGWR)                       13298920 2011-04-20 19:41:33
125,7               oracle@rh2.oracle.com (CJQ0)                       13045176 2011-04-20 19:41:50
131,1               oracle@rh2.oracle.com (LMS0)                       11791592 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                       11791592 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                       11660520 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                       10398280 2011-04-20 19:41:33
10,1                oracle@rh2.oracle.com (DBW0)                        8923976 2011-04-20 19:41:33
140,1               oracle@rh2.oracle.com (MARK)                        8383720 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                        8056104 2011-04-20 19:41:33
31,1                oracle@rh2.oracle.com (SMCO)                        6876392 2011-04-20 19:43:52
3,1                 oracle@rh2.oracle.com (DIAG)                        6548712 2011-04-20 19:41:33
25,57               SYS                                                 6163896 2011-04-21 19:56:59
136,1               oracle@rh2.oracle.com (SMON)                        5893352 2011-04-20 19:41:33
138,1               oracle@rh2.oracle.com (MMON)                        5294872 2011-04-20 19:41:33
16,1                oracle@rh2.oracle.com (RSMN)                        5237992 2011-04-20 19:41:35
150,5               oracle@rh2.oracle.com (Q000)                        3910216 2011-04-20 19:41:46
11,1                oracle@rh2.oracle.com (CKPT)                        2904104 2011-04-20 19:41:33
23,1                oracle@rh2.oracle.com (Q001)                        2551016 2011-04-20 19:41:46
22,1                oracle@rh2.oracle.com (QMNC)                        2485480 2011-04-20 19:41:45
12,1                oracle@rh2.oracle.com (RECO)                        2485480 2011-04-20 19:41:33
128,1               oracle@rh2.oracle.com (DBRM)                        2223336 2011-04-20 19:41:33
14,1                oracle@rh2.oracle.com (MMNL)                        1961192 2011-04-20 19:41:33
127,1               oracle@rh2.oracle.com (GEN0)                        1830120 2011-04-20 19:41:33
158,183             SYS                                                 1510840 2011-04-21 07:44:57
143,23              SYS                                                 1445304 2011-04-21 07:45:01
142,1               oracle@rh2.oracle.com (LCK0)                        1117272 2011-04-20 19:41:34
149,1               oracle@rh2.oracle.com (RCBG)                         912616 2011-04-20 19:41:45
33,59               oracle@rh2.oracle.com (W000)                         716008 2011-04-21 19:55:14
4,1                 oracle@rh2.oracle.com (PING)                         716008 2011-04-20 19:41:33
144,1               oracle@rh2.oracle.com (O000)                         650472 2011-04-20 19:41:36
137,1               oracle@rh2.oracle.com (RBAL)                         650472 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         650472 2011-04-20 19:41:33
133,1               oracle@rh2.oracle.com (RMS0)                         650472 2011-04-20 19:41:33
129,1               oracle@rh2.oracle.com (ACMS)                         650472 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         650472 2011-04-20 19:41:32
21,1                oracle@rh2.oracle.com (GTX0)                         650472 2011-04-20 19:41:45
18,3                oracle@rh2.oracle.com (O001)                         650472 2011-04-20 19:41:37
13,1                oracle@rh2.oracle.com (ASMB)                         650472 2011-04-20 19:41:33
9,1                 oracle@rh2.oracle.com (LMHB)                         650472 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         650472 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         650472 2011-04-20 19:41:32

Worst possible total and average values of concurrent PGA memory usage:

528694504 bytes (total) and ~11248819 bytes (average), for ~47 sessions.

Maximum value of UGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                                   VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
152,987             SYS                                                10573576 2011-04-21 18:40:59
25,57               SYS                                                 3690216 2011-04-21 19:56:59
29,465              SYS                                                 3231832 2011-04-21 19:52:16
136,1               oracle@rh2.oracle.com (SMON)                        2883704 2011-04-20 19:41:33
125,7               oracle@rh2.oracle.com (CJQ0)                        2781256 2011-04-20 19:41:50
138,1               oracle@rh2.oracle.com (MMON)                        1920312 2011-04-20 19:41:33
150,5               oracle@rh2.oracle.com (Q000)                        1091392 2011-04-20 19:41:46
23,1                oracle@rh2.oracle.com (Q001)                         894968 2011-04-20 19:41:46
22,1                oracle@rh2.oracle.com (QMNC)                         829480 2011-04-20 19:41:45
12,1                oracle@rh2.oracle.com (RECO)                         763968 2011-04-20 19:41:33
128,1               oracle@rh2.oracle.com (DBRM)                         443968 2011-04-20 19:41:33
158,183             SYS                                                  247504 2011-04-21 07:44:57
149,1               oracle@rh2.oracle.com (RCBG)                         247504 2011-04-20 19:41:45
143,23              SYS                                                  247504 2011-04-21 07:45:01
33,59               oracle@rh2.oracle.com (W000)                         247504 2011-04-21 19:55:14
31,1                oracle@rh2.oracle.com (SMCO)                         247504 2011-04-20 19:43:52
146,1               oracle@rh2.oracle.com (ARC3)                         182016 2011-04-20 19:41:43
145,1               oracle@rh2.oracle.com (ARC1)                         182016 2011-04-20 19:41:43
144,1               oracle@rh2.oracle.com (O000)                         182016 2011-04-20 19:41:36
142,1               oracle@rh2.oracle.com (LCK0)                         182016 2011-04-20 19:41:34
140,1               oracle@rh2.oracle.com (MARK)                         182016 2011-04-20 19:41:33
137,1               oracle@rh2.oracle.com (RBAL)                         182016 2011-04-20 19:41:33
135,1               oracle@rh2.oracle.com (LGWR)                         182016 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         182016 2011-04-20 19:41:33
133,1               oracle@rh2.oracle.com (RMS0)                         182016 2011-04-20 19:41:33
131,1               oracle@rh2.oracle.com (LMS0)                         182016 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                         182016 2011-04-20 19:41:33
129,1               oracle@rh2.oracle.com (ACMS)                         182016 2011-04-20 19:41:33
127,1               oracle@rh2.oracle.com (GEN0)                         182016 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         182016 2011-04-20 19:41:32
21,1                oracle@rh2.oracle.com (GTX0)                         182016 2011-04-20 19:41:45
19,5                oracle@rh2.oracle.com (ARC2)                         182016 2011-04-20 19:41:43
18,3                oracle@rh2.oracle.com (O001)                         182016 2011-04-20 19:41:37
17,7                oracle@rh2.oracle.com (ARC0)                         182016 2011-04-20 19:41:42
16,1                oracle@rh2.oracle.com (RSMN)                         182016 2011-04-20 19:41:35
14,1                oracle@rh2.oracle.com (MMNL)                         182016 2011-04-20 19:41:33
13,1                oracle@rh2.oracle.com (ASMB)                         182016 2011-04-20 19:41:33
11,1                oracle@rh2.oracle.com (CKPT)                         182016 2011-04-20 19:41:33
10,1                oracle@rh2.oracle.com (DBW0)                         182016 2011-04-20 19:41:33
9,1                 oracle@rh2.oracle.com (LMHB)                         182016 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                         182016 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                         182016 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                         182016 2011-04-20 19:41:33
4,1                 oracle@rh2.oracle.com (PING)                         182016 2011-04-20 19:41:33
3,1                 oracle@rh2.oracle.com (DIAG)                         182016 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         182016 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         182016 2011-04-20 19:41:32

Worst possible total and average values of concurrent UGA memory usage:

35984688 bytes (total) and ~765631 bytes (average), for ~47 sessions.

Current value of PGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                                   VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
29,465              SYS                                               177802680 2011-04-21 19:52:16
146,1               oracle@rh2.oracle.com (ARC3)                       36302056 2011-04-20 19:41:43
19,5                oracle@rh2.oracle.com (ARC2)                       35187944 2011-04-20 19:41:43
17,7                oracle@rh2.oracle.com (ARC0)                       32959720 2011-04-20 19:41:42
145,1               oracle@rh2.oracle.com (ARC1)                       19655912 2011-04-20 19:41:43
135,1               oracle@rh2.oracle.com (LGWR)                       13298920 2011-04-20 19:41:33
131,1               oracle@rh2.oracle.com (LMS0)                       11791592 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                       11791592 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                       11660520 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                       10398280 2011-04-20 19:41:33
10,1                oracle@rh2.oracle.com (DBW0)                        8530760 2011-04-20 19:41:33
140,1               oracle@rh2.oracle.com (MARK)                        8383720 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                        8056104 2011-04-20 19:41:33
3,1                 oracle@rh2.oracle.com (DIAG)                        6548712 2011-04-20 19:41:33
16,1                oracle@rh2.oracle.com (RSMN)                        5237992 2011-04-20 19:41:35
152,987             SYS                                                 4582632 2011-04-21 18:40:59
125,7               oracle@rh2.oracle.com (CJQ0)                        3935672 2011-04-20 19:41:50
25,57               SYS                                                 3787544 2011-04-21 19:56:59
136,1               oracle@rh2.oracle.com (SMON)                        3140840 2011-04-20 19:41:33
138,1               oracle@rh2.oracle.com (MMON)                        3066648 2011-04-20 19:41:33
150,5               oracle@rh2.oracle.com (Q000)                        2468424 2011-04-20 19:41:46
11,1                oracle@rh2.oracle.com (CKPT)                        2379816 2011-04-20 19:41:33
31,1                oracle@rh2.oracle.com (SMCO)                        2288872 2011-04-20 19:43:52
12,1                oracle@rh2.oracle.com (RECO)                        2223336 2011-04-20 19:41:33
128,1               oracle@rh2.oracle.com (DBRM)                        2092264 2011-04-20 19:41:33
23,1                oracle@rh2.oracle.com (Q001)                        1961192 2011-04-20 19:41:46
22,1                oracle@rh2.oracle.com (QMNC)                        1961192 2011-04-20 19:41:45
127,1               oracle@rh2.oracle.com (GEN0)                        1830120 2011-04-20 19:41:33
14,1                oracle@rh2.oracle.com (MMNL)                        1764584 2011-04-20 19:41:33
158,183             SYS                                                 1510840 2011-04-21 07:44:57
143,23              SYS                                                 1379768 2011-04-21 07:45:01
142,1               oracle@rh2.oracle.com (LCK0)                        1117272 2011-04-20 19:41:34
149,1               oracle@rh2.oracle.com (RCBG)                         912616 2011-04-20 19:41:45
33,59               oracle@rh2.oracle.com (W000)                         716008 2011-04-21 19:55:14
4,1                 oracle@rh2.oracle.com (PING)                         716008 2011-04-20 19:41:33
144,1               oracle@rh2.oracle.com (O000)                         650472 2011-04-20 19:41:36
137,1               oracle@rh2.oracle.com (RBAL)                         650472 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         650472 2011-04-20 19:41:33
133,1               oracle@rh2.oracle.com (RMS0)                         650472 2011-04-20 19:41:33
129,1               oracle@rh2.oracle.com (ACMS)                         650472 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         650472 2011-04-20 19:41:32
21,1                oracle@rh2.oracle.com (GTX0)                         650472 2011-04-20 19:41:45
18,3                oracle@rh2.oracle.com (O001)                         650472 2011-04-20 19:41:37
13,1                oracle@rh2.oracle.com (ASMB)                         650472 2011-04-20 19:41:33
9,1                 oracle@rh2.oracle.com (LMHB)                         650472 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         650472 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         650472 2011-04-20 19:41:32

Current total and average values of concurrent PGA memory usage:

449247816 bytes (total) and ~9558464 bytes (average), for ~47 sessions.

Current value of UGA memory usage per session:

SID AND SERIAL#     USERNAME OR PROGRAM                                   VALUE SESSION START TIME
------------------- ------------------------------------------------ ---------- -------------------
152,987             SYS                                                 1641408 2011-04-21 18:40:59
138,1               oracle@rh2.oracle.com (MMON)                        1098800 2011-04-20 19:41:33
25,57               SYS                                                  950960 2011-04-21 19:56:59
125,7               oracle@rh2.oracle.com (CJQ0)                         919152 2011-04-20 19:41:50
136,1               oracle@rh2.oracle.com (SMON)                         722664 2011-04-20 19:41:33
150,5               oracle@rh2.oracle.com (Q000)                         640424 2011-04-20 19:41:46
23,1                oracle@rh2.oracle.com (Q001)                         378480 2011-04-20 19:41:46
12,1                oracle@rh2.oracle.com (RECO)                         314784 2011-04-20 19:41:33
29,465              SYS                                                  281144 2011-04-21 19:52:16
22,1                oracle@rh2.oracle.com (QMNC)                         281144 2011-04-20 19:41:45
149,1               oracle@rh2.oracle.com (RCBG)                         247504 2011-04-20 19:41:45
128,1               oracle@rh2.oracle.com (DBRM)                         247504 2011-04-20 19:41:33
33,59               oracle@rh2.oracle.com (W000)                         247504 2011-04-21 19:55:14
31,1                oracle@rh2.oracle.com (SMCO)                         247504 2011-04-20 19:43:52
158,183             SYS                                                  182016 2011-04-21 07:44:57
146,1               oracle@rh2.oracle.com (ARC3)                         182016 2011-04-20 19:41:43
145,1               oracle@rh2.oracle.com (ARC1)                         182016 2011-04-20 19:41:43
144,1               oracle@rh2.oracle.com (O000)                         182016 2011-04-20 19:41:36
143,23              SYS                                                  182016 2011-04-21 07:45:01
142,1               oracle@rh2.oracle.com (LCK0)                         182016 2011-04-20 19:41:34
140,1               oracle@rh2.oracle.com (MARK)                         182016 2011-04-20 19:41:33
137,1               oracle@rh2.oracle.com (RBAL)                         182016 2011-04-20 19:41:33
135,1               oracle@rh2.oracle.com (LGWR)                         182016 2011-04-20 19:41:33
134,1               oracle@rh2.oracle.com (MMAN)                         182016 2011-04-20 19:41:33
133,1               oracle@rh2.oracle.com (RMS0)                         182016 2011-04-20 19:41:33
131,1               oracle@rh2.oracle.com (LMS0)                         182016 2011-04-20 19:41:33
130,1               oracle@rh2.oracle.com (LMON)                         182016 2011-04-20 19:41:33
129,1               oracle@rh2.oracle.com (ACMS)                         182016 2011-04-20 19:41:33
127,1               oracle@rh2.oracle.com (GEN0)                         182016 2011-04-20 19:41:33
126,1               oracle@rh2.oracle.com (PSP0)                         182016 2011-04-20 19:41:32
21,1                oracle@rh2.oracle.com (GTX0)                         182016 2011-04-20 19:41:45
19,5                oracle@rh2.oracle.com (ARC2)                         182016 2011-04-20 19:41:43
18,3                oracle@rh2.oracle.com (O001)                         182016 2011-04-20 19:41:37
17,7                oracle@rh2.oracle.com (ARC0)                         182016 2011-04-20 19:41:42
16,1                oracle@rh2.oracle.com (RSMN)                         182016 2011-04-20 19:41:35
14,1                oracle@rh2.oracle.com (MMNL)                         182016 2011-04-20 19:41:33
13,1                oracle@rh2.oracle.com (ASMB)                         182016 2011-04-20 19:41:33
11,1                oracle@rh2.oracle.com (CKPT)                         182016 2011-04-20 19:41:33
10,1                oracle@rh2.oracle.com (DBW0)                         182016 2011-04-20 19:41:33
9,1                 oracle@rh2.oracle.com (LMHB)                         182016 2011-04-20 19:41:33
7,1                 oracle@rh2.oracle.com (LMS1)                         182016 2011-04-20 19:41:33
6,1                 oracle@rh2.oracle.com (LMD0)                         182016 2011-04-20 19:41:33
5,1                 oracle@rh2.oracle.com (DIA0)                         182016 2011-04-20 19:41:33
4,1                 oracle@rh2.oracle.com (PING)                         182016 2011-04-20 19:41:33
3,1                 oracle@rh2.oracle.com (DIAG)                         182016 2011-04-20 19:41:33
2,1                 oracle@rh2.oracle.com (VKTM)                         182016 2011-04-20 19:41:33
1,1                 oracle@rh2.oracle.com (PMON)                         182016 2011-04-20 19:41:32

Current total and average values of concurrent UGA memory usage:

14225504 bytes (total) and ~302670 bytes (average), for ~47 sessions.

Current SGA structure sizings:

Total System Global Area  939495424 bytes
Fixed Size                  2232088 bytes
Variable Size             398459112 bytes
Database Buffers          532676608 bytes
Redo Buffers                6127616 bytes

Some initialization parameter values at instance startup:

large_pool_size=0
pga_aggregate_target=0
sga_target=0
shared_pool_size=0
sort_area_size=65536
streams_pool_size=0

Current Time: 2011.04.21-19:57:16

/* 可以从以上输出看到sid,serial=29,465会话的PGA内存使用量异常，达到了170M，
    虽然跟以上案例中的PGA泄露情况比较不算什么  */

/* 使用sid和serial定位到具体的操作系统进程号 */

SQL> select spid,pid,PGA_USED_MEM,PGA_MAX_MEM from v$process
  2  where addr=(select paddr from v$session where sid=&1 and serial#=&2);
Enter value for 1: 29
Enter value for 2: 465
old   2: where addr=(select paddr from v$session where sid=&1 and serial#=&2)
new   2: where addr=(select paddr from v$session where sid=29 and serial#=465)

SPID                            PID PGA_USED_MEM PGA_MAX_MEM
------------------------ ---------- ------------ -----------
26932                            48    129716228   130034996

1 row selected.

SQL> oradebug setospid 26932;
Oracle pid: 48, Unix process pid: 26932, image: oracle@rh2.oracle.com (TNS V1-V3)

SQL> oradebug dump heapdump 536870917;
Statement processed.

SQL> oradebug dump processstate 10;
Statement processed.

SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_26932.trc

/* 接下来对堆转储文件进行分析，通过grep可以找出其中较大的SubHEAP子堆  */

[oracle@rh2 ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space"
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_26932.trc

HEAP DUMP heap name="session heap"  desc=0x2ac0b37a67f8
Total heap size    =   130840
Permanent space    =    62680
HEAP DUMP heap name="Alloc environm"  desc=0x2ac0b37ce090
Total heap size    =     4040
Permanent space    =     4040
HEAP DUMP heap name="PLS UGA hp"  desc=0x2ac0b37be7f0
Total heap size    =     1992
Permanent space    =     1080
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2ac0b37cf660
Total heap size    =     1128
Permanent space    =       80
HEAP DUMP heap name="pga heap"  desc=0xb7c8ba0
Total heap size    =  2689432
Permanent space    =   660560
HEAP DUMP heap name="Alloc environm"  desc=0x2ac0b35ba5c8
Total heap size    =  1706816
Permanent space    =      464
HEAP DUMP heap name="Alloc server h"  desc=0x2ac0b35b9000
Total heap size    =  1704400
Permanent space    =  1694816
HEAP DUMP heap name="diag pga"  desc=0x2ac0b32537e0
Total heap size    =    65448
Permanent space    =     3672
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x2ac0b35eb2b0
Total heap size    =    10992
Permanent space    =       80
HEAP DUMP heap name="peshm.c:Proces"  desc=0x2ac0b35e7ad0
Total heap size    =     4000
Permanent space    =       80
HEAP DUMP heap name="KSFQ heap"  desc=0x2ac0b35c6d70
Total heap size    =     3256
Permanent space    =     3256
HEAP DUMP heap name="top call heap"  desc=0xb7ce3c0
Total heap size    =155918560
Permanent space    =      448
HEAP DUMP heap name="callheap"  desc=0xb7cd4c0
Total heap size    =152906784
Permanent space    =       80
HEAP DUMP heap name="TCHK^30c42b7a"  desc=0x2ac0b378ff48
Total heap size    =151414512
Permanent space    =       80
HEAP DUMP heap name="kggec.c.kggfa"  desc=0x2ac0b4e76ec8
Total heap size    =     1016
Permanent space    =      736
HEAP DUMP heap name="kxs-heap-c"  desc=0x2ac0b37800c0
Total heap size    =  1489464
Permanent space    =  1485928
HEAP DUMP heap name="top uga heap"  desc=0xb7ce5e0
Total heap size    =   131024
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x2ac0b37a67f8
Total heap size    =   130840
Permanent space    =    62680
HEAP DUMP heap name="Alloc environm"  desc=0x2ac0b37ce090
Total heap size    =     4040
Permanent space    =     4040
HEAP DUMP heap name="PLS UGA hp"  desc=0x2ac0b37be7f0
Total heap size    =     1992
Permanent space    =     1080
HEAP DUMP heap name="koh-kghu sessi"  desc=0x2ac0b37cf660
Total heap size    =     1128
Permanent space    =       80
HEAP DUMP heap name="SQLA^30c42b7a"  desc=0x6f4c3ab8
Total heap size    =  4919904
Permanent space    =       80
HEAP DUMP heap name="KGLH0^30c42b7a"  desc=0x6ef44290
Total heap size    =     4032
Permanent space    =     2648


/* 以上heapdump表明TCHK^30c42b7a子堆占用了PGA中绝大多数的内存,
    其中子堆的包含结构为PGA->top call heap -> call heap  -> TCHK 
*/

/* 接着processstate dump还可以让我们了解该问题进程的最近活动历史，及之前所运行的SQL语句
    以便进一步诊断,以下为其调用堆栈      */

ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-qcsfccc()+206
<-qcsIsColInFro()+309<-qcsRslvColWithinQbc()+179<-qcsWeakColRslv()+94<-qcsRslvName()+2541<-qcsridn()+105
<-qcsraic()+455<-qcspqbDescendents()+527<-qcspqb()+260<-qcspqbDescendents()+2744<-qcspqb()+260
<-kkmdrv()+182<-opiSem()+1947<-opiprs()+293<-__PGOSF632_kksParseChildCursor()+572<-rpiswu2()+1618
<-kksLoadChild()+5167<-kxsGetRuntimeLock()+2066<-kksfbc()+14527<-kkspsc0()+2025<-kksParseCursor()+144
<-opiosq0()+2027<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+2289<-opitsk()+1670<-opiino()+966
<-opiodr()+910<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244
<-_start()+36

通过以上获取的信息"call heap-> TCHK"可以从MOS查找到11.2上的”Bug 11782790: ORA-4030 IS GENERATED IN HARD PARSE"和"Bug 12360198: ORA-04030 (TCHK^82665CD8,CHEDEF : QCUATC)"；虽然我们这里故意为之而非真实的Bug，也可以看出"TCHK subheap"在11.2中充当SQL解析时的临时调用子堆(parse call subheap)。

to be continued.....

Filed Under: Oracle Tagged With: , , , ,

ORA-4030 PGA Usage Diagnostic Script

2009/07/07 by 2 Comments 
REM  Locate the top PGA user

set lines 75
set pages 999
set serveroutput on

spool topuser.out

select * from gv$version;

declare a1 number;
            a2 number;
            a3 varchar2(30);
            a4 varchar2(30);
            a5 number;
            a6 number;
            a7 number;
            a8 number;
            blankline varchar2(70);

cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program",
PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');

begin
  blankline:=chr(13);
  open code;
  fetch code into a1, a2, a3, a4, a5, a6, a7, a8;

  dbms_output.put_line(blankline);
  dbms_output.put_line('               Top PGA User');
  dbms_output.put_line(blankline);

  dbms_output.put_line('PID:   '||a1||'             '||'SPID:   '||a2);
  dbms_output.put_line('User Info:           '||a3);
  dbms_output.put_line('Program:            '||a4);
  dbms_output.put_line('PGA Used:            '||a5);
  dbms_output.put_line('PGA Allocated:        '||a6);
  dbms_output.put_line('PGA Freeable:             '||a7);
  dbms_output.put_line('Maximum PGA:            '||a8);

end;
/

set lines 132
col value format 999,999,999,999,999

select * from v$pgastat;

spool off

REM
REM  Investigate memory from the database side
REM

col TTL format 999,999,999,999 heading "Total Memory"

break on report
compute sum on report of TTL

select bytes TTL from v$sgainfo where name='Maximum SGA Size'
union
select value from v$pgastat where name='total PGA allocated'
/

set lines 132
set pages 999

spool workareaoverview.out

REM overview of PGA usage

col name format a40 head "Name"
col value format 999,999,999 head "Total"
col unit format a10 head "Units"
col pga_size format a25 head "PGA Size"
col optimal_executions format 999,999,999,999 head "Optimal"
col onepass_executions format 999,999,999,999 head "One-Pass"
col multipasses_executions format 999,999,999,999 head "Multi-Pass"
col optimal_count format 999,999,999,999 head "Optimal Count"
col optimal_perc format 999 head "Optimal|PCT"
col onepass_count format 999,999,999,999 head "One-Pass Count"
col onepass_perc format 999 head "One|PCT"
col multipass_count format 999,999,999,999 head "Multi-Pass Count"
col multipass_perc format 999 head "Multi|PCT"

col sid format 999,999 Head "SID"
col operation format a30 head "Operation"
col esize format 999,999,999 head "Expected Size"
col mem format 999,999,999 head "Actual Mem"
col "MAX MEM" format 999,999,999 head "Maximum Mem"
col pass format 999,999 head "Passes"
col tsize format 999,999,999,999,999 head "Temporary|Segment Size"

spool workareaoverview.out

SELECT  name,  decode(unit, 'bytes', trunc(value/1024/1024), value) value ,
decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT
/

REM Review workarea buckets to see how efficient memory is utilized
REM  Ideal to see OPTIMAL EXECUTIONS vs. ONE-PASS and Multi-PASS

select case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') || 'kb  0
order by low_optimal_size
/

REM Review workarea buckets as percentages overall
REM      this script assuming 64K optimal size

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
       (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
               sum(OPTIMAL_EXECUTIONS) optimal_count,
               sum(ONEPASS_EXECUTIONS) onepass_count,
               sum(MULTIPASSES_EXECUTIONS) multipass_count
        FROM   v$sql_workarea_histogram
        WHERE  low_optimal_size > 64*1024)
/

REM   Review current activity in Work Areas

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;

select * from v$pgastat;

col time form a30
col name form a30
select a.BEGIN_INTERVAL_TIME time, b.*
from DBA_HIST_SNAPSHOT a, DBA_HIST_PGASTAT b
where a.SNAP_ID=b.SNAP_ID
and b.name='total PGA allocated'
order by a.BEGIN_INTERVAL_TIME desc
/

show parameter parallel_execution_message_size

show parameter memory

show parameter pga



spool off
clear col



#collect AIX info

ls -al $ORACLE_HOME/bin/oracle >> /tmp/support.txt
oslevel -s
whoami >> /tmp/support.txt
ulimit -a >> /tmp/support.txt
svmon -O unit=MB >> /tmp/support.txt
/usr/sbin/lsps -a >> /tmp/support.txt
/usr/sbin/lsattr -HE -l sys0 -a realmem >> /tmp/support.txt
ipcs -m >> /tmp/support.txt

opatch lsinventory -detail

#collect Linux info

arch
cat /etc/issue
whoami
ulimit -a
df -h /dev/shm
ipcs -ma
cat /etc/sysctl.conf
cat /proc/meminfo
cat /proc/swaps
cat /proc/vmstat
opatch lsinventory -detail
Filed Under: Oracle, Oracle脚本script Tagged With: , ,

PGA Usage Larger than PGA_AGGREGATE_TARGET setting?

2008/07/07 by Leave a Comment

pga_aggregate_target is a target, as opposed to a hard limit – so it isn’t unusual to go above that.
13G above that, now that’s unusual though! There IS an enhancement request in,
to make a hard-limit setting, but that does not currently exist.
There is a known bug in 10203 with certain statements burning up memory – bug 5947623 – however,
the 10203/aix version of this patch is 64-bit, and the SR header says you are on 32-bit, so that isn’t
an option….and 10203 is old enough that I can’t get a new version of the patch made.

As I was unable to see any errors (e.g., ORA-4030) thre does not seem to be any problem with the operation of the database.

PGA_AGGREGATE_TARGET does not set a hard limit on pga usage. It is only a target value used to dynamically size the process work areas. It also does not affect other areas of the pga that are allowed to grow beyond this limit.

There are certain areas of pga that cannot be controlled by initialization parameters. Such areas include pl/sql memory collections such as pl/sql tables and varrays.

Depending on the programming code and amount of data being handled these areas can grow very large (up to 20G internal limit on 10) and can consume large amounts of memory. This memory growth can be controlled by good programming practices. As an example, use LIMIT clause with BULK COLLECT.

Additionally, programming mistakes can also lead to excessive memory usage.

You can take steps to control the size of a process. However, from within the database framework you cannot place a hard limit on the size of a process by setting any initialization parameters or database configuration.

You can limit the size of a process from the OS side by setting kernel limits or user shell limits but this leads to the ORA-4030 and will cause transaction rollback.

As noted in bug 7279150, “… this is not a hard limit and that we will exceed it when it is undersized and the workload increases rapidly, such as when they start the workload for their testing or when they spawn a new set of sessions from their application servers.”

As the DBA you need to get confirmation from your operating system administrator that the amount of memory reported as being in use by a process includes or does not include shared memory. If shared memory is included in the value displayed by the operating system utility, then the shared pool size must be deducted from that value to know how much private memory the process is actually using.

See note 174555.1 “UNIX Determining the Size of an Oracle Process”.

If an RDBMS user process is using more private memory than expected, then the DBA has three options:

– Do nothing
– Monitor the RDBMS iuser session to find out what SQL statements are being performed or were being performed by that RDBMS session. Using the SQL*Trace functionality of the database would normally be done if information from the end user cannot be obtained directly as to what they were doing since the memory usage was higher than expected or what they are doing right now.
– Kill that RDBMS user session.

Filed Under: Oracle Tagged With: , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号