ITPub博客

首页 > 数据库 > Oracle > oracle 一条简单sql的优化

oracle 一条简单sql的优化

原创 Oracle 作者:bestpaydata 时间:2016-05-18 18:04:12 0 删除 编辑
sql_id:5s1wx1pppquax:
select count(1) count from t_vou_XXXX voucher
                where batch_no = :1       and customer_no is not null;
执行计划:
Plan hash value: 730693288
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |       |       |  1049 (100)|          |       |       |
|   1 |  SORT AGGREGATE                     |                     |     1 |    27 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T_VOU_XXXX          |  7882 |   207K|  1049   (1)| 00:00:13 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | I_VOU_INFO_BATCH_NO | 23743 |       |   271   (1)| 00:00:04 |       |       |
---------------------------------------------------------------------------------------------------------------------------
执行信息:


表信息:
SQL> select NUM_ROWS  from dba_tables where table_name='T_VOU_XXXX';
  NUM_ROWS
   ----------
 184266247
大小 42.4G
索引信息:
batch_no,customer_no列上 各自有单独的索引
列统计信息:

执行计划分析:
该sql 首先走的是batch_no列上的索引(id=3),然后再通过rowid回表(id=2),再聚合(id=1)
从上面的信息我们可以发现 此sql使用730693288的执行计划,平均单次执行需要333s,执行效率较差。
优化思路:
从执行计划的步骤和sql内容上,我们可以发现该sql 是统计满足这2个条件的数据量,并且执行计划中有回表的操作,
至此我们就有了优化的思路,建立组合索引,避免执行计划中的回表操作。
怎么建组合索引?
该表这2个列都有独自的索引。因此需要改造下。 列的统计信息如上, 通常我们建组合索引,选择性好的放在前面(可以避免index skip scan),因此按照常规的我们的索引列顺序为 ('CUSTOMER_NO','BATCH_NO'),但是如果这样建索引,对于我们的这个sql优化,没有用。从sql的where条件可以发现,若是走这个索引,则是(index full scan),因此这样是行不通的。故我们建索引的顺序为('BATCH_NO','CUSTOMER_NO'),并且删掉原来的batch_no上的冗余索引,因CUSTOMER_NO有单独的索引,因此index skip scan的情景就不必担心会发生。

实施步骤:

1、--估算索引大小:

4.6G    现有空间12G   估算建完后还剩余7G   使用率(85-7)/85*100%=91.7%,会触发告警因此需要加空间

set serverout on

variable used_bytes number

variable alloc_bytes number

exec dbms_space.create_index_cost('create index i_vou_info_batch_cust_no on t_vou_XXXX( batch_no ,customer_no ) tablespace TBS_VOUCHER_IDX   local online parallel 16',:used_bytes,:alloc_bytes);

print :used_bytes

print :alloc_bytes

2、建立索引:

create index i_vou_info_batch_cust_no on t_vou_XXXX( batch_no ,customer_no ) tablespace TBS_VOUCHER_IDX   local online parallel 8;

alter index i_vou_info_batch_cust_no noparallel;

3、invisible原有的索引:

alter index I_VOU_INFO_BATCH_NO invisible;

4、观察是否使用新建的索引,及前后的性能变化:

Plan hash value: 1183751283
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                          |       |       |   179 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |                          |     1 |    27 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                          |  7882 |   207K|   179   (1)| 00:00:03 |     1 |1048575|
|*  3 |    INDEX RANGE SCAN  | I_VOU_INFO_BATCH_CUST_NO |  7882 |   207K|   179   (1)| 00:00:03 |     1 |1048575|
-----------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (VARCHAR2(30), CSID=871): 'B20160512112812105'
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("BATCH_NO"=:1)
       filter("CUSTOMER_NO" IS NOT NULL)
优化后的sql,执行时间由原来的平均每次332s,到优化有的平均每次不超过2s,优化后的sql执行性能有明显的提高。

5、删除原有的索引:

drop index I_VOU_INFO_BATCH_NO;

---gt


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-2102641/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985898