sql优化思路分析-2

在生产中，绝大部分性能问题是由差sql或错误的执行计划导致。如何排查，如何优化呢？记录几个排查思路

• 差sql
  首先，一个差的sql再如何优化，也是有限度的。比如遇到过一个几十G的表中以下语句：

select * from table_name where c1 like '%ssss%';
复制

模糊查询无论是否有索引也不会走索引的。执行计划全表扫要3小时才能完成。改写示例：

SQL> select object_name from t1 where object_name like '%AUDIT_POL_ROLE%';

OBJECT_NAME
--------------------------------------------------------------------------------
KU$_AUDIT_POL_ROLE_T
KU$_AUDIT_POL_ROLE_LIST_T

SQL> select object_name from t1 where substr(object_name,5,14) ='AUDIT_POL_ROLE';


OBJECT_NAME
--------------------------------------------------------------------------------
KU$_AUDIT_POL_ROLE_T
KU$_AUDIT_POL_ROLE_LIST_T
然后基于substr(object_name,5,14)建立索引。
复制

• 统计信息不准
  sql一直跑的很好，突然变慢了，说明sql本身没有大问题，极大部分原因时错误的统计信息走了错误的执行计划。尤其在数据量变化非常大的业务情况下。比如日表月表定期批量dml。
  首先查看统计信息与执行计划情况：

select owner,
       table_name,
       partition_name,
       stale_stats,      
       last_analyzed
  from dba_tab_statistics
 where table_name = upper('&table_name')；
--STALE是YES，说明统计信息过期了。或者通过以下语句统计上次收集统计信息后到现在表的dml量级。
select t.table_owner,t.table_name,sum(deletes),sum(updates) from dba_tab_modifications t where t.table_name='xxx' group by table_owner,table_name;
复制

如果发生统计信息不准，执行计划变更，可以通过手工收集统计信息。可以加上no_invalidate=>false控制之后sql生效，否则已存在的执行计划默认5小时后才会使用新的统计信息。

• 执行计划
  错误的执行计划原因太多，这里总结一些日常查询的思路

1、查询执行计划是否有过变化，如果是统计信息不准可以参考上述统计信息排查收集统计信息
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan where SQL_ID = '&SQL_ID' order by TIMESTAMP;
2、很多时候并不能很快定位到执行计划有问题的部分，尤其是特别长sql，执行计划也很长，光看就头大。可以先定位sql耗时异常，比如消耗在cpu/IO/buffer那个环节。
SQL> select sum(ELAPSED_TIME)/sum(EXECUTIONS) avg_time,sum(CPU_TIME)/sum(EXECUTIONS) avg_cputime,sum(DISK_READS)/sum(EXECUTIONS) avg_diskio,sum(BUFFER_GETS)/sum(EXECUTIONS) avg_bufferio from v$sql where sql_id='8vr9hwjn1zzgp';

  AVG_TIME AVG_CPUTIME AVG_DISKIO AVG_BUFFERIO
---------- ----------- ---------- ------------
      1245         957          0          7.5
这些数据无论是v$动态视图还是dba_hist历史数据中都能查到。示例中可以看到sql平均执行时间为1245微秒，cpu时间占用了大部分时间957微秒。
cpu time 占用时间：重点关注cpu代价突增的部分，比如错误的连接计算方式；
Phy   io 占用时间：重点关注磁盘IO性能，是否有需要建立索引的，或者错误的索引及回表；
log   io 占用时间：重点关注sga相关，是否有热块，block_session等。
复制