【性能优化】之 表分析及动态采样

书面作业，如果有要求，请给出整个过程的SQL输出信息，不允许单纯用语言描述：
 1.演示一个表分析后执行计划比动态采样更准确的例子。
 2.演示在缺乏直方图时，CBO计算出错误的执行计划的例子，并给出最后正确的执行计划。
 3.演示在分区表上，全局信息和分区信息是如何影响执行计划的，给出演示过程。
 4.演示用extended statistics 解决列相关性的例子，给出演示过程。
 5.对一张表进行导出/导入，看它的统计信息是否发生改变，给出演示过程。

 #######################################################################################

 1.演示一个表分析后执行计划比动态采样更准确的例子。

答：



1.1创建表

SQL> create table t as select * from dba_objects ;
Table created.
SQL> select count(0) from t;

  COUNT(0)
----------
     76376


1.2 查看表的统计信息

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED
---------- ----------- ---------- ------------------

因为没有做表信息的统计，这时的表行数、数据块等信息都为空。
我们使用动态分析查看一下一条SQL 的执行计划：

SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4550 |   919K|   305   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  4550 |   919K|   305   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">30)


再使用查询的方式，查看一下SQL 查询的总数据量：


select count(0) from t where object_id>30;

       COUNT(0)
    --------------
1    76340

可以看到，CBO 使用动态采样估算的值，以实际值，差得是那个相当的远。

下面使用通过DBMS_STATS 来做表的分析。

SQL> exec dbms_stats.gather_table_stats('TANG','T');

这时我们查询表的统计信息可以看到，数据量，占用的数据块等信息都有了。

SQL>select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

       NUM_ROWS    AVG_ROW_LEN    BLOCKS    LAST_ANALYZED
    -------------------------------------------------
1    76376        98            1114    2013/11/21 15:21:23

这时再使用上面的查询，查看两者的执行计划的差别
这时可以看出，CBO使用表的统计信息给出的执行计划中，数据行数76347 与实际行数 76340 可以说是约等于了。


SQL> select * from t where object_id>30;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 76347 |  7306K|   305   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 76347 |  7306K|   305   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">30)

下面再建立一个索引：

SQL> create index idx_t_id on t(object_id);

Index created.

查询索引的统计信息：

SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

       BLEVEL    LEAF_BLOCKS    DISTINCT_KEYS    LAST_ANALYZED
    --------------------------------------------------
1    1    169    76369    2013/11/21 15:40:39

发现，这时的索引统计信息是有的。并且值还是很接近数据问题。
但是不是也是估算出来的呢，下面再做一次索引统计信息后，再查询，
看到是一致的。说明在表已做统计分析后，建立的索引，索引会在表分析数据的基础上，自动生成
索引统计信息

SQL> exec dbms_stats.gather_index_stats('TANG','IDX_T_ID');

PL/SQL procedure successfully completed.


SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

       BLEVEL    LEAF_BLOCKS    DISTINCT_KEYS    LAST_ANALYZED
    --------------------------------------------------
1    1    169    76369    2013/11/21 15:40:39

========================================================================================
 2.演示在缺乏直方图时，CBO计算出错误的执行计划的例子，并给出最后正确的执行计划。

    答：
    直方图为：ORACLE 对数据列上的数据分布进行统计后得出的图示。
    常常用来查看数据倾斜（值在整个数据中所占比例），了解某个值所占有的记录数。

    直方图类型：
    1.FREQUENCY 频率直方图：主要用于基数少，记录多，重复率高的字段。
    2.HEIGHT BALANCED 高度平衡直方图：用于基数少，唯一值多，重复率低的字段。

    2.1 建立演示环境：

    SQL> drop table t1 purge;

    Table dropped.


    SQL> create table t1 as select * from dba_objects;

    Table created.

    2.2 查看一下查询的数据量
    select count(*) from t1 where object_type='TABLE';
    --------
    3207

    2.3 做信息收集但没有做直方图分析 后的执行计划

    SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 1');

    PL/SQL procedure successfully completed.


    SQL> select count(*) from t1 where object_type='TABLE';

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3724264953

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     9 |   304   (1)| 00:00:04 |
    |   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
    |*  2 |   TABLE ACCESS FULL| T1   |  1697 | 15273 |   304   (1)| 00:00:04 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - filter("OBJECT_TYPE"='TABLE')

    2.3 做信息收集但即做直方图分析 后的执行计划

        SQL> execute dbms_stats.gather_table_stats('TANG','T1',METHOD_OPT=>'for all columns size 254');

        PL/SQL procedure successfully completed.

        SQL> select count(*) from t1 where object_type='TABLE';

        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3724264953

        ---------------------------------------------------------------------------
        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
        ---------------------------------------------------------------------------
        |   0 | SELECT STATEMENT   |      |     1 |     9 |   304   (1)| 00:00:04 |
        |   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
        |*  2 |   TABLE ACCESS FULL| T1   |  2723 | 24507 |   304   (1)| 00:00:04 |
        ---------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------

           2 - filter("OBJECT_TYPE"='TABLE')


    我们来看一下前后的对比，在没有做直方图前，因为CBO并不知道 OBJECT_TYPE='TABLE' 的数据量所占比例，
    估算的值 1697 与真实值  3207 误差太大的原因。

    在进行了直方图分析后，计算出的rows=2723 ，与真实值已非常接近了。


 ========================================================================================
 3.演示在分区表上，全局信息和分区信息是如何影响执行计划的，给出演示过程。
    
    3.1 先查询一下数据分布情况
    select  floor(object_id/10000),count(0) from t1  
    group by  floor(object_id/10000)
    order by floor(object_id/10000)

       FLOOR(OBJECT_ID/10000)    COUNT(0)
    ---------------------------------
    0    9829
    1    9848
    2    10000
    3    10000
    4    10000
    5    9959
    6    9538
    7    6133
    8    224
    9    838
        7

    3.2建立一个与object_id 为分区字段的分区表：
    drop table t3;
    create table t3
    partition by range (object_id)
    (
    partition p1 values less than(10000),
    partition p2 values less than(20000),
    partition p3 values less than(30000),
    partition p4 values less than(40000),
    partition p5 values less than(50000),
    partition p6 values less than(60000),
    partition pm values less than(maxvalue)
    ) as select * from dba_objects;


    3.3对表进行一次统计分析

    SQL> execute dbms_stats.gather_table_stats('TANG','T3');
    PL/SQL procedure successfully completed

    3.4 查询表的统计信息，从统计表中看，数据为76384
    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
    TABLE_NAME                       NUM_ROWS     BLOCKS GLOBAL_STATS
    ------------------------------ ---------- ---------- ------------
    T3                                  76384       1216 YES

    3.5查询表各分区数据量
    select table_name,partition_name,num_rows,blocks,global_stats
    from dba_tab_partitions where table_name='T3';

        TABLE_NAME    PARTITION_NAME    NUM_ROWS    BLOCKS    GLOBAL_STATS
        ----------------------------------------------------------------
    1    T3    P1    9829    145    YES
    2    T3    P2    9848    158    YES
    3    T3    P3    10000    164    YES
    4    T3    P4    10000    165    YES
    5    T3    P5    10000    165    YES
    6    T3    P6    9959    160    YES
    7    T3    PM    16748    259    YES



    查看一下这时的查询的查询计划：
    第一条我只对一个分区的数据进行查询从上面的统计可以知道，查询 object_id<=9000的数据只在第一个分区。
    而第二条查询 object_id<=30000 就已经是跨分区P1-P3


    SQL> select count(*) from T3 where object_id<=9000;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4037133807

    ------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |      |     1 |     4 |    41   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE         |      |     1 |     4 |            |          |       |       |
    |   2 |   PARTITION RANGE SINGLE|      |  8848 | 35392 |    41   (0)| 00:00:01 |     1 |     1 |
    |*  3 |    TABLE ACCESS FULL    | T3   |  8848 | 35392 |    41   (0)| 00:00:01 |     1 |     1 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter("OBJECT_ID"<=9000)

    SQL>




    SQL> select count(*) from T3 where object_id<=30000;

        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3481849819

        --------------------------------------------------------------------------------------------------
        | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
        --------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT          |      |     1 |     5 |   174   (1)| 00:00:03 |       |       |
        |   1 |  SORT AGGREGATE           |      |     1 |     5 |            |          |       |       |
        |   2 |   PARTITION RANGE ITERATOR|      | 24075 |   117K|   174   (1)| 00:00:03 |     1 |     4 |
        |*  3 |    TABLE ACCESS FULL      | T3   | 24075 |   117K|   174   (1)| 00:00:03 |     1 |     4 |
        --------------------------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------

           3 - filter("OBJECT_ID"<=30000)



    3.6 我再来一次删除数据后，再只对分区进行统计分析


    SQL> delete from t3;
    76384 rows deleted

    SQL> commit;
    Commit complete

    SQL> execute dbms_stats.gather_table_stats('TANG','T3',granularity=>'partition');
    PL/SQL procedure successfully completed

    SQL>

    3.7 查看全局统计信息及分区统计信息，可以看到全局统计信息中的数据没有改变，还是 NUM_ROWS=76384
    而分区统计信息中已变化了。数据为 0
    
    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T3');
    TABLE_NAME                       NUM_ROWS     BLOCKS GLOBAL_STATS
    ------------------------------ ---------- ---------- ------------
    T3                                  76384       1346 YES

    SQL> select table_name,partition_name,num_rows,blocks,global_stats  from dba_tab_partitions where table_name='T3';
    TABLE_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS GLOBAL_STATS
    ------------------------------ ------------------------------ ---------- ---------- ------------
    T3                             P1                                      0        174 YES
    T3                             P2                                      0        174 YES
    T3                             P3                                      0        174 YES
    T3                             P4                                      0        174 YES
    T3                             P5                                      0        174 YES
    T3                             P6                                      0        174 YES
    T3                             PM                                      0        302 YES
    7 rows selected

    SQL>
    
    3.8 这时我们再回来查看两个查询的执行计划：
        3.8.1 查询  object_id<=30000 时使用的是全局统计信息，这时查询的数据还是 24045
        显然不正确。

        3.8.2 查询 object_id<=9000 时使用的是分区统计信息，这时查询数据返回 Rows=1。


    SQL> select count(*) from T3 where object_id<=30000;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3481849819

    --------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |      |     1 |     5 |   191   (1)| 00:00:03 |       |       |
    |   1 |  SORT AGGREGATE           |      |     1 |     5 |            |          |       |       |
    |   2 |   PARTITION RANGE ITERATOR|      | 24045 |   117K|   191   (1)| 00:00:03 |     1 |     4 |
    |*  3 |    TABLE ACCESS FULL      | T3   | 24045 |   117K|   191   (1)| 00:00:03 |     1 |     4 |
    --------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter("OBJECT_ID"<=30000)

    SQL> select count(*) from T3 where object_id<=9000;

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4037133807

    ------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |      |     1 |    13 |    49   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE         |      |     1 |    13 |            |          |       |       |
    |   2 |   PARTITION RANGE SINGLE|      |     1 |    13 |    49   (0)| 00:00:01 |     1 |     1 |
    |*  3 |    TABLE ACCESS FULL    | T3   |     1 |    13 |    49   (0)| 00:00:01 |     1 |     1 |
    ------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       3 - filter("OBJECT_ID"<=9000)

    SQL>
    


 ========================================================================================
 4.演示用extended statistics 解决列相关性的例子，给出演示过程。
    4.1 建立测试数据


    SQL> CREATE TABLE T4 AS SELECT * FROM DBA_OBJECTS;

    Table created.

    4.2 对表进行统计分析
    SQL> exec dbms_stats.gather_table_stats('TANG','T4');

    PL/SQL procedure successfully completed.
    

    4.2查看查询结果，以方便后面的对比
    SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';

        COUNT(*)
        ---------------
    1    1066

    4.3 查看查询计划，看到统计的行数为 1431

    SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 405148644

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    15 |   305   (1)| 00:00:04 |
    |   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
    |*  2 |   TABLE ACCESS FULL| T4   |  1431 | 21465 |   305   (1)| 00:00:04 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')

    4.4 再对表进行一次列相关性分析
    SQL> execute dbms_stats.gather_table_stats('TANG','T4',method_opt=>'for columns (object_type,owner) size skewonly');

    PL/SQL procedure successfully completed.

    4.5 再查看执行计划，看到统计行数为 1203 ，与真实值 1066 比上一次更为接近。

    SQL> select count(*) from T4 where object_type='TABLE' and owner='SYS';

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 405148644

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    15 |   305   (1)| 00:00:04 |
    |   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
    |*  2 |   TABLE ACCESS FULL| T4   |  1203 | 18045 |   305   (1)| 00:00:04 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SYS')

    SQL>


 ========================================================================================
 5.对一张表进行导出/导入，看它的统计信息是否发生改变，给出演示过程。

 5.1 建立测试表：

    SQL>
    SQL>
    SQL> drop table t5
    SQL> create table t5 as select * from dba_objects;
    Table created.
    SQL> set autotrace off  ;
    5.2 查询数据总量
    SQL> select count(0) from t5;

      COUNT(0)
    ----------
         76385

    C:\Users\Administrator>

    SQL> execute dbms_stats.gather_table_stats('TANG','T5');

    PL/SQL procedure successfully completed.

    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');

    TABLE_NAME   NUM_ROWS     BLOCKS GLOBAL_ST
    ---------- ---------- ---------   ---------
    T5                76385       1114 YES


    SQL>

    select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('LEO7');


    查询统计分析数据

    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');

    TABLE_NAME
    --------------------------------------------------------------------------------
      NUM_ROWS     BLOCKS GLOBAL_ST
    ---------- ---------- ---------
    T5
         76385       1114 YES



    5.2 导出数据
    C:\Users\Administrator>exp userid=tang rows=Y tables=(t5) file='exp_t5' log='exp
    _t5.log'

    Export: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:28:48 2013

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

    口令:

    连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
    tion
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

    即将导出指定的表通过常规路径...
    . . 正在导出表                              T5导出了       76385 行
    成功终止导出, 没有出现警告。

    再次查询统计分析数据，看到数据没有变化

    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');

    TABLE_NAME
    --------------------------------------------------------------------------------
      NUM_ROWS     BLOCKS GLOBAL_ST
    ---------- ---------- ---------
    T5
         76385       1114 YES


    SQL>


    5.3 导入数据


        C:\Users\Administrator>imp tang/sa ignore=Y rows=Y fromuser=tang touser=tang fil
        e='exp_t5.dmp' log='imp_t5.log'

        Import: Release 11.2.0.3.0 - Production on 星期日 11月 24 14:32:54 2013

        Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


        连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Produc
        tion
        With the Partitioning, OLAP, Data Mining and Real Application Testing options

        经由常规路径由 EXPORT:V11.02.00 创建的导出文件
        已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
        . 正在将 TANG 的对象导入到 TANG
        . . 正在导入表                            "T5"导入了       76385 行
        成功终止导入, 没有出现警告。

        C:\Users\Administrator>

    5.4 查询一下数据总量，再查询统计分析数据，


    SQL> select count(0) from t5;

      COUNT(0)
    ----------
        152770

    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');

    TABLE_NAME
    --------------------------------------------------------------------------------
      NUM_ROWS     BLOCKS GLOBAL_ST
    ---------- ---------- ---------
    T5
         76385       1114 YES


    SQL>

        可以看到，数据总量已发生变化，但统计分析数据没有改变。说明导入，导出数据后，
        表并不会自动进行统计分析，收集表的信息。如果要更加准确的表统计分析数据，还要手工进行。

    （后面是进行统计后的数据）
    SQL> execute dbms_stats.gather_table_stats('TANG','T5');

    PL/SQL procedure successfully completed.

    SQL> select table_name,num_rows,blocks,global_stats from dba_tables where table_name in ('T5');

    TABLE_NAME
    --------------------------------------------------------------------------------
      NUM_ROWS     BLOCKS GLOBAL_ST
    ---------- ---------- ---------
    T5
        152770       2286 YES