SQL优化_高水位线导致的性能问题
Jun182015
今天晨会，开发人员说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操作锁粒度太大，并不适合使用位图索引，最终定为压缩索引。
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