Oracle 分析及动态采样__the_DEFAULT_statistics_VARCHAR2_DBMS_STATS_

当前位置: 数据库>oracle

Oracle 分析及动态采样

来源: 互联网发布时间：2017-04-07

本文导语: 之前在说Oracle Optimizer中的CBO时讲到，当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息...

之前在说Oracle Optimizer中的CBO时讲到，当表没有做分析的时候，Oracle 会使用动态采样来收集统计信息。获取准确的段对象（表，表分区，索引等）的分析数据，是CBO存在的基石，CBO的机制就是收集尽可能多的对象信息和系统信息，通过对这些信息进行计算，分析，评估，最终得出一个成本最低的执行计划。所以对于CBO，数据段的分析就非常重要。

一．先演示一个示例，来理解分析的作用

1.1创建表

SQL> create table t as select object_id,object_name from dba_objects where 1=2;

表已创建。

SQL> create index index_t on t(object_id);

索引已创建。

SQL> insert into t select object_id,object_name from dba_objects;

已创建72926行。

SQL> commit;

提交完成。

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> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED

---------- ----------- ------------- --------------

0 0 0 25-8月 -10

从查询结果看出，表的行数，行长，占用的数据块数及最后的分析时间都是空。索引的相关信息也没有，说明这个表和说因都没有被分析，如果此时有一条SQL 对表做查询，CBO 由于无法获取这些信息，很可能生成错误的执行计划，如：

SQL> set linesize 200

SQL> set autot trace exp;

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

执行计划

----------------------------------------------------------

Plan hash value: 80339723

---------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 4 | 316 | 0 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 0 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INDEX_T | 1 | | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">30)

SQL>

在Oracle 10g以后，如果一个表没有做分析，数据库将自动对它做动态采样分析，所以这里采用hint的方式将动态采样的级别设置为0，即不使用动态采样。

从这个执行计划，看书CBO 估计出表中满足条件的记录为4条，索引使用了索引。我们对表做一下分析，用结果比较一下。

1.3 分析表及查看分析之后的执行计划

分析可以通过两中方式：

一种是analyze 命令，如：

analyze table tablename compute statistics for all indexes;

还有一种就是通过DBMS_STATS包来分析，从9i 开始，Oracle 推荐使用DBMS_STATS包对表进行分析操作，因为DBMS_STATS 提供了更多的功能，以及灵活的操作方式。

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

PL/SQL 过程已成功完成。

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

BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED

---------- ----------- ------------- --------------

1 263 72926 25-8月 -10

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

---------- ----------- ---------- --------------

72926 29 345 25-8月 -10

从上面的结果，可以看出DBMS_STATS.gather_table_stats已经对表和索引都做了分析。现在我们在来看一下执行计划。

SQL> set autot trace exp;

SQL> select * from t where object_id>30;

执行计划

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 72899 | 2064K| 96 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T | 72899 | 2064K| 96 (2)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">30)

从这个计划，我们看出CBO 估算出的结果是72899 条记录，与实际的72926很近。此时选择全表扫描更优。通过这个例子，我们也看出了分析对执行计划的重要性。

二．直方图（Histogram）

DBMS_STATS 包对段表的分析有三个层次：

（1）表自身的分析：包括表中的行数，数据块数，行长等信息。

（2）列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。

（3）索引的分析：包括索引叶块的数量，索引的深度，索引的聚合因子等。

直方图就是列分析中数据在列上的分布情况。

当Oracle 做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket,这样CBO就可以非常容易地知道这个列上的数的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行计划成本的计算当中。

对于数据分布非常倾斜的表，做直方图是非常有用的。如：　1,10,20,30,40,50. 那么在一个数值范围（bucket）内，它的数据记录基本上一样。如果是：1,5,5,5,5,10,10,20,50,100. 那么它在bucket内，数据分布就是严重的倾斜。

直方图有时对于CBO非常重要，特别是对于有字段数据非常倾斜的表，做直方图分析尤为重要。可以用dbms_stats包来分析。默认情况下，dbms_stats 包会对所有的列做直方图分析。如：

SQL> exec dbms_stats.gather_table_stats('SYS','T',cascade=>true);

PL/SQL 过程已成功完成。

然后从user_histograms视图上查看到相关的信息：

SQL> select table_name,column_name,endpoint_number,endpoint_value from user_histograms where table_name='T';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ -------------------- --------------- --------------

T OBJECT_ID 0 2

T OBJECT_NAME 0 2.4504E+35

T OBJECT_ID 1 76685

T OBJECT_NAME 1 1.0886E+36

如果一个列上的数据有比较严重的倾斜，对这个列做直方图是必要的，但是，Oracle 对数据分析是需要消耗资源的，特别是对于一些很大的段对象，分析的时间尤其长。对于OLAP系统，可能需要几个小时才能完成。

所以做不做分析就需要DBA 权衡好了。但有一点要注意，不要在生产环境中随便修改分析方案，除非你有十足的把握。否则可能导致非常严重的后果。

三． DBMS_STATS包

DBMS_STAS包不仅能够对表进行分析，它还可以对数据库分析进行管理。按照功能可以分一下几类：

（1）性能数据的收集

（2）性能数据的设置

（3）性能数据的删除

（4）性能数据的备份和恢

3.1 DBMS_STATS包的几个常用功能：性能的手机，设定和删除

性能数据的收集包含这样几个存储过程：

GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure

从名字也可以看出各自的作用，这些存储过程用来收集数据库不同级别对象的性能数据，包括：数据库，数据字典，表，索引，SCHEMA的性能等。

3.1.1 GATHER_TABLE_STATS Procedure 存储过程

在10g中, GATHER_TABLE_STATS的参数如下：

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

到了11g，对参数做了调整：

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

force BOOLEAN DEFAULT FALSE);

对参数的说明：

Parameter

Description

ownname

Schema of table to analyze

tabname

Name of table

partname

Name of partition

estimate_percent

Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_PARAM Procedure.

block_sample

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics.

method_opt

Accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms.
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO.The default value can be changed using the SET_PARAM Procedure.

degree

Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_PARAM Procedure NULL means use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement. Use the constant DBMS_STATS.DEFAULT_DEGREE to specify the default value based on the initialization parameters. The AUTO_DEGREE value determines the degree of parallelism automatically. This is either 1 (serial execution) or DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to size of the object.

granularity

Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL' - gathers all (subpartition, partition, and global) statistics

'AUTO'- determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - gathers global statistics

'GLOBAL AND PARTITION' - gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics.

cascade

Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes. Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics to be collected or not. This is the default. The default value can be changed using theSET_PARAM Procedure.

stattab

User statistics table identifier describing where to save the current statistics

statid

Identifier (optional) to associate with these statistics within stattab

statown

Schema containing stattab (if different than ownname)

no_invalidate

Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

force

Gather statistics of table even if it is locked

在gather_table_stats 存储过程的所有参数中，除了ownname和tabname，其他的参数都有默认值。所以我们在调用这个存储过程时，Oracle 会使用参数的默认值对表进行分析。如：

SQL> exec dbms_stats.gather_table_STATS('SYS','T');

PL/SQL 过程已成功完成。

如果想查看当前的默认值，可以使用dbms_stats.get_param函数来获取：

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')

------------------------------------------------------------

FOR ALL COLUMNS SIZE AUTO

结合上面对参数的说明：

- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.