今天晨会,开发人员说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操作,那么就应该不是碎片的问题导致的。查询这张表上的索引如下:
2
------------------------------ --------------------
索引还没少建,看看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' ;
10
----------------------------------------------------------
11
Plan hash value: 2394103272
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
-------------------------------------------------------------------------------
21
Predicate Information (identified by operation id):
22
---------------------------------------------------
24
1 - filter(ROWNUM<=100)
25
2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
29
----------------------------------------------------------
32
442185 consistent gets
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
SQL运行一次需要的物理读442185个数据块,块大小设置8K,也就是需要物理读3 .4GB,外带逻辑读149261个数据块,约等于1.1GB,查看一下这个表的大小和数据量。
01
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';
06
SQL> select count(*) from TB_XXXX;
算一下平均每条记录奖金5.3MB,每条记录5M是什么概念?表中难道存在大字段?查询结果如下:
03
------------------------------------------------------------- -------- ----------------
04
ORD_ID NOT NULL VARCHAR2(30)
09
ORD_LVL_CD VARCHAR2(3)
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)
28
RCPT_RCVER_NM VARCHAR2(100)
29
RCPT_ADDR VARCHAR2(200)
34
IF_RESULTS VARCHAR2(200)
38
MEDI_LCLSS_ID NUMBER(7)
39
CONTACT_2 VARCHAR2(120)
43
PAY_TYPE_CD VARCHAR2(2)
45
INCLU_VALUABLES NUMBER(1)
46
BL_ORDER_NO_OLD VARCHAR2(40)
50
ARCHIVE_FLAG NUMBER(2)
52
BONDED_AREA VARCHAR2(8)
所有字段加起来,一条记录也不到1KB呀,到这里就可以断定就是高水位导致的问题。由于是SIT环境,比较随意,回收下这张表的碎片,看看能回收多少空间。
1
SQL> alter table TB_XXXX move;
4
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='TB_XXXX';
回收完碎片,这张表才250MB,碎片硬把表撑大近15倍,MOVE完之后,索引全部失效,需要rebuild。
1
SQL> select index_name,status from user_indexes where table_name='TB_XXXX';
4
------------------------------ --------
8
IX_XXXX_EDIFLAG UNUSABLE
索引重建之后,也缩小不少,不过这一堆索引这个SQL都用不上。
01
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';
06
SQL> alter index IX_TB8_ON rebuild;
09
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';
14
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';
19
SQL> alter index IX_TF008IED rebuild;
22
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';
27
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';
32
SQL> alter index IX_IF008_EDIFLAG rebuild;
35
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';
40
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_008';
45
SQL> alter index PK_IF_XXXX rebuild;
48
SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_XXXX';
回收碎片后,这个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' ;
再看下执行计划和统计信息。
01
SQL> set autotrace traceonly
09
----------------------------------------------------------
10
Plan hash value: 2394103272
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
-------------------------------------------------------------------------------
20
Predicate Information (identified by operation id):
21
---------------------------------------------------
23
1 - filter(ROWNUM<=100)
24
2 - filter("EDIFLAG"<>'90' AND "SITE_NO"='C07' AND "BONDED_AREA"='1'
28
----------------------------------------------------------
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
下面在看下这张表的数据分布,看看哪个列适合建索引。
01
SQL> select distinct BONDED_AREA from TB_XXXX ;
07
SQL> select distinct EDIFLAG from TB_XXXX ;
14
SQL> select distinct SITE_NO from TB_XXXX ;
30
SQL> select count(SITE_NO) from TB_XXXX where SITE_NO='C07';
针对这条SQL,SITE_NO列创建索引最为有效,其他列上的索引都不会被这条SQL用到。
01
SQL> create index ix_SITE_NO on TB_XXXX (SITE_NO);
06
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
Plan hash value: 272980480
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
-------------------------------------------------------------------------------------------
25
Predicate Information (identified by operation id):
26
---------------------------------------------------
28
1 - filter(ROWNUM<=100)
29
2 - filter("EDIFLAG"<>'90' AND "BONDED_AREA"='1' AND "EDIFLAG"<>'99')
30
3 - access("SITE_NO"='C07')
33
----------------------------------------------------------
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
在SITE_NO列上创建索引后,SQL运行时间下降到0.07秒。针对这条SQL,最理想的索引是在ITE_NO列和EDIFLAG列上创建BITMAP位图索引,或者创建在EDIFLAG列上做压缩的SITE_NO列和EDIFLAG列的复合索引,下面测试下效果。
下面在创建在SITE_NO列上的压缩索引,看看是否还有提升的空间。
01
SQL> drop index IX_SITE_NO;
07
SQL> create index IX_SITE_NO on TB_IF008 (SITE_NO,EDIFLAG) compress 1;
12
SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
19
----------------------------------------------------------
20
Plan hash value: 272980480
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
-------------------------------------------------------------------------------------------
31
Predicate Information (identified by operation id):
32
---------------------------------------------------
34
1 - filter(ROWNUM<=100)
35
2 - filter("BONDED_AREA"='1')
36
3 - access("SITE_NO"='C07')
37
filter("EDIFLAG"<>'90' AND "EDIFLAG"<>'99')
40
----------------------------------------------------------
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
可以看到,索引压缩后各项性能指标均降低,SQL运行时间也降为0.02秒,再看看BITMAP位图索引。
01
SQL> drop index IX_SITE_NO;
06
SQL> create bitmap index ix_SITE_NO on TB_XXXX (SITE_NO,EDIFLAG);
11
SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
18
----------------------------------------------------------
19
Plan hash value: 25286296
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
--------------------------------------------------------------------------------------------
31
Predicate Information (identified by operation id):
32
---------------------------------------------------
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')
40
----------------------------------------------------------
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
可是呢,这数据库是OLTP系统,位图索引DML操作锁粒度太大,并不适合使用位图索引,最终定为压缩索引。