Oracle Internal Event:10235 Heap Checking诊断事件

10235 (check memory manager internal structures) event内部诊断事件,当oracle SGA内存堆heap 管理操作发生错误时会触发该检测事件(For the sga heap, the internal error is signaled only when heap checking (event 10235) is enabled),其级别列表如下:

 

[oracle@nas ~]$ oerr ora 10235
10235, 00000, "check memory manager internal structures"
// *Cause:
// *Action:

List of Levels for Event 10235
Level 1: Check heap before heap is freed.
Level 2: Level 1 + fill allocation and frees with 0xff.
Level 3: level 2 + checks to make sure chunk belongs to heap prior to operation (free, grow, make chunk as
freeable with mark). Scan of extents, could get expensive
Level 4: level 3 + allocates permanent chunks as freeable chunks. This accomplishes two things:
all permanent allocations are in their own chunks and the comments for the permanent chunks appear in a heap dump.
The following levels should be OR'ed in together. For example, in order to use levels 2, 8 and 16,
event 10235 needs to be set to level 26.
Level 8: Check heap on every operation to the heap.
Level 16: Level 8 + check top PGA heap and SGA heap.
Level 32: Level 16 + check all heaps in the top pga heap, recursively; check SGA heap.
Level 64: Level 32 + check SGA heap if started up in single_process=true.
Level 256: align chunks at the bottom of a page and page protect the page that follows the chunk,
to catch writes off the end of a chunk, for all heaps.
Level 512: Like level 256, except for all heaps in the pga heap; so this event is useful for detecting
corruption in the pga heap and pga subheaps. This can also be enabled on specific heaps or chunks with
specific comments.
Level 65536: Enable CPM(Commented PerManent chunk) Keep comment for permanent allocation. It's useful for
detecting memory growth/overrun in permanent space. If you need to investigate the allocations for "library cache",
The combination of event 10049 level 10100 and this event will help to have more descriptive information.

 

注意只有当 怀疑oracle实例存在内存讹误(memory corruption)或者深入研究KGH堆管理内部原理的时候才有必要使用该10235事件,不要在生产库设置该诊断事件!! 不推荐在session会话级别设置该event 可能引起问题,具体的设置方法如下:

 

alter system set event=' 10235 trace name context forever,level 512 ' scope=spfile;
restart instance;

 

 

实际上绝大多数情况下我们不会用到10235 event, 即便是提交有关内存泄露(memory leak)或者内存讹误(memory corruption)的SR后 oracle Support 要求你上传一些heapdump的trace信息, 直接做heapdump转储也已经足够了, 具体用法如下:

 

 

alter session set event 'trace name immediate heapdump level <n>';

level 级别列表如下:

 1:          pga heap,  1025:            pga heap w/ contents
 2:          sga heap,  2050:            sga heap w/ contents
 4:          uga heap,  5000:            uga heap w/ contents
 8:          current call heap,  8200: current call heap w/ contents
16:         user call heap, 16400:     user call heap w/ contents
32:         large alloc heap, 32800:  large alloc heap w/ contents

若希望dump转储某个特定的subheap ,则先要知道该heap descriptor 的address地址

alter session set event 'trace name immediate headump_addr level <addr>';

Data Pump failed with ORA-04031/ORA-4030?

在10g中引入了数据泵Data Pump导入导出工具,DataPump的工作流如下图:

data_pump_workflow

我们在使用Data Pump工具时经常会遇到著名的ORA-04031/ORA-04030错误,主要影响DataPump的内存组件有PGA和SGA中的共享池Shared Pool、流池Streams Pool。Expdp/Impdp对shared Pool的开销主要体现在其运行过程中需要调用一系列的包体PACKGE BODY,它们包括:

PACKAGE_NAME                             TYPE                 SHARABLE_MEM
---------------------------------------- -------------------- ------------
SYS.KUPM$MCP                             PACKAGE BODY               425448
SYS.KUPW$WORKER                          PACKAGE BODY               386000
SYS.DBMS_METADATA_INT                    PACKAGE BODY               325856
SYS.DBMS_REPCAT_UTL                      PACKAGE BODY               269064
SYS.DBMS_METADATA                        PACKAGE BODY               226624
SYS.DBMS_DATAPUMP                        PACKAGE BODY               192888
SYS.DBMS_PRVTAQIS                        PACKAGE BODY               147288
SYS.DBMS_PRVTAQIM                        PACKAGE BODY               142680
SYS.KUPF$FILE                            PACKAGE BODY               142008
SYS.DBMS_METADATA_UTIL                   PACKAGE BODY               115224
SYS.KUPD$DATA                            PACKAGE BODY               109400
SYS.DBMS_LOGREP_EXP                      PACKAGE BODY               102648
SYS.DBMS_SCHED_MAIN_EXPORT               PACKAGE BODY                86816
SYS.DBMS_SYS_SQL                         PACKAGE BODY                72280
SYS.DBMS_AW                              PACKAGE BODY                68128
SYS.DBMS_SQL                             PACKAGE BODY                68064
SYS.DBMS_DM_EXP_INTERNAL                 PACKAGE BODY                57040
SYS.DBMS_AW_EXP                          PACKAGE BODY                52256
SYS.KUPC$QUE_INT                         PACKAGE BODY                52088
SYS.DBMS_CUBE_EXP                        PACKAGE BODY                48432
SYS.KUPV$FT_INT                          PACKAGE BODY                47184
SYS.DBMS_LOGREP_UTIL                     PACKAGE BODY                45856
SYS.DBMS_CDC_EXPDP                       PACKAGE BODY                44616
SYS.DBMS_EXPORT_EXTENSION                PACKAGE BODY                38728
SYS.DBMS_CDC_UTILITY                     PACKAGE BODY                37712
SYS.KUPV$FT                              PACKAGE BODY                34536
SYS.DBMS_DM_MODEL_EXP                    PACKAGE BODY                33904
SYS.DBMS_AQ                              PACKAGE BODY                33512
SYS.DBMS_IJOB                            PACKAGE BODY                33488
SYS.DBMS_AQ_SYS_EXP_INTERNAL             PACKAGE BODY                29464
SYS.DBMS_FILE_GROUP_EXP                  PACKAGE BODY                29440
SYS.KUPD$DATA_INT                        PACKAGE BODY                29424
SYS.DBMS_RULE_EXP_RL_INTERNAL            PACKAGE BODY                29400
SYS.KUPP$PROC                            PACKAGE BODY                25368
SYS.DBMS_AQ_IMPORT_INTERNAL              PACKAGE BODY                25352
SYS.DBMS_AQADM                           PACKAGE BODY                25320
SYS.DBMS_ODCI                            PACKAGE BODY                21200
SYS.UTL_XML                              PACKAGE BODY                21200
SYS.DBMS_CDC_DPUTIL                      PACKAGE BODY                21192
SYS.DBMS_STREAMS_DATAPUMP_UTIL           PACKAGE BODY                21144
SYS.KUPF$FILE_INT                        PACKAGE BODY                17104
SYS.DBMS_SESSION                         PACKAGE BODY                17048
SYS.DBMS_AQ_SYS_EXP_ACTIONS              PACKAGE BODY                17048
SYS.DBMS_STREAMS_DATAPUMP                PACKAGE BODY                17032
SYS.KUPC$QUEUE_INT                       PACKAGE BODY                17032
SYS.DBMS_RULE_ADM                        PACKAGE BODY                17032
SYS.DBMS_LOCK                            PACKAGE BODY                17032
SYS.DBMS_RULEADM_INTERNAL                PACKAGE BODY                12952
SYS.DBMS_TRANSFORM_EXIMP_INTERNAL        PACKAGE BODY                12952
SYS.DBMS_REFRESH_EXP_SITES               PACKAGE BODY                12952
SYS.DBMS_REPCAT_RGT_EXP                  PACKAGE BODY                12936
SYS.UTL_RAW                              PACKAGE BODY                12936
SYS.DBMS_FLASHBACK                       PACKAGE BODY                12936
SYS.DBMS_TRANSFORM_EXIMP                 PACKAGE BODY                12936
SYS.DBMS_SCHED_JOB_EXPORT                PACKAGE BODY                12936
SYS.DBMS_REFRESH_EXP_LWM                 PACKAGE BODY                12936
SYS.DBMS_SCHED_EXPORT_CALLOUTS           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_QUEUE_TABLES             PACKAGE BODY                 8856
SYS.DBMS_DEFER_IMPORT_INTERNAL           PACKAGE BODY                 8856
SYS.DBMS_INTERNAL_SAFE_SCN               PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_INDEX_TABLES             PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_HISTORY_TABLES           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SIGNATURE_TABLES         PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_DEQUEUELOG_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SUBSCRIBER_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_CMT_TIME_TABLES          PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_TIMEMGR_TABLES           PACKAGE BODY                 8856
SYS.DBMS_RULE_EXP_RULES                  PACKAGE BODY                 8840
SYS.DBMS_RULE_EXP_UTLI                   PACKAGE BODY                 8840
SYS.DBMS_AQADM_INV                       PACKAGE BODY                 8840
SYS.DBMS_ZHELP_IR                        PACKAGE BODY                 8840
SYS.KUPC$QUEUE                           PACKAGE BODY                 8840
SYS.DBMS_AQ_EXP_ZECURITY                 PACKAGE BODY                 8840
SYS.KUPU$UTILITIES_INT                   PACKAGE BODY                 8840
SYS.KUPU$UTILITIES                       PACKAGE BODY                 8840
SYS.DBMS_REPCAT_EXP                      PACKAGE BODY                 8840
SYS.DBMS_CDC_EXPVDP                      PACKAGE BODY                 8840

total<<10MB

DataPump内部利用高级队列 Advanced Queue技术,所以也会用到流池Streams Pool,与之相关的Streams Pool组件包括:

POOL         NAME                            BYTES
------------ -------------------------- ----------
streams pool sob_kgqmrec                     19584
streams pool Sender info                     17616
streams pool recov_kgqbtctx                  11904
streams pool kwqbcqini:spilledovermsgs        3168
streams pool kgqbt_alloc_block                2096
streams pool recov_kgqmsub                    1608
streams pool kwqbsinfy:bqg                    1232
streams pool recov_kgqmctx                    1104
streams pool kwqbsinfy:mpr                    1088
streams pool kwqbsinfy:sta                     768
streams pool kgqmsub                           584
streams pool fixed allocation callback         448
streams pool kwqbsinfy:cco                     376
streams pool image handles                     288
streams pool kwqbsinfy:bms                     256
streams pool name_kgqmsub                      256
streams pool spilled:kwqbl                     256
streams pool deqtree_kgqmctx                   144
streams pool substree_kgqmctx                  144
streams pool kgqmdm_fl_1                       144
streams pool time manager index                144
streams pool msgtree_kgqmctx                   144

当Streams Pool分配过小同样可能引发Expdp/Impdp因ORA-04031 (“streams pool”, …)错误而意外终止,详见<EXPDP Fails With ORA-04031 (“streams pool”, …) [ID 457724.1]>

此外DataPump还可能从Large Pool中分配PX msg pool作为并行进程通信池,但是这种内存开销很小。

DataPump对PGA的消耗主要体现在koh-kghu sessi sub-heap上,已知的Bug 10404544(ORA – 4030 DURING EXPDP)、7681160(EXPDP FAILS WITH ORA-4030 WHEN SELECT FROM SYS.KU$_PROCACT_SCHEMA_VIEW)说明该子堆sub-heap(和另一个sub-heap kxs-heap-w)在10.2.0.4上使用expdp时可能引发内存泄露memory-leak。我们来具体看一下DataPump Manager DM00的PGA使用情况:

SQL> oradebug setospid 5278;
Oracle pid: 51, Unix process pid: 5278, image: oracle@rh2.oracle.com (DM00)

SQL> oradebug dump heapdump 536870917;
Statement processed.

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

SQL> select pga_alloc_mem / 1024 / 1024, pga_used_mem / 1024 / 1024, pname
  2    from v$process
  3   where addr = '00000000DCBC51F8'
  4  /

PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PNAME
----------------------- ---------------------- -----
             8.06555557             7.25846481 DM00

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

HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568
HEAP DUMP heap name="pga heap"  desc=0xb7c8ba0
Total heap size    =  2353064
Permanent space    =   654544
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x7fae4fdfaa98
Total heap size    =   730640
Permanent space    =       80
HEAP DUMP heap name="koh-kghu call "  desc=0x7fae4fb650d0
Total heap size    =   246080
Permanent space    =       80
HEAP DUMP heap name="diag pga"  desc=0x7fae500347e0
Total heap size    =    70816
Permanent space    =    13472
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fdd15c8
Total heap size    =    68064
Permanent space    =     1336
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb50ee0
Total heap size    =    12272
Permanent space    =     2096
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb66c00
Total heap size    =    11784
Permanent space    =     1856
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc14fc0
Total heap size    =     9816
Permanent space    =     1392
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc10068
Total heap size    =     9664
Permanent space    =     1240
HEAP DUMP heap name="PLS PGA hp"  desc=0x7fae4fdeeab8
Total heap size    =    46784
Permanent space    =       80
HEAP DUMP heap name="top call heap"  desc=0xb7ce3c0
Total heap size    =   458584
Permanent space    =     1920
HEAP DUMP heap name="callheap"  desc=0xb7cd578
Total heap size    =   343104
Permanent space    =    65536
HEAP DUMP heap name="callheap"  desc=0xb7cd4c0
Total heap size    =    21616
Permanent space    =      968
HEAP DUMP heap name="kti call subhe"  desc=0x7fae4fdee018
Total heap size    =    20584
Permanent space    =       80
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd108
Total heap size    =     8952
Permanent space    =      200
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd030
Total heap size    =     2072
Permanent space    =     1672
HEAP DUMP heap name="top uga heap"  desc=0xb7ce5e0
Total heap size    =  4520496
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568

一般遇到这类memory leak的问题,Oracle内部会使用一个名叫heap.awk的dump分析工具(类似于ass.awk)来找出问题子堆(problematic sub-heap),我们可以使用图形化的免费工具Membai来替代heap.awk。

heap_dump_analysis

总结:

DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误,我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数,如果使用手动的SGA管理的话,那么有必要保证shared_pool_size的设置适宜,对于Streams Pool和Large Pool一般设置为150MB大小。

推荐一款性能诊断工具-Membai

Marcus Mönnig是一名居住在德国汉堡的Oracle Database顾问及DBA,他开发了一款基于Windows平台的面向Oracle DBA和Consultant的图形化工具-Membai,该软件侧重于性能分析。

Membai在现有的Windows主流操作系统包括XP、2003和win7上都可以流畅工作。唯一的先决条件是需要预装Full Oracle Client客户端软件。Membai所支持的Oracle数据库版本包括从9.2到最新的11.2,因为主要是在10.2上测试,所以部分功能可能在9.2中无法正常工作。

Membai的特性包括:

  • Instant access to important database views vital for DBAs and consultants
  • Retrieved data in Mumbai can be slices and dices in various ways (sorting, filtering, grouping, group arithmetic, etc.)
  • Visualization of data in graphs
  • SQL console window inducing support for bind variables
  • Executions plans for SQL statements from cursor cache, through EXPLAIN PLAN or through DBMS_XPLAN from the Mumbai SQL console window
  • Retrieve alert.log and trace files from the database server to Mumbai and process/analyze them there.
  • Start and stop 10046 traces for any session or process and trace the statements from your SQL console session with just a few clicks
  • Powerful 10046 trace file viewer if you need to dig into the details of a TKPROF or OraSRP report. OraSRP reports can be generated from within Mumbai.
  • Heap dump analyzer that aggregates data from trace files
  • Instant connections to DB server with RDP, putty or WinSCP
  • Run snapper from within Mumbai and let Mumbai visualize the data collected by snapper
  • Analyze Statspack or AWR data, e.g. to find interesting intervals for further analysis

值得一提的是Membai提供了heapdump analyze的功能,这对于经常和heapdump打交道的Oracle Consultant来说是一个福音,此外它同时还提供create dump trace的功能,该功能基本涵盖了主要的Oracle转储和诊断事件,有了它我们无需在忘记dump/trace语句时再去翻手册了。其heapdump analyze和create dump trace功能见下图:

heapdump_analyze

create_dump_function

Membai最新的版本是1.1.1.185 (released 17 Apr. 2011): Download here

 

 

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内存泄露的问题,我们因当如何第一时间收集有用的信息,以供后续诊断呢?我在这里提供一些可选的方案:

  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.....

沪ICP备14014813号-2

沪公网安备 31010802001379号