Problem about small table

昨天出于无聊, 测试了一下关于Oracle9idual表的相关处理.

发现一个问题, 对于很小的表, 使用full table 桑蚕并不是最好的选择..:-)

SQL> create table dual_x(dummy varchar2(1) primary key);

Table created.

SQL> insert into dual_x values('X');

1 row created.
SQL> commit;

Commit complete.

SQL> select * from dual_x;

D                                                                              
-                                                                              
X                                                                              


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE                                    
   1    0   TABLE ACCESS (FULL) OF 'DUAL_X'                                    

Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          3  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
        375  bytes sent via SQL*Net to client                                  
        503  bytes received via SQL*Net from client                            
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
          1  rows processed                                                    

使用普通的全表扫面, 一次全表扫面需要3个consistent read
SQL> /

D                                                                              
-                                                                              
X                                                                              


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE                                    
   1    0   TABLE ACCESS (FULL) OF 'DUAL_X'                                    

Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          3  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
SQL> select /*+index_ffs(a)*/ * from dual_x a;

D                                                                              
-                                                                              
X                                                                              


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)          
   1    0   INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C         
          ard=41 Bytes=82)                                                     

Statistics
----------------------------------------------------------                     
         22  recursive calls                                                   
          0  db block gets                                                     
          5  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         

SQL> /

D                                                                              
-                                                                              
X                                                                              

Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=41 Bytes=82)          
   1    0   INDEX (FAST FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=2 C         
          ard=41 Bytes=82)                                                     
                                                                               
Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          3  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         
我修改这条语句的执行计划, 强制使用index fast full scan, 得到的结果是相同的, 还是3个consistent read.

我们来给这个表坐一下分析, 统计一下表/索引相关的信息.
SQL> analyze table dual_x compute statistics for table for all indexes;

Table analyzed.

SQL> select * from dual_x;

D                                                                              
-                                                                              
X                                                                              


Execution Plan
----------------------------------------------------------                     
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)            
   1    0   INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1         
           Bytes=2)                                                            
                                                                               
Statistics
----------------------------------------------------------                     
          0  recursive calls                                                   
          0  db block gets                                                     
          1  consistent gets                                                   
          0  physical reads                                                    
          0  redo size                                                         

SQL> spool off
我们可以看到现在这个语句的执行计划为INDEX FULL SCAN, 对应的io为1个consistent read. 这是我们的这个小表的执行计划才是最优的.

由上面的例子, 我们可以得出两个结论.

1. 小表也需要优化, 特别是dual表, 每天使用的频率相当高, 这样调整之后, 可以给系统节约大量io读写.
2. 对于小表而言, 全表扫面不一定是最好的.:-)


之后, 我又对v$bh表做了一下查询, 得到下面的这个结果,
SQL> select index_name from ind where table_name = 'DUAL_X';

INDEX_NAME
------------------------------
SYS_C002610

SQL> select * from v$bh where objd = 31819;

     FILE#     BLOCK#     CLASS# STATU        XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD        TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
         1      89425          4 xcur           0            0             0
00                                            Y N N N N N      31819          0

         1      89426          1 xcur           0            0             0
00                                            Y N N N N N      31819          0


SQL> select * from obj where object_name = 'SYS_C002610';

OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SYS_C002610
                                    31820          31820 INDEX
11-APR-05 11-APR-05 2005-04-11:22:25:18 VALID   N Y N


SQL> select * from v$bh where objd = 31820;

     FILE#     BLOCK#     CLASS# STATU        XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD        TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
         1      89441          4 xcur           0            0             0
00                                            Y N N N N N      31820          0

         1      89442          1 xcur           0            0             0
00                                            Y N N N N N      31820          0

问题: 这个地方只有两条记录, 一个是segment header, 一个是segment data, 但是为什么会产生3个consistent read, 哪位大师能给我解释一下吗:-)

重启数据库之后, 再查询dual_x表, 得到以下的信息, 也就是select * from dual_x 只在v$bh中产生一条记录.
SQL> set autotrace on
SQL> select * from dual_x;

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
   1    0   INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
           Bytes=2)





Statistics
----------------------------------------------------------
        171  recursive calls
          0  db block gets
         26  consistent gets
          2  physical reads
         60  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
   1    0   INDEX (FULL SCAN) OF 'SYS_C002610' (UNIQUE) (Cost=1 Card=1
           Bytes=2)





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

SQL> select * from v$bh where objd = 31820;

     FILE#     BLOCK#     CLASS# STATU        XNC FORCED_READS FORCED_WRITES
---------- ---------- ---------- ----- ---------- ------------ -------------
LOCK_ELE LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD        TS#
-------- ----------------- ------------------ - - - - - - ---------- ----------
         1      89442          1 xcur           0            0             0
00                                            Y N N N N N      31820          0


[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94317/viewspace-795736/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94317/viewspace-795736/