June 2009 Archives

对index直接rebuild的时候一定会是index fast full scan吗

By cui hua on June 30, 2009 1:09 PM | 4 Comments

对index直接rebuild的时候一定会是index fast full scan吗？

呵呵，当然不是，我们来看一个反例：

 

SQL> create table testindex

  2  (pvalue varchar2(1),

  3   pkey number)

  4  partition by range (pkey)

  5  (

  6  partition testindex_part_1 values less than (2)

  7  tablespace TESTTBS1,

  8  partition testindex_part_2 values less than (3)

  9  tablespace TESTTBS2

 10  )

 11  enable row movement;

 

Table created

 

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

 

1 row inserted

 

SQL> insert into testindex values('b',1);

 

1 row inserted

 

SQL> insert into testindex values('c',2);

 

1 row inserted

 

SQL> insert into testindex values('d',2);

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> create index idx_testindex_pvalue on testindex(pvalue);

 

Index created

 

我把pvalue='a'的那条记录的pkey由1改为2，这时候因为上述分区在不同的表空间下，所以索引idx_testindex_pvalue中对应于键值为'a'的global rowid一定会变。

 

好了，我们来验证一下：

未改之前：

row#0[8017] flag: -----, lock: 0

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 00

row#1[8002] flag: -----, lock: 0

col 0; len 1; (1):  62

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 01

row#2[7987] flag: -----, lock: 0

col 0; len 1; (1):  63

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 00

row#3[7972] flag: -----, lock: 0

col 0; len 1; (1):  64

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 01

 

SQL> update testindex set pkey=2 where pvalue='a';

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select * from testindex;

 

PVALUE       PKEY

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

b               1

c               2

d               2

a               2

 

改过之后：

row#0[8017] flag: ---D-, lock: 2

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 00

row#1[7957] flag: -----, lock: 2

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 02

row#2[8002] flag: -----, lock: 0

col 0; len 1; (1):  62

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 01

row#3[7987] flag: -----, lock: 0

col 0; len 1; (1):  63

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 00

row#4[7972] flag: -----, lock: 0

col 0; len 1; (1):  64

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 01

这里我们可以从trace文件中看到索引idx_testindex_pvalue中对于键值为'a'的global rowid已经由00 01 50 dd 21 80 07 99 00 00变成了00 01 50 de 22 00 11 19 00 02。

 

现在我们重建上述index，再来验证将上述index设为unusable的情况下索引idx_testindex_pvalue中对应于键值为'a'的global rowid是否会变？

SQL> update testindex set pkey=1 where pvalue='a';

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select * from testindex;

 

PVALUE       PKEY

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

b               1

a               1

c               2

d               2

 

SQL> drop index idx_testindex_pvalue;

 

Index dropped

 

SQL> create index idx_testindex_pvalue on testindex(pvalue);

 

Index created

 

未改之前：

row#0[8017] flag: -----, lock: 0

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 02

row#1[8002] flag: -----, lock: 0

col 0; len 1; (1):  62

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 01

row#2[7987] flag: -----, lock: 0

col 0; len 1; (1):  63

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 00

row#3[7972] flag: -----, lock: 0

col 0; len 1; (1):  64

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 01

 

SQL> alter index idx_testindex_pvalue unusable;

 

Index altered

 

SQL> update testindex set pkey=2 where pvalue='a';

 

update testindex set pkey=2 where pvalue='a'

 

ORA-01502: index 'DRAS.IDX_TESTINDEX_PVALUE' or partition of such index is in unusable state

 

SQL> ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

 

Session altered

 

SQL> update testindex set pkey=2 where pvalue='a';

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select * from testindex;

 

PVALUE       PKEY

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

b               1

c               2

d               2

a               2

 

改过之后：

row#0[8017] flag: -----, lock: 0

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 02

row#1[8002] flag: -----, lock: 0

col 0; len 1; (1):  62

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 01

row#2[7987] flag: -----, lock: 0

col 0; len 1; (1):  63

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 00

row#3[7972] flag: -----, lock: 0

col 0; len 1; (1):  64

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 01

可以从结果里看到，这时候当我将上述index设为unusable的情况下索引idx_testindex_pvalue中对应于键值为'a'的global rowid没有变！

 

这时候如果我们rebuild online的话是能修正上述错误的，因为rebuild online走的是全表扫描。但是如果我们只是rebuild，因为rebuild走的是index fast full scan，那这里oracle能正确的修正上述global rowid的错误吗？我们来验证一下：

SQL> alter index idx_testindex_pvalue rebuild;

 

Index altered

 

rebuild之后：

row#0[8017] flag: -----, lock: 0

col 0; len 1; (1):  61

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 03

row#1[8002] flag: -----, lock: 0

col 0; len 1; (1):  62

col 1; len 10; (10):  00 01 50 dd 21 80 07 99 00 01

row#2[7987] flag: -----, lock: 0

col 0; len 1; (1):  63

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 00

row#3[7972] flag: -----, lock: 0

col 0; len 1; (1):  64

col 1; len 10; (10):  00 01 50 de 22 00 11 19 00 01

从结果里我们可以看到，即便我这里用的是rebuild，oracle这里也已经将上述global rowid的错误修正。

 

为什么会这样？

很简单，因为在index是unusable的情况下，即使是直接rebuild，oracle这里也会走全表扫描。

SQL> select status from dba_indexes where index_name='IDX_TESTINDEX_PVALUE';

 

STATUS

--------

VALID

 

SQL> explain plan for alter index idx_testindex_pvalue rebuild;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation              |  Name                 | Rows  | Bytes | Cost  |

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

|   0 | ALTER INDEX STATEMENT  |                       |  9802 | 19604 |    13 |

|   1 |  INDEX BUILD NON UNIQUE| IDX_TESTINDEX_PVALUE  |       |       |       |

|   2 |   SORT CREATE INDEX    |                       |  9802 | 19604 |       |

|   3 |    INDEX FAST FULL SCAN| IDX_TESTINDEX_PVALUE  |  9802 | 19604 |       |

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

Note: cpu costing is off, PLAN_TABLE' is old version

 

11 rows selected

 

SQL> alter index idx_testindex_pvalue unusable;

 

Index altered

 

SQL> select status from dba_indexes where index_name='IDX_TESTINDEX_PVALUE';

 

STATUS

--------

UNUSABLE

 

SQL> explain plan for alter index idx_testindex_pvalue rebuild;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation              |  Name                 | Rows  | Bytes | Cost  |

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

|   0 | ALTER INDEX STATEMENT  |                       |  9802 | 19604 |    13 |

|   1 |  INDEX BUILD NON UNIQUE| IDX_TESTINDEX_PVALUE  |       |       |       |

|   2 |   SORT CREATE INDEX    |                       |  9802 | 19604 |       |

|   3 |    PARTITION RANGE ALL |                       |       |       |       |

|   4 |     TABLE ACCESS FULL  | TESTINDEX             |  9802 | 19604 |    13 |

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

Note: cpu costing is off, PLAN_TABLE' is old version

 

12 rows selected

 

当无统计信息的时候ix_sel的值取决于什么

By cui hua on June 29, 2009 10:56 AM | 1 Comment

在"利用10053分析执行计划的一个例子"这篇文章里，我提到----"这个默认值不是固定的，感觉这里这个默认值取决于Blks"。

 

当时为什么这么说是因为我看到的所有关于10053的文章中都或直接或隐约的指出当没有统计信息的时候，oracle是根据Blks来计算默认的统计信息。

 

呵呵，这个观点误导了我，我们来看一下真相是什么。

 

SQL> create table tb0624 (type number,ts timestamp) pctfree 90;

 

Table created

 

SQL> begin

  2  for i in 1..10000 loop

  3  insert into tb0624 values (1,sysdate);

  4  insert into tb0624 values (3,sysdate);

  5  end loop;

  6  commit;

  7  end;

  8  /

 

PL/SQL procedure successfully completed

 

SQL> begin

  2  for i in 1..10 loop

  3  insert into tb0624 values (2,sysdate);

  4  end loop;

  5  commit;

  6  end;

  7  /

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

 

PL/SQL procedure successfully completed

 

SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';

 

  NUM_ROWS     BLOCKS AVG_ROW_LEN

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

     20010        438          14

 

SQL> create index idx_type on tb0624(type);

 

Index created

 

这里我们分别修改NUM_ROWS，BLOCKS和AVG_ROW_LEN，以便来看看ix_sel到底取决于哪个值。

 

1、修改#Blks后的10053：

SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 538);

 

PL/SQL procedure successfully completed

 

SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';

 

  NUM_ROWS     BLOCKS AVG_ROW_LEN

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

     20010        538          14

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 20010  #Blks:  538  AvgRowLen:  14.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 40  #DK: 3  LB/K: 13.00  DB/K: 267.00  CLUF: 801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)  NO STATISTICS (using defaults)

    AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992

  Table: TB0624  Alias: TB0624    

    Card: Original: 20010  Rounded: 200  Computed: 200.10  Non Adjusted: 200.10

  Access Path: TableScan

    Cost:  121.28  Resp: 121.28  Degree: 0

      Cost_io: 119.00  Cost_cpu: 7837335

      Resp_io: 119.00  Resp_cpu: 7837335

  Access Path: index (AllEqGuess)

    Index: IDX_TYPE

    resc_io: 17.00  resc_cpu: 151884

    ix_sel: 0.004  ix_sel_with_filters: 0.004

    Cost: 17.04  Resp: 17.04  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_TYPE

         Cost: 17.04  Degree: 1  Resp: 17.04  Card: 200.10  Bytes: 0

可以看到，当我在只改变#Blks的情况下，ix_sel的值未变。

 

2、修改#Rows后的10053

SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 438,numrows => 30010);

 

PL/SQL procedure successfully completed

 

SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';

 

  NUM_ROWS     BLOCKS AVG_ROW_LEN

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

     30010        438          14

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 30010  #Blks:  438  AvgRowLen:  14.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 40  #DK: 3  LB/K: 13.00  DB/K: 267.00  CLUF: 801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)  NO STATISTICS (using defaults)

    AvgLen: 22.00 NDV: 938 Nulls: 0 Density: 0.0010663

  Table: TB0624  Alias: TB0624    

    Card: Original: 30010  Rounded: 300  Computed: 300.10  Non Adjusted: 300.10

  Access Path: TableScan

    Cost:  100.66  Resp: 100.66  Degree: 0

      Cost_io: 98.00  Cost_cpu: 9127191

      Resp_io: 98.00  Resp_cpu: 9127191

  Access Path: index (AllEqGuess)

    Index: IDX_TYPE

    resc_io: 18.00  resc_cpu: 173806

    ix_sel: 0.005999  ix_sel_with_filters: 0.005999

    Cost: 18.05  Resp: 18.05  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_TYPE

         Cost: 18.05  Degree: 1  Resp: 18.05  Card: 300.10  Bytes: 0

可以看到，当我在只改变#ROWS的情况下，ix_sel的值已经发生了改变。

 

3、修改AvgRowLen后的10053：

SQL> exec dbms_stats.set_table_stats(ownname => 'IPRA',tabname => 'TB0624',numblks => 438,numrows => 20010,avgrlen => 24);

 

PL/SQL procedure successfully completed

 

SQL> SELECT NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='TB0624';

 

  NUM_ROWS     BLOCKS AVG_ROW_LEN

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

     20010        438          24

 

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 20010  #Blks:  438  AvgRowLen:  24.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 40  #DK: 3  LB/K: 13.00  DB/K: 267.00  CLUF: 801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)  NO STATISTICS (using defaults)

    AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992

  Table: TB0624  Alias: TB0624    

    Card: Original: 20010  Rounded: 200  Computed: 200.10  Non Adjusted: 200.10

  Access Path: TableScan

    Cost:  100.08  Resp: 100.08  Degree: 0

      Cost_io: 98.00  Cost_cpu: 7125191

      Resp_io: 98.00  Resp_cpu: 7125191

  Access Path: index (AllEqGuess)

    Index: IDX_TYPE

    resc_io: 17.00  resc_cpu: 151884

    ix_sel: 0.004  ix_sel_with_filters: 0.004

    Cost: 17.04  Resp: 17.04  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_TYPE

         Cost: 17.04  Degree: 1  Resp: 17.04  Card: 200.10  Bytes: 0

可以看到，当我在只改变AvgRowLen的情况下，ix_sel的值未变。

 

从上述测试中我们可以很清晰的得出如下结论：

在无统计信息的情况下，ix_sel的值取决于#Rows，而不是取决于#Blks。

利用10053分析执行计划的一个例子

By cui hua on June 26, 2009 5:44 PM | No Comments

远邦昨天给我出了一道题，里面涉及到了用10053分析执行计划，很有趣，我这里paste一下分析过程，与大家共享。

 

远邦的题目是这样的：

create table tb0624 (type number,ts timestamp) pctfree 90;

 

begin

for i in 1..10000 loop

insert into tb0624 values (1,sysdate);

insert into tb0624 values (3,sysdate);

end loop;

commit;

end;

/

 

begin

for i in 1..10 loop

insert into tb0624 values (2,sysdate);

end loop;

commit;

end;

/

 

SELECT TYPE,COUNT(*) FROM TB0624 GROUP BY TYPE;

 

      TYPE   COUNT(*)

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

         1      10000

         3      10000

         2         10

 

exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

 

-------检查列是否有柱状图

SET LINESIZE 200 PAGESIZE 100

COL ENDPOINT_ACTUAL_VALUE FOR A20

COL COLUMN_NAME FOR A5

SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';

SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME='TB0624';

SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';

SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME='TB0624';

SELECT NUM_ROWS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS FROM USER_INDEXES WHERE INDEX_NAME='IDX_TYPE';

 

-------开始分析索引

create index idx_type on tb0624(type);

 

 

-------再次检查列是否有柱状图

SET LINESIZE 200 PAGESIZE 100

COL ENDPOINT_ACTUAL_VALUE FOR A20

COL COLUMN_NAME FOR A5

SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';

SELECT * FROM USER_HISTOGRAMS WHERE TABLE_NAME='TB0624';

SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';

SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME='TB0624';

SELECT NUM_ROWS,BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,DISTINCT_KEYS FROM USER_INDEXES WHERE INDEX_NAME='IDX_TYPE';---因为是10G，这里建索引的时候会帮你分析

 

---开始测试

SQL> set autotrace on

SQL> select * from tb0624 where type=2;

Plan hash value: 4077598990

 

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |          |   353 |  9178 |     5   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB0624   |   353 |  9178 |     5   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TYPE |   141 |       |     1   (0)| 00:00:01 |

 

为什么oracle这里自己知道走索引？

10053显示索引选择率为0.004而不是1/3?

 

按照上述测试过程原封不动的做一遍，然后分析一下10053产生的trace文件，我这里称这个文件为trace文件1，因为后续我还会产生两个10053的trace文件2和trace文件3。

Trace文件1的部分内容：

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 20010  #Blks:  438  AvgRowLen:  14.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 40  #DK: 3  LB/K: 13.00  DB/K: 267.00  CLUF: 801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)  NO STATISTICS (using defaults)

    AvgLen: 22.00 NDV: 625 Nulls: 0 Density: 0.0015992

  Table: TB0624  Alias: TB0624    

    Card: Original: 20010  Rounded: 200  Computed: 200.10  Non Adjusted: 200.10

  Access Path: TableScan

    Cost:  100.08  Resp: 100.08  Degree: 0

      Cost_io: 98.00  Cost_cpu: 7125191

      Resp_io: 98.00  Resp_cpu: 7125191

  Access Path: index (AllEqGuess)

    Index: IDX_TYPE

    resc_io: 17.00  resc_cpu: 151884

    ix_sel: 0.004  ix_sel_with_filters: 0.004

    Cost: 17.04  Resp: 17.04  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_TYPE

         Cost: 17.04  Degree: 1  Resp: 17.04  Card: 200.10  Bytes: 0

这里index range scan的cost是17.04，table scan的cost是100.08，idx_type的DK是3，ix_sel确实是0.004。

 

其实远邦的问题上述trace文件已经给出答案了，就是我红字标明的部分，即：

上述测试案例里ix_sel=0.004而不是1/3，这是因为在type这一列上没有统计信息，所以oracle在这里用到了默认值来计算ix_sel，而不是用index上的DK来计算ix_sel（什么情况下也不会用DK来计算ix_sel）。换句话说，这里即使你用条件type=2（或者3）来查询的话，oracle这个地方也会用index range scan。

 

远邦，这个默认值不是固定的，感觉这里这个默认值取决于Blks，我这里证明给你看。

将上述表drop掉，插入数据的语句更改成

begin

for i in 1..10000 loop

insert into tb0624 values (1,sysdate);

insert into tb0624 values (3,sysdate);

insert into tb0624 values (4,sysdate);

end loop;

commit;

end;

/

 

begin

for i in 1..10 loop

insert into tb0624 values (2,sysdate);

end loop;

commit;

end;

/

 

然后再如法炮制分析10053产生的trace文件2：

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 30681  #Blks:  628  AvgRowLen:  14.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 59  #DK: 4  LB/K: 14.00  DB/K: 450.00  CLUF: 1801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)  NO STATISTICS (using defaults)

    AvgLen: 22.00 NDV: 959 Nulls: 0 Density: 0.001043

  Table: TB0624  Alias: TB0624    

    Card: Original: 30681  Rounded: 307  Computed: 306.81  Non Adjusted: 306.81

  Access Path: TableScan

    Cost:  142.09  Resp: 142.09  Degree: 0

      Cost_io: 139.00  Cost_cpu: 10614604

      Resp_io: 139.00  Resp_cpu: 10614604

  Access Path: index (AllEqGuess)

    Index: IDX_TYPE

    resc_io: 22.00  resc_cpu: 203032

    ix_sel: 0.0040894  ix_sel_with_filters: 0.0040894

    Cost: 22.06  Resp: 22.06  Degree: 1

  Best:: AccessPath: IndexRange  Index: IDX_TYPE

         Cost: 22.06  Degree: 1  Resp: 22.06  Card: 306.81  Bytes: 0

看到了吗，这里的ix_sel已经变成了0.0040894。

 

现在我们来让type这一列上产生统计信息：

将上述表drop掉，全部重来一遍，只不过在create index idx_type on tb0624(type)之后再执行一下

exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1')

或

exec dbms_stats.gather_table_stats('IPRA','TB0624',CASCADE=>FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE REPEAT')

 

上述两个之一执行完后，type这一列上已经有统计信息了：

SQL> SELECT TABLE_NAME,/* LOW_VALUE,HIGH_VALUE , */ COLUMN_NAME,NUM_NULLS,DENSITY,NUM_DISTINCT,HISTOGRAM FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TB0624';

 

TABLE_NAME                     COLUMN_NAME                     NUM_NULLS    DENSITY NUM_DISTINCT HISTOGRAM

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

TB0624                         TYPE                                    0       0.25            4 NONE

TB0624                         TS                                                                NONE

 

而且这里居然也产生了histogram信息，这和文档里描述的不一致，这里我的本意是不想产生histogram的：

SQL> SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TB0624';

 

TABLE_NAME                     COLUMN_NAME                                                                      ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

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

TB0624                         TYPE                                                                                           0              1

TB0624                         TYPE                                                                                           1              4

 

再次产生10053的trace文件3，发现现在oracle已经走全表扫描了，我们来看一下这个trace文件的内容：

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

BASE STATISTICAL INFORMATION

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

Table Stats::

  Table: TB0624  Alias: TB0624

    #Rows: 29590  #Blks:  628  AvgRowLen:  14.00

Index Stats::

  Index: IDX_TYPE  Col#: 1

    LVLS: 1  #LB: 59  #DK: 4  LB/K: 14.00  DB/K: 450.00  CLUF: 1801.00

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

SINGLE TABLE ACCESS PATH

  Column (#1): TYPE(NUMBER)

    AvgLen: 3.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 1 Max: 4

  Table: TB0624  Alias: TB0624    

    Card: Original: 29590  Rounded: 7398  Computed: 7397.50  Non Adjusted: 7397.50

  Access Path: TableScan

    Cost:  142.07  Resp: 142.07  Degree: 0

      Cost_io: 139.00  Cost_cpu: 10538224

      Resp_io: 139.00  Resp_cpu: 10538224

  Access Path: index (AllEqRange)

    Index: IDX_TYPE

    resc_io: 466.00  resc_cpu: 6095551

    ix_sel: 0.25  ix_sel_with_filters: 0.25

    Cost: 467.78  Resp: 467.78  Degree: 1

  Best:: AccessPath: TableScan

         Cost: 142.07  Degree: 1  Resp: 142.07  Card: 7397.50  Bytes: 0

注意这里ix_sel变成了type列上的density了（也就是0.25），直接导致index range scan的cost激增成467.78，已经超过了table scan的142.07，所以oracle这里当然会选择全表扫描。

关于parallel rebuild index

By cui hua on June 23, 2009 4:46 PM | 4 Comments

先说一下结论：

1、parallel rebuild index的并行度并不取决于"alter index index_name rebuild parallel n"中的数字n，而是取决于一系列条件，但并行度不能大于PARALLEL_MAX_SERVERS。对于这一系列条件，oracle文档中也有描述：

PARALLEL

Specify PARALLEL if you want Oracle to select default degree of parallelism.

 

PARALLEL integer

Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

 

Default Degree of Parallelism：

The default DOP is used when you ask to parallelize an operation but you do not specify a DOP in a hint or within the definition of a table or index. The default DOP is appropriate for most applications.

 

The default DOP for a SQL statement is determined by the following factors:

The value of the parameter CPU_COUNT, which is, by default, the number of CPUs on the system, the number of RAC instances, and the value of the parameter PARALLEL_THREADS_PER_CPU.

 

For parallelizing by partition, the number of partitions that will be accessed, based on partition pruning.

 

For parallel DML operations with global index maintenance, the minimum number of transaction free lists among all the global indexes to be updated. The minimum number of transaction free lists for a partitioned global index is the minimum number across all index partitions. This is a requirement to prevent self-deadlock.

 

These factors determine the default number of parallel execution servers to use. However, the actual number of processes used is limited by their availability on the requested instances during run time. The initialization parameter PARALLEL_MAX_SERVERS sets an upper limit on the total number of parallel execution servers that an instance can have.

 

2、"alter index index_name rebuild parallel n"中的数字n决定了这个被rebuild的索引被访问的并行度，但这并不意味着oracle就会依据这个并行度去访问这个索引，这里仅仅意味着CBO会把并行访问作为一种备选项。

 

好了，我们来验证上述结论：

SQL> select count(*) from armshistemp.uplpdt;

 

  COUNT(*)

----------

   6962716

 

SQL> create index idx_uplpdt_updtkt on armshistemp.uplpdt(updtkt);

 

Index created

 

SQL> show parameter PARALLEL_THREADS_PER_CPU;

 

NAME                                 TYPE        VALUE

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

parallel_threads_per_cpu             integer     2

 

SQL> show parameter CPU_COUNT;

 

NAME                                 TYPE        VALUE

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

cpu_count                            integer     8

 

show parameter PARALLEL_MAX_SERVERS;

 

NAME                                 TYPE        VALUE

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

parallel_max_servers                 integer     5

 

现在PARALLEL_THREADS_PER_CPU和CPU_COUNT的乘积是16，PARALLEL_MAX_SERVERS的值是 5，所以当我rebuild idx_uplpdt_updtkt的时候并行度一定不可能大于5，来验证一下，开两个session:

 

Session 1:

13:35:37 SQL> alter index idx_uplpdt_updtkt rebuild;

 

Index altered

 

13:36:06 SQL> select sysdate from dual;

 

SYSDATE

-----------

2009-6-23

 

13:37:08 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;

 

Index altered

 

13:37:51 SQL> select sysdate from dual;

 

SYSDATE

-----------

2009-6-23

 

13:40:37 SQL> alter index idx_uplpdt_updtkt rebuild parallel 4;

 

Index altered

 

13:41:34 SQL> select sysdate from dual;

 

SYSDATE

-----------

2009-6-23

 

13:42:40 SQL> alter index idx_uplpdt_updtkt rebuild parallel;

 

Index altered

 

在session 1 rebuild index的时候去session 2中观察v$session中的session的数量，这里因为uplpdt中有接近700万的数据，所以在rebuild index的时候我有充足的时间去观察v$session中的session的状况，下面是我在session 2中观察到的情况：

 

Session 2:

在执行alter index idx_uplpdt_updtkt rebuild的时候session 2中观察到的v$session的情况为：

SQL> select sid,status,osuser,module,action from v$session;

 

SID        STATUS   OSUSER    MODULE                ACTION

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

         1 ACTIVE   oracle                                                                          

         2 ACTIVE   oracle                                                                         

         3 ACTIVE   oracle                                                                         

         4 ACTIVE   oracle                                                                         

         5 ACTIVE   oracle                                                                         

         6 ACTIVE   oracle                                                                          

         7 ACTIVE   oracle                                                                         

         9 INACTIVE cuihua        PL/SQL Developer             Main session

        13 INACTIVE cuihua        PL/SQLDeveloper     Command Window - New

        16 ACTIVE   cuihua        PL/SQL Developer    Command Window - New

 

10 rows selected

 

在执行alter index idx_uplpdt_updtkt rebuild parallel 2，alter index idx_uplpdt_updtkt rebuild parallel 4和alter index idx_uplpdt_updtkt rebuild parallel的时候session 2中观察到的v$session中的情况均为：

SQL> select sid,status,osuser,module,action from v$session;

 

   SID  STATUS   OSUSER       MODULE                        ACTION

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

         1 ACTIVE   oracle                                                                         

         2 ACTIVE   oracle                                                                         

         3 ACTIVE   oracle                                                                         

         4 ACTIVE   oracle                                                                         

         5 ACTIVE   oracle                                                                          

         6 ACTIVE   oracle                                                                         

         7 ACTIVE   oracle                                                                         

         9 INACTIVE cuihua          PL/SQL Developer           Main session

        12 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

        13 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

        15 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

        16 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

        18 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

        19 ACTIVE  cuihua          PL/SQL Developer       Command Window - New

 

14 rows selected

可以看到多出了4个session。

 

现在我们把PARALLEL_MAX_SERVERS由5改为6，然后再验证一下在做parallel rebuild的时候的状况：

SQL> alter system set parallel_max_servers=6 scope=spfile;

 

System altered

 

重启上述数据库后如法炮制，然后再在session 2中观察v$session中的状况。

SQL> show parameter PARALLEL_MAX_SERVERS;

 

NAME                                 TYPE        VALUE

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

parallel_max_servers                 integer     6

 

这里在session 1中分别执行：

14:34:02 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;

 

Index altered

 

14:35:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 16;

 

Index altered

 

14:36:38 SQL> alter index idx_uplpdt_updtkt rebuild parallel 4;

 

Index altered

 

15:02:00 SQL> alter index idx_uplpdt_updtkt rebuild parallel;

 

Index altered

 

15:16:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;

 

Index altered

 

Session 2中观察到的情况为：

1、执行alter index idx_uplpdt_updtkt rebuild parallel 2的时候多了4个session。

2、执行alter index idx_uplpdt_updtkt rebuild parallel 16的时候多了6个session。

3、执行alter index idx_uplpdt_updtkt rebuild parallel 4的时候多了6个session。

4、执行alter index idx_uplpdt_updtkt rebuild parallel的时候多了6个session。

5、再次执行alter index idx_uplpdt_updtkt rebuild parallel 2的时候还是多了4个session。

 

至此，结论1得到验证。

 

现在我们来验证结论2：

15:16:05 SQL> alter index idx_uplpdt_updtkt rebuild parallel 2;

 

Index altered

 

15:20:29 SQL> set time off;

SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';

 

DEGREE

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

2

 

SQL> alter index idx_uplpdt_updtkt rebuild parallel;

 

Index altered

 

SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';

 

DEGREE

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

DEFAULT

 

SQL> alter index idx_uplpdt_updtkt rebuild parallel 16;

 

Index altered

 

SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';

 

DEGREE

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

16

 

SQL> alter index idx_uplpdt_updtkt noparallel;

 

Index altered

 

SQL> select degree from dba_indexes where index_name='IDX_UPLPDT_UPDTKT';

 

DEGREE

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

1

 

至此，结论2也得到验证。

这就是为什么在parallel rebuild index后一定要执行alter index index_name noparallel的原因。

 

最后试了一下传说中的可以加快rebuild index的方法，发现parallel的最短时间还不如直接rebuild快！

SQL> alter session set workarea_size_policy=MANUAL;

 

Session altered

 

SQL> alter session set sort_area_size=1073741824;

 

alter session set sort_area_size=1073741824

 

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00600: internal error code, arguments: [925], [hash_area_size], [-2147483648], [], [], [], [], []

 

SQL> alter session set sort_area_size=800000000;

 

Session altered

 

SQL> alter session set sort_area_retained_size=800000000;

 

Session altered

 

SQL> alter session set db_file_multiblock_read_count=128;

 

Session altered

 

SQL> set time on

 

15:42:29 SQL> alter index idx_uplpdt_updtkt rebuild online parallel compute statistics;

 

Index altered

 

15:43:04 SQL> select sysdate from dual;

SYSDATE

-----------

2009-6-23

 

15:46:25 SQL> alter index idx_uplpdt_updtkt rebuild parallel;

 

Index altered

 

15:47:05 SQL> select sysdate from dual;

SYSDATE

-----------

2009-6-23

 

15:50:52 SQL> alter index idx_uplpdt_updtkt rebuild;

 

Index altered

 

15:51:18 SQL>

 

注意，对于结论2，当你将某个index的degree改变之后oracle这里并不一定会去并行访问的，对这一点，196938.1有如下描述：

Note that the affect of setting a degree of parallelism on an index has changed. Prior to 8i this would have had no affect. Post 8i a parallel plan will be investigated. If degree is set ( to an integer value > 1 or to 'DEFAULT' ) on an object then this will mean a parallel plan is considered. Remember that the optimizer works on a cost basis so just because a parallel plan may be considered, does not mean that it will be chosen. Since 8i we have changed the syntax for the setting of degree of parallelism See Note 260845.1 Old and new Syntax for setting Degree of Parallelism.

关于physical guess

By cui hua on June 19, 2009 10:13 AM | No Comments

在"logical rowid的结构"这篇文章里，我人为构造了一个stale的physical guess的例子，这里我们继续，做如下的三个测试：

1、修改logical rowid的primary key但不修改physical guess，以验证oracle在physical guess准确的情况下是否需要primary key。

2、同时修改logical rowid的primary key和physical guess，以验证oracle在这样的情况下的表现。

3、修改IOT里一条记录的primary key，验证在这种情况下其索引上的physical guess是否会stale。

 

这里的测试对象是ID为2的那条记录：

SQL> select id,rowid,dump(rowid,16) from t3;

 

        ID ROWID                           DUMP(ROWID,16)

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

         1 *BCFAABMCwQL+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,2,fe

         2 *BCFAABMCwQP+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,3,fe

         3 *BCFAABMCwQT+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,4,fe

         4 *BCFAABQCwQX+                 Typ=208 Len=10: 2,4,21,40,0,14,2,c1,5,fe

 

这是未修改前的查询结果：

SQL>  select id,substr(c2,1,1) from t3 where c1='B';

 

        ID SUBSTR(C2,1,1)

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

         2 B

 

具体的测试过程我就不再赘述了，我们来看一下上述三个测试的结果。

 

测试一：

这里我是把主键ID=2改为3，即C1 04，但不改physical guess 0x21400013

BBED> modify /x 0x0400014202c1042c00010421400013

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8094 to 8191           Dba:0x2140002a

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

 04000142 02c1042c 00010421 40001304 00014102 c1022c00 01042140 0013c30d

 1c1eac00 03000100 010040dd 67000100 40dd6700 0102c122 02c15b04 c30d1c0e

 ac000300 01000100 40dd6700 000040dd 67000002 c12202c1 5b04c30d 1b62cdec

 0601

 

 <32 bytes per line>

 

改完后startup上述数据库再执行上述查询：

SQL> select id,substr(c2,1,1) from t3 where c1='B';

 

        ID SUBSTR(C2,1,1)

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

         3 C

 

SQL> select /*+ full(t3) */id,substr(c2,1,1) from t3 where c1='B';

 

        ID SUBSTR(C2,1,1)

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

         2 B

 

从测试结果里可以看到，只要我改了主键，即使physical guess是准确的，oracle这里也会得到错误的值。

 

既然测试一的结果是这样，那测试二也就不用做了。

 

测试三：

ID=1的那条记录的physical guess是0x21400013，我现在把ID=1改成ID=5，这时候ID=1的那条记录必然要挪到0x21400014中，等修改完成后我们再dump一下，看看ID=5的那条记录所对应的index block的physical guess是0x21400013还是0x21400014。

SQL> update t3 set id=5 where id=1;

 

1 row updated

 

SQL> commit;

 

Commit complete

 

SQL> select id,rowid,dump(rowid,16) from t3;

 

        ID ROWID                           DUMP(ROWID,16)

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

         2 *BCFAABMCwQP+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,3,fe

         3 *BCFAABMCwQT+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,4,fe

         4 *BCFAABQCwQX+                 Typ=208 Len=10: 2,4,21,40,0,14,2,c1,5,fe

         5 *BCFAABQCwQb+                 Typ=208 Len=10: 2,4,21,40,0,14,2,c1,6,fe

 

SQL> alter system checkpoint;

 

System altered

 

Dump一下133_42这个index block：

row#0[8017] flag: K--D-, lock: 2

col 0; len 1; (1):  41

col 1; len 2; (2):  c1 02

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275FB8 to 0x0000000110275FBC

110275FB0                   21400013                   [!@..]   

row#1[7957] flag: K----, lock: 2

col 0; len 1; (1):  41

col 1; len 2; (2):  c1 06

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275F7C to 0x0000000110275F80

110275F70                            21400014              [!@..]

row#2[8002] flag: K----, lock: 0

col 0; len 1; (1):  42

col 1; len 2; (2):  c1 04

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275FA9 to 0x0000000110275FAD

110275FA0                   04214000 13050201          [.!@.....]

row#3[7987] flag: K----, lock: 0

col 0; len 1; (1):  43

col 1; len 2; (2):  c1 04

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275F9A to 0x0000000110275F9E

110275F90                   01042140 00130400          [..!@....]

row#4[7972] flag: K----, lock: 0

col 0; len 1; (1):  44

col 1; len 2; (2):  c1 05

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275F8B to 0x0000000110275F8F

110275F80                   00010421 40001404          [...!@...]

----- end of leaf block dump -----

End dump data blocks tsn: 84 file#: 133 minblk 42 maxblk 42

 

现在ID=5所在的offset是7957+68+24=8049

好了，我们现在来看其physical guess：

BBED> set offset 8049

        OFFSET          8049

 

BBED> dump

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8049 to 8191           Dba:0x2140002a

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

 04020141 02c1062c 00010421 40001404 00014402 c1052c00 01042140 00140400

 014302c1 042c0001 04214000 13040001 4202c104 2c000104 21400013 05020141

 02c1022c 00010421 400013c3 0d1c1eac 00030001 00010040 dd670001 0040dd67

 000102c1 2202c15b 04c30d1c 0eac0003 00010001 0040dd67 00000040 dd670000

 02c12202 c15b04c3 0d1b621e 620601

 

 <32 bytes per line>

可以从结果里看到，现在ID=5的那条记录所对应的index block的physical guess是0x21400014，也就是说单纯的修改primary key并不会导致physical guess成stale。

logical rowid的结构

By cui hua on June 18, 2009 3:23 PM | No Comments

先说一下结论，然后详细描述我得到这个结论的过程。

logical rowid的结构为：

length(logical rowid) || 2 || length(physical guess dba) || physical guess dba || length(primary key) || primary key || ending flag

 

IOT上non unique索引里存的logical rowid的结构为：

04或者05（05表示已被删掉） || 00或者02 || length(index key) || index key || length(primary key) || primary key || 2c || 00或者01等(取决于ITC) || 01 || length(physical guess dba) || physical guess dba

 

IOT上unique索引里存的logical rowid的结构为：

04或者05（05表示已被删掉）|| 00或者02 || length(index key) || index key || 2c || 00或者01等(取决于ITC) || 01 || length(primary key+physical guess)+1 || length(primary key) || primary key || physical guess dba

 

 

先来看一个IOT的overflow的情况：

SQL> create table t3 (id number, c1 varchar2(1),c2 char(2000), c3 char(2000), c4 char(2000),constraint pk_t2_id primary key (id))

  2  organization index tablespace testtbs

  3  pctthreshold 5

  4  overflow tablespace testtbs;

 

Table created

 

SQL>  insert into t3 VALUES (1,'A', 'A', 'A', 'A');

 

1 row inserted

 

SQL>  insert into t3 VALUES (2,'B', 'B', 'B', 'B');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select id,rowid,dump(rowid,16) from t3;

 

        ID ROWID                           DUMP(ROWID,16)

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

         1 *BCFAACoCwQL+                 Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,2,fe

         2 *BCFAACoCwQP+                 Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,3,fe

 

我们来看ID为1的这条记录的rowid，这里dump出来是值是Typ=208 Len=10: 2,4,21,40,0,2a,2,c1,2,fe，这里我从左往右依次解释每个byte的含义：

这里type是208，即urowid

length为10个byte

2表示是logical rowid

4表示physical guess记录的dba的长度

紧接着的四个byte 21 40 00 2a是physical guess记录的dba

SQL> exec sys.cdba('2140002a','H');

 

The file is 133

The block is 42

 

PL/SQL procedure successfully completed

 

接着的2表示primary key的长度

紧接着的两个byte C1 02是primary key的值，C1 02也就是1

SQL> select dump(1,16) from dual;

 

DUMP(1,16)

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

Typ=2 Len=2: c1,2

 

最后的一个byte是fe，即254，估计是一个ending flag。

 

我们现在dump 133_42这个block：

row#0[8016] flag: K----, lock: 2

col 0; len 2; (2):  c1 02

tl: 11 fb: --H-F--- lb: 0x0  cc: 1

nrid:  0x21400012.0

col  0: [ 1]

Dump of memory from 0x0000000110275FBB to 0x0000000110275FBC

110275FB0                   00000141                   [...A]   

row#1[8000] flag: K----, lock: 2

col 0; len 2; (2):  c1 03

tl: 11 fb: --H-F--- lb: 0x0  cc: 1

nrid:  0x21400013.0

col  0: [ 1]

Dump of memory from 0x0000000110275FAB to 0x0000000110275FAC

110275FA0                   00000142                   [...B]

这里我们可以看到oracle把表t3中的c2、c3、c4给存在0x21400012和0x21400013里了，这符合我们建t3时指定pctthreshold为5%的要求。

 

现在我们重建表t3:

SQL> create table t3 (id number, c1 varchar2(1),c2 char(2000),constraint pk_t2_id primary key (id))

  2  organization index tablespace testtbs;

 

Table created

 

SQL> insert into t3 VALUES (1,'A', 'A');

 

1 row inserted

 

SQL> insert into t3 VALUES (2,'B', 'B');

 

1 row inserted

 

SQL> insert into t3 VALUES (3,'C', 'C');

 

1 row inserted

 

SQL> insert into t3 VALUES (4,'D', 'D');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select id,rowid,dump(rowid,16) from t3;

 

        ID ROWID                           DUMP(ROWID,16)

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

         1 *BCFAABMCwQL+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,2,fe

         2 *BCFAABMCwQP+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,3,fe

         3 *BCFAABMCwQT+                 Typ=208 Len=10: 2,4,21,40,0,13,2,c1,4,fe

         4 *BCFAABQCwQX+                 Typ=208 Len=10: 2,4,21,40,0,14,2,c1,5,fe

 

按照重建后t3的定义，现在一个block里最多能能存3条记录，所以我们可以从dump的结果里看到ID为4的那条记录的已经被存在了0x21400014里。

 

现在我们在t3上对c1建一个索引：

SQL> create index idx_t3_c1 on t3(c1) tablespace testtbs;

 

Index created

 

对上述index做一个treedump：

----- begin tree dump

leaf: 0x2140002a 557842474 (0: nrow: 4 rrow: 4)

----- end tree dump

 

再dump 0x2140002a：

row#1[8002] flag: K----, lock: 0

col 0; len 1; (1):  42

col 1; len 2; (2):  c1 03

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]

Dump of memory from 0x0000000110275FA9 to 0x0000000110275FAD

110275FA0                   04214000 13040001          [.!@.....]

 

按道理讲，IOT上的索引应该会存索引键值、physical guess和primary key，这里我们来验证一下索引键值为2的那条记录是如何存的：

因为testtbs是MSSM的LMT，且itc为2，所以这里的offset为：

8002+68+(2-1)*24=8094

 

BBED> set offset 8094

        OFFSET          8094

 

BBED> dump

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8094 to 8191           Dba:0x2140002a

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

 04000142 02c1032c 00010421 40001304 00014102 c1022c00 01042140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000afee

 0601

 

 <32 bytes per line>

从dump结果里可以看到索引键值为2的那条记录的索引键值是42（也就是B），primary key是C103（也就是2），physical guess是21400013，这和我们dump rowid的结果一致。

 

好了，我们现在再来构造一个physical guess为stale的情况：

索引键值为2的那条记录的physical guess是21 400013，我们现在将其改成21400014，按照oracle文档中的说法，我这里即使改了也没关系的，oracle当检索到stale的physical guess的时候，会再根据primary key再去扫描一遍IOT，从而得到正确的值。

 

在改之前的结果是：

SQL> select id,substr(c2,1,1) from t3 where c1='B';

 

        ID SUBSTR(C2,1,1)

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

         2 B

 

先shutdown，再改：

BBED> set offset 8094

        OFFSET          8094

 

BBED> dump

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8094 to 8191           Dba:0x2140002a

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

 04000142 02c1032c 00010421 40001305 00014102 c1022c00 01042140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b7c4

 0601

 

 <32 bytes per line>

 

BBED> modify /x 0x0400014202c1032c00010421400014  

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/astca/test01.dbf (133)

 Block: 42               Offsets: 8094 to 8191           Dba:0x2140002a

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

 04000142 02c1032c 00010421 40001405 00014102 c1022c00 01042140 00130000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000b7c4

 0601

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 133, Block 42:

current = 0x725f, required = 0x725f

 

改完了，现在索引键值为2的那条记录的physical guess已经变成了21400014，这已经是stale的了，我们现在再startup上述数据库，然后看一下改过之后的结果：

SQL> explain plan for select id,substr(c2,1,1) from t3 where c1='B';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

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