SQL优化_高水位线导致的性能问题

Jun182015

作者：dbdream 发布：2015-06-18 18:22 分类：Oracle, oracle 11g 5条评论

今天晨会，开发人员说SIT数据库有一张表，之前查询很快就能返回结果，现在需要5分钟才能返回结果，需要协助查找原因并优化，数据库版本11.2.0.4 64 bit for linux，SQL语句如下：

select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;

这是很简单的一条SQL，性能下降这么大，肯定是有问题的，问了开发人员，他们说这张表并不存在DELETE操作，那么就应该不是碎片的问题导致的。查询这张表上的索引如下：

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
IX_08_INVC_ID                  INVC_ID
IX_TB8_ON                      ORD_NO
IX_TF008IED                    IF_ETR_DATE
IX_XXXX_EDIFLAG               EDIFLAG
PK_IF_008                      ORD_ID

索引还没少建，看看SQL的执行计划和统计信息。

SQL> set autotrace TRACEONLY 
SQL> set lines 200 pages 200
SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;

no rows selected

Elapsed: 00:04:12.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2394103272

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     3 |   645 |   120K  (1)| 00:24:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TB_XXXX |     3 |   645 |   120K  (1)| 00:24:01 |
-------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
              AND "EDIFLAG"<>'99')

Statistics
----------------------------------------------------------
       1795  recursive calls
          0  db block gets
     442185  consistent gets
     149261  physical reads
          0  redo size
       3779  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         39  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL运行一次需要的物理读442185个数据块，块大小设置8K，也就是需要物理读3 .4GB，外带逻辑读149261个数据块，约等于1.1GB，查看一下这个表的大小和数据量。

SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';

BYTES/1024/1024/1024
--------------------
               3.375
SQL> select count(*) from TB_XXXX;

  COUNT(*)
----------
    669387

算一下平均每条记录奖金5.3MB，每条记录5M是什么概念？表中难道存在大字段？查询结果如下：

SQL> desc TB_XXXX                        
 Name                                                          Null?    Type
 ------------------------------------------------------------- -------- ----------------
 ORD_ID                                                        NOT NULL VARCHAR2(30)
 SITE_NO                                                                VARCHAR2(7)
 OUTGO_CMD_DATE                                                         DATE
 DLVER_CD                                                               VARCHAR2(3)
 CHG_CD                                                                 VARCHAR2(3)
 ORD_LVL_CD                                                             VARCHAR2(3)
 DLV_RQST_DATE                                                          DATE
 RTN_ID                                                                 NUMBER(12)
 COD_YN                                                                 VARCHAR2(1)
 CUST_ID                                                                NUMBER(12)
 INVC_RCVER_NM                                                          VARCHAR2(20)
 INVC_TELD                                                              VARCHAR2(20)
 INVC_HP_TELD                                                           VARCHAR2(12)
 INVC_ZIP_NO                                                            VARCHAR2(6)
 INVC_ADDR_LRGN                                                         VARCHAR2(30)
 INVC_ADDR_MRGN                                                         VARCHAR2(30)
 INVC_ADDR_SRGN                                                         VARCHAR2(30)
 INVC_ADDR_DTL                                                          VARCHAR2(200)
 PURCH_CANCEL_NOTICE                                                    VARCHAR2(40)
 PRSNT_MSG                                                              VARCHAR2(60)
 INVC_MSG                                                               VARCHAR2(200)
 COD_RCV_AMT                                                   NOT NULL NUMBER(15,2)
 RCPT_GB                                                       NOT NULL VARCHAR2(3)
 RCPT_SO_ID                                                             NUMBER(7)
 RCPT_RCVER_NM                                                          VARCHAR2(100)
 RCPT_ADDR                                                              VARCHAR2(200)
 IF_ETR_DATE                                                            DATE
 IF_MDF_DATE                                                            DATE
 IF_RESULT_DATE                                                         DATE
 EDIFLAG                                                                VARCHAR2(2)
 IF_RESULTS                                                             VARCHAR2(200)
 PVC_ID                                                                 NUMBER(7)
 LOCAL_ID                                                               NUMBER(7)
 COUTY_ID                                                               NUMBER(7)
 MEDI_LCLSS_ID                                                          NUMBER(7)
 CONTACT_2                                                              VARCHAR2(120)
 ORD_AMT                                                                NUMBER(15,2)
 BORD_ID                                                                VARCHAR2(40)
 ORD_NO                                                                 VARCHAR2(20)
 PAY_TYPE_CD                                                            VARCHAR2(2)
 ADDR_T                                                                 NUMBER(1)
 INCLU_VALUABLES                                                        NUMBER(1)
 BL_ORDER_NO_OLD                                                        VARCHAR2(40)
 CASES_ID                                                               NUMBER(11)
 COD_FLAG                                                               VARCHAR2(2)
 CASH                                                                   NUMBER(15,2)
 ARCHIVE_FLAG                                                           NUMBER(2)
 CHNL_ID                                                                VARCHAR2(2)
 BONDED_AREA                                                            VARCHAR2(8)
 INVC_ID                                                                VARCHAR2(30)
 ADDR_ID                                                                NUMBER(11)
 SN_GUID                                                                VARCHAR2(40)

所有字段加起来，一条记录也不到1KB呀，到这里就可以断定就是高水位导致的问题。由于是SIT环境，比较随意，回收下这张表的碎片，看看能回收多少空间。

SQL> alter table TB_XXXX move;

Table altered.
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';

BYTES/1024/1024/1024
--------------------
          .248046875

回收完碎片，这张表才250MB，碎片硬把表撑大近15倍，MOVE完之后，索引全部失效，需要rebuild。

SQL> select index_name,status from user_indexes where table_name='TB_XXXX';

INDEX_NAME                     STATUS
------------------------------ --------
IX_08_INVC_ID                  UNUSABLE
IX_TB8_ON                      UNUSABLE
IX_TF008IED                    UNUSABLE
IX_XXXX_EDIFLAG                UNUSABLE
PK_IF_008                      UNUSABLE

索引重建之后，也缩小不少，不过这一堆索引这个SQL都用不上。

SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';

BYTES/1024/1024/1024
--------------------
           .46484375
SQL> alter index IX_TB8_ON rebuild;

Index altered.
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';

BYTES/1024/1024/1024
--------------------
          .016601563
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';

BYTES/1024/1024/1024
--------------------
           .25390625
SQL> alter index IX_TF008IED rebuild;

Index altered.
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';

BYTES/1024/1024/1024
--------------------
          .014648438
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';

BYTES/1024/1024/1024
--------------------
          .018554688
SQL> alter index IX_IF008_EDIFLAG rebuild;

Index altered.
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';

BYTES/1024/1024/1024
--------------------
          .010742188
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_008';

BYTES/1024/1024/1024
--------------------
          .569335938
SQL> alter index PK_IF_XXXX rebuild;

Index altered.
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_XXXX';

BYTES/1024/1024/1024
--------------------
          .014648438

回收碎片后，这个SQL运行只需要0.12秒。

SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ; 

no rows selected

Elapsed: 00:00:00.12

再看下执行计划和统计信息。

SQL> set autotrace traceonly
SQL> /

no rows selected

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2394103272

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     3 |   645 |   120K  (1)| 00:24:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TB_XXXX  |     3 |   645 |   120K  (1)| 00:24:01 |
-------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
              AND "EDIFLAG"<>'99')

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

下面在看下这张表的数据分布，看看哪个列适合建索引。

SQL> select distinct BONDED_AREA from TB_XXXX ;

BONDED_A
--------
1

SQL> select distinct EDIFLAG from TB_XXXX ;

ED
--
10
90

SQL> select distinct SITE_NO  from TB_XXXX ;

SITE_NO
-------
C10
C06
C81
C99
C05
C07
C01
C03
C04

9 rows selected.

SQL> select count(SITE_NO) from TB_XXXX where SITE_NO='C07';

COUNT(SITE_NO)
--------------
         40674

针对这条SQL，SITE_NO列创建索引最为有效，其他列上的索引都不会被这条SQL用到。

SQL> create index ix_SITE_NO on TB_XXXX (SITE_NO);

Index created.

SQL> set autotrace on
SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;

no rows selected

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 272980480

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     3 |   645 | 11802   (1)| 00:02:22 |
|*  1 |  COUNT STOPKEY               |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TB_XXXX   |     3 |   645 | 11802   (1)| 00:02:22 |
|*  3 |    INDEX RANGE SCAN          | IX_SITE_NO | 41680 |       |    91   (2)| 00:00:02 |
-------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   2 - filter("EDIFLAG"<>'90' AND "BONDED_AREA"='1' AND "EDIFLAG"<>'99')
   3 - access("SITE_NO"='C07')

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

在SITE_NO列上创建索引后，SQL运行时间下降到0.07秒。针对这条SQL,最理想的索引是在ITE_NO列和EDIFLAG列上创建BITMAP位图索引,或者创建在EDIFLAG列上做压缩的SITE_NO列和EDIFLAG列的复合索引,下面测试下效果。

下面在创建在SITE_NO列上的压缩索引,看看是否还有提升的空间。

SQL> drop index IX_SITE_NO;

Index dropped.

Elapsed: 00:00:00.26

SQL> create index IX_SITE_NO on TB_IF008 (SITE_NO,EDIFLAG) compress 1;

Index created.

Elapsed: 00:00:05.46
SQL>  select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;

no rows selected

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 272980480

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     3 |   645 |    86   (2)| 00:00:02 |
|*  1 |  COUNT STOPKEY               |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TB_XXXX   |     3 |   645 |    86   (2)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IX_SITE_NO |     3 |       |    85   (2)| 00:00:02 |
-------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   2 - filter("BONDED_AREA"='1')
   3 - access("SITE_NO"='C07')
       filter("EDIFLAG"<>'90' AND "EDIFLAG"<>'99')

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

可以看到,索引压缩后各项性能指标均降低,SQL运行时间也降为0.02秒,再看看BITMAP位图索引。

SQL> drop index IX_SITE_NO;

Index dropped.

Elapsed: 00:00:00.53
SQL> create bitmap index ix_SITE_NO on TB_XXXX (SITE_NO,EDIFLAG);

Index created.

Elapsed: 00:00:00.69
SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 25286296

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     3 |   645 | 46359   (1)| 00:09:17 |
|*  1 |  COUNT STOPKEY                |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID | TB_XXXX   |     3 |   645 | 46359   (1)| 00:09:17 |
|   3 |    BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|*  4 |     BITMAP INDEX RANGE SCAN   | IX_SITE_NO |       |       |            |          |
--------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)
   2 - filter("BONDED_AREA"='1')
   4 - access("SITE_NO"='C07')
       filter("EDIFLAG"<>'90' AND "EDIFLAG"<>'99' AND "SITE_NO"='C07')

Statistics
----------------------------------------------------------
        432  recursive calls
          0  db block gets
        118  consistent gets
         11  physical reads
          0  redo size
       3779  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
          0  rows processed

可是呢，这数据库是OLTP系统，位图索引DML操作锁粒度太大，并不适合使用位图索引，最终定为压缩索引。

本文固定链接: https://www.dbdream.com.cn/2015/06/sql%e4%bc%98%e5%8c%96_%e9%ab%98%e6%b0%b4%e4%bd%8d%e7%ba%bf%e5%af%bc%e8%87%b4%e7%9a%84%e6%80%a7%e8%83%bd%e9%97%ae%e9%a2%98/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2015年06月18日发表在 Oracle, oracle 11g 分类下，你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: SQL优化_高水位线导致的性能问题 | 信春哥，系统稳，闭眼上线不回滚！
关键字: autotrace, ORACLE, 优化, 碎片, 高水位线

【上一篇】linux scp限速传输
【下一篇】使用NID修改数据库DB NAME

您可能还会对这些文章感兴趣！

SQL优化_高水位线导致的性能问题：目前有5 条留言

板凳
至尊宝:

如果正常的大表降低高水位的话用 sink是不是更好一些？

2016-08-05 10:34 [回复]
- 宋春风:
  
  这个会锁表的，而且会很慢，如果有这需求，如果考虑最小影响业务的话，我认为在线重定义比较好。
  
  2016-08-05 11:12 [回复]
沙发
顺其自然:

这张表这么高的水位线是什么导致的呢

2016-09-28 11:29 [回复]
- 宋春风:
  
  高水位线基本都是DELETE操作太频繁，导致的，如果开启了数据文件的自动扩展，DELETE操作频繁就会出现，数据文件很大，但数据量远远小于数据文件大小的情况，delete删除数据的空间虽然被标记为可再用空间，一般数据文件能扩展或者有可用空间的情况下，基本不会用这部分可用空间的，这样，水位线就会越来越高。
  
  2016-10-12 16:19 [回复]
  - 顺其自然:
    
    上面的实例你的描述是基本没有delete操作呢
    
    2016-10-18 13:47 [回复]

SQL优化_高水位线导致的性能问题

您可能还会对这些文章感兴趣！

SQL优化_高水位线导致的性能问题：目前有5 条留言

发表评论

最新日志热评日志随机日志

最活跃的读者

标签云集

分类目录

文章归档

博客统计

用户登录