2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
Probe how does your PGA consume
前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内存泄露的问题，我们因当如何第一时间收集有用的信息，以供后续诊断呢？我在这里提供一些可选的方案:
- 定期收集系统内PGA/UGA的使用情况，具体可以使用脚本<Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage>
- 防御式地在系统级别设置4030 dump heapdump 536870917级别的event dump事件，虽然此案例中未发生ORA-04030错误，但不代表下一次也不发生
- 在问题发生时，第一时间使用oradebug PGA_DETAIL_GET命令填充v$process_memory_detail视图，以便了解问题进程PGA内存的使用细节
- 若觉得v$process_memory_detail动态性能视图的信息还不够丰富的话，也可以做systemstate 266和heapdump 536870917级别的dump
[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.....
posted on 2013-03-19 00:47 Oracle和MySQL 阅读(268) 评论(0) 编辑 收藏 举报
