惜分飞

hint指定index的深入理解

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

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

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

Table created.

SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);

Index created.

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

Index created.

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

PL/SQL procedure successfully completed.

无index hint

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

845708 rows selected.


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

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


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

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

index hint b_tree_t_xifenfei

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

845708 rows selected.


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

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


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

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

index hint 一个无效index

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

845708 rows selected.


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

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


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

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

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

发表在 Oracle | 一条评论

通过dump分析undo镜像内容

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

一.模拟表并插入数据

SQL> select * from v$version;

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

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

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

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

二.dump当前表数据

SQL> alter system dump datafile 4 block 175;

System altered.

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

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

SQL> select * from t_xff;

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

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

1 row updated.

SQL> select * from t_xff;

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

四.dump修改后数据块

SQL> alter system checkpoint;

System altered.

SQL> alter system dump datafile 4 block 175;

System altered.


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

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


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

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

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

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

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

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

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

System altered.

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

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

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

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

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

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

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

六.dump 对应undo数据块

SQL> alter system dump datafile 3 block 8155;

System altered.


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

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

发表在 Oracle | 3 条评论

17813235971

QQ 咨询

DB2实例管理

hint指定index的深入理解

通过dump分析undo镜像内容

文章分类

最近发表

DB2实例管理

hint指定index的深入理解

通过dump分析undo镜像内容

标签云

文章分类

最近发表