Oracle表分析2

表、索引等方案的分析

为什么要分析？

在一个采用基于CBO优化器的数据库中，为了选择更加的理想的执行计划。必须定时的对表、索引等方案进行分析。因为在对表进行大量的DML操作之后，索引等结构上可能会发生变化。而CBO优化器执行的基础就是数据库中的统计资料，获得最新、最完善的统计资料的方式就是对表、索引等的分析。

对表和对索引进行分析完之后，在DBA_tables 视图中，以前很多列值是空的，现在开始有数据了。

在一个使用CBO优化器的数据库中，必须定期对表、索引等方案进行分析。需要注意的是，并不是分析之后，查询的速度一定会变快。因为对表的统计信息进行更新之后，有可能导致执行计划的改变，也就有可能导致执行计划变的更慢。个人认为每次分析表之后都得实时抓却awr报告，观察系统运行变化。

分析表的方式


1、语句方式：analyze table compute statistics for table||for all columns||for all indexed columns;

--查看表的统计信息（我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息）
analyze table compute statistics for table

--查看字段的统计信息（在指定for all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed columns时,oracle只给有索引的字段进行字段信息统计,如果我们别有必要给所有字段统计信息时,这个属性就很有用了）
analyze table compute statistics for all columns

--查看索引的统计信息
analyze table compute statistics for all indexed columns;

综上：如果在运行analyze table是我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)

2、程序包的方式：8i之后新增了dbms程序包，使得统计数据的生成和处理更加的方便。

--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'test',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )

--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'sm_user',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

pl/sql procedure successfully completed

--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'TEST',indname => 'pk_user_index',estimate_percent => '10',degree => '4') ;

pl/sql procedure successfully completed

--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'SM_USER') ;

pl/sql procedure successfully completed

--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'TEST',tabname => 'SM_USER',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'TEST',stattab => 'SM_USER');

pl/sql procedure successfully completed

--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'TEST',indname => 'PK_USER_INDEX',stattab => 'stat_table')


analyze和dbms_stats不同的地方


analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan

3.重建索引
alter index index_name rebuild tablespace tablespace_name
alter index index_name rebuild tablespace tablespace_name 加入表空间名，会将指定的索引移动到指定的表空间当中。

注：
analyze 操作只是统计信息，并将统计信息存放起来供日后分析SQL使用，不进行重建之类的具体实施性操作,因此要重建索引的话
还是要用 alter index index_name rebuild

=================================

此外，两个特殊的命令：

set autotrace on explain; ---默认显示每一个执行计划

set timing on;---默认显示每一个SQL的执行时长



===================其它更详细的分析

特定索引分析

--创建索引
create index idx_test_nick on test(nick);

--只对索引进行分析
analyze index idx_test_nick compute statistics;

--查看索引名,对应存储的数据块,不同的key数量,记录数（行数）的分析信息
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
from user_indexes
where index_name = 'IDX_TEST_NICK';

--dba_tab_col_statistics

--查看表的统计信息
select COLUMN_NAME, NUM_BUCKETS, num_distinct
from USER_tab_columns
where table_name = 'TEST';



分析特定字段

--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;

--分析表
analyze table test compute statistics for table;



从以上一系列的实验可以看出，对ORACLE的优化器CBO来说，表的分析与列的分析才是最重要的，索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时产生执行计划的复杂度问题。

完全计算法： analyze table abc compute statistics;
抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent

对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，假如不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。