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

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

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

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

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

01SQL> set autotrace TRACEONLY
02SQL> set lines 200 pages 200
03SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
04 
05no rows selected
06 
07Elapsed: 00:04:12.31
08 
09Execution Plan
10----------------------------------------------------------
11Plan 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 
21Predicate 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 
28Statistics
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，查看一下这个表的大小和数据量。

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

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

01SQL> 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环境，比较随意，回收下这张表的碎片，看看能回收多少空间。

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

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

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

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

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

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

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

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

01SQL> set autotrace traceonly
02SQL> /
03 
04no rows selected
05 
06Elapsed: 00:00:00.12
07 
08Execution Plan
09----------------------------------------------------------
10Plan 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 
20Predicate 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 
27Statistics
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

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

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

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

01SQL> create index ix_SITE_NO on TB_XXXX (SITE_NO);
02 
03Index created.
04 
05SQL> set autotrace on
06SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
07 
08no rows selected
09 
10Elapsed: 00:00:00.07
11 
12Execution Plan
13----------------------------------------------------------
14Plan 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 
25Predicate 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 
32Statistics
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列上的压缩索引,看看是否还有提升的空间。

01SQL> drop index IX_SITE_NO;
02 
03Index dropped.
04 
05Elapsed: 00:00:00.26
06 
07SQL> create index IX_SITE_NO on TB_IF008 (SITE_NO,EDIFLAG) compress 1;
08 
09Index created.
10 
11Elapsed: 00:00:05.46
12SQL>  select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
13 
14no rows selected
15 
16Elapsed: 00:00:00.02
17 
18Execution Plan
19----------------------------------------------------------
20Plan 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 
31Predicate 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 
39Statistics
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位图索引。

01SQL> drop index IX_SITE_NO;
02 
03Index dropped.
04 
05Elapsed: 00:00:00.53
06SQL> create bitmap index ix_SITE_NO on TB_XXXX (SITE_NO,EDIFLAG);
07 
08Index created.
09 
10Elapsed: 00:00:00.69
11SQL> select * FROM TB_XXXX a where EDIFLAG<>'90' and EDIFLAG<>'99' and SITE_NO='C07' and rownum <=100 and BONDED_AREA='1' ;
12 
13no rows selected
14 
15Elapsed: 00:00:00.01
16 
17Execution Plan
18----------------------------------------------------------
19Plan 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 
31Predicate 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 
39Statistics
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操作锁粒度太大，并不适合使用位图索引，最终定为压缩索引。

Logo
Linux

更多推荐

网卡速率和双工模式的配置

http://linux.chinaitlab.com/system/792187.html1、mii-tool 配置网络设备协商方式的工具；    1.1 mii-tool 介绍；    mii-tool - view, manipulate media-independent interface status （mii-tool 是查看，管理介质的网络接口的状态）

avatar Linux

Linux虚拟文件系统之文件系统卸载（sys_umount())

Linux中卸载文件系统由umount系统调用实现，入口函数为sys_umount()。较于文件系统的安装较为简单，下面是具体的实现。1. /*sys_umont系统调用*/2. SYSCALL_DEFINE2(umount, char __user *, name, int, flags)3. {4.struct path path;

avatar Linux

Linux系统下超级终端Minicom的使用方法（例如：连接交换机，路由器等）转http://baike.baidu.com/view/2911642.htm?fr=ala0_1

<br />　　Linux系统下超级终端Minicom的使用方法 <br />　　Linux下的Minicom的功能与下的超级终端功能相似，适于在通过超级终端对设备的管理以及对嵌入操作系统的升级，现写出Minicom的使用手册： <br />　　1． 启动minicom <br />　　以root权限登录系统 <br />　　使用命令 <br />　　minicom –s 则minicom启动，屏

avatar Linux