Oracle SQL优化 --- 索引、分区、SQL改写

 问题发现

      日常巡检中发现一条SQL运行次数多，且单次运行时间超过5秒

select a.*
  from NP_TASK_INSTANCE_DOC a, np_task b
 where a.TASKID = b.taskid
   and b.usestatus = 1
   and (a.RUNSTATUS = 0 or a.RUNSTATUS = 1 and a.RUNTIME < sysdate - 1 / 24)

运行结果如下： 

SQL执行计划

问题：“table access full” 导致cost很高，执行效率低下

初步优化

解决方案：NP_TASK_INSTANCE_DOC的“RUNSTATUS”创建索引

create index NP_TASK_INSTANCE_DOC_IDX2 on NP_TASK_INSTANCE_DOC (RUNSTATUS)
  tablespace OCEAN_INDEX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

创建索引后的执行计划

 执行效率提高了339倍，由原来的339秒提高到了1秒执行完成。SQL开销也有原来的28243减少到了44。

深入优化

执行计划的cost虽然减少了，可是rows和buffer都增长了许多。NP_TASK_INSTANCE_DOC有17590335行数据，那么我们来根据runtime创建分区表。

--- 原表更名

alter table NP_TASK_INSTANCE_DOC rename to NP_TASK_INSTANCE_DOC_old;

--- 创建分区表

create table NP_TASK_INSTANCE_DOC
(
  instanceid  NUMBER(11) not null,
  taskid      NUMBER(11),
  serviceid   NUMBER(11),
  runtime     DATE,
  realruntime DATE,
  runstatus   NUMBER(1),
  finishtime  DATE
)
tablespace TASLY20190416
PARTITION BY RANGE (runtime)
(
    PARTITION p1 VALUES LESS THAN (TO_DATE('2019-01-01', 'yyyy-mm-dd')) ,
    PARTITION p2 VALUES LESS THAN (TO_DATE('2019-02-01', 'yyyy-mm-dd')) ,
    PARTITION p3 VALUES LESS THAN (TO_DATE('2019-03-01', 'yyyy-mm-dd')) ,
    PARTITION p4 VALUES LESS THAN (TO_DATE('2019-04-01', 'yyyy-mm-dd')) ,
    PARTITION p5 VALUES LESS THAN (TO_DATE('2019-05-01', 'yyyy-mm-dd')) ,
    PARTITION p6 VALUES LESS THAN (TO_DATE('2019-06-01', 'yyyy-mm-dd')) ,
    partition p7 values less than (maxvalue)
);

--- 插入数据

insert into NP_TASK_INSTANCE_DOC(instanceid,taskid,serviceid,runtime,realruntime,runstatus,finishtime) select instanceid,taskid,serviceid,runtime,realruntime,runstatus,finishtime from NP_TASK_INSTANCE_DOC_old;

commit;

--- 查看分区信息

select t.table_name,t.partition_name,t.num_rows,t.last_analyzed from dba_tab_partitions t where t.table_name = 'NP_TASK_INSTANCE_DOC';

--- 收集统计信息

exec dbms_stats.gather_table_stats(ownname => 'TASLY20190416',tabname => 'NP_TASK_INSTANCE_DOC',estimate_percent => 10,method_opt=> 'for all indexed columns');

分区表改造完成。

那么我们再来看看SQL的执行计划

执行计划的cost值由44下降至17，rows和buffer也都有显著下降。至此针对于数据结构的优化已经完成了。

SQL逻辑优化

上述执行计划中，同样的操作执行了两次。我们可以看到predicate information中的2-5和6-9执行计划完全一致，唯一不同的是5和9，分别是runstatus=0和runstatus=1（匹配条件“a.runstatus=0 or a.runstatus=1”）。当然这不是我们想要的执行过程。

SQL语义分析：SQL中or的执行优先级低于and，那么结果集是runstatus=1并且一小时以内的数据，并且获得runstatus=0的数据。

原语句中"a.RUNSTATUS = 0 or a.RUNSTATUS = 1 and a.RUNTIME < sysdate - 1 / 24"条件or左边结果为真则右边条件遍不执行，导致查询结果偏差。

select a.*
  from ((select *
           from np_task_instance_doc
          where runstatus = 1
            and (runtime > sysdate - 1 / 24 and runtime < sysdate)) union
        (select * from np_task_instance_doc where runstatus = 0)) a,
       (select * from np_task where usestatus = 1) b
 where b.taskid = a.taskid

新SQL的执行计划 

依据需求重新编写SQL，执行计划的cost值最低、rows值91与实际结果集的88很接近。仅buffer比原SQL多了2.5k，可以接受。