ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 9i索引范围扫描逻辑读问题

9i索引范围扫描逻辑读问题

原创 Linux操作系统 作者:yangtingkun 时间:2009-01-14 23:57:07 0 删除 编辑

在论坛里面看到一个帖子,发现9i在默写情况下,执行索引范围扫描,并没有过滤结果,导致大量不必要的逻辑读。

 

 

看一个具体的例子吧:

SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO TEST SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已创建31400行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_TEST_NAME ON TEST (NAME);

索引已创建。

下面执行一个查询,这个查询的WHERE条件对NAME字段进行限制,不过LIKE操作添加了前通配符,所以不能通过索引来快速定位结果:

SQL> SET AUTOT TRACE
SQL> SELECT * FROM TEST WHERE NAME LIKE '%TEST';

已选择21行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

 


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        153  consistent gets
          0  physical reads
          0  redo size
        800  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

可以看到,Oracle选择了全表扫描,如果使用HINT指定索引:

SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';

已选择21行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)

 


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24243  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

问题出现了,Oracle选择了索引范围扫描,但是通过统计信息可以看到,查询导致了大量的逻辑读,已经远远超过了全表扫描的大小,显然Oracle没有在索引扫描后排除掉不满足结果的记录,而是将所有的ROWID全部返回到上一步,根据这些ROWID读取记录后,Oracle才进行的过滤。

而这种操作的代价导致了严重的性能问题,如果将查询改变一下,增加一个排序,尝试让Oracle选择ORACLE全索引扫描,看看执行结果:

SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;

已选择21行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)

 


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      24243  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

Oracle仍然选择了索引范围扫描,问题依旧,下面修改一下SESSION级的OPTIMIZER_INDEX选项:

SQL> SET AUTOT OFF
SQL> SHOW PARAMETER OPTIMIZER_INDEX

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 100;

会话已更改。

SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;

会话已更改。

SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;

已选择21行。


执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=7 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=7 Card=4 Bytes=120)
   2    1     INDEX (FULL SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=26 Card=4)

 


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        168  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

这时大量逻辑读的问题就不存在了。Oracle的全索引扫描时,会根据索引先过滤WHERE条件,然后将过滤结果的ROWID去访问表。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

这是9204中的现象,在9201中表现有所区别,只要加上ORDER BY语句,即使执行计划仍然是索引范围扫描,依然可以避免大量的逻辑读,而不带ORDER BY的查询语句,索引范围扫描就会导致大量的逻辑读:

SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)

  

Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
      20590  consistent gets
        121  physical reads
          0  redo size
        490  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)
          3  rows processed

SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)

  

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        126  consistent gets
          0  physical reads
          0  redo size
        490  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)
          3  rows processed

SQL> SET AUTOT OFF                    
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

而在10g中,则Oracle直接会采用索引全扫描,不会产生这个问题:

SQL> SET AUTOT TRACE
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';

已选择17行。


执行计划
----------------------------------------------------------
Plan hash value: 1655750108

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    23 |   690 |  1485   (1)| 00:00:21 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |    23 |   690 |  1485   (1)| 00:00:21 |
|*  2 |   INDEX FULL SCAN           | IND_TEST_NAME |  2854 |       |   134   (1)| 00:00:02 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NAME" LIKE '%TEST')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
        750  bytes sent via SQL*Net to client
        349  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed

SQL> SET AUTOT OFF
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

看来这个问题属于9ibug,不过这个bug92019204上的表现还有所区别。

 

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

下一篇: ORA-600(15599)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10469858