SQL优化-同SQL不同执行计划(二)

  【疑惑二解答】

在绝大多数时候，都会认为【全表扫描】的SQL会慢于【索引快速扫描】的SQL，我们应该相信Oracle的选择，在【疑惑一解答】中已经排除了【统计信息】错误的可能，同时统计信息也给我们提供了一个重要的线索，不知道大家主要到了没有，数据库创建的时候默认块大小应该是8Kb。

【TBNC_P】表存储了3万记录，而数据块使用了244块。

【TBNC_A】表存储了4万记录，而数据块使用了19535块。

 

且不说合理与否，但是这样巨大的差异，肯定隐藏这什么玄机。

我的第一个感觉，数据很有可能存在Lob字段，或是其他。

先改造了一下【SQL1】语句，强制使用全表扫描并查看执行计划：

【SQL1全表扫描】

Select/*+ FULL(aps) */

 art.article_id, art.article_title, aps.adminaccount

 from TBNC_A art, TBNC_P aps

 where art.column_id = aps.scopestr

   and aps.funcnodepath = 'A001B002C002D002E003'

   and aps.adminaccount = 'lgm';

 

我又重新自己看了一下执行计划每步的cost，发现个的疑问，执行计划片段如下：

【SQL1】

步骤                                                                                                                 成本   估计返回行   估计返回字节

5    Select statement                                                                                         4291            888         64.172

4         Hash Join                                                                                               4291            888         64.172

2             TBNC_P TABLE ACCESS [BY INDEX ROWID]         3             11          0.322

1                 WEB.SYS_LGM INDEX [RANGE SCAN]                                        1             87            --

3             TBNC_A TABLE ACCESS [FULL]                                    4288          40782       1752.352

 

【SQL1全表扫描】

步骤                                                                                                                 成本     估计返回行   估计返回字节

4    Select statement                                                                                         4344          44229       3196.236

3         Hash Join                                                                                               4344          44229       3196.236

1             TBNC_P TABLE ACCESS [FULL]                           56            541          15.85

2             TBNC_A TABLE ACCESS [FULL]                                    4288          40782       1752.352

 

 

【SQL2】

步骤                                                                                                               成本     估计返回行   估计返回字节

4    Select statement                                                                                      4344          44229       3196.236

3         Hash Join                                                                                            4344          44229       3196.236

1             TBNC_P TABLE ACCESS [FULL]                        56            541          15.85

2             TBNC_A TABLE ACCESS [FULL]                                 4288          40782       1752.352

 

【SQL1全表扫描】和【SQL2】执行计划【总成本】都是4344，但从myepoch的反馈，

【SQL1全表扫描】比改造前能有所改善，但【SQL1全表扫描】还是明显比【SQL2】慢。

到此【SQL1全表扫描】改造失败，未能彻底解决问题。

 

从另一条线索继续前行(数据块疑问)，为什么【TBNC_A】表存储了4万记录，

为什么Oraclde使用了那么多数据块呢？如果一条记录被存储在多个数据块中，

那么Oracle在读取此条数据的时候会如何处理呢？带着好多猜想继续调查。

我们可以清楚看到【SQL1】成本陡变的地方是【步骤3】，所以Oracle选择【SYS_LGM】从成本角度确实可以降低成本，【SYS_LGM】属于【TBNC_P】表，但是因为【TBNC_A】的全表扫描，

导致整个成本急剧增加，占据了整个成本的99%，很显然问题存在这里，

消灭【TBNC_A】的全表扫描，变成了首要目标。

为了加快进度，email联络了myepoch联络后，得到了完整表结构，结构如下：

为了看出重点，我把有问题的地方都是表结构上需要注意的地方标上红色。

create table TBNC_A
(
  ARTICLE_ID           NUMBER(10) not null,
  ARTICLE_TITLE        VARCHAR2(255) not null,
  ARTICLE_CONTENT      CLOB not null,
  COLUMN_ID            VARCHAR2(255) not null,
  ARTICLE_INDATETIME   DATE,
  ARTICLE_ORDER        NUMBER(10),
  ARTICLE_MENU         CLOB,
  ARTICLE_BODY         CLOB not null,
  ARTICLE_ATTACHMENT   CLOB,
  ARTICLE_CHECKID      VARCHAR2(1),
   ARTICLE_PROOFREADID  VARCHAR2(1),
  ARTICLE_CHECKTEXT    CLOB,
  LOCKACCOUNT          VARCHAR2(255),
  ARTICLE_RELATIVEFROM VARCHAR2(500)
)
tablespace WEB
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 160
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table TBNC_A
  add primary key (ARTICLE_ID)
  using index
  tablespace WEB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 768K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes
create index INDEX_COLUMN on TBNC_A (COLUMN_ID)
  tablespace WEB
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

 

可以清楚看出，【TBNC_A】表设计，它拥有两个索引，一个是主键【ARTICLE_ID】，

另换一个就是我们检索条件中的【COLUMN_ID】，从使用的角度考虑，索引建立正确。

从表设计角度考虑，发现表中大量的使用了5个CLOB字段，到此困惑了半天的问题终于明白了点为什么4万记录要使用2万个数据块了。

所以为什么使用索引比全表扫描慢的原因也能推断个差不多了，一条记录存储在多个数据库块的事情在【TBNC_A】中是比较多，这样对数据库的全表扫描必须大量的读取物理数据块，谁都知道IO是最慢的操作之一，IO多了，SQL快不了。

所以综合上述，使用索引【SYS_LGM】没有错，只不过【TBNC_A】表的【TBNC_A】索引没有被使用，导致整个SQL性能急剧下降。

 

到此【疑惑二】基本就能解释的通了，但还需要继续求索。

 

接-> SQL优化-同SQL不同执行计划(三)

2008-05-15早      凌蓝风