关闭

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

328人阅读 评论(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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:368415次
    • 积分:6647
    • 等级:
    • 排名:第3713名
    • 原创:299篇
    • 转载:124篇
    • 译文:0篇
    • 评论:61条
    最新评论