Oracle性能优化2- 依据场景选择技术

1.索引的坏处

  索引可以加快查询效率，但是使用不当，会造成插入性能很低

drop table test1 purge;
drop table test2 purge;
drop table test3 purge;
drop table t purge;
create table t as select * from dba_objects;
create table test1 as select * from t;
create table test2 as select * from t;
create table test3 as select * from t;
create index idx_owner on test1(owner);
create index idx_object_name on test1(object_name);
create index idx_data_obj_id on test1(data_object_id);
create index idx_created on test1(created);
create index idx_last_ddl_time on test1(last_ddl_time);
create index idx_status on test1(status);
create index idx_t2_sta on test2(status);
create index idx_t2_objid on test2(object_id);
set timing on 
--语句1(test1表有6个索引）
insert into test1 select * from t;
commit;
--语句2(test2表有2个索引）
insert into test2 select * from t;
commit;
--语句3(test3表有无索引）
insert into test3 select * from t;
commit;


一次与出账相关的小故事
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
--请从这里开始注意累加的时间（从建索引到插入记录完毕）
set timing on 
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);

--语句1(t表有6个索引）
insert into t select * from t;
commit;
--以下进行试验2
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
---也从这里开始这里开始注意累加的时间（从插入记录完毕到建索引完毕）
set timing on 
--语句1(t表有6个索引,此时先不建）
insert into t select * from t;
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);

2索引与排序

set linesize 266
drop table t purge;
create table t as select * from dba_objects;
select t1.name, t1.STATISTIC#, t2.VALUE
  from v$statname t1, v$mystat t2
 where t1.STATISTIC# = t2.STATISTIC#
   and t1.name like '%sort%';

create index idx_object_id on t(object_id);

select t1.name, t1.STATISTIC#, t2.VALUE
  from v$statname t1, v$mystat t2
 where t1.STATISTIC# = t2.STATISTIC#
   and t1.name like '%sort%';
 

3.分区表使用不当

  在分区表中一定要加上分区条件，否则有可能会更慢

drop table part_tab purge;
create table part_tab (id int,col2 int,col3 int)
        partition by range (id)
        (
        partition p1 values less than (10000),
        partition p2 values less than (20000),
        partition p3 values less than (30000),
        partition p4 values less than (40000),
        partition p5 values less than (50000),
        partition p6 values less than (60000),
        partition p7 values less than (70000),
        partition p8 values less than (80000),
        partition p9 values less than (90000),
        partition p10 values less than (100000),
        partition p11 values less than (maxvalue)
        );  
insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_par_tab_col2 on part_tab(col2) local;
create  index idx_par_tab_col3 on part_tab(col3) ;

drop table norm_tab purge;
create table norm_tab  (id int,col2 int,col3 int);
insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;
commit;
create  index idx_nor_tab_col2 on norm_tab(col2) ;
create  index idx_nor_tab_col3 on norm_tab(col3) ;

set autotrace traceonly statistics
set linesize 1000
set timing on 
select * from part_tab where col2=8 ;
select * from norm_tab where col2=8 ;
select * from part_tab where col2=8 and id=2;
select * from norm_tab where col2=8 and id=2;

--查看索引高度等信息
select index_name,
          blevel,
          leaf_blocks,
          num_rows,
          distinct_keys,
          clustering_factor
     from user_ind_statistics
    where table_name in( 'NORM_TAB');
    
select index_name,
          blevel,
          leaf_blocks,
          num_rows,
          distinct_keys,
          clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%';
select * from part_tab where col3=8 ;

4.各种索引性能比较

a.最慢速度(无索引）
drop table t purge;
create table t as  select * from dba_objects;
alter table T modify OBJECT_NAME not null;
select count(*) from t;
set autotrace traceonly
set linesize 1000
set timing on 
select COUNT(*) FROM T; 

b.快了一点（有普通索引）

drop table t purge;
create table t as  select * from dba_objects;
alter table T modify OBJECT_NAME not null;
create  index idx_object_name on t(object_name);
set autotrace traceonly
set timing on 
select count(*) from t;

c.又快一点（有了一个合适的位图索引）

 位图索引占用空间很小
drop table t purge;
create table t as  select * from dba_objects;
 Update t  Set object_name='abc'; 
 Update t Set object_name='evf' Where rownum<=20000;
create bitmap index idx_object_name on t(object_name);
set autotrace traceonly
set timing on
select count(*) from t;

注：如果记录数不重复或者说重复度很低，ORACLE会选择全表扫描，如果用
来强制，可以发现性能很低下。
alter session set statistics_level=all ;
set linesize 1000
set pagesize 1
select /*+index(t,idx_object_name)*/ count(*) from test t;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

d.再快一点（物化视图，注意使用的场景）
实时性要求不高
drop materialized view MV_COUNT_T;
drop table t purge;
create table t as  select * from dba_objects;
 Update t  Set object_name='abc'; 
 Update t Set object_name='evf' Where rownum<=20000;

create  materialized view  mv_count_t
                    build immediate
                    refresh on commit
                    enable query rewrite
                    as
                    select count(*) FROM T;
set autotrace traceonly
set linesize 1000
select COUNT(*) FROM T; 

e.又再快一点（缓存结果集，也是要注意使用的场景）
drop table t purge;
create table t as  select * from dba_objects;
select count(*) from t;
set linesize 1000
set autotrace traceonly
select /*+ result_cache */ count(*) from t;

f.速度之王来咯！（原来需求才是王道）
select count(*) from t where rownum=1;