基于CBO的SQL优化之统计信息（1）

一、背景：对于Oracle数据库来说，性能的问题往往出现在系统部署一段时间后，大量用户开始使用该系统，系统的数据量和各种计算的复杂性增加。为了避免的性能调整，最好在编码阶段就编写高效的SQL语句，称为主动性能调整。而一旦系统部署好，应用系统本身无法改变，我们只能通过对文件存储做合理分布以减少I/O，对表创建合理的索引、建立分区表等，这样的系统性能优化称为被动的性能调整，而这就是作为DBA大多数的工作内容。

CBO优化包括以下3部分：

1、统计信息：CBO使用统计信息确定最优的执行计划；

2、系统资源：考虑系统资源的使用情况，SQL_TRACE主要用于检查数据库的异常情况，通过跟踪数据库的活动，找到有问题的语句；

3、SQL语句的复杂度：EXPLAIN PLAN用于分析SQL语句的执行计划，便于优化SQL语句。

二、实际操作之统计信息

这节我们先来介绍下统计信息：

统计信息是优化器选择最优执行计划的重要依据之一，统计信息包括表的行数、列的数据分布、索引的高度等等。当Oracle执行SQL语句时，优化器会根据统计信息来选择最优的执行计划。如果统计信息不准确，那么优化器选择的执行计划就可能不是最优的。
举个例子，如果一个表有1000万行数据，但是统计信息中显示该表只有1000行数据，那么优化器就会认为全表扫描比使用索引更快。这样就会导致性能问题。

oracle提供了三种级别上的CBO优化：实例级、会话级、语句级，优化方式分别为

1) ALL_ROWS（默认）：满足最大吞吐量

2) FIRST_ROWS_n：输出查询的前n行数据，满足最快响应的查询需求

3) FIRST_ROWS：输出查询的前几行数据，满足最快响应时间的查询需求

针对自己的需求，可以修改不同的优化方式

查看当前数据库的CBO优化方式：（system用户）

SQL> show  parameter  optimizer_mode;

在实例级设置优化方式

SQL> alter system set optimizer_mode=first_rows_10 scope=both;

在会话级设置优化方式

SQL> alter session set optimizer_mode=first_rows;

1、自动统计信息

查看gather_stats_job的当前运行状态

SQL> select job_name,state,owner from dba_scheduler_jobs;

通过数据字典DBA_TABLES查询某一用户下表的统计情况
SQL> select last_analyzed,table_name,owner,num_rows,sample_size  from dba_tables  where owner='QUANT';

 分别表示为统计时间、表名、用户、总行数，统计的行数

2、手工统计信息

使用DBMS_STATS包手工收集统计信息

1）gather_database_statistics为全库的表统计信息

2）gather_schema_statistics为某个模式统计信息（和自动统计信息方式类似）

3）gather_table_statistics为某个特定的表统计信息

4）gather_index_statistics为某个索引表统计信息

1）、为某用户下的所有表统计信息

SQL> execute dbms_stats.gather_schema_stats(ownname=>'QUANT');

验证是否统计成功

SQL>select last_analyzed,table_name,owner,num_rows,sample_size  from dba_tables  where owner='QUANT';

2）、特定表的统计数据

SQL> execute dbms_stats.gather_table_stats('QUANT','TRADE_CAL');

 3）、特定索引的统计数据

查询索引

SQL> select index_name,table_name from dba_indexes where owner='QUANT';

为表TRADE_CAL的索引统计数据

SQL> execute dbms_stats.gather_index_stats('QUANT','SYS_C007469');

4）、收集数据库级别的统计数据

需要对初始化参数job_queue_processes设置一个非0值，才能保证gather_database_statistics正常运行

查询参数job_queue_processes的值

SQL> show parameter job_queue_processes;

 说明job_queue_processes值是400，不需要修改

如果值为0，修改方式如下：

SQL> alter system set job_queue_processes=20,scope=both;

收集数据库的统计信息

SQL> begin
  2  dbms_stats.gather_database_stats(estimate_percent=>null);
  3  end;
  4  /

查询某表的统计数据

SQL> select num_rows,avg_space,avg_row_len,num_freelist_blocks,last_analyzed from dba_tab_statistics where table_name='TRADE_CAL';

查询某表每列的统计数据

SQL> select column_name,num_distinct,low_value,high_value,sample_size,avg_col_len from dba_tab_col_statistics where table_name='TRADE_CAL';

分析了不同值的列数量，某列中的最大值和最小值，采样数以及列的长度。

5）、收集操作系统的统计数据

使用gather_system_stats过程来统计操作系统数据

在负载模式下收集10分钟的系统统计数据

SQL> execute dbms_stats.gather_system_stats('NOWORKLOAD',10);

收集系统统计数据（自定义间隔时间）

SQL> execute dbms_stats.gather_system_stats('start');

SQL> execute dbms_stats.gather_system_stats('stop');

上述两个指令间隔3分钟执行，采用sys.aux_stats$查询统计结果信息

SQL> select * from sys.aux_stats$;

显示了：有负载模式下的平均CPU周期数、I/O传销速度等字段，想知道的读者可以具体查一下

6）、收集字典的统计数据

收集动态性能视图字典的统计数据

SQL> execute dbms_stats.gather_fixed_objects_stats;

收集数据字典的统计数据

SQL> execute dbms_stats.gather_dictionary_stats;

也可以使用gather_schema_stats统计数据字典数据

SQL> execute dbms_stats.gather_schema_stats('sys');