SQL优化之统计信息和索引

ゝ一世哀愁。 2022-04-04 11:44 243阅读 0赞

大部分情况下的sql优化的场景，都是发生在我们想让我们的sql语句运行的更快一点，这个时候我们往往会采取一些“措施”调整sql的执行路径以便它们能更快的运行结束。

刚入dba行业，常常听到导师和我说的一句话“20%的sql优化技能就能优化80%的sql性能问题”。大部分情况下sql优化并不复杂，而很关键的一步是我们能不能发现问题，然后才能对症下药。今天我们先从sql优化中的统计信息、索引这2个方面来学习了解一下sql。

对于cbo模式，统计信息极为重要，这是影响sql语句执行的一个重要的因素。对于数据变化量大的数据库，会很容易出现统计信息过旧的问题即系统记录的表信息和实际表的信息不一致。那么优化器在选择最优执行路径的时候就可能选择不合适的执行路径（非最优路径）。所以我们一线运维常常需要定期的收集统计信息。那么问题来了，统计信息收集依据呢？

既然要收集，那么很多时候我们可以通过dba\_tables视图中的LAST\_ANALYZED字段看看上一次的收集日期，做个是否有必要做统计收集的预判断。

sys@RAC>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
    
    Session altered.    
    
    sys@RAC>select owner,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T1';
    
    OWNER                TABLE_NAME           LAST_ANALYZED
    -------------------- -------------------- -------------------
    TEST                 T1                   2017-10-28 15:08:25

既然有近一年没有收集过统计信息，那么这类表统计信息不准的嫌疑就会很大。

还有一点就是当我们在看sql执行计划最后有输出dynamic sampling used for thisstatement ，那么说明本次sql执行发生了**动态采样**。动态采样是指在段（表，索引，分区）没有分析的情况下，为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术，可以把它看做系统自动收集统计信息的一种手段。当对象没有统计信息时（即没有做分析），动态采样技术可以通过直接从需要分析的对象抽样数据块来抽象出CBO需要的统计信息。

动态采样需要额外的消耗数据库资源，所以如果SQL执行频率很高，这部分资源的消耗可能对sql执行效率的影响也会很大，这一点需要我们注意，在这样一个环境中，是不宜使用动态采样的。所以为了防止动态采样带来的系统性能的额外消耗，所以我们建议定期对数据库中的缺失或者过旧的统计信息表做定期的统计信息收集。

在上述两种情况中，我们一般需要收集统计信息。那么，如何收集又是另外一个问题。

示例：

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'xxxxx',
                                TABNAME => 'SR_PROBLEM_xxxx',
                                PARTNAME=>'P201802',  
                                ESTIMATE_PERCENT => 1,
                                METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
                                NO_INVALIDATE=> FALSE,
                                FORCE=>TRUE,
                                CASCADE => TRUE,
                                DEGREE  => 10);
    END;
    /

参数说明:

**·** ownname:要分析表的拥有者

**·** tabname:要分析的表名

**·** partname:分区的名字，只对分区表或分区索引有用

**·** estimate\_percent:采样行的百分比，取值范围\[0.000001,100\]

**·** method\_opt:决定histograms信息是怎样被统计的，method\_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO)

**·** for columns <list> SIZE <N> |REPEAT | AUTO | SKEWONLY:统计指定列的histograms，N的取值范围\[1,254\]

**·** force:即使表锁住了也收集统计信息

**·** cascade:是收集索引的信息，默认为FALSE

**·** degree:决定并行度，默认值为null

那么只有上述两种情况需要收集统计信息么？答案显然是否定的。而且统计信息收集的参数丰富，这里也只是示例，够日常场景下使用而已。很多情况下还需要根据实际情况动态调整的。

下面我们看看索引，在大多数场景下合理的使用索引会使sql语句的执行会有指数级的性能提升，但是索引虽然好，但是千万不能过度使用，因为维护索引需要消耗一定的资源的。