数据库优化报告

一、    性能现状

在WIN2008服务器上，目前oracle数据库服务占用大量的IO时间，磁盘繁忙率100%，为了缓解现状，对数据库活动和SQL语句进行分析，下面是分析这个优化过程。

二、   优化分析

AWR报告分析:

分析：从AWR报告来看，数据库消耗的等待时间主要是direct path read，这种等待事件是全表扫描导致的等待，从SQL层面分析，主要集中在sqlid为7zfa0wc61scp9的SQL，占用了数据库95%的数据库时间和逻辑读，目前主要对这个SQL进行分析优化。

原SQL语句：

SELECT a.*, b.customerid

   FROM SPECIMENREPORT a, specimenhead b

where a.status =3

   and a.active ='1'

   and a.barcode = b.barcode

   and a.reportoption !=1

   andrownum<10

   and(selectcount(*)

          from specimenreportdetail h

         where h.specimenreportid = a.specimenreportid

           and h.status ='0')>0

   AND a.createdate >SYSDATE-60

unionall

SELECT c.*, d.customerid

   FROM SPECIMENREPORT c, specimenhead d, specimenheaddetail e

where c.status =3

   and c.active ='1'

   and c.barcode = d.barcode

   and c.reportoption =1

   and e.subbarcode = c.subbarcode

   and e.status >=130

   and e.status <=140

   andrownum<10

   AND c.createdate >SYSDATE–60

执行计划：

分析：可见该SQL语句，执行计划耗费COST：49753，执行时间0.624秒，分析该SQL，由于该SQL是几个大表的关联查询，我们借助ORACLE的优化建议包进行分析：

记下sql_id直接调用下面PLSQL：

Setserveroutput On

declare

tuning_taskvarchar2(30);

begin

tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 7zfa0wc61scp9);

dbms_output.put_line(tuning_task);

end;

/

任务_20008

记下该任务名'任务_84'

执行调优包：

execdbms_sqltune.execute_tuning_task('任务_84');

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

SELECTstatus FROM USER_ADVISOR_TASKS WHERE task_name = '任务_84';

查看调优报告：

SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('任务_84')FROM DUAL;

提炼优化报告具有价值的信息（其他的忽略）：

1- SQLProfile Finding (see explain plans section below)

--------------------------------------------------------

   为此语句找到了性能更好的执行计划 2。选择以下 SQL 概要文件之一进行实施。

   Recommendation (estimated benefit: 39.85%)

   ------------------------------------------

   - 考虑接受推荐的 SQL 概要文件。

    executedbms_sqltune.accept_sql_profile(task_name => '任务_84',task_owner=> 'SYS', replace => TRUE);                                       

2- IndexFinding (see explain plans section below)

--------------------------------------------------

   通过创建一个或多个索引可以改进此语句的执行计划。

   Recommendation (estimated benefit: 71.9%)

   -----------------------------------------

   - 考虑运行访问指导以改进物理方案设计或者创建推荐的索引。如果选择创建推荐的索引,请考虑删除索引

   "DRJYLIS"."IDX_SPECIMENREPORTID", 因为它是推荐的索引的前缀。

    create index DRJYLIS.IDX$$_00540001 on

DRJYLIS.SPECIMENREPORTDETAIL("SPECIMENREPORTID","STATUS");

分析：可见接收该概要文件（关于概要文件，是一种类似于统计信息的元数据，直接影响优化器对执行计划的判断）和创建索引，可提升50%以上的性能，注：如到迁移到其他环境，可只创建该索引进行优化，至于概要文件暂无需调整。

优化后观察该语句的执行计划和执行时间：

分析：可见，经过索引与概要文件的优化后，该SQL语句的COST从49753下降到23160，执行时间也由0.62下降到0.3以下，优化效果明显。

三、     优化总结

1.        本次优化主要是结合AWR报告定位性能问题的SQL语句进行优化，由于该SQL语句影响明显，占据了95%的数据库时间，故重点优化；

2.        利用调用oracle自带的sql_tunning包进行调优，发现出该语句较好的概要文件与索引优化思路；

3.        直接应用该概要文件于索引创建，从性能数据来看，数据库性能提升了一倍。

公司地址：广州市南沙区丰泽东路106号

公司官网：www.dbs-service.com

电话/微信：13926108245

QQ客服：282321952