ora-7445 [kghalp+0500] [SIGSEGV]错误

2010/03/26 by 1 Comment

今天没有外出(似乎人不到现场就特别容易出问题),早上10点左右接到电话被告知crm11实例上出现了7445错误，准备用web vpn拨上去查看一下，赫然发觉windows 7 不支持这种vpn(准确说ie8和firefox都不支持)；无奈无奈只好用拨号。
发现alert log中出现大量 7445错误记录：

Fri Mar 26 09:24:53 2010
Errors in file /oravl01/oracle/admin/CRMDB1/udump/crmdb11_ora_6754320.trc:
ORA-07445: exception encountered: core dump [kghalp+0500] [SIGSEGV] [Invalid permissions for mapped object] [0x00000003B] [] []
Fri Mar 26 09:24:55 2010
Trace dumping is performing id=[cdmp_20100326092455]
Fri Mar 26 09:31:16 2010
Errors in file /oravl01/oracle/admin/CRMDB1/udump/crmdb11_ora_2994552.trc:
ORA-07445: exception encountered: core dump [kghalp+0500] [SIGSEGV] [Invalid permissions for mapped object] [0x00000003B] [] []

看到kghalp函数第一印象 ，是Oracle中堆管理使用的函数；
让我们猜猜字面意思？ k -> kernel g -> generic h-> heap a-> allocation p-> point
再让我们来看一下当时的call stack:

Exception signal: 11 (SIGSEGV), code: 51 (Invalid permissions for mapped object), addr: 0x3b, PC: [0x1000973e0, kghalp+0500]
Registers:
iar: 00000001000973e0, msr: a00000000000d0b2
 lr: 00000001013a6df8,  cr: 0000000022292484
r00: 0000000000000010, r01: 0ffffffffffcb160, r02: 000000011022a9c0,
r03: 0000000000000002, r04: 0000000000000000, r05: 0000000000000100,
r06: 0000000000000001, r07: 0000000000000000, r08: 0000000000000000,
r09: 0000000000000000, r10: 00000000101b60d8, r11: 0000000000000004,
r12: 0000000024592484, r13: 000000011026bfe0, r14: 0000000000000000,
r15: 0000000000009000, r16: 0000000110195b2c, r17: 0000000000000000,
r18: 0000000000000001, r19: 0000000000000000, r20: 0000000000001000,
r21: 0000000000000000, r22: 0000000000000100, r23: 0000000000000001,
r24: 0000000000000000, r25: 0000000000000000, r26: 0000000000000001,
r27: 0000000104c7fd44, r28: 0000000000000000, r29: 0000000000000100,
r30: 0000000000000000, r31: 0000000110195a58,
*** 2010-03-26 09:57:28.679
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kghalp+0500] [SIGSEGV] [Invalid permissions for mapped object] [0x00000003B] [] []
Current SQL statement for this session:
INSERT INTO AUDIT_DDL_LOG (DDL_TIME, SESSION_ID, OS_USER, IP_ADDRESS, TERMINAL, HOST, USER_NAME, DDL_TYPE, OBJECT_TYPE, OWNER, OBJECT_NAME, SQL_TEXT) VALUES (SYSDATE, SYS_CONTEXT('USERENV','SESSIONID'), SYS_CONTEXT('USERENV','OS_USER'), SYS_CONTEXT('USERENV','IP_ADDRESS'), SYS_CONTEXT('USERENV','TERMINAL'), SYS_CONTEXT('USERENV','HOST'), ORA_LOGIN_USER, ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME, :B1 )
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000043da500d0        10  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 104A54EED ?
ksedmp+0290          bl       ksedst               104A54870 ?
ssexhd+03e0          bl       ksedmp               300001D15 ?
000044C0             ?        00000000
parchk+01f4          bl       kghalp               000000000 ?
                                                   2842288200000001 ?
                                                   000000000 ? 000000000 ?
                                                   000001040 ? 110195B2C ?
ptmak+0168           bl       parchk               FFFFFFFFFFCB560 ?
                                                   FFFFFFFFFFCB430 ?
                                                   FFFFFFFFFFCB430 ?
pdybF00_Init+0244    bl       ptmak                10008049C ? 000000000 ?
                                                   FFFFFFFFFFCB4F0 ? 07FFFFFFF ?
pdy1F79_Init+00c8    bl       pdybF00_Init         110BEB1D0 ?
pdy1F01_Driver+0048  bl       pdy1F79_Init         FFFFFFFFFFCBC40 ?
pdli_new_cog+00f0    bl       pdy1F01_Driver       FFFFFFFFFFCBCE0 ? 000000000 ?
pdlifu+0264          bl       pdli_new_cog         1013885F4 ? FFFFFFFFFFCCB00 ?
                                                   7000004383E7680 ?
phpcog+0010          bl       pdlifu               FFFFFFFFFFCD958 ?
                                                   7000004383E7680 ? 104C95048 ?
phpcmp+0f80          bl       phpcog               FFFFFFFFFFCC4F0 ? 000000000 ?
pcicms2+02d4         bl       phpcmp               FFFFFFFFFFCD958 ?

发生错误的最上层 kghalp 函数由 parchk 调用， 这似乎是一个package check函数(猜测，呵呵). 我们来整理一下思路， parchk 函数调用了 kghalp函数以帮其分配内存，但却得到了一个非法的低地址[[0x00000003B],正常情况下正文段使用的空间； 这看起来显然是一个bug。
让我们来查查support.oracle.com , 键入7445 kghalp 和sigsegv 关键字 (很多时候不需要使用ora 600/7445 lookup tools).
bug 8244533 赫然显目：

Bug 8244533: ORA-07445 [KGHALP] ERRORS COMPILING PACKAGE WITH DEBUG
    STACK TRACE:
    ------------
       ksedst <- ksedmp <- ssexhd <- 000044BC <- parchk        <- ptmak <-
    pdybF00_Init <- pdy1F79_Init <- pdy1F01_Driver <- pdli_new_cog         <-
    pdlifu <- phpcog <- phpcmp <- pcicms2 <- pcicms          <- kkxcms <- kkxswcm
    <- kkxmpbms <- kkxmesu <- xtypls           <- qctopls <- qctcopn <- qctcopn

    Exception signal: 11 (SIGSEGV), code: 51 (Invalid permissions for mapped
    object),
    addr: 0x3b, PC: [0x1000973e0, kghalp+0500]
    Registers:
    iar: 00000001000973e0, msr: a00000000000d0b2
    lr: 000000010139ffb8,  cr: 00000000222a2484
    r00: 0000000000000010, r01: 0ffffffffffe2980, r02: 00000001101e5ab8,
    r03: 0000000000000002, r04: 0000000000000000, r05: 0000000000000100,
    r06: 0000000000000001, r07: 0000000000000000, r08: 0000000000000000,
    r09: 0000000000000000, r10: 0000000010171200, r11: 0000000000000004,
    r12: 00000000245a2484, r13: 000000011021fbc0, r14: 0000000000000000,
    r15: 0000000000009000, r16: 0000000110150c54, r17: 0000000000000000,
    r18: 0000000000000001, r19: 0000000000000000, r20: 0000000000001000,
    r21: 0000000000000000, r22: 0000000000000100, r23: 0000000000000001,
    r24: 0000000000000000, r25: 0000000000000000, r26: 0000000000000001,
    r27: 0000000104c5983c, r28: 0000000000000000, r29: 0000000000000100,
    r30: 0000000000000000, r31: 0000000110150b80,
    *** 16:37:14.603
    ksedmp: internal or fatal error
    ORA-7445: exception encountered: core dump [kghalp+0500] [SIGSEGV]
    [Invalid permissions for mapped object] [0x00000003B] [] []
    Current SQL statement for this session:
    select dummy from dual where  ora_dict_obj_type = 'TABLE'
----- Call Stack Trace -----ptmak pdybF00_Init pdy1F79_Init pdy1F01_Driver pdli_new_cog pdlifuphpcog phpcmp pcicms2 pcicms kkxcms kkxswcm kkxmpbms kkxmesu xtyplsTo Filer.Based on this call stack this would appear a likely match forbug 6951953 Abstract: ORA-7445 [PTMAK] IMPORTING PACKAGE COMPILED DEBUG.This bug is fixed on 10.2.0.5 and there is a 10.2.0.4 patch available for IBM AIX Based Systems (64-bit).It maybe worth while to have the customer apply the patch to seeif it resolves the issue.Also the uploaded files included test.sql is this a reproducable testcase?

这个bug 似乎仅在 IBM AIX on POWER Systems (64-bit) 发生，当以DEBUG 模式编译包时有一定几率出现。
好了，既然已经了解了可能发生的诱因，我们可以进一步分析了，接下来看看 errorstack trace信息中 的SO 记录。

      SO: 70000043d217668, type: 53, owner: 70000048cee2238, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=70000043d217668 handle=700000446261588 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=70000043d2176e8[70000042b52b368,70000042bb9a808] htb=70000044929b460 ssga=70000044929ad68
      user=70000048cee2238 session=70000048eb33010 count=1 flags=[0000] savepoint=0x4bac1488
      LIBRARY OBJECT HANDLE: handle=700000446261588 mtx=7000004462616b8(1) cdp=1
      name=ALTER TRIGGER "SHUCRM3O"."TRI_PRODUCT_INSTANCE_RELATED" COMPILE DEBUG REUSE SETTINGS
      hash=164e6a8942406cee159f8943a1a3c85e timestamp=03-26-2010 09:52:12
      namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=16 hpc=0002 hlc=0002
      lwt=700000446261630[700000446261630,700000446261630] ltm=700000446261640[700000446261640,700000446261640]
      pwt=7000004462615f8[7000004462615f8,7000004462615f8] ptm=700000446261608[700000446261608,700000446261608]
      ref=700000446261660[700000446261660,700000446261660] lnd=700000446261678[700000446261678,700000446261678]
        LIBRARY OBJECT: object=70000045adbc1e8
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
             5 70000041776f5c0 70000045ae44720 70000042bfa3a20
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
            0 70000043d9fed20 70000045adbc300 I/P/A/-/-    0 NONE   00

的确有以debug 模式编译对象的语句，不过对象不是包而是trigger ; 看起来只要是可以以debug 模式compile 的对象都有可能引发该问题。
好了，问题到这里已经比较明确了： 应用端以DEBUG模式重新编译包引发了 Oracle bug 8244533,从而导致了对应服务进程的崩溃；总算是虚惊一场，之后通过trace内的machine和user信息找到了实施变更的应用方人员并教育之。

Filed Under: Oracle Tagged With: , , , , , ,

对Oracle中索引叶块分裂而引起延迟情况的测试和分析

2010/03/21 by 12 Comments

在版本10.2.0.4未打上相关one-off补丁的情况下，分别对ASSM和MSSM管理模式表空间进行索引分裂测试，经过测试的结论如下：

l  在10gr2版本中MSSM方式是不能避免索引分裂引起交易超时问题；

l  10.2.0.4上的one-off补丁因为目前仅存在Linux版本，可以考虑声请补丁后具体测试（因目前没有补丁所以处于未知状态）。

l  合并索引是目前最具可行性的解决方案(alter index coalesce)。

l  最新的11gr2中经测试仍存在该问题。

具体测试过程如下：

1．    自动段管理模式下的索引块分裂

SQL> drop tablespace idx1 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace idx1 datafile ‘?/dbs/idx1.dbf’ size 500M

2  segment space management AUTO

3  extent management local uniform size 10M;

—创建自动段管理的表空间

Tablespace created.

SQL> create table idx1(a number) tablespace idx1;

Table created.

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

Index created.         — 创建实验对象表及索引

SQL> insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000;           — 插入25万条记录

250000 rows created.

SQL> commit;

Commit complete.

SQL>create table idx2 tablespace idx1 as select * from idx1 where 1=2;

Table created.

insert into idx2

select * from idx1 where rowid in

(select rid from

(select rid, rownum rn from

(select rowid rid from idx1 where a between 10127 and 243625 order by a)                    —取出后端部分记录,即每250条取一条

)

where mod(rn, 250) = 0

)

/

933 rows created.

SQL> commit;

Commit complete.

SQL> analyze index idx1_idx validate structure; —分析原索引

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499           0               — 未删除情况下499个叶块

SQL> delete from idx1 where a between 10127 and 243625;                             — 大量删除

commit;

233499 rows deleted.

SQL> SQL>

Commit complete.

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499      233499            — 删除后叶块数量不变

SQL> insert into idx1 select * from idx2;                   — 令那些empty块 不再empty，但每个块中只有一到二条记录，空闲率仍为75-100%

commit;

933 rows created.

Commit complete.

SQL> insert into idx1 select 250000+rownum from all_objects where rownum <= 126;          — 造成leaf块分裂前提

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

997 leaf node splits

997 leaf node 90-10 splits

0 branch node splits

0 queue splits                 —找出当前会话目前的叶块分裂次数

SQL>insert into idx1 values (251000);                                        — 此处确实叶块分裂

1 row created.

SQL> commit;

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

998 leaf node splits

998 leaf node 90-10 splits

0 branch node splits

0 queue splits         — 可以看到对比之前的查询多了一个叶块分裂

SQL> set linesize 200 pagesize 1500;

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1603          0     271601       271601            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         156          0      82803        82803            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1         177 0       3728         3728              1

insert into idx1 values (251000)     — 读了那些实际不空的块，较多buffer_get

1        1409          0      40293        40293            933

insert into idx1 select * from idx2

1      240842          0    3478341      3478341         250000

SQL> insert into idx1 values (251001);                                  — 不分裂的插入

1 row created.

SQL> commit;

Commit complete.

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1603          0     271601       271601            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         156          0      82803        82803            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1           9          0       1640         1640              1

insert into idx1 values (251001) —不分裂的插入，少量buffer_gets

1         177          0       3728         3728              1

insert into idx1 values (251000)

1        1409          0      40293        40293            933

insert into idx1 select * from idx2

1      240842          0    3478341      3478341         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

如演示1所示，在自动段管理模式下大量删除后插入造成许多块为75%-100%空闲率且不完全为空，此后叶块分裂时将引起插入操作的相关前台进程扫描大量“空块“，若这些块不在内存中（引发物理读）且可能需要延迟块清除等原因时，减缓了该扫描操作的速度，造成叶块分裂缓慢，最终导致了其他insert操作被split操作所阻塞，出现enq:tx index contention等待事件。

2．  手动段管理模式下的索引块分裂

SQL> drop tablespace idx1 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace idx1 datafile ‘?/dbs/idx1.dbf’ size 500M

2  segment space management MANUAL                                      — MSSM的情况

3  extent management local uniform size 10M;

Tablespace created.

SQL> create table idx1(a number) tablespace idx1;

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

Table created.

SQL> SQL> insert into idx1 select rownum from all_objects, all_objects where rownum <= 250

Index created.

SQL> SQL> 000;

commit;

create table idx2 tablespace idx1 as select * from idx1 where 1=2;

insert into idx2

select * from idx1 where rowid in

(select rid from

(select rid, rownum rn from

(select rowid rid from idx1 where a between 10127 and 243625 order by a)

)

where mod(rn, 250) = 0

)

/

commit;

250000 rows created.

SQL> SQL>

Commit complete.

SQL> SQL>

Table created.

SQL> SQL>   2    3    4    5    6    7    8    9

933 rows created.

SQL> SQL>

Commit complete.

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499           0

SQL> delete from idx1 where a between 10127 and 243625;

233499 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into idx1 select * from idx2;

commit;

933 rows created.

SQL> SQL>

Commit complete.

SQL> SQL> insert into idx1 select 250000+rownum from all_objects where rownum <= 126;

commit;

126 rows created.

SQL> SQL>

Commit complete.

SQL>

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

1496 leaf node splits

1496 leaf node 90-10 splits

0 branch node splits

0 queue splits

SQL> insert into idx1 values (251000);                                  — 确实分裂

1 row created.

SQL> commit;

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

1497 leaf node splits

1497 leaf node 90-10 splits

0 branch node splits

0 queue splits

— 以上与ASSM时完全一致

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1553          0     283301       283301            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         153          0      78465        78465            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1        963 0      10422        10422              1              — 比ASSM模式下更大量的“空块”读

insert into idx1 values (251000)

1         984          0      35615        35615            933

insert into idx1 select * from idx2

1      238579          0    3468326      3469984         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

SQL> insert into idx1 values (251001);

1 row created.

SQL> commit;

Commit complete.

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1553          0     283301       283301            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         153          0      78465        78465            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1           7 0       1476         1476              1

insert into idx1 values (251001)    —不分裂的情况与ASSM时一致

1         963 0      10422        10422              1

insert into idx1 values (251000)

1         984          0      35615        35615            933

insert into idx1 select * from idx2

1      238579          0    3468326      3469984         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

6 rows selected.

如演示2所示，MSSM情况下叶块分裂读取了比ASSM模式下更多的“空块“；MSSM并不能解决大量删除后叶块分裂需要扫描大量非空块的问题，实际上可能更糟糕。从理论上讲MSSM的freelist只能指出那些未达到pctfree和曾经到达pctfree后来删除记录后使用空间下降到pctused的块（doc：A free list is a list of free data blocks that usually includes blocks existing in a number of different extents within the segment. Free lists are composed of blocks in which free space has not yet reached PCTFREE or used space has shrunk below PCTUSED.），换而言之MSSM模式下”空块“会更多。

3．  自动段管理模式下coalesce后的索引块分裂

SQL> drop tablespace idx1 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace idx1 datafile ‘?/dbs/idx1.dbf’ size 500M

2  segment space management AUTO                                       — ASSM 下 coalesce情况

3  extent management local uniform size 10M;

Tablespace created.

SQL> create table idx1(a number) tablespace idx1;

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

Table created.

SQL> SQL>

Index created.

SQL> SQL> insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000;

commit;

create table idx2 tablespace idx1 as select * from idx1 where 1=2;

insert into idx2

select * from idx1 where rowid in

(select rid from

(select rid, rownum rn from

(select rowid rid from idx1 where a between 10127 and 243625 order by a)

)

where mod(rn, 250) = 0

)

/

commit;

250000 rows created.

SQL> SQL>

Commit complete.

SQL> SQL>

Table created.

SQL> SQL>   2    3    4    5    6    7    8    9

933 rows created.

SQL> SQL>

Commit complete.

SQL> SQL> SQL>

SQL>

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499           0

SQL> delete from idx1 where a between 10127 and 243625;

commit;

233499 rows deleted.

SQL> SQL>

Commit complete.

SQL> alter index idx1_idx coalesce;

Index altered.

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280         33           0 — coalesc后 lf块合并了

SQL> insert into idx1 select * from idx2;

933 rows created.

SQL> SQL> commit;

Commit complete.

SQL>

SQL> insert into idx1 select 250000+rownum from all_objects where rownum <= 126;

commit;

126 rows created.

SQL> SQL>

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

1999 leaf node splits

1995 leaf node 90-10 splits

0 branch node splits

0 queue splits

SQL> insert into idx1 values (251000);                                       — 确实分裂

1 row created.

SQL> commit;

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

2000 leaf node splits

1996 leaf node 90-10 splits

0 branch node splits

0 queue splits

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1603          0     268924       268924            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         156          0      78349        78349            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1          23 0       2218         2218              1                             —少量buffer gets

insert into idx1 values (251000)

1         191          0      15596        15596            933

insert into idx1 select * from idx2

1      240852          0    3206130      3206130         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

SQL> insert into idx1 values (251001);

1 row created.

SQL> commit;

Commit complete.

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1603          0     268924       268924            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         156          0      78349        78349            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1           9 0       1574         1574              1

insert into idx1 values (251001)

1          23 0       2218         2218              1

insert into idx1 values (251000)

1         191          0      15596        15596            933

insert into idx1 select * from idx2

1      240852          0    3206130      3206130         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

6 rows selected.

如演示三所示在删除后进行coalesce操作，合并操作将大量空块分离出了索引结构(move empty out of index structure)，之后的叶块分裂仅读取了少量必要的块。

4．  手动段管理模式下coalesce后的索引块分裂

SQL> drop tablespace idx1 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace idx1 datafile ‘?/dbs/idx1.dbf’ size 500M

2  segment space management MANUAL                               — mssm情况下 coalesce

3  extent management local uniform size 10M;

Tablespace created.

SQL> create table idx1(a number) tablespace idx1;

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

Table created.

SQL> SQL> insert into idx1 select rownum from all_objects, all_objects where rownum <= 250

Index created.

SQL> SQL> 000;

commit;

create table idx2 tablespace idx1 as select * from idx1 where 1=2;

insert into idx2

select * from idx1 where rowid in

(select rid from

(select rid, rownum rn from

(select rowid rid from idx1 where a between 10127 and 243625 order by a)

)

where mod(rn, 250) = 0

)

/

commit;

250000 rows created.

SQL> SQL>

Commit complete.

SQL> SQL>

Table created.

SQL> SQL>   2    3    4    5    6    7    8    9

933 rows created.

SQL> SQL>

Commit complete.

SQL> SQL> SQL>

SQL>

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499           0

SQL> delete from idx1 where a between 10127 and 243625;

commit;

233499 rows deleted.

SQL> SQL>

Commit complete.

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280        499      233499

SQL> alter index idx1_idx coalesce;

Index altered.

SQL> analyze index idx1_idx validate structure;

select blocks,lf_blks,del_lf_rows from index_stats;

Index analyzed.

SQL>

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

1280         33           0

SQL> insert into idx1 select * from idx2;

933 rows created.

SQL> SQL> commit;

Commit complete.

SQL>

SQL> insert into idx1 select 250000+rownum from all_objects where rownum <= 126;

commit;

126 rows created.

SQL> SQL>

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

2502 leaf node splits

2494 leaf node 90-10 splits

0 branch node splits

0 queue splits

SQL> insert into idx1 values (251000);                       — 确实分裂

1 row created.

SQL> commit;

Commit complete.

SQL> select ss.value,sy.name from v$sesstat ss ,v$sysstat sy where ss.statistic#=sy.statistic# and name like ‘%split%’  and sid=(select distinct sid from v$mystat);

VALUE NAME

———- —————————————————————-

2503 leaf node splits

2495 leaf node 90-10 splits

0 branch node splits

0 queue splits

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1553          0     281059       281059            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         153          0      77817        77817            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1          19          0       2010         2010              1                       — 少量buffer get

insert into idx1 values (251000)

1         126          0      15364        15364            933

insert into idx1 select * from idx2

1      238644          0    3229737      3230569         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

SQL> insert into idx1 values (251001);

1 row created.

SQL> commit;

Commit complete.

SQL> select  executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql

2  where sql_text like ‘%insert%idx1%’ and sql_text not like ‘%v$sql%’;

EXECUTIONS BUFFER_GETS DISK_READS   CPU_TIME ELAPSED_TIME ROWS_PROCESSED

———- ———– ———- ———- ———— ————–

SQL_TEXT

——————————————————————————————————————————————————————————————————–

1        1553          0     281059       281059            933

insert into idx2 select * from idx1 where rowid in (select rid from (select rid, rownum rn from (select rowid rid from idx1 where a between 10127 and 243625 order by a) ) where mod(rn, 250) = 0 )

1         153          0      77817        77817            126

insert into idx1 select 250000+rownum from all_objects where rownum <= 126

1          7 0       1460         1460              1

insert into idx1 values (251001)

1          19 0       2010         2010              1

insert into idx1 values (251000)

1         126          0      15364        15364            933

insert into idx1 select * from idx2

1      238644          0    3229737      3230569         250000

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000

6 rows selected.

如演示4所示，MSSM模式下合并操作与ASSM情况下大致一样，合并操作可以有效解决该问题。

5．  Coalesce合并操作的锁影响

SQL> create table coal (t1 int);

Table created.

SQL> create index pk_t1 on coal(t1);

Index created.

SQL> begin

2    for i in 1..3000 loop

3      insert into coal values(i);

4      commit;

5      end loop;

6      end;

7  /

PL/SQL procedure successfully completed.

SQL> delete coal where t1>500;

2500 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze index pk_t1 validate structure;

Index analyzed.    — 注意analyze validate操作会block一切dml操作

SQL> select blocks,lf_blks,del_lf_rows from index_stats;

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

8          6        2500          — 删除后的状态

此时另开一个会话，开始dml操作：

SQL> update coal set t1=t1+1;

500 rows updated.

— 回到原会话

SQL> alter index pk_T1 coalesce;             — coalesce 未被阻塞

Index altered.

— 在另一个会话中commit,以便执行validate structure

SQL> analyze index pk_t1 validate structure;

Index analyzed.

SQL> select blocks,lf_blks,del_lf_rows from index_stats;

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

8          3         500

— 显然coalesce的操作没有涉及有dml操作的块

在没有dml操作的情况下：

SQL> truncate table coal;

Table truncated.

SQL> begin

2    for i in 1..3000 loop

3      insert into coal values(i);

4      commit;

5      end loop;

6      end;

7  /

PL/SQL procedure successfully completed.

SQL> analyze index pk_t1 validate structure;

Index analyzed.

SQL> select blocks,lf_blks,del_lf_rows from index_stats;

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

8          6           0

SQL> delete coal where t1>500;

2500 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze index pk_t1 validate structure;

Index analyzed.

SQL> select blocks,lf_blks,del_lf_rows from index_stats;

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

8          6        2500

SQL> alter index pk_t1 coalesce;

Index altered.

SQL> analyze index pk_t1 validate structure;

Index analyzed.

SQL> select blocks,lf_blks,del_lf_rows from index_stats;

BLOCKS    LF_BLKS DEL_LF_ROWS

———- ———- ———–

8          1           0

—没有dml时，coalesce 操作涉及了所有块

如演示5所示coalesce会避开dml操作涉及的块，但在coalesec的短暂间歇出现在索引上有事务的块不会太多。且coalesce操作不会降低索引高度。

附件是关于rebuild及coalesce索引操作的详细描述：

6．  Coalesce操作总结

优点：

l  是一种快速的操作，对整体性能影响最小（not performance sensitive）。

l  不会锁表，绕过有事务的索引块。

l  可以有效解决现有的问题。

l  不会降低索引高度，引起再次的root split

缺点：

l  需要针对个别对象，定期执行合并操作；无法一劳永逸地全局地解决该问题。

7．  Linux 10.2.0.4上相关补丁的技术交流

Metalink bug 8286901 note中叙述了一位用户遇到相同的问题并提交了SR,当时oracle support给出了one-off补丁，但该用户在apply了该补丁后仍未解决问题。

以下为note 原文：

It is similar to bug8286901, but after applied patch8286901, still see enq tx
contentiona with high “failed probes on index block reclamation”

Issue encountered by customer and Oracle developer (Stefan Pommerenk).




He describes is thus:






"Space search performed by the index splitter can't find space in neighboring






blocks, and then instead of allocating new space, we go and continue to






search for space elsewhere, which manifests itself in block reads from disk,






block cleanouts, and subsequent blocks written due to aggressive MTTR






setting."












"To clarify: the cleanouts are not the problem per se. The culprit seems to






be that the space search performed by the index splitter can't find space in






neighboring blocks, and then instead of allocating new space, we go and






continue to search for space elsewhere, which manifests itself in block reads






from disk, block cleanouts, and subsequent blocks written due to aggressive






MTTR setting. This action has caused other sessions to get blocked on TX






enqueue contention, blocked on the splitting session. Advice was to set 10224






trace event for the splitter for a short time only in order to get






diagnostics as to why the space search rejected most blocks.






> A secondary symptom are the bitmap level 1 block updates, which may or may






not be related to the space search; I've not seen them before, maybe because






I didn't really pay attention :P , but the symptoms seen in the ASH trace






indicate it's the same problem. Someone in space mgmt has to look at it to






confirm it is the same problem."








与该用户进行了mail私下交流，他的回复：






I still have a case open   with Oracle. I believe that this is a bug in the Oracle code. The problem is   that it has been difficult to create a reproducible test case for Oracle   support. My specific issue was basically put on hold pending the results of   another customer’s service request that appeared to have had the same issue,   (9034788). Unfortunately they couldn’t reproduce the issue in that case either.


I believe that there is a   correlation between the enq TX – index contention wait event and a spike in the number of ‘failed probes on index block   reclamation. I have specifically asked Oracle to explain why there is a spike   in the ‘failed   probes on index block reclamation’ during the same time frame as the enq TX index contention wait   event, but they have not answered my question.


I was hoping that some   investigation by Oracle Support into the failed probes metric might get   someone on the right track to discovering the bug. That hasn’t happened though.


Hi ,


Thanks for your sharing .  The bug (or specific ktsp behave) is fatal in response time sensitive  OLTP env.


I would like to ask my customer to coalesce those index where massive deleted   regularly.


Thanks for your help again!


Yes, I saw that. I have   applied patch 8286901 and set the event for version 10.2.0.4, but the problem   still occurs periodically. And as I mentioned before, we see a correlation   between enq TX waits and the failed probes on index block reclamation. Which is   why I still think that it is a bug. I agree that trying to rebuild or   coalesce the indexes are simply attempts to workaround the issue and not   solve the root cause.


Early on when I started on   this issue I did do some index dumps and could clearly see that we had lots   of blocks with only 1 or 2 records after our mass delete jobs. I have   provided Oracle Support with this information as well as oradump files while   the problem is occurring, but they don’t seem to be able to find anything wrong so far.


If you are interested in   seeing if you are experiencing a high ‘failed probes on index block reclamation’ event run the query below.


select SS.snap_id,

SS.stat_name,

TO_CHAR(S.BEGIN_INTERVAL_TIME, ‘DAY’) DAY,

S.BEGIN_INTERVAL_TIME,

S.END_INTERVAL_TIME,

SS.value,

SS.value –   LAG(SS.VALUE, 1, ss.value) OVER (ORDER BY SS.SNAP_ID) AS DIFF

from DBA_HIST_SYSSTAT SS,

DBA_HIST_SNAPSHOT S

where S.SNAP_ID = SS.SNAP_ID

AND SS.stat_NAME = ‘failed probes on index block reclamation’

ORDER BY SS.SNAP_ID ;







    

  1. 在11gr2上的测试
    2. 









—在最新的11gr2中进行了测试，仍可以重现该问题（如图单条insert引起了6675的buffer_gets,这是在更大量数据的情况下）。







我们可以猜测Oracle提供的one-off补丁中可能是为叶块分裂所会扫描的“空块”附加了一个上限，在未达到上限的情况下扫描仍会发生。而在主流的公开的发行版本中Oracle不会引入该补丁的内容。尝试在没有缓存的情况下引起分裂问题，分裂引起了大约4000个块的物理读，但该操作仍在0.12秒（有缓存是0.02秒，如图）内完成了（该测试使用普通ata硬盘，读取速度在100MB/S: Timing buffered disk reads:  306 MB in  3.00 seconds = 101.93 MB/sec）；从1月21日的ash视图中可以看到引起split的260会话处于单块读等待(db file sequential read)中,且已等待了43950us约等于44ms;这与良好io的经验值10ms左右有较大出入；我们可以确信io性能问题也是引发此叶块分裂延迟如此显性的一个重要因素。


具体结论


综上所述，在之前讨论的几个方案中,MSSM方式是不能避免索引分裂引起交易超时问题的；不删除数据的方案在许多对象上不可行；10.2.0.4上的one-off补丁因为目前仅存在Linux版本，可以考虑声请补丁后具体测试（因目前没有补丁所以处于未知状态）。Coalesce合并索引是目前既有的最具可操作性且无副作用的解决方案。


Filed Under: Oracle Tagged With: , , , , , 
简易高负载进程记录脚本


2010/03/19  by  Leave a Comment 
Oracle 10g 中引入了v$osstat 视图方便了dba了解主机负载情况，同时也可以通过oem网页观察到一段时间内主机上负载较高的进程；但如果db未开启oem管理界面，则无法了解过去时段内高负载服务进程的相关信息。以下脚本可以给予一定的帮助。


CREATE TABLE "SYS"."HIGHLOAD_HISTORY"
(
"SAMPLE_TIME" DATE,
"SPID"     NUMBER(10,0),
"LOAD"     VARCHAR2(7 BYTE),
"SID"      VARCHAR2(30 BYTE),
"USERNAME" VARCHAR2(40 BYTE),
"MACHINE"  VARCHAR2(64 BYTE),
"PROGRAM"  VARCHAR2(48 BYTE),
"SQL_ID"   VARCHAR2(13 BYTE),
"SQL_FULLTEXT" CLOB,
"INST_ID" NUMBER(2,0),
"STATUS"  VARCHAR2(8 BYTE)
)    --建立记录高负载进程信息的表，内容包括了cpu使用率，及sql(并不十分准确,因为获取spid后需要进行查询)



ps aux|grep $ORACLE_SID|awk '{ if($3>=0.3) print "insert into highload_history select sysdate rec_time,"$2,","$3"%",", ss.sid,ss.username,ss.machine,ss.program,ss.sql_id,(select sql_fulltext from v$sqlarea sq where sq.sql_id=ss.sql_id),(select instance_number from v$instance),ss.status from v$session ss,v$process pr where  pr.addr=ss.paddr and pr.spid=",$2";"}'  | sqlplus / as sysdba  --直接运行即可



Filed Under: Oracle Tagged With: , , , 
职业生涯的一个中转站


2009/12/08  by  Leave a Comment 
00年代的最后一个月，总算没有辜负这三年来对于oracle的学习；在一个月的无尽等待不断反思中，祝贺信终于来了:


Dear Xiang Bing,


Congratulations! on the successful completion of the Oracle DBA 10g Certified Master practicum.


You are now a member of an elite group of Oracle professionals.  You will receive your Oracle DBA 10g Certified Master fulfillment kit that includes a congratulations letter, OCM certificate, OCM ID card, and denim OCM shirt to the mailing address mentioned in your Oracle DBA 10g OCM Hands-on course requirement Form.


OCM, Hello World!


10年代是我们80后的年代！！


Filed Under: Oracle Tagged With: , , , , , , 
Java程序导入文本文件生成XLS格式


2009/11/15  by  Leave a Comment 
最近工作中碰到一件事，要从文本文件中导入数据生成xls文件。 想想不难的，前端时间写过一个java程序，是放在server上跑的，从Oracle数据库里取数据再生成xls文件。 于是，把原先的那个程序给拿过来改改，很快就成了。由于这个是给用户在客户端操作的，所以用swing写了个很简朴的GUI界面。

但实际运行测试时，发现了一个很大的问题，那就是内存的问题。原先的那个程序，将要导入的数据先都写到一个vector中。 在服务器上，30多万行的数据都轻松搞定， 但一放到Win XP上就不行了。（我的机器内存512MB） 我测试的文本文件，有10万行数据，导入到vector后，再将数据通过POI包写到xls文件时就会使java虚拟机堆溢出，我手动调整了虚拟机最大可用内存量，即设置 -Xmx也还是不行，最后将测试数据减少到25000行程序顺利生成了xls文件。 故最终的程序的原理改成，先将导入的文本数据输出到temp文件，每个temp文件第一行为列名，从第二行起为数据，最多25000行。每个temp文件对应生成一个xls文件。经测试，效率还是相当不错的。哈哈，java现在效率其实很不错的啦，除非是那种对时间很苛刻的环境，那没有办法。


最后发布的时候，由于是给无专业IT知识的用户使用，所以我使用exe4j软件将其打包成一个exe文件，方便用户使用。 用户只要保证装有JRE 5.0或以上版本就可以了。（我没测试过6.0，相信没有问题的，哈哈，想当然了）


使用方法：点击select按钮，选中要导入的文件就可以了。生成的xls文件在被导入文件同一目录下；若有忽略的数据，在程序所在目录下生成log文件。


数据文件说明：

数据的第一行应为头信息，即表的列名，余下为数据本身，每个字段之间用|分割。  可下载测试样本来看。


软件下载地址：

TextConverter.zip


测试数据下载：

test2.txt


软件界面
软件界面


Filed Under: Oracle Tagged With: , , 
优化模式区别(all_rows & first_rows_n)


2009/11/12  by  3 Comments 
FIRST_ROWS优化模式以最快速度地检索出结果 集中的一行为其指导目标。当系统用户正在使用OLTP系统检索单条记录时，该 优化模式最为有效。但是该模式对于批处理密集型(batch)作业环境来说并不是最理想 的选择，在这种环境中一个查询通常需要检索许多行。FIRST_ROWS提示 一般会强制使用某些索引，而在默认环境（ALL_ROWS）中可能不采用这些索引。在使 用UPDATE和DELETE语句时FIRST_ROWS模式会被忽略，因这些DML操 作中所查询到的所有记录都会被更新或删除。另当使用以下分组语句(如GROUP BY,DISTINCT,INTERSECT,MINUS和UNION)时FIRST_ROWS模式均被ALL_ROWS模式取代，因为这些语句进行分组时必须检索所有行。当语句中有ORDER BY子句时，如果索引扫描可以进行实际的排序工作，则优化器将避免额外的排 序。当索引扫描可用并且索引处于内部表(inner table)时，优化器将更倾向于NESTED LOOPS即嵌套循环而非SORT MERGE排 序连接。


另10g中现有的FIRST_ROWS模式的变体FIRST_ROWS_N来 指定以多少行数最快返回。这个值介于10~1000之间，这个使用FIRST_ROWS_N的新方法是完全基于成本的方法，它对于N的取值较敏感，若N甚小，优化器就会产生包 括嵌套循环以及索引查找的计划。如果N值较大，优化器也可能生成由散列连接和全表扫描组 成的计划(类似于ALL_ROWS)。 又FIRST_ROW与FIRST_ROWS_N存 在不同，FIRST_ROW模式中保量了部分基于规则的代码，而FIRST_ROWS_N模式则是完完全全基于统计信息计算相应成本，如Oracle文档所述：




ALL_ROWS优化模式指导查询以最快速度检索出所 有行(最佳吞吐量)。当系统用户 处于需要大量批处理报告的环境中，该模式较理想。


在实际的SQL硬解析过程中，FIRST_ROWS_N模式将首先以ALL_ROWS模 式的方式计算一次各执行计划的具体代价，之后将我们需要的N条记录代入成本计算中代替实 际全部的候选行(CARD)以得出FIRST_ROWS_N中 的计划成本。


create table test as select  *  from   dba_objects;


create table testa as select *  from test;


alter session set events’10053  trace name   context forever,level 1′;    –使用10053事 件获取成本计算过程trace


alter session set   optimizer_mode=all_rows;


select test.owner from  test,testa where   test.object_id=testa.object_id


alter session set events’10053  trace name   context off’;


下为ALL_ROWS模式中，最佳连接方式的选 取：


NL Join


Outer table: Card: 9622.00    Cost: 35.37  Resp: 35.37  Degree: 1  Bytes: 7


Inner table: TESTA  Alias:   TESTA


Access Path: TableScan


NL Join:  Cost:   318924.52  Resp: 318924.52  Degree: 0


Cost_io:   315358.00  Cost_cpu: 27736509932


Resp_io:   315358.00  Resp_cpu: 27736509932


Access Path: index (index  (FFS))


Index: INDA_ID


resc_io: 5.69    resc_cpu: 1304190


ix_sel:   0.0000e+00  ix_sel_with_filters: 1


Inner table: TESTA  Alias:   TESTA


Access Path: index (FFS)


NL Join:  Cost:   56375.98  Resp: 56375.98  Degree: 0


Cost_io:   54762.00  Cost_cpu: 12551800804


Resp_io:   54762.00  Resp_cpu: 12551800804


Access Path: index   (AllEqJoinGuess)


Index: INDA_ID


resc_io: 1.00    resc_cpu: 8171


ix_sel:   1.0393e-04  ix_sel_with_filters: 1.0393e-04


NL Join: Cost:   9667.48  Resp: 9667.48  Degree: 1


Cost_io:   9657.00  Cost_cpu: 81507910


Resp_io:   9657.00  Resp_cpu: 81507910


Best NL cost: 9667.48


resc: 9667.48 resc_io: 9657.00 resc_cpu: 81507910


resp: 9667.48 resp_io: 9657.00 resp_cpu: 81507910


Join  Card:    9622.00 = outer (9622.00) * inner (9622.00) * sel (1.0393e-04)


Join Card –  Rounded:   9622 Computed: 9622.00


SM Join


Outer table:


resc: 35.37  card   9622.00  bytes: 7  deg: 1  resp: 35.37


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      17 Row   size:           14 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         10


Total IO   sort cost: 27      Total CPU sort cost: 13931876


Total Temp   space used: 254000


SM join: Resc: 203.62  Resp:   203.62  [multiMatchCost=0.00]


HA Join


Outer table:


resc: 35.37  card   9622.00  bytes: 7  deg: 1  resp: 35.37


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


Cost per ptn:   0.81  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 43.35  Resp: 43.35    [multiMatchCost=0.00]


HA Join (swap)


Outer table:


resc: 7.17  card   9622.00  bytes: 3  deg: 1  resp: 7.17


Inner table: TEST  Alias:   TEST


resc: 35.37    card: 9622.00  bytes: 7  deg: 1  resp: 35.37


using dmeth: 2    #groups: 1


Cost per ptn:   0.81  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 43.35  Resp: 43.35    [multiMatchCost=0.00]


HA cost: 43.35


resc: 43.35   resc_io: 42.00 resc_cpu: 10480460


resp: 43.35   resp_io: 42.00 resp_cpu: 10480460


Best:: JoinMethod: Hash


Cost: 43.35  Degree: 1  Resp: 43.35  Card: 9622.00    Bytes: 10


***********************


Best so  far: Table#:   0  cost: 35.3706  card: 9622.0000  bytes: 67354


Table#: 1  cost: 43.3476  card: 9622.0000  bytes: 96220


可以看到连接中二表上的候选行都是9622条，实际结果集也是9622条。


我们来看FIRST_ROWS_10情况下的trace:


alter session set events’10053  trace name   context forever,level 1′;


alter session set   optimizer_mode=first_rows_10;


select test.owner from  test,testa where   test.object_id=testa.object_id;


alter session set events’10053  trace name   context off’;


Now joining: TEST[TEST]#0


***************


NL Join


Outer table: Card: 11.00    Cost: 2.00  Resp: 2.00  Degree: 1  Bytes: 3


Inner table: TEST  Alias:   TEST


Access Path: TableScan


NL Join:  Cost:   368.08  Resp: 368.08  Degree: 0


Cost_io:   364.00  Cost_cpu: 31713898


Resp_io:   364.00  Resp_cpu: 31713898


Access Path: index   (AllEqJoinGuess)


Index: IND_ID


resc_io: 2.00    resc_cpu: 15503


ix_sel:   1.0393e-04  ix_sel_with_filters: 1.0393e-04


NL Join (ordered):   Cost: 24.02  Resp: 24.02  Degree: 1


Cost_io:   24.00  Cost_cpu: 178973


Resp_io:   24.00  Resp_cpu: 178973


Best NL cost: 24.02


resc: 24.02 resc_io: 24.00 resc_cpu: 178973


resp: 24.02 resp_io: 24.00 resp_cpu: 178973


Join Card:  11.00 = outer  (11.00) *   inner (9622.00) * sel (1.0393e-04)


Join Card – Rounded: 11  Computed: 11.00


SM Join


Outer table:


resc: 7.17  card   9622.00  bytes: 3  deg: 1  resp: 7.17


Inner table: TEST  Alias:   TEST


resc: 35.37    card: 9622.00  bytes: 7  deg: 1  resp: 35.37


using dmeth: 2    #groups: 1


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      22 Row   size:           18 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         14


Total IO   sort cost: 36      Total CPU sort cost: 14055006


Total Temp   space used: 320000


SORT   resource      Sort statistics


Sort   width:          70 Area size:        131072 Max Area size:    12582912


Degree:                 1


Blocks to   Sort:      17 Row   size:           14 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         10


Total IO   sort cost: 27      Total CPU sort cost: 13931876


Total Temp   space used: 254000


SM join: Resc: 109.14  Resp:   109.14  [multiMatchCost=0.00]


SM cost: 109.14


resc: 109.14   resc_io: 105.00 resc_cpu: 32173386


resp: 109.14   resp_io: 105.00 resp_cpu: 32173386


SM Join (with index on outer)


Access Path: index  (FullScan)


Index: IND_ID


resc_io: 167.00    resc_cpu: 5134300


ix_sel: 1    ix_sel_with_filters: 1


Cost: 167.66    Resp: 167.66  Degree: 1


Outer table:


resc:  167.66  card 11.00  bytes: 7  deg:   1  resp: 167.66


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      17 Row   size:           14 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost / pass:           10


Total IO   sort cost: 27      Total CPU sort cost: 13931876


Total Temp   space used: 254000


SM join: Resc: 203.62  Resp:   203.62  [multiMatchCost=0.00]


HA Join


Outer table:


resc: 35.37  card   9622.00  bytes: 7  deg: 1  resp: 35.37


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


Cost per ptn:   0.81  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 43.35  Resp: 43.35    [multiMatchCost=0.00]


HA Join (swap)


Outer table:


resc: 7.17  card 9622.00  bytes: 3  deg: 1  resp:   7.17


Inner table: TEST  Alias:   TEST


resc: 2.00  card: 11.00  bytes: 7  deg: 1  resp:   2.00


using dmeth: 2    #groups: 1


Cost per ptn:   0.69  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 9.85  Resp: 9.85  [multiMatchCost=0.00]


HA cost: 9.85


resc: 9.85   resc_io: 9.00 resc_cpu: 6646477


resp: 9.85   resp_io: 9.00 resp_cpu: 6646477


First K Rows: copy A one plan,  tab=TESTA


Best:: JoinMethod: Hash


Cost: 9.85  Degree: 1  Resp: 9.85  Card: 9622.00  Bytes:   17


***********************


Best so  far: Table#:   0  cost: 2.0012  card: 11.0000  bytes: 77


Table#: 1  cost: 9.8546  card: 9622.0000  bytes: 163574


可以看到此次计算中代入了用户希望最先返回的结果 条数11(为10+1),通过设 置连接对象的候选结果集(Card)以到达相关优化目的，相应的COST均有所下降。


下为FIRST_ROWS_1000的情况：


alter session set events’10053  trace name   context forever,level 1′;


alter session set   optimizer_mode=first_rows_1000;


select test.owner from  test,testa where   test.object_id=testa.object_id;


alter session set events’10053  trace name   context off’;


NL Join


Outer table: Card: 1000.00    Cost: 5.04  Resp: 5.04  Degree: 1  Bytes: 7


Inner table: TESTA  Alias:   TESTA


Access Path: TableScan


NL Join:  Cost:   33147.66  Resp: 33147.66  Degree: 0


Cost_io:   32777.00  Cost_cpu: 2882616819


Resp_io:   32777.00  Resp_cpu: 2882616819


Access Path: index (index  (FFS))


Index: INDA_ID


resc_io: 5.69    resc_cpu: 1304190


ix_sel:   0.0000e+00  ix_sel_with_filters: 1


Inner table: TESTA  Alias:   TESTA


Access Path: index (FFS)


NL Join:  Cost:   5861.74  Resp: 5861.74  Degree: 0


Cost_io:   5694.00  Cost_cpu: 1304492819


Resp_io:   5694.00  Resp_cpu: 1304492819


Access Path: index   (AllEqJoinGuess)


Index: INDA_ID


resc_io: 1.00    resc_cpu: 8171


ix_sel:   1.0393e-04  ix_sel_with_filters: 1.0393e-04


NL Join: Cost:   1006.09  Resp: 1006.09  Degree: 1


Cost_io:   1005.00  Cost_cpu: 8474019


Resp_io:   1005.00  Resp_cpu: 8474019


Best NL cost: 1006.09


resc: 1006.09 resc_io: 1005.00 resc_cpu: 8474019


resp: 1006.09 resp_io: 1005.00 resp_cpu: 8474019


Join  Card:    1000.00 = outer (1000.00) * inner (9622.00) * sel (1.0393e-04)


Join Card –  Rounded:   1000 Computed: 1000.00


SM Join


Outer table:


resc: 35.37  card   9622.00  bytes: 7  deg: 1  resp: 35.37


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      22 Row   size:           18 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         14


Total IO   sort cost: 36      Total CPU sort cost: 14055006


Total Temp   space used: 320000


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      17 Row   size:           14 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         10


Total IO   sort cost: 27      Total CPU sort cost: 13931876


Total Temp   space used: 254000


SM join: Resc: 109.14  Resp:   109.14  [multiMatchCost=0.00]


SM cost: 109.14


resc: 109.14   resc_io: 105.00 resc_cpu: 32173386


resp: 109.14   resp_io: 105.00 resp_cpu: 32173386


SM Join (with index on outer)


Access Path: index  (FullScan)


Index: IND_ID


resc_io: 167.00    resc_cpu: 5134300


ix_sel: 1    ix_sel_with_filters: 1


Cost: 167.66    Resp: 167.66  Degree: 1


Outer table:


resc: 167.66  card 1000.00  bytes: 7  deg: 1  resp:   167.66


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


SORT   resource      Sort statistics


Sort   width:          70 Area   size:      131072 Max Area size:      12582912


Degree:                 1


Blocks to   Sort:      17 Row size:             14 Total   Rows:           9622


Initial   runs:         2 Merge   passes:        1 IO Cost /   pass:         10


Total IO   sort cost: 27      Total CPU sort cost: 13931876


Total Temp   space used: 254000


SM join: Resc: 203.62  Resp:   203.62  [multiMatchCost=0.00]


HA Join


Outer table:


resc: 35.37  card   9622.00  bytes: 7  deg: 1  resp: 35.37


Inner table: TESTA  Alias:   TESTA


resc: 7.17  card:   9622.00  bytes: 3  deg: 1  resp: 7.17


using dmeth: 2    #groups: 1


Cost per ptn:   0.81  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 43.35  Resp: 43.35    [multiMatchCost=0.00]


HA Join (swap)


Outer table:


resc: 7.17  card   9622.00  bytes: 3  deg: 1  resp: 7.17


Inner table: TEST  Alias:   TEST


resc: 5.04  card:   1000.00  bytes: 7  deg: 1  resp: 5.04


using dmeth: 2    #groups: 1


Cost per ptn:   0.70  #ptns: 1


hash_area: 124   (max=3072)   Hash join: Resc: 12.91  Resp: 12.91    [multiMatchCost=0.00]


HA cost: 12.91


resc: 12.91   resc_io: 12.00 resc_cpu: 7038524


resp: 12.91   resp_io: 12.00 resp_cpu: 7038524


First K Rows: copy A one plan,  tab=TESTA


Best:: JoinMethod: Hash


Cost: 12.91  Degree: 1  Resp: 12.91  Card: 9622.00    Bytes: 17


***********************


Best so  far: Table#:   0  cost: 5.0389  card: 1000.0000  bytes: 7000


Table#: 1  cost: 12.9051  card: 9622.0000  bytes: 163574


可以看到此处代入了1000为某一连接对象的候选行数。


MOS上有一个著名的《MIGRATING TO THE COST-BASED OPTIMIZER》教材，详细介绍了RBO和CBO的区别：

[gview file=”http://askmac.cn/wp-content/uploads/resource/40178_rbo_rip.doc”]


Filed Under: Oracle Tagged With: , , , 
如何在windows vista/2008/7中 安装Oracle OMS 即Grid Control


2009/11/04  by  Leave a Comment 
之前因为考试的原因，希望能在本机的windows上安装 Oracle OMS 以方便练习，OCM考试中使用的Grid Control 版本为10.2.0.1, 仅支持Windows相关系统号5,对于6或6.1的vista/2008/7安装过程中都会遇到问题无法解决，最为明显的是10.2.0.1自带数据库为10.1.0.4版本在windows相关版本号6以上的平台中均无法创建数据库。Oracle OCM考试中会要求你使用DBCA 建立一个干净的Customer定制数据库，仅需要建立必要的试图和过程（catalog.sql与catproc.sql运行过）并带有label security 组件（其他如Spatial与OLAP组建包括Oracle JVM等均不需要），如图：


labelsecurity


Oracle Grid Control 的10.2.0.5版本已经发布，其中10.2.0.4版本已支持最新的Windows操作系统，但只有patch install版本而无直接安装的full install 版本，且直接安装10.2.0.1 版本在OMS configuration部分将出错导致配置失败故我们需要另一种安装方式，如文档 ID:     763072.1




Installing On Windows 2008/Vista


The following sections discuss installing Enterprise Manager 10g Grid Control on Windows 2008/Vista.




Installing Enterprise Manager 10g Grid Control Using A New Database On Windows 2008/Vista


Enterprise Manager 10g Grid Control will not support the New Database install option on Windows 2008/Vista as the Database 10.1.0.4 (which is the default Database for Grid Control) is not supported for Windows 2008/Vista.




Installing Enterprise Manager 10g Grid Control Using an Existing Database On Windows 2008/Vista


To install Enterprise Manager 10g Grid Control using an existing database on Windows 2008/Vista, follow these steps:


    

  1. Download the fixed OUI 10204 from ARU Microsoft Windows (32-bit) (Bug 6640752) and extract it to a designated location, for example, C:OUI_Shiphome
    2. 

  2. Copy Enterprise Manager 10g Grid Control 10.2.0.2.0 base shiphome (Disk1) into a location, for example, C:EMGC_102020shiphome
    3. 

  3. Install the database that supports Vista/2008, such as 10.2.0.3.0/10.2.0.4. Make the following changes in the response file:

    C:EMGC_Shiphome102020shiphomeDisk1installresponseemnoseed.rsp
    

      

    1. Give the absolute path of products.xml for the FROM_LOCATION parameter.

      FROM_LOCATION="C:EMGC_102020shiphomeDisk1/oms/Disk1/stage/products.xml"
      2. 

    2. Give the OMS Oracle Home location. For example:

      ORACLE_HOME=c:emnoseedoms10g
      3. 

    3. Give the Oracle Home name. For example:

      ORACLE_HOME_NAME=oms10g12
      4. 

    4. Give the Agent Oracle Home location. For example:

      s_agentHome=c:emnoseedagent10g
      5. 

    5. Give the Agent Home Name. For example:

      s_agentHomeName=agent10g12
      6. 

    

    

    Note:
    

    Ensure that the Oracle Home location and name are not identical to any previous Homes.
    

    4. 

  4. Invoke the setup.exe for Install in the following way:

    C:OUI_ShiphomecdDisk1installsetup.exe -ignoreDiskLabel -responseFile C:EMGC_102020shiphomeDisk1installresponseemnoseed.rsp
    5. 

  5. If the Agent is not coming up, and if the value of agentTZRegion in emd.properties is GMT then do the following:

      

    1. Reset TimeZone on Agent box to GMT.
      2. 

    2. Execute the following command in <Oracle home of Agent>BIN>emctl resetTZ agent
      3. 

    

    6. 

  6. Patch OMS and Agent to version 10.2.0.5.
    7. 







Installing An Additional Management Service On Windows 2008/Vista


Follow the below instructions to install an additional Management Service on Windows 2008/Vista.


    

  1. Download the fixed OUI 10204 from ARU Microsoft Windows (32-bit) (Bug 6640752) and extract it to a designated location, for example, C:OUI_Shiphome
    2. 

  2. Copy Enterprise Manager 10g Grid Control 10.2.0.2.0 base shiphome (Disk1) into a location, for example, C:EMGC_102020shiphome
    3. 

  3. Make the following changes in the response file:

    C:EMGC_102020shiphomeDisk1installresponseoms.rsp
    

      

    1. Supply the absolute path of products.xml for FROM_LOCATION parameter.

      FROM_LOCATION="C:EMGC_102020shiphome/Disk1/oms/Disk1/stage/products.xml"
      2. 

    2. Supply the OMS Oracle Home location. For example: ORACLE_HOME=C:OH102020oms10g
      3. 

    3. Specify the Oracle Home name. For example, ORACLE_HOME_NAME=oms10g20
      4. 

    4. Supply the Agent Oracle Home location. For example, s_agentHome=C:OH102020agent10g
      5. 

    5. Supply the Agent Home Name. For example, s_agentHomeName=agent10g20
      6. 

    

    

    Note:
    

    Ensure that the Oracle Home location and name not be the same as any previous homes.
    

    4. 

  4. Invoke the setup.exe for Install in the following way:

    C:OUI_ShiphomecdDisk1installsetup.exe -ignoreDiskLabel -responseFile
    

    C:EMGC_102020shiphomeDisk1installresponseoms.rsp
    5. 

  5. If the Agent does not come up, and if the value of agentTZRegion in emd.properties is GMT, then do the following:

      

    1. Reset TimeZone on the Agent installation box to GMT
      2. 

    2. Execute the following command in <Oracle home of Agent>BIN>emctl resetTZ agent
      3. 

    

    6. 

  6. Patch OMS and Agent to version 10.2.0.5.
    7. 







Installing An Additional Management Agent On Windows 2008/Vista


Follow the below instructions to install an additional Management Agent on Windows 2008/Vista.


    

  1. Download the fixed OUI 10204 from ARU Microsoft Windows (32-bit) (Bug 6640752) and extract it to a designated location, for example, C:OUI_Shiphome
    2. 

  2. Copy Enterprise Manager 10g Grid Control 10.2.0.2.0 base shiphome (Disk1) into a location, for example, C:EMGC_102020shiphome
    3. 

  3. Make the following changes in the response file:

    C:EMGC_102020shiphomeDisk1installresponseagent.rsp
    

      

    1. Supply the absolute path of products.xml for FROM_LOCATION parameter.

      FROM_LOCATION="C:EMGC_102020shiphomeDisk1/oms/Disk1/stage/products.xml"
      2. 

    2. Supply the OMS Oracle Home location. For example, ORACLE_HOME=c:agtagent10g
      3. 

    3. Supply the Oracle Home name. For example, ORACLE_HOME_NAME=agent10g13
      4. 

    

    

    Note:
    

    Ensure that Oracle Home location and name are not the same as any previous homes.
    

    4. 

  4. Invoke the setup.exe for Install in the following way:

    C:OUI_ShiphomecdDisk1installsetup.exe -ignoreDiskLabel -responseFile
    

    C:EMGC_102020shiphomeDisk1installresponseagent.rsp
    5. 

  5. If the Agent is not coming up, and if the value of agentTZRegion in emd.properties is GMT then do the following:

      

    1. Reset TimeZone on the Agent installation box to GMT.
      2. 

    2. Execute the following command in <Oracle home of Agent>BIN>emctl resetTZ agent
      3. 

    

    6. 

  6. 





Patch Agent to version 10.2.0.5.




  


Filed Under: Oracle Tagged With: , , , , , , , , , , , 
11g内存管理新特性的internal表现


2009/09/27  by  3 Comments 
11g中自动内存管理(Automatic Memory Management ,amm), 令dba在数据库内存配置的相关工作更加简单. AMM现在将SGA与PGA整合到一起管理,而您只需要设置memory_target参数即可限定Oracle将使用到的内存尺寸,Oracle将自动分配这些内存空间.


您一定很困惑Oracle在unix平台上是如何对共享的sga内存空间与私有的pga内存空间进行切换的?这意味着Oracle需要经常释放sga中的部分内存以便允许pga去使用它们.传统的sys V 使用的共享内存shm接口不具备如此的灵活性.我们来看看Oracle是如何做到的?


先来获取我们需要的11g实例共享内存id(shared memory id)


[oracle@rh2 ~]$ sysresv　　　　　　　　　　　　　　　　// 该命令需要设置了正确的LD_LIBRARY_PATH

IPC Resources for ORACLE_SID “T11” :

Shared Memory:

ID              KEY

65537           0x95c84bb8

Semaphores:

ID              KEY

327681          0xdf521034

Oracle Instance alive for sid “T11”


试着找出对应的sys V共享内存段：


[oracle@rh2 ~]$ ipcs -m


—— Shared Memory Segments ——–

key        shmid      owner      perms      bytes      nattch     status

0x95c84bb8 65537      oracle    660        4096       0


对应的存在着共享内存段，但该段很小只有　4096 byte哦，既然Oracle不再把sga放到共享段中，那藏到哪里去了呢？


我们接下来检查Oracle实例进程的内存影射状况．


[oracle@rh2 ~]$  pmap `pgrep -f lgwr`|less

14889:   ora_lgwr_T11

0000000000400000 155016K r-x–  /usr/oracle/product/11g/db_1/bin/oracle

0000000009c62000  12404K rw—  /usr/oracle/product/11g/db_1/bin/oracle

000000000a87f000    732K rwx–    [ anon ]

0000000060000000      4K r–s-  /dev/shm/ora_T11_65537_0

0000000060001000  16380K rw-s-  /dev/shm/ora_T11_65537_0

0000000061000000  16384K rw-s-  /dev/shm/ora_T11_65537_1

0000000062000000  16384K rw-s-  /dev/shm/ora_T11_65537_2

0000000063000000  16384K rw-s-  /dev/shm/ora_T11_65537_3

0000000064000000  16384K rw-s-  /dev/shm/ora_T11_65537_4

0000000065000000  16384K rw-s-  /dev/shm/ora_T11_65537_5

0000000066000000  16384K rw-s-  /dev/shm/ora_T11_65537_6

0000000067000000  16384K rw-s-  /dev/shm/ora_T11_65537_7

0000000068000000  16384K rw-s-  /dev/shm/ora_T11_65537_8

0000000069000000  16384K rw-s-  /dev/shm/ora_T11_65537_9

000000006a000000  16384K rw-s-  /dev/shm/ora_T11_65537_10

000000006b000000  16384K rw-s-  /dev/shm/ora_T11_65537_11

000000006c000000  16384K rw-s-  /dev/shm/ora_T11_65537_12

000000006d000000  16384K rw-s-  /dev/shm/ora_T11_65537_13

000000006e000000  16384K rw-s-  /dev/shm/ora_T11_65537_14

000000006f000000  16384K rw-s-  /dev/shm/ora_T11_65537_15

0000000070000000  16384K rw-s-  /dev/shm/ora_T11_65537_16

0000000071000000  16384K rw-s-  /dev/shm/ora_T11_65537_17

0000000072000000  16384K rw-s-  /dev/shm/ora_T11_65537_18

0000000073000000  16384K rw-s-  /dev/shm/ora_T11_65537_19

0000000074000000  16384K rw-s-  /dev/shm/ora_T11_65537_20

0000000075000000  16384K rw-s-  /dev/shm/ora_T11_65537_21

0000000076000000  16384K rw-s-  /dev/shm/ora_T11_65537_22

0000000077000000  16384K rw-s-  /dev/shm/ora_T11_65537_23

0000000078000000  16384K rw-s-  /dev/shm/ora_T11_65537_24

0000000079000000  16384K rw-s-  /dev/shm/ora_T11_65537_25

000000007a000000  16384K rw-s-  /dev/shm/ora_T11_65537_26

000000007b000000  16384K rw-s-  /dev/shm/ora_T11_65537_27

000000007c000000  16384K rw-s-  /dev/shm/ora_T11_65537_28

000000007d000000  16384K rw-s-  /dev/shm/ora_T11_65537_29

000000007e000000  16384K rw-s-  /dev/shm/ora_T11_65537_30

000000007f000000  16384K rw-s-  /dev/shm/ora_T11_65537_31

0000000080000000  16384K rw-s-  /dev/shm/ora_T11_65537_32

0000000081000000  16384K rw-s-  /dev/shm/ora_T11_65537_33

0000000082000000  16384K rw-s-  /dev/shm/ora_T11_65537_34

0000000083000000  16384K rw-s-  /dev/shm/ora_T11_65537_35

0000000084000000  16384K rw-s-  /dev/shm/ora_T11_65537_36

0000000085000000  16384K rw-s-  /dev/shm/ora_T11_65537_37

0000000086000000  16384K rw-s-  /dev/shm/ora_T11_65537_38

0000000087000000  16384K rw-s-  /dev/shm/ora_T11_65537_39

0000000088000000  16384K rw-s-  /dev/shm/ora_T11_65537_40

0000000089000000  16384K rw-s-  /dev/shm/ora_T11_65537_41

000000008a000000  16384K rw-s-  /dev/shm/ora_T11_65537_42


．．．．．．．．．．．．


0000003e79109000      4K rw—  /lib64/tls/librt-2.3.4.so

0000003e7910a000     64K rw—    [ anon ]

0000003e79600000     84K r-x–  /lib64/libnsl-2.3.4.so

0000003e79615000   1020K —–  /lib64/libnsl-2.3.4.so

0000003e79714000      4K r—-  /lib64/libnsl-2.3.4.so

0000003e79715000      4K rw—  /lib64/libnsl-2.3.4.so

0000003e79716000      8K rw—    [ anon ]

0000007fbfff3000     52K rwx–    [ stack ]

ffffffffff600000      4K r-x–    [ anon ]

total          2497724K


pmap工具诠释了进程相关共享内存的情况，可以看到许多个16MB的＂文件＂对应了Oracle服务进程的空间地址．这是linux上POSIX风格的共享内存管理模式，使用＂文件＂形式包含共享内存段．


借助于将sga分割成许多块，Oracle可以很容易地把sga部分内存返回给OS，而服务器进程即可以利用到这些内存．（当memory_max_target>1024时，颗粒为16MB,否则为4MB)．


接下来我们测试下Oracle是如何释放部分sga内存的．


对比实例启动前后：


启动前：


[oracle@rh2 ~]$ ls -l /dev/shm

总用量 0


启动后：

[oracle@rh2 ~]$ ls -l /dev/shm

总用量 1373704

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_100

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_101

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_102

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_103

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_110

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_111

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_112

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_113

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_114

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_115

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_116

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_117

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_118

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_119

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12


可以看到启动后出现的16MB文件形式共享内存中部分大小为０，这些块被选出当发生内存交换时来被’destory’．使用pmap工具仍可以看到该部分影射，而实际上已经被Oracle释放了．


现在我们加大pga，观察其交换情况．


SQL> alter system set pga_aggregate_target=1900M ;


System altered.


[oracle@rh2 ~]$ ls -l /dev/shm

总用量 289984

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_0

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_1

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_10

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_100

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_101

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_102

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_103

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_104

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_105

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_106

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_107

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_108

-rw-r—–  1 oracle oinstall 16777216  9月 27 18:59 ora_T11_327680_109

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_11

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_110

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_111

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_112

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_113

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_114

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_115

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_116

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_117

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_118

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_119

-rw-r—–  1 oracle oinstall        0  9月 27 18:59 ora_T11_327680_12

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_120

-rw-r—–  1 oracle oinstall        0  9月 27 19:09 ora_T11_327680_121


可以看到出现了大量size为0的＂文件＂，期许的交换出现了．


可见在11g中Oracle采用了新的共享内存实现方式，区别于旧的＂一块式＂共享段，更为灵活了．


Filed Under: Oracle Tagged With: , , , , , , , , , , , , , , , , , , , 
Oracle中RMAN恢复目录性能问题研究


2009/09/15  by  Leave a Comment 
下列文本摘自metalink:




Applies to:


Oracle Server – Enterprise Edition – Version: 9.0.1.0 to 11.1.0.8

Information in this document applies to any platform.


Purpose


This note discusses what to look for when queries against the RMAN repository (whether in the controlfile or a catalog database) are performing badly and what diagnostics needs to be gathered when raising a Service Request with Oracle Support Services.


This note is intended for use by Database Administrators and Support personnel investigating RMAN performance problems relating specifically to catalog resyncs or queries against the controlfile or catalog database whilst running RMAN.


Last Review Date


November 7, 2008


Instructions for the Reader


A Troubleshooting Guide is provided to assist  in debugging a specific issue. When possible, diagnostic tools are included in the document  to assist in troubleshooting.


Troubleshooting Details


Identifying a problem with the RMAN repository


The RMAN repository is updated whenever new backups are taken or housekeeping is done to maintain the backup metadata; it is queried when RMAN reports are run and during restore to check backup history and determine the best and most efficient way to carry out the restore. If a catalog is used, a number of resyncs are done first to update the catalog with any new information in the controlfile but once the resyncs are complete, ALL subsequent queries of RMAN metadata (during crosscheck, report, restore etc) are run against the catalog database only.


You know there is a problem if:


    

  • An explicit catalog resync takes a long time to complete
    • 

  • Statspack or AWR reports for a nocatalog run shows top wait events in the target are for CPU or control file IO:

      

    • “control file parallel write”
      • 

    • “control file sequential read”
      • 

    

    • 

  • Statspack or AWR reports for catalog run shows top wait events in the catalog are for CPU or the catalog datafile IO:

      

    • “db file scattered read”
      • 

    • “db file sequential read”
      • 

    

    • 

  • Statspack or AWR reports shows most expensive queries in terms of CPU or elapsed time are those issued against the controlfile or rman catalog tables
    • 

  • You have worked through Note 360443.1: RMAN Backup Performance, and identified the bottleneck to be in the Oracle Layer before the backup actually starts
    • 

  • You have worked through Note 740911.1: RMAN Restore Performance, and identified the bottleneck to be in the Oracle Layer before the restore actually starts
    • 



Control_file_record_keep_time and recovery window


Control_file_record_keep_time specifies the minimum amount of RMAN metadata guarranteed to be retained in the controlfile; the default is 7 days.  In practice the rate at which records age out of the controlfile depends on how long it takes for a recyclable section to fill up which may well be more than 7 days. It is important to note that control_file_record_keep_time and recovery window are NOT the same; the former governs when records can be overwritten in the controlfile, primarily to prevent unlimited controlfile growth whilst the latter is concerned with retention of RMAN metadata according to business requirements (see Bug 6448254: RAISE WARNING IF RECOVERY WINDOW IS > CONTROL_FILE_RECORD_KEEP_TIME IN NOCAT MODE). If a catalog is used and RMAN is run daily or at a frequency LESS than control_file_record_keep_time days, RMAN metadata will never be lost as implicit catalog resyncs ensure data is regularly propogated into the catalog.


Integrity of RMAN metadata


Constraints in the catalog ensure the integrity of the rman metadata. For example, a backup_set must have a corresponding backup_piece which must have corresponding backup_X (datafile, redolog ) entries. There are no such constraints in a controlfile; records in different sections can age out at different rates. For example, the one:many relationship between backup_set and backup_redolog entries means the backup_redologs section will fill up faster than the backup_set section and over time, a backup_redolog entry may age out of the controlfile BEFORE its corresponding backup_set entry, leaving a backup_set entry with missing backup_redolog entries (mis-matched). Running housekeeping operations (report/delete obsolete, crosscheck etc) against such a CONTROLFILE with mismatched entries will produce unpredictable results (see Bug 7357779: RMAN REPORT/DELETE OBSOLETE RETURNS ARCHIVELOG BACKUPS INSIDE RETENTION POLICY).


To avoid any issues with mis-matched entries, set control_file_record_keep_time equal to the recovery window +1 which ensures that RMAN metadata INSIDE the retention policy will never be affected. In practice this may not be possible if a very large recovery window is needed so typically a catalog is used, a retention policy configured and control_file_record_keep_time left to its default of 7 days. But there may still be times when the catalog database is unavailable (it is being upgraded perhaps or is under investigation) and an RMAN backup must still proceed in which case:


    

  • set control_file_record_keep_time days to the recovery window plus 1 to ensure no loss of metadata whilst the catalog is unavailable
    • 

  • DO NOT RUN ANY MAINTENANCE OPERATIONS until the catalog is again available expecially if your recovery window is greater than the original setting of control_file_record_keep_time days
    • 



Tackling a performance issue


AWR snapshot reports from the target (nocatalog) or catalog database should identify  the specific SQL that is causing a problem in terms of CPU, high IO or elapsed times.


Refer to the following notes to see  if the problematic SQL is a known issue:


Note 247611.1:Known RMAN Performance Problems

Note 463227.1: Oracle10g RMAN Recovery Catalog Known Performance Issues


If none of the issues in the above notes are relevant, then further investigation is needed and the following diagnostics will be useful and should be uploaded if a Service Request is raised with Oracle Support Services. There are three processes to consider:


    

  • The RMAN client – this should always be traced
    • 

  • The 1st default channel (for nocatalog operations only, this process runs all the rman queries against the controlfile)
    • 

  • The RMAN session in the catalog instance
    • 



All the traces will generate a lot of information so consider FIRST how to minimise the output by stripping the job down to a SINGLE rman command. Execute each command in a script in isolation to see which command produces the problem and trace just that one command. It is always worth running an explicit catalog resync first to see if the real problem lies in the implicit resync done for virtually all commands.


Tracing the RMAN client


Ideally, set debug trace on the RMAN command line and execute a simplied script to illustrate the problem eg


%rman target / catalog usr/pw@<alias> log rman.log trace rman.trc debug

run {

allocate channel t1 type sbt parms=(…………);

backup database plus archivelog all;

}




Otherwise trace only the command that you are interested in. The following example traces only the crosscheck command:


%rman target / catalog usr/pw@<alias> log rman.log trace rman.trc

run {

allocate channel t1 type sbt parms=(….);

allocate channel t2 type sbt parms=(….);

backup database;

backup  archivelog all until time ' sysdate –7' delete all input;

debug on;

crosscheck backup;

debug off;

}




Tracing queries against the RMAN repository


Make sure before you invoke rman that the following environmental variables are set:


NLS_LANG='american_america.<charset>'

NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'




And for the target database (nocatalog operations) or catalog database (catalog operations):


TIME_STATISTICS=TRUE

MAX_DUMP_FILE_SIZE=UNLIMITED




If a recovery catalog is normally used, try again WITHOUT the catalog; if the problem persists then for diagnostic purposes, get the trace against the CONTROLFILE only. Tracing the RMAN catalog session should only be done if the problem only occurs when a catalog is used.


The Controlfile (nocatalog)


%rman target / log rman.log trace rman.trc debug

run {

sql "alter session set events ''10046 trace name context forever, level 12''";

set command id to 'rman';

allocate channel for maintenance type sbt…………..;

backup database;

} 


Note: Two single quotes before : 10046

Two single quotes and a double quote after : level 12


This traces the first default channel; trace file will be generated in the target udump directory using the format <SID>_ora_<spid>.trc. To find the <spid> of the 1st default channel:


col program format a20

col action format a20

select s.sid, p.spid, s.program, s.client_info,s.action, seq#, event, wait_time,

seconds_in_wait AS sec_wait

from v$session s, v$process p

where s.paddr = p.addr and s.program like '%rman%';




Typical output:


SID SPID PROGRAM                          CLIENT_INFO

---------------------------------------------------------

ACTION              SEQ# EVENT                      WAIT_TIME SEC_WAIT

-----------------------------------------------------------------------------

139 24822 rman@celcsol4  (TNS V1-V3)

0000012 FINISHED129    853  SQL*Net message from client 0        1794

147  4574 rman@celcsol4 (TNS V1-V3)

115  SQL*Net message from client 0        1797

135  5056 rman@celcsol4 (TNS V1-V3)       rman channel=ORA_DISK_1

0000046 FINISHED129   2219  SQL*Net message from client 0        1794




Note:

– sid 147 is the polling channel; it never has a value in ACTION

– sid 139 is the 1st default Channel (CLIENT_INFO  is null)

– sid 135 is the allocated channel ORA_DISK_1 (per CLIENT_INFO)


The Catalog


Allow the rman job to make the catalog connection first – it may help to use the host command to allow time to find the catalogn session:


%rman target / catalog usr/pw@<alias> log rman.log trace rman.trc debug

run {

set command id to 'rman';

allocate channel for maintenance type sbt…………..;

host;

backup database;

}




The host command will halt execution and return control to the OS prompt.

To identify the RMAN session in the catalog database:


SELECT s.sid, s.serial#, p.spid, s.program, s.action

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.program like '%rman%';




Note the spid value eg 3164 then do:


oradebug setospid 3164

oradebug unlimit

oradebug event 10046 trace name context forever, level 12

exit




Once this is done, to return to the RMAN session and to continue execution of the script type: exit

A trace file will be  generated in the CATALOG udump directory using the format <SID>_ora_<spid>.trc.


Upload Diagnostics


1. RMAN client trace file

2. 1st default channel 10046 trace file (nocatalog only)

3. RMAN catalog 10046 trace file (catalog issues only)

4. Related AWR/Statspack reports


Filed Under: Oracle Tagged With: , , , , , , , , , , , , , , , , , , , 
如何找出Oracle中需要或值得重建的索引


2009/09/10  by  4 Comments 
This script determines whether an index is a good candidate for a rebuild or for

a bitmap index.  All indexes for a given schema or for a subset of schema’s are

analyzed (except indexes under SYS and SYSTEM)


Instructions


Execution Environment:

<SQL, SQL*Plus, iSQL*Plus>


Access Privileges:

Requires DBA privileges in order to be executed.


Usage:

sqlplus <user>/<pw> @rebuild.index.sql


Instructions:

Copy the script into the file ind_an.sql. Execute the script from SQL*Plus connected

with a user with DBA privileges.  The script requires to parameters:


1. Name of the output file where the report while be generated

2. Name of the SCHEMA to be analyzed.


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text

editors, e-mail packages, and operating systems handle text formatting (spaces,

tabs, and carriage returns), this script may not be in an executable state

when you first receive it. Check over the script to ensure that errors of

this type are corrected.


Description


This script determines whether an index is a good candidate for a rebuild or for

a bitmap index.  All indexes for a given schema or for a subset of schema’s are

analyzed (except indexes under SYS and SYSTEM).


REM =============================================================
REM
REM                         rebuild_indx.sql
REM
REM  Copyright (c) Oracle Software, 1998 - 2000
REM
REM  Author  : Jurgen Schelfhout
REM
REM  The sample program in this article is provided for educational
REM  purposes only and is NOT supported by Oracle Support Services.
REM  It has been tested internally, however, and works as documented.
REM  We do not guarantee that it will work for you, so be sure to test
REM  it in your environment before relying on it.
REM
REM  This script will analyze all the indexes for a given schema
REM  or for a subset of schema's. After this the dynamic view
REM  index_stats is consulted to see if an index is a good
REM  candidate for a rebuild or for a bitmap index.
REM
REM  Database Version : 7.3.X and above.
REM
REM  NOTE:  If running this on 10g, you must exclude the
REM  objects in the Recycle Bin
REM        cursor c_indx is
REM          select owner, table_name, index_name
REM            from dba_indexes
REM           where owner like upper('&schema')
REM             and table_name not like 'BIN$%'
REM             and owner not in ('SYS','SYSTEM');
REM
REM  Additional References for Recycle Bin functionality:
REM  Note.265254.1 Flashback Table feature in Oracle Database 10g
REM  Note.265253.1 10g Recyclebin Features And How To Disable it(_recyclebin)
REM
REM =============================================================

prompt
ACCEPT spoolfile CHAR prompt 'Output-file : ';
ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';
prompt
prompt
prompt Rebuild the index when :
prompt   - deleted entries represent 20% or more of the current entries
prompt   - the index depth is more then 4 levels.
prompt Possible candidate for bitmap index :
prompt   - when distinctiveness is more than 99%
prompt

spool &spoolfile;
set serveroutput on;
set verify off;
set linesize 140;
declare
  c_name        INTEGER;
  ignore        INTEGER;
  height        index_stats.height%TYPE := 0;
  lf_rows       index_stats.lf_rows%TYPE := 0;
  del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
  distinct_keys index_stats.distinct_keys%TYPE := 0;
  cursor c_indx is
    select owner, table_name, index_name
      from dba_indexes
     where owner like upper('&schema')
       and owner not in ('SYS', 'SYSTEM');
begin
  dbms_output.enable(1000000);
  dbms_output.put_line('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
  dbms_output.put_line('--------------  ---------------------------------            ------------  -----           -----');

  c_name := DBMS_SQL.OPEN_CURSOR;
  for r_indx in c_indx loop
    DBMS_SQL.PARSE(c_name,
                   'analyze index ' || r_indx.owner || '.' ||
                   r_indx.index_name || ' validate structure',
                   DBMS_SQL.NATIVE);
    ignore := DBMS_SQL.EXECUTE(c_name);

    select HEIGHT,
           decode(LF_ROWS, 0, 1, LF_ROWS),
           DEL_LF_ROWS,
           decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
      into height, lf_rows, del_lf_rows, distinct_keys
      from index_stats;
    /*
    - Index is considered as candidate for rebuild when :
    -   - when deleted entries represent 20% or more of the current entries
    -   - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
    - Index is (possible) candidate for a bitmap index when :
    -   - distinctiveness is more than 99%
    */
    if (height > 5) OR ((del_lf_rows / lf_rows) > 0.2) then
      dbms_output.put_line(rpad(r_indx.owner, 16, ' ') ||
                           rpad(r_indx.index_name, 40, ' ') ||
                           lpad(round((del_lf_rows / lf_rows) * 100, 3),
                                17,
                                ' ') || lpad(height - 1, 7, ' ') ||
                           lpad(round((lf_rows - distinct_keys) * 100 /
                                      lf_rows,
                                      3),
                                16,
                                ' '));
    end if;

  end loop;
  DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off;
set verify on;



Sample Output:


SQL> @rebuild_index

Output-file : index_rebuild
Schema name (% allowed) : maclean

Rebuild the index when :
- deleted entries represent 20% or more of the current entries
- the index depth is more then 4 levels.
Possible candidate for bitmap index :
- when distinctiveness is more than 99%

Owner           Index Name                              % Deleted Entries Blevel Distinctiveness
--------------  ---------------------------------            ------------  -----           -----
MACLEAN         SYS_MTABLE_00000CFD4_IND_2                             25      0              25
MACLEAN         SYS_MTABLE_00000D3F3_IND_2                         33.333      0          33.333
PL/SQL procedure successfully completed.



Filed Under: Oracle Tagged With: , , , , , , , , , , , , , , , , , , , 







        
沪ICP备14014813号-2

	
沪公网安备 31010802001379号