关闭

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

615人阅读 评论(0) 收藏 举报
分类:

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

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

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

1 INDEX_NAME                     COLUMN_NAME
2 ------------------------------ --------------------
3 IX_08_INVC_ID                  INVC_ID
4 IX_TB8_ON                      ORD_NO
5 IX_TF008IED                    IF_ETR_DATE
6 IX_XXXX_EDIFLAG               EDIFLAG
7 PK_IF_008                      ORD_ID

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

01 SQL> set autotrace TRACEONLY
02 SQL> set lines 200 pages 200
03 SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
04  
05 no rows selected
06  
07 Elapsed: 00:04:12.31
08  
09 Execution Plan
10 ----------------------------------------------------------
11 Plan hash value: 2394103272
12  
13 -------------------------------------------------------------------------------
14 | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
15 -------------------------------------------------------------------------------
16 |   0 | SELECT STATEMENT   |          |     3 |   645 |   120K  (1)| 00:24:01 |
17 |*  1 |  COUNT STOPKEY     |          |       |       |            |          |
18 |*  2 |   TABLE ACCESS FULL| TB_XXXX |     3 |   645 |   120K  (1)| 00:24:01 |
19 -------------------------------------------------------------------------------
20  
21 Predicate Information (identified by operation id):
22 ---------------------------------------------------
23  
24    1 - filter(ROWNUM<=100)
25    2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
26               AND "EDIFLAG"<>'99')
27  
28 Statistics
29 ----------------------------------------------------------
30        1795  recursive calls
31           0  db block gets
32      442185  consistent gets
33      149261  physical reads
34           0  redo size
35        3779  bytes sent via SQL*Net to client
36         481  bytes received via SQL*Net from client
37           1  SQL*Net roundtrips to/from client
38          39  sorts (memory)
39           0  sorts (disk)
40           0  rows processed

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

01 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';
02  
03 BYTES/1024/1024/1024
04 --------------------
05                3.375
06 SQL> select count(*) from TB_XXXX;
07  
08   COUNT(*)
09 ----------
10     669387

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

01 SQL> desc TB_XXXX                       
02  Name                                                          Null?    Type
03  ------------------------------------------------------------- -------- ----------------
04  ORD_ID                                                        NOT NULL VARCHAR2(30)
05  SITE_NO                                                                VARCHAR2(7)
06  OUTGO_CMD_DATE                                                         DATE
07  DLVER_CD                                                               VARCHAR2(3)
08  CHG_CD                                                                 VARCHAR2(3)
09  ORD_LVL_CD                                                             VARCHAR2(3)
10  DLV_RQST_DATE                                                          DATE
11  RTN_ID                                                                 NUMBER(12)
12  COD_YN                                                                 VARCHAR2(1)
13  CUST_ID                                                                NUMBER(12)
14  INVC_RCVER_NM                                                          VARCHAR2(20)
15  INVC_TELD                                                              VARCHAR2(20)
16  INVC_HP_TELD                                                           VARCHAR2(12)
17  INVC_ZIP_NO                                                            VARCHAR2(6)
18  INVC_ADDR_LRGN                                                         VARCHAR2(30)
19  INVC_ADDR_MRGN                                                         VARCHAR2(30)
20  INVC_ADDR_SRGN                                                         VARCHAR2(30)
21  INVC_ADDR_DTL                                                          VARCHAR2(200)
22  PURCH_CANCEL_NOTICE                                                    VARCHAR2(40)
23  PRSNT_MSG                                                              VARCHAR2(60)
24  INVC_MSG                                                               VARCHAR2(200)
25  COD_RCV_AMT                                                   NOT NULL NUMBER(15,2)
26  RCPT_GB                                                       NOT NULL VARCHAR2(3)
27  RCPT_SO_ID                                                             NUMBER(7)
28  RCPT_RCVER_NM                                                          VARCHAR2(100)
29  RCPT_ADDR                                                              VARCHAR2(200)
30  IF_ETR_DATE                                                            DATE
31  IF_MDF_DATE                                                            DATE
32  IF_RESULT_DATE                                                         DATE
33  EDIFLAG                                                                VARCHAR2(2)
34  IF_RESULTS                                                             VARCHAR2(200)
35  PVC_ID                                                                 NUMBER(7)
36  LOCAL_ID                                                               NUMBER(7)
37  COUTY_ID                                                               NUMBER(7)
38  MEDI_LCLSS_ID                                                          NUMBER(7)
39  CONTACT_2                                                              VARCHAR2(120)
40  ORD_AMT                                                                NUMBER(15,2)
41  BORD_ID                                                                VARCHAR2(40)
42  ORD_NO                                                                 VARCHAR2(20)
43  PAY_TYPE_CD                                                            VARCHAR2(2)
44  ADDR_T                                                                 NUMBER(1)
45  INCLU_VALUABLES                                                        NUMBER(1)
46  BL_ORDER_NO_OLD                                                        VARCHAR2(40)
47  CASES_ID                                                               NUMBER(11)
48  COD_FLAG                                                               VARCHAR2(2)
49  CASH                                                                   NUMBER(15,2)
50  ARCHIVE_FLAG                                                           NUMBER(2)
51  CHNL_ID                                                                VARCHAR2(2)
52  BONDED_AREA                                                            VARCHAR2(8)
53  INVC_ID                                                                VARCHAR2(30)
54  ADDR_ID                                                                NUMBER(11)
55  SN_GUID                                                                VARCHAR2(40)

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

1 SQL> alter table TB_XXXX move;
2  
3 Table altered.
4 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';
5  
6 BYTES/1024/1024/1024
7 --------------------
8           .248046875

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

1 SQL> select index_name,status from user_indexes where table_name='TB_XXXX';
2  
3 INDEX_NAME                     STATUS
4 ------------------------------ --------
5 IX_08_INVC_ID                  UNUSABLE
6 IX_TB8_ON                      UNUSABLE
7 IX_TF008IED                    UNUSABLE
8 IX_XXXX_EDIFLAG                UNUSABLE
9 PK_IF_008                      UNUSABLE

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

01 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';
02  
03 BYTES/1024/1024/1024
04 --------------------
05            .46484375
06 SQL> alter index IX_TB8_ON rebuild;
07  
08 Index altered.
09 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';
10  
11 BYTES/1024/1024/1024
12 --------------------
13           .016601563
14 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';
15  
16 BYTES/1024/1024/1024
17 --------------------
18            .25390625
19 SQL> alter index IX_TF008IED rebuild;
20  
21 Index altered.
22 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';
23  
24 BYTES/1024/1024/1024
25 --------------------
26           .014648438
27 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';
28  
29 BYTES/1024/1024/1024
30 --------------------
31           .018554688
32 SQL> alter index IX_IF008_EDIFLAG rebuild;
33  
34 Index altered.
35 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';
36  
37 BYTES/1024/1024/1024
38 --------------------
39           .010742188
40 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_008';
41  
42 BYTES/1024/1024/1024
43 --------------------
44           .569335938
45 SQL> alter index PK_IF_XXXX rebuild;
46  
47 Index altered.
48 SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_XXXX';
49  
50 BYTES/1024/1024/1024
51 --------------------
52           .014648438

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

1 SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
2  
3 no rows selected
4  
5 Elapsed: 00:00:00.12

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

01 SQL> set autotrace traceonly
02 SQL> /
03  
04 no rows selected
05  
06 Elapsed: 00:00:00.12
07  
08 Execution Plan
09 ----------------------------------------------------------
10 Plan hash value: 2394103272
11  
12 -------------------------------------------------------------------------------
13 | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
14 -------------------------------------------------------------------------------
15 |   0 | SELECT STATEMENT   |          |     3 |   645 |   120K  (1)| 00:24:01 |
16 |*  1 |  COUNT STOPKEY     |          |       |       |            |          |
17 |*  2 |   TABLE ACCESS FULL| TB_XXXX  |     3 |   645 |   120K  (1)| 00:24:01 |
18 -------------------------------------------------------------------------------
19  
20 Predicate Information (identified by operation id):
21 ---------------------------------------------------
22  
23    1 - filter(ROWNUM<=100)
24    2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
25               AND "EDIFLAG"<>'99')
26  
27 Statistics
28 ----------------------------------------------------------
29           0  recursive calls
30           0  db block gets
31       32303  consistent gets
32           0  physical reads
33           0  redo size
34        3779  bytes sent via SQL*Net to client
35         481  bytes received via SQL*Net from client
36           1  SQL*Net roundtrips to/from client
37           0  sorts (memory)
38           0  sorts (disk)
39           0  rows processed

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

01 SQL> select distinct BONDED_AREA from TB_XXXX ;
02  
03 BONDED_A
04 --------
05 1
06  
07 SQL> select distinct EDIFLAG from TB_XXXX ;
08  
09 ED
10 --
11 10
12 90
13  
14 SQL> select distinct SITE_NO  from TB_XXXX ;
15  
16 SITE_NO
17 -------
18 C10
19 C06
20 C81
21 C99
22 C05
23 C07
24 C01
25 C03
26 C04
27  
28 9 rows selected.
29  
30 SQL> select count(SITE_NO) from TB_XXXX where SITE_NO='C07';
31  
32 COUNT(SITE_NO)
33 --------------
34          40674

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

01 SQL> create index ix_SITE_NO on TB_XXXX (SITE_NO);
02  
03 Index created.
04  
05 SQL> set autotrace on
06 SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
07  
08 no rows selected
09  
10 Elapsed: 00:00:00.07
11  
12 Execution Plan
13 ----------------------------------------------------------
14 Plan hash value: 272980480
15  
16 -------------------------------------------------------------------------------------------
17 | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
18 -------------------------------------------------------------------------------------------
19 |   0 | SELECT STATEMENT             |            |     3 |   645 | 11802   (1)| 00:02:22 |
20 |*  1 |  COUNT STOPKEY               |            |       |       |            |          |
21 |*  2 |   TABLE ACCESS BY INDEX ROWID| TB_XXXX   |     3 |   645 | 11802   (1)| 00:02:22 |
22 |*  3 |    INDEX RANGE SCAN          | IX_SITE_NO | 41680 |       |    91   (2)| 00:00:02 |
23 -------------------------------------------------------------------------------------------
24  
25 Predicate Information (identified by operation id):
26 ---------------------------------------------------
27  
28    1 - filter(ROWNUM<=100)
29    2 - filter("EDIFLAG"<>'90' AND "BONDED_AREA"='1' AND "EDIFLAG"<>'99')
30    3 - access("SITE_NO"='C07')
31  
32 Statistics
33 ----------------------------------------------------------
34           0  recursive calls
35           0  db block gets
36       22048  consistent gets
37           0  physical reads
38           0  redo size
39        3779  bytes sent via SQL*Net to client
40         481  bytes received via SQL*Net from client
41           1  SQL*Net roundtrips to/from client
42           0  sorts (memory)
43           0  sorts (disk)
44           0  rows processed

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

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

01 SQL> drop index IX_SITE_NO;
02  
03 Index dropped.
04  
05 Elapsed: 00:00:00.26
06  
07 SQL> create index IX_SITE_NO on TB_IF008 (SITE_NO,EDIFLAG) compress 1;
08  
09 Index created.
10  
11 Elapsed: 00:00:05.46
12 SQL>  select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
13  
14 no rows selected
15  
16 Elapsed: 00:00:00.02
17  
18 Execution Plan
19 ----------------------------------------------------------
20 Plan hash value: 272980480
21  
22 -------------------------------------------------------------------------------------------
23 | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
24 -------------------------------------------------------------------------------------------
25 |   0 | SELECT STATEMENT             |            |     3 |   645 |    86   (2)| 00:00:02 |
26 |*  1 |  COUNT STOPKEY               |            |       |       |            |          |
27 |*  2 |   TABLE ACCESS BY INDEX ROWID| TB_XXXX   |     3 |   645 |    86   (2)| 00:00:02 |
28 |*  3 |    INDEX RANGE SCAN          | IX_SITE_NO |     3 |       |    85   (2)| 00:00:02 |
29 -------------------------------------------------------------------------------------------
30  
31 Predicate Information (identified by operation id):
32 ---------------------------------------------------
33  
34    1 - filter(ROWNUM<=100)
35    2 - filter("BONDED_AREA"='1')
36    3 - access("SITE_NO"='C07')
37        filter("EDIFLAG"<>'90' AND "EDIFLAG"<>'99')
38  
39 Statistics
40 ----------------------------------------------------------
41         353  recursive calls
42           0  db block gets
43         160  consistent gets
44           0  physical reads
45           0  redo size
46        3779  bytes sent via SQL*Net to client
47         481  bytes received via SQL*Net from client
48           1  SQL*Net roundtrips to/from client
49          14  sorts (memory)
50           0  sorts (disk)
51           0  rows processed

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

01 SQL> drop index IX_SITE_NO;
02  
03 Index dropped.
04  
05 Elapsed: 00:00:00.53
06 SQL> create bitmap index ix_SITE_NO on TB_XXXX (SITE_NO,EDIFLAG);
07  
08 Index created.
09  
10 Elapsed: 00:00:00.69
11 SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
12  
13 no rows selected
14  
15 Elapsed: 00:00:00.01
16  
17 Execution Plan
18 ----------------------------------------------------------
19 Plan hash value: 25286296
20  
21 --------------------------------------------------------------------------------------------
22 | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
23 --------------------------------------------------------------------------------------------
24 |   0 | SELECT STATEMENT              |            |     3 |   645 | 46359   (1)| 00:09:17 |
25 |*  1 |  COUNT STOPKEY                |            |       |       |            |          |
26 |*  2 |   TABLE ACCESS BY INDEX ROWID | TB_XXXX   |     3 |   645 | 46359   (1)| 00:09:17 |
27 |   3 |    BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
28 |*  4 |     BITMAP INDEX RANGE SCAN   | IX_SITE_NO |       |       |            |          |
29 --------------------------------------------------------------------------------------------
30  
31 Predicate Information (identified by operation id):
32 ---------------------------------------------------
33  
34    1 - filter(ROWNUM<=100)
35    2 - filter("BONDED_AREA"='1')
36    4 - access("SITE_NO"='C07')
37        filter("EDIFLAG"<>'90' AND "EDIFLAG"<>'99' AND "SITE_NO"='C07')
38  
39 Statistics
40 ----------------------------------------------------------
41         432  recursive calls
42           0  db block gets
43         118  consistent gets
44          11  physical reads
45           0  redo size
46        3779  bytes sent via SQL*Net to client
47         481  bytes received via SQL*Net from client
48           1  SQL*Net roundtrips to/from client
49          14  sorts (memory)
50           0  sorts (disk)
51           0  rows processed

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

0
0
查看评论

高水位线和全表扫描

高水位线好比水库中储水的水位线,用于描述数据库中段的扩展方式。高水位线对全表扫描方式有着至关重要的影响。当使用delete 操作 表记录时,高水位线并不会下降,随之导致的是全表扫描的实际开销并没有任何减少。本文给出高水位线的描述,如何降低高水位线,以及高水 位线对全表扫描的影响。  ...
  • robinson_0612
  • robinson_0612
  • 2011-11-08 18:15
  • 8181

mysql----快速删除数据表(drop,truncate.delete)

概念:三者均可删除数据表 TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。...
  • crazyhacking
  • crazyhacking
  • 2014-03-20 17:05
  • 19201

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

今天晨会,开发人员说SIT数据库有一张表,之前查询很快就能返回结果,现在需要5分钟才能返回结果,需要协助查找原因并优化,数据库版本11.2.0.4 64 bit for linux,SQL语句如下: 1 select * FROM TB_XXXX a where EDIF...
  • anzhen0429
  • anzhen0429
  • 2017-11-26 14:24
  • 238

很精辟的oracle高水位线,终于知道DELETE和TRUNCATE为什么不一样了

一、Oracle表段中的高水位线HWM 在Oracle数据的存储中,可以把存储空间想象为一个水库,数据想象为水库中的水。水库中的水的位置有一条线叫做水位线,在Oracle中,这条线被称为高水位线(High-warter mark, HWM)。在数据库表刚建立的时候,由于没有任何数据,所以这个时候水...
  • houdi2108
  • houdi2108
  • 2014-04-17 10:48
  • 2186

Oracle表查询慢的问题(高水位)解决方案

最近工作中碰到一个问题,生产环境中某一张表查询变的好慢,而本地同样的镜像环境查询却很快,研究了半天都没有发现问题。  开会进行讨论,发现一条线索, 由于业务需求,这表中的大部分数据全部被delete删除了,只保留了2w. 因此,推断此问题很有可能就是表的高水位问题。 下面我们就来验...
  • hh652400660
  • hh652400660
  • 2014-12-17 15:33
  • 1812

Sql性能优化之UNION、UNION ALL

SQL UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。1、SQL UNION 语法SELECT column_...
  • qq_20989105
  • qq_20989105
  • 2017-09-06 17:17
  • 669

Mybatis中SQL性能优化注意事项

Mybatis SQL性能调优     1.  Mapper层参数为Map,由Service层负责重载       Mapper由于机制的问题,不能重载,参数一般设置成M...
  • liuwenyu01
  • liuwenyu01
  • 2017-08-31 16:12
  • 412

Sql性能优化之检查问题sql的方法

Sql性能优化之检查问题sql的方法   出现查询性能问题时,首先要做的是确定问题点是什么,只有正确的找到问题后才能有针对性的解决问题。 下面简单介绍我们一般从哪些角度入手,来确定问题所在: 首先,从业务上理解该处功能,理解用户的真正意图,用户真正关注的是什么,想要的是什么数据,是...
  • u010241906
  • u010241906
  • 2014-04-28 22:38
  • 834

SQL SERVER中 性能问题

1.对查询进行优化,应尽量避免全表扫描,考虑在 where 及 order by 涉及的列上建立索引。  2.避免使用 left join 和 null 值判断。left join...
  • oqqKen12345
  • oqqKen12345
  • 2017-09-09 09:15
  • 82

性能优化总结(三)一对多join的级联查询

最近在对一个已经运行了4年的老项目进行性能优化,这是一个app,我主要还是从后台优化。这个项目后台提供的API并不多,只有十几个,但是性能非常差。有些API连每分钟2000的request都扛不住,虽然后台的并发node已经增加到8个。一看代码就知道为什么了,原来这些API都需要返回很多的数据,而且...
  • nokiaisacat
  • nokiaisacat
  • 2016-12-23 21:20
  • 814
    个人资料
    • 访问:613116次
    • 积分:9063
    • 等级:
    • 排名:第2473名
    • 原创:319篇
    • 转载:125篇
    • 译文:0篇
    • 评论:89条
    最新评论