Oracle-SQL性能优化（7）-SQL优化常见手段

SQL优化其实是一项体力活，其实最主要关注三点：表的连接方式、访问路径和执行顺序。遇到问题SQL时，我们可以先使用自己经常使用的工具，比如PL/SQL 或者 TOAD进行格式化。首先确保统计信息的准确性，然后通过awrsqlrpt或dbms_xplan获取SQL的详细执行计划和资源消耗信息，重点关注FROM后面的表以及包含WHERE语句的条件。

1.创建索引

当查看执行计划发现瓶颈是全表扫描，对应列上没有索引，就可以使用我们之前的脚本结合实际情况,如连接方式、列的选择性等创建合适的索引。

--统计JASON表中列的选择性如下：
select /*+ NO_MERGE LEADING(a b) */
b.table_name,
 a.column_name,
a.num_distinct Cardinality,
b.num_rows,
 ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = 'SCOTT'
   and a.table_name = 'JASON';
--查询需要创建索引的列，查询命令如下：
select owner,
       column_name,
       num_rows,
       Cardinality,
       selectivity,
       'Need create index on column ' || column_name as suggest
  from (select /*+ NO_MERGE LEADING(a b) */
         b.owner,
         b.table_name,
         a.column_name,
         b.num_rows,
         a.num_distinct Cardinality,
         ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity
          from dba_tab_col_statistics a, dba_tables b
         where a.owner = b.owner
           and a.table_name = b.table_name
           and a.owner = 'SCOTT'
           and a.table_name = 'JASON')
 where selectivity >= 15
   and column_name not in (select column_name
                             from dba_ind_columns
                            where table_owner = 'SCOTT'
                              and table_name = 'SCOTT')
   and column_name in
       (select c.name
          from sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c
         where o.obj# = u.obj#
           and oo.user# = o.owner#
           and c.obj# = u.obj#
           and c.intcol# = u.intcol#
           and oo.name = 'SCOTT'
           and o.name = 'JASON');
复制

2.无法使用索引

有时候我们会遇到一条SQL，对应列有索引且应该使用索引，但就是没使用。在确保统计信息的情况下我们还需要关注是否有隐式转换。
在执行计划谓词信息部分有说明，如果有的话看能否改写SQL消除转换。

3.直方图

直方图导致的问题表现一般是SQL语句的执行效率不稳定，时好时坏。这时候我们可以针对该列收集直方图。

--查询表格中哪些列需要收集直方图，我们可以根据以下语句直接抓取：
select /*+ NO_MERGE LEADING(a b) */
 a.owner,
 a.table_name,
 a.column_name,
 b.num_rows,
 a.num_distinct,
 ROUND(num_distinct * 100 / num_rows, 1) selectivity,
 'Column ' || a.column_name || ' need Gather Histogram' notice
  from dba_tab_col_statistics a, dba_tables b
 where a.owner = b.owner
   and a.owner = '&1'
   and a.table_name = '&2'
   and a.table_name = b.table_name
   AND ROUND(num_distinct * 100 / num_rows, 1) < 1
   and (a.owner, a.table_name, a.column_name) in
       (select oo.name owner, o.name table_name, c.name column_name
          from sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c
         where o.obj# = u.obj#
           and oo.user# = o.owner#
           and c.obj# = u.obj#
           and c.intcol# = u.intcol#
           and oo.name = '&1'
           and o.name = '&2')
   and a.histogram = 'NONE';

复制

4.联合索引

虽然使用了索引，但还是很慢，这时候我们就需要关注返回的数据量以及是否产生了大量的回表操作。
创建适当的联合索引能够有效减少范围甚至能够避免回表。

5.大表清理

对于历史数据的清理，分区表较为简单，只需要truncate分区就可以了，但是对于一张普通的大表来说，清理历史数据就比较麻烦。传统会采用delete加批量提交的方式，但速度往往不太理想。我们可以利用函数DBMS_ROWID.ROWID_CREATE帮助我们按区进行rowid分片,模拟分区表的方式。

select A.FILE_ID,
       A.EXTENT_ID,
       A.BLOCK_ID,
       'rowid between ' || '''' || 
       dbms_rowid.rowid_create(1,
                               b.data_object_id,
                               a.relative_fno,
                               a.block_id,
                               0) || '''' || ' and ' || '''' ||
       dbms_rowid.rowid_create(1,
                               b.data_object_id,
                               a.relative_fno,
                               a.block_id + blocks - 1,
                               999) || ''';'
from dba_extents a,dba_objects b
where a.segment_name = b.object_name
and a.owner = b.owner
and b.object_name='JASON'
and b.owner='SCOTT'
order by a.relative_fno,a.block_id;
复制

输出如下：

有了以上的分片信息，我们只需要带入筛选的条件，使用匿名块批量删除即可，具体实现方式如下：

declare 
  cursor cur_rowid is
    select  dbms_rowid.rowid_create(1,
                    b.data_object_id,
                    a.relative_fno,
                    a.block_id,
                    0) begin_rowid,
                dbms_rowid.rowid_create(1,
                    b.data_object_id,
                    a.relative_fno,
                    a.block_id + blocks - 1,
                    999) end_rowid
    from dba_extents a,dba_objects b
    where a.segment_name = b.object_name
    and a.owner = b.owner
    and b.object_name='JASON'
    and b.owner='SCOTT'
    order by a.relative_fno,a.block_id;
    r_sql varchar2(4000);
begin
  FOR cur in cur_rowid LOOP
    r_sql := 'delete SCOTT.JASON where object_type=' || '''' || 'INDEX' || '''' || ' and rowid between :1 and :2';
    EXECUTE IMMEDIATE r_sql
      using cur.begin_rowid,cur.end_rowid;
    COMMIT;
  END LOOP;
end;
/

复制

注：本文参考于：《DBA攻坚指南》