Oracle统计信息（1）-云搜网

统计信息

统计信息概述

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划！可以说统计信息关乎sql的执行计划是否正确，属于sql执行的指导思想，oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值:

BASIC :—收集基本的统计信息

TYPICAL：–收集大部分统计信息(数据库的默认设置)

ALL：–收集全部统计信息

Oracle 10g之后，Query Optimizer就已经将CBO作为默认优化器，并且Oracle官方不再支持RBO服务。但是，通过优化器参数optimizer_mode，我们可以控制Oracle优化器生成不同模式下的执行计划。

如何收集统计信息
1. 统计信息的内容：

1）Table statistics

Number of rows –行数量

Number of blocks –block数量

Average row length –平均行的长度.

2）Column statistics

Number of distinct values (NDV) in column –列中distinct的值

Number of nulls in column –列中null的值

Data distribution (histogram) –数据分布

3）Index statistics

Number of leaf blocks –子节点的块数量

Levels –子节点数量

Clustering factor –集群因子

4）System statistics

I/O performance and utilization –IO性能和利用率

CPU performance and utilization –CPU的性能和利用率

收集统计信息的方式

Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包来收集，Oracle 建议使用DBMS_STATS包来收集统计信息，因为DBMS_STATS包收集的更广，并且更准确。analyze 在以后的版本中可能会被移除。

DBMS_STATS常用的几个过程如下：

dbms_stats.gather_table_stats 收集表、列和索引的统计信息；

dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；

dbms_stats.gather_index_stats 收集索引的统计信息；

dbms_stats.gather_system_stats 收集系统统计信息

dbms_stats.GATHER_DICTIONARY_STATS：所有字典对象的统计；

DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计

dbms_stats.delete_table_stats 删除表的统计信息

dbms_stats.delete_index_stats 删除索引的统计信息

dbms_stats.export_table_stats 输出表的统计信息

dbms_stats.create_state_table

dbms_stats.set_table_stats 设置表的统计

dbms_stats.auto_sample_size

analyze 命令的语法如下：

analyze table tablename compute statistics;

analyze table tablename compute statistics for all indexes;

analyze table tablename delete statistics;

2.3 自动收集统计信息

Oracle 的Statistic 信息的收集分两种：自动收集和手工收集。

自动收集的工作原理

Oracle 的Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息：

（1）Missing statistics（统计信息缺失）

（2）Stale statistics（统计信息陈旧）

该Job 是在数据库创建的时候自动创建，并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。默认情况下，11g在周一到周五（晚22:00-次日2:00），通过一个job（gather_stat_job）自动收集对象的统计信息。这种自动收集统计信息的方式并不是收集所有对象的统计信息，而是收集没有统计信息的对象和统计信息过旧的对象。然后确定优先级，再开始进行统计信息。

Scheduler Job的stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False，那么GATHER_STATS_JOB 会中断，而没有收集完的对象将在下次启动时继续收集。

gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。该过程收集对象statistics的条件如下：

（1）对象的统计信息之前没有收集过。

（2）当对象有超过10%的rows 被修改，此时对象的统计信息也称为stale statistics。

但是对于高度变化的表在白天的活动期间被TRUNCATE/DROP并重建或者块加载超过本身总大小10%的对象；我们可以将这些表上的统计设置为NULL

可以通过以下SQL来查看job：

select job_name, program_name, enabled, stop_on_window_close

from dba_scheduler_jobs

where job_name = ‘gather_stats_job’;

控制自动收集统计信息的参数：STATISTICS_LEVEL

为了决定是否对对象进行监控，Oracle 提供了一个参数STATISTICS_LEVEL。通过设置初始化参数STATISTIC_LEVEL

typical 或all，就可以自动收集统计信息(默认值为TYPICAL，因此可以随即启用自动收集统计信息的功能)。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。
basic：STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能，在10g中表监控默认是激活的：

（1）ASH(Active Session History)

（2）ASSM(Automatic Shared Memory Management)

（3）AWR(Automatic Workload Repository)

（4）ADDM(Automatic Database Diagnostic Monitor)

自动收集统计信息的过程讲解

当启动对象的监控后，从上次统计信息收集之后的的信息，如inserts，updates，deletes 等，这些改变的信息会记录到user_tab_modifications 视图。

当对象的数据发生改变之后，经过几分钟的延时，这些信息写入user_tab_modifications视图，然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息，并将这些信息保存在内存中。

当监控的对象被修改的部分10%时，gather_database_stats 或者gather_schema_stats 过程就会去收集这些stale statistics。

查看自动收集统计信息

查看自动收集统计信息的任务及状态：

select client_name,status from dba_autotask_client;

禁止自动收集统计信息的任务

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

再次查看

```
启用自动收集统计信息的任务
```

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

获得当前自动收集统计信息的执行时间

col  WINDOW_NAME format a20

col REPEAT_INTERVAL format a70

col DURATION format a20

set line 180

SELECT w.window_name, w.repeat_interval,w.duration, w.enabled

 FROM dba_autotask_window_clients c, dba_scheduler_windows w

 WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

下面对上图的参数讲解

FREQ 关键字用来指定间隔的时间周期，可选参数有：YEARLY（年）, MONTHLY（月）, WEEKLY（周）, DAILY（日）, HOURLY（时）, MINUTELY（分）, SECONDLY（秒）等单位。

INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-999。

BYHOUR 指定一天中的小时。可指定的值的范围从1-24。16，17，18就表示每天下午的4、5、6点。

BYDAY 关键字用来指定每周的哪天运行。

BYMONTHDAY 关键字用来指定每月中的哪一天。-1 表示每月最后一天。

BYMONTH 关键字用来指定每年的月份。

BYDATE 指定日期。0310就表示3月10日。

https://blog.csdn.net/zq9017197/article/details/6985109

--WINDOW_NAME：任务名

--REPEAT_INTERVAL：任务重复间隔时间

--DURATION：持续时间

--enabled为true表明当前的这些作业处于激活状态

查看自动收集统计信息历史执行情况

SELECT * FROM dba_autotask_client_history

WHERE client_name LIKE ‘%stats%’;

-维护窗口组

select * from dba_scheduler_window_groups;

--维护窗口组对应窗口

select * from dba_scheduler_wingroup_members

--维护窗口历史信息

select* from dba_scheduler_windows

--查询自动收集任务正在执行的job

select * from DBA_AUTOTASK_CLIENT_JOB;

--查询自动收集任务历史执行状态

select * from DBA_AUTOTASK_JOB_HISTORY;

select * from DBA_AUTOTASK_CLIENT_HISTORY;

```
修改统计信息执行的时间
```

1.关闭单个调度时间窗口,这里我们关闭MONDAY_WINDOW

BEGIN

DBMS_AUTO_TASK_ADMIN.disable (

 client_name   => 'auto optimizer stats collection',

 operation     => NULL,

 window_name   => 'MONDAY_WINDOW');

 END;

--验证关闭情况，如下，optimizer_stats列为DISABLED

SELECT window_name,window_next_time,window_active,optimizer_stats

FROM dba_autotask_window_clients

WHERE window_name = 'MONDAY_WINDOW'

ORDER BY window_next_time;

2.开启单个调度时间窗口及所有时间调度窗口，只需要使用enable过程

 -- 注：单个应指定窗口名字，如window_name   => 'MONDAY_WINDOW'

 BEGIN

 DBMS_AUTO_TASK_ADMIN.enable (

 client_name   => 'auto optimizer stats collection',

 operation     => NULL,

 window_name   => 'MONDAY_WINDOW');

 END;

--1.停止任务

BEGIN

DBMS_SCHEDULER.DISABLE(

name=>'"SYS"."MONDAY_WINDOW"',

force=>TRUE);

END;

使用如下脚本验证是否停止

SELECT window_name,window_next_time,window_active,optimizer_stats

FROM dba_autotask_window_clients

WHERE window_name = 'FRIDAY_WINDOW '

ORDER BY window_next_time;

--2.修改任务的持续时间，单位是分钟

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."FRIDAY_WINDOW"',

attribute=>'DURATION',

value=>numtodsinterval(180, 'minute'));

END;

--3.开始执行时间，BYHOUR=2,表示2点开始执行

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(

name=>'"SYS"."FRIDAY_WINDOW"',

attribute=>'REPEAT_INTERVAL',

value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');

END;

--4.开启任务

BEGIN

DBMS_SCHEDULER.ENABLE(

name=>'"SYS"."MONDAY_WINDOW"');

END;

```
修改时间窗口到特定的时间
```

修改时间窗口到特定的时间

–如下示例，将周五时间窗口时间到晚间23点30分

BEGIN

DBMS_SCHEDULER.DISABLE (name => ‘”SYS”.”FRIDAY_WINDOW”‘, force => TRUE);

END;

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

name => ‘”SYS”.”FRIDAY_WINDOW”‘,

attribute => ‘REPEAT_INTERVAL’,

VALUE => ‘FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0’);

END;

BEGIN

DBMS_SCHEDULER.ENABLE (name => ‘”SYS”.”FRIDAY_WINDOW”‘);

END;

–验证修改

SELECT w.window_name,

w.repeat_interval,

w.duration,

w.enabled

FROM dba_autotask_window_clients c, dba_scheduler_windows w

WHERE c.window_name = w.window_name

AND c.optimizer_stats = ‘ENABLED’

AND c.window_name = ‘FRIDAY_WINDOW’;

WINDOW_NAME REPEAT_INTERVAL DURATION ENABL

————— ———————————————————— ———— ——–

FRIDAY_WINDOW FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0 +000 04:00:0 TRUE

7、新增维护时间窗口

— 假定我们要处理的是修改周一的时间窗口

— 首先关闭周一的时间窗口

BEGIN

DBMS_AUTO_TASK_ADMIN.disable (

client_name => ‘auto optimizer stats collection’,

operation => NULL,

window_name => ‘MONDAY_WINDOW’);

END;

–接下来创建一个窗口并设定时间调度间隔

–如下，每周一5点执行，持续时间为1小时

BEGIN

DBMS_SCHEDULER.create_window (

window_name => ‘STATS_WINDOW’,

resource_plan => ‘DEFAULT_MAINTENANCE_PLAN’,

repeat_interval => ‘freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0’,

duration => INTERVAL ‘1’ HOUR,

comments => ‘Test window for stats task’);

EXCEPTION

WHEN OTHERS

THEN

IF SQLCODE = -27477

THEN

NULL;

ELSE

RAISE;

END IF;

END;

BEGIN

DBMS_SCHEDULER.set_attribute (‘STATS_WINDOW’, ‘SYSTEM’, TRUE);

DBMS_SCHEDULER.set_attribute (‘STATS_WINDOW’,

‘FOLLOW_DEFAULT_TIMEZONE’,

TRUE);

EXCEPTION

WHEN OTHERS

THEN

IF SQLCODE = -27477

THEN

NULL;

ELSE

RAISE;

END IF;

END;

SQL> SELECT window_name, repeat_interval, enabled

2 FROM dba_scheduler_windows

3 WHERE window_name = ‘STATS_WINDOW’;

WINDOW_NAME REPEAT_INTERVAL ENABL

—————————— ———————————————————— —–

STATS_WINDOW freq=daily;byday=MON;byhour=5;byminute=0; bysecond=0 TRUE

–接下来将窗口STATS_WINDOW添加到维护窗口组

BEGIN

DBMS_SCHEDULER.add_window_group_member (‘MAINTENANCE_WINDOW_GROUP’,

‘STATS_WINDOW’);

END;

SQL> SELECT WINDOW_NAME,

2 WINDOW_NEXT_TIME,

3 WINDOW_ACTIVE,

4 OPTIMIZER_STATS

5 FROM DBA_AUTOTASK_WINDOW_CLIENTS

6 WHERE WINDOW_NAME in (‘STATS_WINDOW’,’MONDAY_WINDOW’)

7 ORDER BY WINDOW_NEXT_TIME;

WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE

—————————— ————————————– —– ——–

STATS_WINDOW 27-MAR-17 05.00.00.000000 AM PRC FALSE ENABLED –允许

MONDAY_WINDOW 27-MAR-17 10.00.00.000000 PM PRC FALSE DISABLED –当前被disabled

关闭自动统计信息命令，exec DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

启用自动统计信息命令，exec DBMS_SCHEDULER.ENABLE(‘GATHER_STATS_JOB’);

查看自动统计信息是否开启，

oracle 10g ：SELECT OWNER,JOB_NAME,ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ‘GATHER_STATS_JOB’;

oracle 11g ：select t1.owner, t1.job_name, t1.enabled from dba_scheduler_jobs t1 where t1.job_name = ‘BSLN_MAINTAIN_STATS_JOB’;

手动收集统计信息

表的统计信息

包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小：

select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len from user_tables;

NUM_ROWS, –表中的记录数

BLOCKS, –表中数据所占的数据块数

EMPTY_BLOCKS, –表中的空块数

AVG_SPACE, –数据块中平均的使用空间

CHAIN_CNT, –表中行连接和行迁移的数量

AVG_ROW_LEN –每条记录的平均长度

索引列的统计信息

包含索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数。

select blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,

avg_data_blocks_per_key, clustering_factor from user_indexes;

BLEVEL, –索引的层数

LEAF_BLOCKS, –叶子结点的个数

DISTINCT_KEYS, –唯一值的个数

AVG_LEAF_BLOCKS_PER_KEY, –每个KEY的平均叶块个数

AVG_DATA_BLOCKS_PER_KEY, –每个KEY的平均数据块个数

CLUSTERING_FACTOR –群集因子

列的统计信息

包含唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数

select num_distinct,low_value, high_value,

density, num_nulls, num_buckets, histogram from user_tab_columns;

NUM_DISTINCT, –唯一值的个数

LOW_VALUE, –列上的最小值

HIGH_VALUE, –列上的最大值

DENSITY, –选择率因子（密度）

NUM_NULLS, –空值的个数

NUM_BUCKETS, –直方图的BUCKET个数

HISTOGRAM –直方图的类型

对于统计信息的搜集，谈谈个人的几点理解：

1.统计信息默认是存放在数据字典表中的，也只有数据字典中的统计信息，才会影响到CBO。

2.DBMS_STATS 提供的CREATE_STAT_TABLE 过程，只是生成一个用户自定义的特定格式的表，用来存放统计信息罢了，这个表中的统计信息是不会影响到统计信息的。

3.GATHER 系列过程中，如果指定stattab，statid，statown 参数（也可以不指定），则是搜集的统计信息除了更新到数据字典外，还在statown 用户下的stattab 表中存放一份，标示为 statid;

4.EXPORT和IMPORT 系列的过程中,stattab,statid,statown 参数不能为空，分别表示把数据字典中的当前统计信息导出到用户自定义的表中，以及把用户表中的统计信息导入到数据字典中，很明显可以看出，这里的导入操作和上面GATHER 操作会改变统计信息，可能会引起执行执行计划的改变，因此要慎重操作。

5.每次统计信息搜集前，将旧的统计信息备份起来是很有必要的；特别是保留一份或多份系统在稳定时期的统计信息也是很有必要的。

6.多长时间搜集一次统计信息，对于统计信息如何备份和保留，搜集统计信息时如何选择合适的采样，并行，直方图设置等都比较重要，需要设计一个较好的统计信息搜集策略。

3. 统计信息的收集以及常用数据字典

3.1 统计信息常用数据字典

统计信息收集如下数据：

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

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

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

这些统计信息存放在以下的数据字典里：

DBA_TABLES

DBA_OBJECT_TABLES

DBA_TAB_STATISTICS

DBA_TAB_COL_STATISTICS

DBA_TAB_HISTOGRAMS

DBA_INDEXES

DBA_IND_STATISTICS

DBA_CLUSTERS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_PART_COL_STATISTICS

DBA_PART_HISTOGRAMS

DBA_SUBPART_COL_STATISTICS

DBA_SUBPART_HISTOGRAMS

3.2 OLAP和OLTP的统计信息

在OLTP系统中，一定要收集统计信息而且数据库自带的 JOB 一定要关闭并且要自己定制统计信息收集策略，这是因为OLTP系统中数据变化频繁，业务非常复杂我举个例子：本来估算返回1W行那么导致 CBO 估算返回1行

很多的 OLAP 系统都不收集，直接用HINT固定，因为OLAP系统I/O压力大，表也非常大，收集统计信息很耗费资源并且OLAP系统表的数据随时都在变化(因为实时在入库),那么就需要经常收集统计信息，所以OLAP系统收集统计信息不现实。

4. 统计信息的动态采样

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

据不同的optimizer_dynamic_sampling级别而不同，他总共有10个级别，分别如下：

Level 0: Do not use dynamic sampling.

Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: The number of blocks sampled is 2 times the default number of dynamic sampling blocks.

Level 3: The number of blocks sampled is 4 times the default number of dynamic sampling blocks.

Level 4: The number of blocks sampled is 8 times the default number of dynamic sampling blocks.

Level 5: The number of blocks sampled is 16 times the default number of dynamic sampling blocks.

Level 6: The number of blocks sampled is 32 times the default number of dynamic sampling blocks.

Level 7: The number of blocks sampled is 64 times the default number of dynamic sampling blocks.

Level 8: The number of blocks sampled is 128 times the default number of dynamic sampling blocks.

Level 9: The number of blocks sampled is 256 times the default number of dynamic sampling blocks.

Level 10: Read all blocks in the table

在一个系统中对临时表收集统计信息使用动态采样的方法

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

http://www.cnblogs.com/kerrycode/p/3854971.html

实验

对于新创建的表，当访问此表时，oracle会动态的收集这个表的相关信息，等到晚上10点，再将其收集到数据字典中。

[html] view plain copy

SQL> set autotrace off
SQL> set linesize 1000
SQL> drop table t_sample purge;
drop table t_sample purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table t_sample as select * from dba_objects;
Table created.
SQL> create index idx_t_sample_objid on t_sample(object_id);
Index created.

新建的表，查不到统计信息

[html] view plain copy

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = ‘T_SAMPLE’;
NUM_ROWS BLOCKS LAST_ANAL
———- ———- ———

[html] view plain copy

SQL> set autotrace traceonly
SQL> set linesize 1000
SQL> select * from t_sample where object_id=20;
Execution Plan
———————————————————-
Plan hash value: 1453182238
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_SAMPLE | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_SAMPLE_OBJID | 1 | | 1 (0)| 00:00:01 |
————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID”=20)
Note
—–
– dynamic sampling used for this statement (level=2)
Statistics
———————————————————-
24 recursive calls
0 db block gets
93 consistent gets
1 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

– dynamic sampling used for this statement (level=2) 表示动态采样，但是不记录数据字典，除非手动收集表的统计信息。

[html] view plain copy

SQL> select num_rows, blocks, last_analyzed from user_tables where table_name = ‘T_SAMPLE’;
NUM_ROWS BLOCKS LAST_ANAL
———- ———- ———
SQL>

Oracle统计信息（1）

统计信息

统计信息概述

如何收集统计信息

统计信息的内容：

收集统计信息的方式

2.3 自动收集统计信息

手动收集统计信息

表的统计信息

索引列的统计信息

列的统计信息

3. 统计信息的收集以及常用数据字典

3.1 统计信息常用数据字典

3.2 OLAP和OLTP的统计信息

4. 统计信息的动态采样

相关推荐

热门推荐

分类目录