概述

比较建议大家每天从awr报告拿一条问题sql做优化分析，坚持一段时间肯定很有收获，下面记录一下最近做的一个优化sql过程，比较简单。

问题sql：

SQL(3bayn67swv5yn) 在采样期平均单次执行逻辑读为 2645529, 其最新执行计划涉及 3 个对象，平均单个对象的逻辑读较大。

在其最新执行计划中，发现较差的执行步骤：

9:TABLE ACCESS FULL (TABLE:RFUSER.CAR_APPLY_TRAYS_BOXES [5171.88 MB])；

1、查看具体sql:

select listagg(sql_text,' ') within group (order by piece)
 from v$sqltext
 where sql_id = '3bayn67swv5yn'
 group by sql_id

sql如下：

MERGE INTO CAR_APPLY_TRAYS_BOXES CATB
USING (SELECT TRAY_ID, LISTAGG(FAILED_REASON, ';') WITHIN
 GROUP(
 ORDER BY NULL) AS RESONS FROM (SELECT DISTINCT TRAY_ID,
 FAILED_REASON
 FROM T_CAR_APPLY_TRAYS_BOXES_FAIL) T1
 GROUP BY TRAY_ID) T2
ON (T2 .TRAY_ID = CATB.TRAY_ID)
WHEN MATCHED THEN
 UPDATE SET ISVALIDATED = -1, VALIDATEFAILEDREASON = T2.RESONS

2、查看执行计划

这里直接F5快捷键看一下执行计划，很明显 CAR_APPLY_TRAYS_BOXES表走了全表扫描。

3、获取sql相关信息

sqlplus / as sysdba @/home/oracle/sql/spoolsql.sql

生成html:

查看sql相关信息：

因为是新接触的数据库，并不是很熟，这里主要了解一下大概字段和表段大小。

4、目前索引情况

检查目前索引情况，看是不是有索引，但却没有调用到。

5、增加单列索引

--大表需加online这个参数，除了create过程中index保持online状态，Oracle还会在create index之前等待所有DML操作结束，然后得到DDL锁，开始create
create index IDX_CAR_APPLY_TRAYS_BOXES_3 on CAR_APPLY_TRAYS_BOXES(TRAY_ID) tablespace rf_indx online;
--如果不commit,上面的操作就会一直hold
commit;

//分析索引

BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => 'RFUSER',
 IndName => 'IDX_CAR_APPLY_TRAYS_BOXES_3',
 Estimate_Percent => 10,
 Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
 No_Invalidate => FALSE);
END;

6、再次查看执行计划

建完索引一定要再看一下执行计划，要不怎么知道效果有没出来。

优化后结果：全表扫描改成table access by index rowid扫描，cost由180K降为2，这里优化还是挺明显的。

大家如果有更好的优化方法，可以在下面留言一起探讨下哦，感谢！

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~