oracle_SQL优化_高水位线导致的性能问题
来源：互联网 发布：全职高手烽火知韩txt 编辑：程序博客网 时间：2024/06/05 18:14
今天晨会，开发人员说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_NAME2------------------------------ --------------------3IX_08_INVC_ID INVC_ID4IX_TB8_ON ORD_NO5IX_TF008IED IF_ETR_DATE6IX_XXXX_EDIFLAG EDIFLAG7PK_IF_008 ORD_ID索引还没少建，看看SQL的执行计划和统计信息。
01SQL> set autotrace TRACEONLY02SQL> set lines 200 pages 20003SQL> 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 selected06 07Elapsed: 00:04:12.3108 09Execution Plan10----------------------------------------------------------11Plan hash value: 239410327212 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 28Statistics29----------------------------------------------------------30 1795 recursive calls31 0 db block gets32 442185 consistent gets33 149261 physical reads34 0 redo size35 3779 bytes sent via SQL*Net to client36 481 bytes received via SQL*Net from client37 1 SQL*Net roundtrips to/from client38 39 sorts (memory)39 0 sorts (disk)40 0 rows processedSQL运行一次需要的物理读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/102404--------------------05 3.37506SQL> select count(*) from TB_XXXX;07 08 COUNT(*)09----------10 669387算一下平均每条记录奖金5.3MB，每条记录5M是什么概念？表中难道存在大字段？查询结果如下：
01SQL> desc TB_XXXX 02 Name Null? Type03 ------------------------------------------------------------- -------- ----------------04 ORD_ID NOT NULL VARCHAR2(30)05 SITE_NO VARCHAR2(7)06 OUTGO_CMD_DATE DATE07 DLVER_CD VARCHAR2(3)08 CHG_CD VARCHAR2(3)09 ORD_LVL_CD VARCHAR2(3)10 DLV_RQST_DATE DATE11 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 DATE31 IF_MDF_DATE DATE32 IF_RESULT_DATE DATE33 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/10247--------------------8 .248046875回收完碎片，这张表才250MB，碎片硬把表撑大近15倍，MOVE完之后，索引全部失效，需要rebuild。
1SQL> select index_name,status from user_indexes where table_name='TB_XXXX';2 3INDEX_NAME STATUS4------------------------------ --------5IX_08_INVC_ID UNUSABLE6IX_TB8_ON UNUSABLE7IX_TF008IED UNUSABLE8IX_XXXX_EDIFLAG UNUSABLE9PK_IF_008 UNUSABLE索引重建之后，也缩小不少，不过这一堆索引这个SQL都用不上。
01SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TB8_ON';02 03BYTES/1024/1024/102404--------------------05 .4648437506SQL> 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/102412--------------------13 .01660156314SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_TF008IED';15 16BYTES/1024/1024/102417--------------------18 .2539062519SQL> 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/102425--------------------26 .01464843827SQL> select bytes/1024/1024/1024 from user_segments where segment_name='IX_IF008_EDIFLAG';28 29BYTES/1024/1024/102430--------------------31 .01855468832SQL> 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/102438--------------------39 .01074218840SQL> select bytes/1024/1024/1024 from user_segments where segment_name='PK_IF_008';41 42BYTES/1024/1024/102443--------------------44 .56933593845SQL> 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/102451--------------------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 selected4 5Elapsed: 00:00:00.12再看下执行计划和统计信息。
01SQL> set autotrace traceonly02SQL> /03 04no rows selected05 06Elapsed: 00:00:00.1207 08Execution Plan09----------------------------------------------------------10Plan hash value: 239410327211 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 27Statistics28----------------------------------------------------------29 0 recursive calls30 0 db block gets31 32303 consistent gets32 0 physical reads33 0 redo size34 3779 bytes sent via SQL*Net to client35 481 bytes received via SQL*Net from client36 1 SQL*Net roundtrips to/from client37 0 sorts (memory)38 0 sorts (disk)39 0 rows processed下面在看下这张表的数据分布，看看哪个列适合建索引。
01SQL> select distinct BONDED_AREA from TB_XXXX ;02 03BONDED_A04--------05106 07SQL> select distinct EDIFLAG from TB_XXXX ;08 09ED10--1110129013 14SQL> select distinct SITE_NO from TB_XXXX ;15 16SITE_NO17-------18C1019C0620C8121C9922C0523C0724C0125C0326C0427 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 on06SQL> 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 selected09 10Elapsed: 00:00:00.0711 12Execution Plan13----------------------------------------------------------14Plan hash value: 27298048015 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 32Statistics33----------------------------------------------------------34 0 recursive calls35 0 db block gets36 22048 consistent gets37 0 physical reads38 0 redo size39 3779 bytes sent via SQL*Net to client40 481 bytes received via SQL*Net from client41 1 SQL*Net roundtrips to/from client42 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.2606 07SQL> create index IX_SITE_NO on TB_IF008 (SITE_NO,EDIFLAG) compress 1;08 09Index created.10 11Elapsed: 00:00:05.4612SQL> 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 selected15 16Elapsed: 00:00:00.0217 18Execution Plan19----------------------------------------------------------20Plan hash value: 27298048021 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 39Statistics40----------------------------------------------------------41 353 recursive calls42 0 db block gets43 160 consistent gets44 0 physical reads45 0 redo size46 3779 bytes sent via SQL*Net to client47 481 bytes received via SQL*Net from client48 1 SQL*Net roundtrips to/from client49 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.5306SQL> create bitmap index ix_SITE_NO on TB_XXXX (SITE_NO,EDIFLAG);07 08Index created.09 10Elapsed: 00:00:00.6911SQL> 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 selected14 15Elapsed: 00:00:00.0116 17Execution Plan18----------------------------------------------------------19Plan hash value: 2528629620 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 39Statistics40----------------------------------------------------------41 432 recursive calls42 0 db block gets43 118 consistent gets44 11 physical reads45 0 redo size46 3779 bytes sent via SQL*Net to client47 481 bytes received via SQL*Net from client48 1 SQL*Net roundtrips to/from client49 14 sorts (memory)50 0 sorts (disk)51 0 rows processed可是呢，这数据库是OLTP系统，位图索引DML操作锁粒度太大，并不适合使用位图索引，最终定为压缩索引。
阅读全文
0 0
- oracle_SQL优化_高水位线导致的性能问题
- SQL优化_高水位线导致的性能问题
- Oracle 高水位线(HWM) 及性能优化
- Oracle高水位线(HWM)及性能优化[转]
- Oracle高水位线(HWM)及性能优化
- Oracle高水位线(HWM)及性能优化
- Oracle高水位线(HWM)及性能优化[转]
- Oracle高水位线(HWM)及性能优化[转]
- Oracle高水位线(HWM)及性能优化[转]
- oracle_sql性能优化
- ORACLE的高水位线
- 高水位线的压缩
- 【Oracle_SQL性能优化】系列目录
- oracle高水位，水位线
- Oracle delete高水位线处理问题
- Oracle delete高水位线处理问题
- 关于oracle中的高水位线问题
- Oracle delete高水位线处理问题
- SpringCloud项目生成Jar包执行
- 免锁容器之CopyOnWriteArrayList、ConcurrentHashMap
- 用星坐标（Star Coordinates）表示高维数据
- java学习——字符串<2>
- 【转载】Intellij IDEA win下快捷键
- oracle_SQL优化_高水位线导致的性能问题
- 嵌入式系统知识大总结
- 何以谓之“自顶向下，逐步求精”
- Effective Java: 方法的设计
- 如何使用epoll？一个完整的C例子
- 关于需求
- 苹果自爆秘籍：看从想法到实现FaceID是怎样炼成的【附下载】| 智东西内参
- nvm 的安装以及使用
- 谷歌推无人车网约车服务 已有60多家共享单车停运
