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 早       凌 蓝风