分类目录归档：Oracle

hint指定index的深入理解

发表于 2012 年 4 月 9 日由惜分飞

模拟环境
创建一个表，含有位图index和b-tree index

SQL> create table t_xifenfei as 
  2  select object_id,object_name from dba_objects;

Table created.

SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);

Index created.

SQL> CREATE BITMAP INDEX  bitmap_t_xifenfei on t_xifenfei(object_name);

Index created.

SQL> BEGIN
  2  dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => true);
  3  END;
  4  / 

PL/SQL procedure successfully completed.

无index hint

SQL> SET AUTOT TRACE EXPL STAT 
SQL> SELECT OBJECT_ID FROM t_xifenfei;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   841K|  4109K|   886   (3)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI |   841K|  4109K|   886   (3)| 00:00:11 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      60525  consistent gets
          0  physical reads
          0  redo size
   15543305  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，预料之中事件

index hint b_tree_t_xifenfei

SQL> SET  LINESIZE 150
SQL> SELECT /*+ INDEX(T b_tree_t_xifenfei) */object_id from t_xifenfei t;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，这里的疑惑是：
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index，因为使用这个的cost会大于全表扫描

index hint 一个无效index

SQL> SELECT /*+ INDEX(T abc) */object_id from t_xifenfei t;

845708 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里使用了一个无效的index，也使用了BITMAP_T_XIFENFEI，让人更加的感觉奇怪

原因分析
If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index，优化器会扫描表中所有可以使用的index，然后选择cost最小的index或者index组合，而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候，因为object_id可能有值为空(列没定义为not null)，所以不能使用该index，从而也就是相当于一个无效的index，从而扫描该表的其他可以使用的index，导致使用了位图索引(该类型index不排除null)，而不是全表扫描.
温馨提示:使用hint指定index的时候需要慎重，如果不合适或者无效，可能导致程序效率更低

发表在 Oracle | 一条评论

通过dump分析undo镜像内容

发表于 2012 年 4 月 7 日由惜分飞

一.模拟表并插入数据

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t_xff(id number,name varchar2(10));

Table created.

SQL> insert into t_xff values(1,'a');

1 row created.

SQL> insert into t_xff values(2,'b');

1 row created.

SQL> insert into t_xff values(3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL>  select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xff;

ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASfUAAEAAAACvAAA          4        175          0
AAASfUAAEAAAACvAAB          4        175          1
AAASfUAAEAAAACvAAC          4        175          2

二.dump当前表数据

SQL> alter system dump datafile 4 block 175;

System altered.

--表中数据
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62     <---注意原始值(b)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63

三.修改表中数据(新建会话并不提交)

SQL> select * from t_xff;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> update t_xff set name='F' where id=2;

1 row updated.

SQL> select * from t_xff;

        ID NAME
---------- --------------------
         1 a
         2 F
         3 c

四.dump修改后数据块

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.


block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  46     <--数据内容已经修改(由b改为了F)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63
end_of_block_dump

五.找出本次更新操作对应undo块
1.通过v$transaction视图找出


SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2         31        750       8155          3          6

2.通过更新块的XID信息找出

Block header dump:  0x010000af
 Object id on Block? Y
 seg/obj: 0x127d4  csc: 0x00.11216d  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10000a8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.000001ff  0x00c01748.009f.10  C---    0  scn 0x0000.00112130
0x02   0x0002.01f.000002ee  0x00c01fdb.00f5.06  ----    1  fsc 0x0000.00000000
bdba: 0x010000af
data_block_dump,data header at 0xb6ce9664
--这里可以看出Itl=0x02为锁信息

SQL> select name from v$rollname where usn=2;

NAME
------------------------------------------------------------
_SYSSMU2_4228238222$

SQL> alter system dump undo header "_SYSSMU2_4228238222$";

System altered.

 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x02ee  0x0019  0x0000.0010cc90  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x01    9    0x00  0x02ee  0x0018  0x0000.0010cf00  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662985
   0x02    9    0x00  0x02ee  0x0000  0x0000.0010cc84  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x03    9    0x00  0x02ee  0x0011  0x0000.00112094  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670810
   0x04    9    0x00  0x02ee  0x0012  0x0000.0010ccc1  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x05    9    0x00  0x02ee  0x0017  0x0000.0010cd13  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x06    9    0x00  0x02ee  0x0004  0x0000.0010ccb9  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x07    9    0x00  0x02ee  0xffff  0x0000.00112119  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x08    9    0x00  0x02ee  0x0006  0x0000.0010ccab  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x09    9    0x00  0x02ee  0x000a  0x0000.0010ccf4  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0a    9    0x00  0x02ee  0x0014  0x0000.0010ccf8  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0b    9    0x00  0x02ee  0x001a  0x0000.0010d061  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x0c    9    0x00  0x02ee  0x0009  0x0000.0010ccdc  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0d    9    0x00  0x02ee  0x0001  0x0000.0010ce1f  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662386
   0x0e    9    0x00  0x02ee  0x001d  0x0000.00112113  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x0f    9    0x00  0x02ed  0x0002  0x0000.0010cc79  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x10    9    0x00  0x02ee  0x001e  0x0000.00112017  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1333670781
   0x11    9    0x00  0x02ed  0x000e  0x0000.001120dd  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670813
   0x12    9    0x00  0x02ee  0x000c  0x0000.0010ccd3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x13    9    0x00  0x02ee  0x0016  0x0000.0010cd2e  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x14    9    0x00  0x02ee  0x0005  0x0000.0010cd0b  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x15    9    0x00  0x02ed  0x0020  0x0000.0010cc9d  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x16    9    0x00  0x02ee  0x000d  0x0000.0010cd33  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x17    9    0x00  0x02ee  0x0013  0x0000.0010cd20  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x18    9    0x00  0x02ee  0x000b  0x0000.0010d051  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x19    9    0x00  0x02ed  0x0015  0x0000.0010cc96  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1a    9    0x00  0x02ed  0x001b  0x0000.0010d102  0x00c01fda  0x0000.000.00000000  0x00000002   0x00000000  1333664305
   0x1b    9    0x00  0x02ee  0x0010  0x0000.0010d13e  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333664453
   0x1c    9    0x00  0x02c5  0x000f  0x0000.0010cc72  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1d    9    0x00  0x02ee  0x0007  0x0000.00112115  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x1e    9    0x00  0x02ee  0x0021  0x0000.00112035  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670797
   0x1f   10    0x80  0x02ee  0x0003  0x0000.00112157  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  0
   0x20    9    0x00  0x02ed  0x0008  0x0000.0010cca3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x21    9    0x00  0x02ec  0x0003  0x0000.00112052  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670803

SQL> select to_number('00c01fdb','xxxxxxxxxxx') from dual;

TO_NUMBER('00C01FDB','XXXXXXXXXXX')
-----------------------------------
                           12591067

SQL> select dbms_utility.data_block_address_file(12591067) file#,
  2  dbms_utility.data_block_address_block(12591067) block  from dual;

     FILE#      BLOCK
---------- ----------
         3       8155

3.通过更新块的Uba信息找出

00c01fdb 对应的2进制为:
0000 0000  11 | 00 0000 0001 1111 1101 1011
2+1=3           4096+2048+1024+512+256+128+64+16+8+2+1=8155

六.dump 对应undo数据块

SQL> alter system dump datafile 3 block 8155;

System altered.


uba: 0x00c01fdb.00f5.04 ctl max scn: 0x0000.0010cc60 prv tx scn: 0x0000.0010cc6e
txn start scn: scn: 0x0000.00112028 logon user: 84
 prev brb: 12591059 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows: 
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x010000af  hdba: 0x010000aa
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62   <---以前的值(b)

试验说明:数据库的undo只是保存修改值的前镜像，而非修改数据块或者行记录的镜像

发表在 Oracle | 3 条评论

ORA-00600[729]分析和处理方法

发表于 2012 年 4 月 6 日由惜分飞

alert中ORA-00600[729]

Fri Apr  6 04:30:04 2012
Errors in file /oracle9/app/admin/crm/udump/crm2_ora_2548236.trc:
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []

a. the first bracketed number [729] is the common argument for space leak problems.
b. the second number [1067976] is the number of bytes leaked by the error.
c. the third argument is always [space leak].

分析trace文件

*** 2012-04-06 04:30:04.656
*** SESSION ID:(1361.35607) 2012-04-06 04:30:04.648
******** ERROR: UGA memory leak detected 1067976 ********
******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0

a. the memory was leaked from the UGA area
b. the amount leaked is reported again in the text (1067976 bytes).
c. the above few lines describe this dump as SESSION HEAP with the descriptor 0x1103a05f0.

计算泄露内存大小

******************************************************
HEAP DUMP heap name="session heap"  desc=1103a05f0
 extent sz=0xff80 alt=32767 het=32767 rec=0 flg=3 opc=3
 parent=110009628 owner=700000c3b6f5620 nex=0 xsz=0xff80
EXTENT 0 addr=1107dbf50
  Chunk        1107dbf60 sz=    65392    free      "               "
EXTENT 1 addr=1107cbf50
  Chunk        1107cbf60 sz=    65392    free      "               "
EXTENT 2 addr=110541da0
  Chunk        110541db0 sz=    61312    free      "               "
EXTENT 3 addr=11062ae88
  Chunk        11062ae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 4 addr=1105dae88
  Chunk        1105dae98 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 5 addr=110550d48
  Chunk        110550d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 6 addr=110500d48
  Chunk        110500d58 sz=   266264    freeable  "kllcqgf:kllsltb"
EXTENT 7 addr=1104e1df0
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104e1ec8 sz=    65192    free      "               "
EXTENT 8 addr=1104c1df0
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1104cbd10 sz=       56    free      "               "
  Chunk        1104cbd48 sz=      408    freeable  "kcbl_structure_"
  Chunk        1104cbee0 sz=     6952    free      "               "
  Chunk        1104cda08 sz=     2424    freeable  "kllcqc:kllcqslt"
  Chunk        1104ce380 sz=    14832    free      "               "
EXTENT 9 addr=1104d1df0
  Chunk        1104d1e00 sz=    65392    free      "               "
EXTENT 10 addr=1104b1df0
  Chunk        1104b1e00 sz=      544    free      "               "
  Chunk        1104b2020 sz=       88    freeable  "kllcqc:kllcq   "
  Chunk        1104b2078 sz=    64760    free      "               "
EXTENT 11 addr=110427390
  Chunk        1104273a0 sz=    65392    free      "               "
EXTENT 12 addr=110417390
  Chunk        1104173a0 sz=    65392    free      "               "
EXTENT 13 addr=110407390
  Chunk        1104073a0 sz=    65392    free      "               "
EXTENT 14 addr=1103f7390
  Chunk        1103f73a0 sz=    65392    free      "               "
EXTENT 15 addr=1103e7390
  Chunk        1103e73a0 sz=    65392    free      "               "
EXTENT 16 addr=1103d7390
  Chunk        1103d73a0 sz=    65392    free      "               "
EXTENT 17 addr=1103c7390
  Chunk        1103c73a0 sz=      408    free      "               "
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        1103c7df0 sz=    62752    free      "               "
EXTENT 18 addr=1103b7390
  Chunk        1103b73a0 sz=    65392    free      "               "
EXTENT 19 addr=110370080
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        110370868 sz=    63384    free      "               "
EXTENT 20 addr=110360098
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
  Chunk        110365070 sz=    44944    free      "               "
Total heap size    =  2172616
FREE LISTS:
 Bucket 0 size=56
  Chunk        1104cbd10 sz=       56    free      "               "
 Bucket 1 size=88
 Bucket 2 size=152
 Bucket 3 size=168
 Bucket 4 size=280
  Chunk        1103c73a0 sz=      408    free      "               "
 Bucket 5 size=432
 Bucket 6 size=536
  Chunk        1104b1e00 sz=      544    free      "               "
 Bucket 7 size=1048
 Bucket 8 size=2072
 Bucket 9 size=4120
  Chunk        1104cbee0 sz=     6952    free      "               "
 Bucket 10 size=8216
  Chunk        1104ce380 sz=    14832    free      "               "
 Bucket 11 size=16408
 Bucket 12 size=32792
  Chunk        110365070 sz=    44944    free      "               "
  Chunk        110370868 sz=    63384    free      "               "
  Chunk        1104d1e00 sz=    65392    free      "               "
  Chunk        1103b73a0 sz=    65392    free      "               "
  Chunk        1103c7df0 sz=    62752    free      "               "
  Chunk        1103d73a0 sz=    65392    free      "               "
  Chunk        1103f73a0 sz=    65392    free      "               "
  Chunk        1104073a0 sz=    65392    free      "               "
  Chunk        1104b2078 sz=    64760    free      "               "
  Chunk        1103e73a0 sz=    65392    free      "               "
  Chunk        1104e1ec8 sz=    65192    free      "               "
  Chunk        1104273a0 sz=    65392    free      "               "
  Chunk        1104173a0 sz=    65392    free      "               "
  Chunk        1107cbf60 sz=    65392    free      "               "
  Chunk        110541db0 sz=    61312    free      "               "
  Chunk        1107dbf60 sz=    65392    free      "               "
 Bucket 13 size=65560
 Bucket 14 size=131096
 Bucket 15 size=262168
 Bucket 16 size=524312
 Bucket 17 size=2097176
Total free space   =  1039056
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
  Chunk        1104e1e00 sz=      200    perm      "perm           "  alo=200
  Chunk        1104c1e00 sz=    40720    perm      "perm           "  alo=40720
  Chunk        1103c7538 sz=     2232    perm      "perm           "  alo=2232
  Chunk        110370090 sz=     2008    perm      "perm           "  alo=2008
  Chunk        1103600a8 sz=    20424    perm      "perm           "  alo=20424
Permanent space    =    65584
******************************************************

FREEABLE and RECREATABLE chunks总和等于1067976 byte(leaked memory)

会话状态分析

*** 2012-04-06 04:30:04.658
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [729], [1067976], [space leak], [], [], [], [], []
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedmp+0148          bl       ksedst               1029746FC ?
ksfdmp+0018          bl       01FD4014             
kgeriv+0118          bl       _ptrgl               
kgesiv+0080          bl       kgeriv               000000001 ? 000000002 ?
                                                   1100610D0 ? 000000000 ?
                                                   00000000A ?
ksesic2+005c         bl       kgesiv               FFFFFFFFFFF9320 ? 1101FAF78 ?
                                                   110006308 ? 1103A0818 ?
                                                   000000009 ?
ksmuhe+026c          bl       ksesic2              2D9000002D9 ? 000000000 ?
                                                   000104BC8 ? 000000001 ?
                                                   00000000A ? 103164968 ?
                                                   12E0BE826D694B2F ?
                                                   000000000 ?
ksmugf+0214          bl       ksmuhe               110002A20 ? 110061238 ?
                                                   000000009 ? 102975DE8 ?
ksuxds+170c          bl       ksmugf               000000000 ? 020000000 ?
                                                   1029754D0 ?
ksudel+006c          bl       ksuxds               700000C3B6F5620 ? 100000001 ?
opilof+03dc          bl       01FD427C             <--表示logoff
opiodr+08cc          bl       _ptrgl               
ttcpip+0cc4          bl       _ptrgl               
opitsk+0d60          bl       ttcpip               11000CF90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
opiino+0758          bl       opitsk               000000000 ? 000000000 ?
opiodr+08cc          bl       _ptrgl               
opidrv+032c          bl       opiodr               3C00000018 ? 4101FAF78 ?
                                                   FFFFFFFFFFFF7B0 ? 0A000F350 ?
sou2o+0028           bl       opidrv               3C0C000000 ? 4A00E8B50 ?
                                                   FFFFFFFFFFFF7B0 ?
main+0138            bl       01FD3A28             
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------
………………
 ----------------------------------------
    SO: 700000c3b6f5620, type: 4, owner: 700000c3c987a18, flag: INIT/-/-/0x00
--flag: (41) USR/- BSY/-/-/DEL/-/- shows that the session has been deleted
    (session) trans: 0, creator: 700000c3c987a18, flag: (41) USR/- BSY/-/-/DEL/-/- 
              DID: 0002-0927-01D67CAD, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, sql: 700000caf2c0e30, psql: 700000caf2c0e30, user: 52/MONITOR
    O/S info: user: oracrm, term: , ospid: 1490968, machine: zwq_crm2
              program: exp@zwq_crm2 (TNS V1-V3)
    last wait for 'SQL*Net message from client' blocking sess=0x0 seq=59222 wait_time=1537
                driver id=54435000, #bytes=1, =0
    temporary object counter: 0
    ----------------------------------------

a.在logoff的时候发生UGA中的session heap发生内存泄露
b.该进程是一个exp导出数据库程序，并且该程序已经被释放

出现ORA-00600[729]原因

Memory leak problems generally occur when Oracle is trying to free memory allocated to a process.
The memory leak dump is generally discovered during session logoff,
when Oracle frees the heaps that are allocated for the user process.

When a user connects to Oracle, a user process is created and at that time the heap is allocated.
Every process will have its own memory heap.

The memory is organized in to heaps and every heap consists of one or more extents.
Each extent contains a series of contiguous memory chunks, and these chunks can be
either FREE or ALLOCATED. The Generic Heap Manager takes care of allocating and deallocating
the memory chunks, with the help of FREE LISTS and LRU LISTS.

Chunk types are as follows:

1. FREE
2. FREEABLE
3. RECREATABLE
4. PERMANENT
5. FREEABLE WITH MARK

It is not mandatory that each extent contain only one type of chunk.
Extents can contain various types of chunks. When processes require memory chunks,
they are allocated as needed. Oracle keeps track of the amount of memory allocated for the process internally.

When the process terminates, all of the memory that has been allocated for the process is automatically released.
When the memory is released the allocated heaps are freed. Generally,
when the heap is freed the only chunks that the process should identify
as allocated are the PERMANENT chunks and FREE chunks on the freelist.
If the process finds there are still FREEABLE or RECREATABLE chunks remaining,
then the process has not properly deallocated the memory.
This situation is considered a space leak.

ORA-00600[729]处理方案

1. If there are no other errors reported at the same time, 
this may be a case where the error was a rare occurrence and can be safely ignored. 
As a rule of thumb, leaks less than 90,000 bytes in size are considered to be of low significance. 
The solution in this case is to set event 10262 (see below). 

a. Set the following event in init.ora parameter file. 
   This example disables reporting for space leaks less than 90000 bytes:
event = "10262 trace name context forever, level 90000"

b. Stop and restart the database.

If the level is set to 1, space leak checking is disabled. 
This is not advised because large memory leaks will be missed.

If the event is set to a value greater than 1, 
any space leak up to the number specified in the event is ignored.

2. Is the leak in the SGA? The alert.log should be reviewed for additional 
errors such as ORA-4030 and ORA-4031 to ensure there are no additional 
problems with the shared pool or operating system memory. 

3. Does the error reproduce with a given task? If so, this is 
a case that should be investigated further because the leak could be a known bug. 
See Note 31056.1 ORA-600 [729] UGA Space Leak for a list of known bugs and fixes.

参考:Understanding and Diagnosing ORA-600 [729] Space Leak Errors [ID 403584.1]

发表在 ORA-xxxxx | 标签为 ORA-600[729] | 一条评论

17813235971

QQ 咨询

分类目录归档：Oracle

hint指定index的深入理解

通过dump分析undo镜像内容

ORA-00600[729]分析和处理方法

文章分类

最近发表

分类目录归档：Oracle

hint指定index的深入理解

通过dump分析undo镜像内容

ORA-00600[729]分析和处理方法

标签云

文章分类

最近发表