统计信息

1、统计信息主要包括 6 种类型，其中表、列和索引的统计信息也可以统称为普通对象的统计信息，如下所示：

1、查询表统计信息的 SQL 如下所示：
SELECT D.NUM_ROWS, --表中的记录数
D.BLOCKS, --轰中数据所占的数据块数
D.EMPTY_BLOCKS, --表中的空块数
D.AVG_SPACE, --数据块中平均的，使用空间
D.CHAIN_CNT, --表中行连接和行迁移的数量
D.AVG_ROW_LEN, --每条记录的平均长度
D.STALE_STATS, --统计信息是否过期
D.LAST_ANALYZED --最近一次搜集统计信息的时间
FROM DBA_TAB_STATISTICS D --DBA_TAB_STATISTICS DBA_TABLES
WHERE D.TABLE_NAME = 'CUSTOMERS';

2、查询表上列的统计信息的 SQL 如下所示：

SELECT D.COLUMN_NAME,
D.NUM_DISTINCT, --唯一值的个数
D.LOW_VALUE, --列上的最小值
D.HIGH_VALUE, --列上的最大值
D.DENSITY, --若不存在柱状图的话,则表示选择率因子（密度）=1/(NDV)
D.NUM_NULLS, --空值的个数
D.NUM_BUCKETS, --直方图的 BUCKETS 个数
D.HISTOGRAM --直方图的类型
FROM DBA_TAB_COLUMNS D --DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'CUSTOMERS';

 

---DBA_TAB_MODIFICATIONS 视图（基表为 SYS.MON_MODS_ALL$）记录了从上次收集统计信息以来表中DML 操作变化的数据量，包括执行 INSERT、UPDATE 和 DELETE 影响的行数，以及是否执行过 TRUNCATE 操作。另外，DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 可以将内存中的数据快速刷新到数据字典SYS.MON_MODS_ALL$中

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SELECT TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP FROM USER_TAB_MODIFICATIONS WHERE TABLE_NAME='T_MON_20170602_LHR';

 

---收集表统计信息

DBMS_STATS.GATHER_TABLE_STATS('OWNER','TB_NAME',CASCADE=>TRUE);--普通表
DBMS_STATS.GATHER_TABLE_STATS(USER,'TB_NAME',PARTNAME=>'PT_PART_NAME',GRANULARITY=>'PARTITION',CASCADE=>TRUE);--针对分区表的单个分区进行收集统计信息
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TABLE_NAME','INCREMENTAL','TRUE'); --只收集数据变动的分区
SELECT DBMS_STATS.GET_PREFS('INCREMENTAL',NULL,'TABLE_NAME') FROM DUAL;--查看分区表 INCREMENTAL的值
EXEC DBMS_STATS.GATHER_DATABASE_STATS(USER);--收集当前数据库下所有用户的统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);--收集当前数据库用户下所有对象的统计信息

exec dbms_stats.gather_schema_stats('FENG',estimate_percent=>20,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,method_opt=>'for all columns size auto',force=>TRUE,cascade=>TRUE,degree=>4);

BEGIN
dbms_stats.gather_table_stats( ownname => 'NC60',
tabname => 'TEST',
estimate_percent => 100, --百分之百采样
block_sample => FALSE,
method_opt => 'FOR ALL COLUMNS SIZE 10', --收集直方图
granularity => 'ALL', --所有分区
cascade => TRUE --收集索引

no_invalidate ---true:表示不进行游标失效动作，原有的shared cursor保持原有状态。false:表示将统计量对象相关的所有cursor全部失效。auto_invalidate:Oracle自己决定shared cursor失效动作。
);
END;

 

2、索引统计信息 

BLEVEL 存储的就是目标索引的层级，它表示的是从根节点到叶子块的深度，BLEVEL 被 CBO 用于计算访问索引叶子块的成本。BLEVEL 的值越大，则从根节点到叶子块所需要访问的数据块的数量就会越多，耗费的
I/O 就会越多，访问索引的成本就会越大。BLEVEL 的值从 0 开始算起，当 BLEVEL 的值为 0 时，表示该 B 树索引只有一层，且根节点和叶子块就是同一个块。在 Oracle 数据库里，如果要降低目标 B 树索引
的层级，那么只能通过 REBUILD 该索引的方式来实现。

3、列的统计信息

1、列的统计信息用于描述 Oracle 数据库里列的详细信息，包含了列的 DISTINCT 值的数量、列的 NULL 值的数量、列的最小值、列的最大值等一些典型维度。这些列统计信息实际上是存储在数据字典基表
SYS.HIST_HEAD$中，可以通过数据字典 DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS 和DBA_SUBPART_COL_STATISTICS 来分别查看表、分区表的分区和分区表的子分区的列统计信息。在这些数
据字典中的字段 NUM_DISTINCT 存储的就是目标列的 DISTINCT 值的数量。CBO 用 NUM_DISTINCT 的值来评估用目标列做等值查询的可选择率（Selectivity）。CBO 会用 NUM_NULLS 的值来调整对有 NULL 值
的目标列做等值查询的可选择率。

数据字典中的字段 DENSITY 和 NUM_BUCKETS 分别存储的是目标列的密度和所用桶的数量，这两个维度仅和直方图有关。在没有直方图统计信息时，DENSITY 的值就等于
 I/NUM_DISTINCT；在有频率直方图的时，DENSITY 的值就等于 1/(2*(NUM_ROWS-NUM_NULLS))。

SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_NULLS,D.NUM_BUCKETS,D.HISTOGRAM,D.DENSITY FROM DBA_TAB_COLUMNS D WHERE D.TABLE_NAME = 'T_MD_20170606_LHR';  

2、直方图
在实际的生产系统中，有很多表的列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与 Cardinality，并据此来计算成本、选择执行计划，那么 
CBO 所选择的执行计划就很可能是不合理的，甚至是错误的，所以，此时应该收集列的直方图。

直方图实际上存储在数据字典基表 SYS.HISTGRM$中，可以通过数据字典 DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS 和 DBA_SUBPART_HISTOGRAMS 来分别查看表、分区表的分区和分区表的子分区的直方图
统计信息。

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
REPEAT：只对己经有直方图统计信息的列收集直方图统计信息。
AUTO：让 Oracle 自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
integer：直方图的 Bucket 的数量，必须是在 1~254 的范围内，1 表示删除该目标列上的直方图统计信息。

对 T 表上所有有索引的列以自动收集的方式收集直方图：FOR ALL INDEXED COLUMNS SIZE AUTO
对 T 表上的列 A 和列 B 以自动收集的方式收集直方图：FOR COLUMNS SIZE AUTO A B
对 T 表上的列 A 和列 B 收集直方图统计信息，同时指定 BUCKET 数量均为 10：FOR COLUMNS SIZE 10 A B
只删除表 T 上列 A 的直方图统计信息：FOR COLUMNS A SIZE 1
删除表 T 上所有列的直方图统计信息：FOR ALL COLUMNS SIZE 1

---注意事项

如果目标列的数据是均匀分布的（例如，主键列、唯一索引列），那么就不需要对这些列收集直方图统计信息。
对于那些从来没有在 WHERE 条件中出现过的列，无论其数据分布是否均匀，都无须对这些列收集直方图统计信息

3）多列统计信息

在一般情况下，SQL 语句的 WHERE 子句后面针对单张表都有多个条件，也就是根据多列的条件筛选得到数据。默认情况下，Oracle 会把多列的选择率（Selectivity）相乘从而得到 WHERE 语句的选择率，但是这样有可能造成选择率不准确，从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断，从而生成准确的执行计划，Oracle 在 11g 数据库中引入了收集多列统计信息。多列统计信息包含列组统计信息（Column Group Statistics）和表达式的统计信息（Expression Statistics）

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'TEST',
TABNAME => 'T',
EXTENSION => '(UPPER(PAD))'),
DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME => 'TEST',
TABNAME => 'T',
EXTENSION => '(VAL2,VAL3)')
FROM DUAL;

以上 SQL 是对 TEST 用户下的 T 表，分别基于表达式和基于多列创建虚拟列，下次再收集表的统计信息时，将会自动收集到多列统计信息。需要注意的是，不能对 SYS 用户下的表创建扩展的统计信息，否则会
报错“ORA-20000: Unable to create extension: not supported for SYS owned table”。

---使用 Oracle 自带的 DBMS_STATS 包提供的存储过程 DROP_EXTENDED_STATS 来删除扩展统计信息：
EXEC DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME => 'TEST',TABNAME => 'T',EXTENSION =>
'(UPPER(PAD))');
EXEC DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME => 'TEST',TABNAME => 'T',EXTENSION =>
'(VAL2,VAL3)');

---查询定义的扩展统计信息

SELECT EXTENSION_NAME, EXTENSION FROM DBA_STAT_EXTENSIONS WHERE TABLE_NAME='BOOKS';

4）基表 COL_USAGE$ 的作用是什么

从 Oracle 9i 开始引入了 SYS.COL_USAGE$表用来跟踪列的使用情况，该功能通过隐含参数“_COLUMN_TRACKING_LEVEL”来控制。若隐含参数“_COLUMN_TRACKING_LEVEL”的值为 0 则取消该功
能，若隐含参数“_COLUMN_TRACKING_LEVEL”的值为 1 则表示该功能生效。缺省情况下，该功能是生效的，并且CBO负责将 SQL 语句中 WHERE 条件的查询谓词信息保存在该表中，数据库在执行 SHUTDOWN
NORMAL或者SHUTDOWN IMMEDIATE会自动将该表中的数据清空。

---查询出表上列的使用情况
SELECT OO.NAME OWNER,
O.NAME TABLE_NAME,
C.NAME COLUMN_NAME,
U.EQUALITY_PREDS,
U.EQUIJOIN_PREDS,
U.NONEQUIJOIN_PREDS,
U.RANGE_PREDS,
U.LIKE_PREDS,
U.NULL_PREDS,
U.TIMESTAMP
FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C
WHERE O.OBJ# = U.OBJ#
AND OO.USER# = O.OWNER#
AND C.OBJ# = U.OBJ#
AND C.COL# = U.INTCOL#
and oo.name='SVSCOMMON' AND o.name='S_CSQ';

可以通过执行存储过程“EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;”或收集直方图的方式“EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T1');”来将
内存中的统计数据刷新到 SYS.COL_USAGE$表中

4、系统统计信息

收集系统统计信息的方法主要是使用系统存储过程：
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('start');
系统正常负载运行一段时间
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('stop');
或：
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'INTERVAL',INTERVAL =>1);--INTERVAL 为间隔
时长，单位为分钟

系统统计信息主要存储在 SYS.AUX_STATS$表中，从 Oracle 9i 开始，Oracle 通过一个隐含参数“_OPTIMIZER_COST_MODEL”来控制是否开启 CPU Cost model
SYS@orclasm > SELECT * FROM SYS.AUX_STATS$;

---数据字典统计信息

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS(degree=>8);

---动态性能视图统计信息

exec dbms_stats.gather_fixed_objects_stats;

5、自动收集统计信息



新建索引后统计信息是否自动收集？


在 Oracle 10g 后有个隐含参数“_OPTIMIZER_COMPUTE_INDEX_STATS”，意思是是否对新建索引收集统计
信息，该参数默认是 TRUE，表示默认收集新建索引的统计信息。


Oracle 的初始化参数 STATISTICS_LEVEL 控制收集统计信息的级别，有三个参数值：


    

  • BASIC：收集基本的统计信息
    • 

  • TYPICAL：收集大部分统计信息（数据库的默认设置）
    • 

  • ALL：收集全部统计信息
    • 









---自动统计信息收集（10g）


方法一：


exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');


exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');


方法二：


alter system set "_optimizer_autostats_job"=false scope=spfile;


alter system set "_optimizer_autostats_job"=true scope=spfile;


---自动统计信息收集（11gR2）


查看状态


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);


---运行日志




SELECT JRD.LOG_ID,
JRD.JOB_NAME,
N.JOB_CLASS,
TO_CHAR(JRD.ACTUAL_START_DATE, 'YYYY-MM-DD HH24:MI:SS') ACTUAL_START_DATE,
TO_CHAR(JRD.LOG_DATE, 'YYYY-MM-DD HH24:MI:SS') LOG_DATE,
JRD.STATUS,
JRD.ERROR#,
JRD.RUN_DURATION,
JRD.ADDITIONAL_INFO
FROM DBA_SCHEDULER_JOB_LOG N, DBA_SCHEDULER_JOB_RUN_DETAILS JRD
WHERE N.LOG_ID = JRD.LOG_ID
AND N.JOB_NAME LIKE 'ORA$AT_OS_OPT_%' --11g
-- AND N.JOB_NAME = 'GATHER_STATS_JOB' --10g
ORDER BY JRD.LOG_ID DESC;




b)统计信息何时变为陈旧状态（10% 的含义）


10%的含义：如果表 A 有10万行数据，那么当表的数据量变化（DELETE、UPDATE、INSERT）超过1万行的时候，这个时候，系统的自动收集统计信息的任务才会对表 A 去收集统计信息。


在 Oracle 11g 中，这个 10%（STALE_PERCENT）是可以修改的，分为全局（DBMS_STATS.SET_GLOBAL_PREFS）、数据库级别（DBMS_STATS.SET_DATABASE_PREFS）、用户级
别（DBMS_STATS.SET_SCHEMA_PREFS）和表级别（DBMS_STATS.SET_TABLE_PREFS）。其中，数据库级别和用户级别都是调用表级别的存储过程 DBMS_STATS.SET_TABLE_PREFS 来对表进行设置的。




表级别的设定如下所示：
 修改为 5%（范围从 1-100）： EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',5);
 恢复为 10%： EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'TB_NAME','STALE_PERCENT',NULL);
 查询表百分比： SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'TB_NAME') FROM DUAL;
 查询全局百分比： SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT') FROM DUAL;




c)修改自动收集统计信息的时间






Oracle 10g 的自动统计信息收集功能没有资源限制，但 Oracle 11g 的统计信息收集功能在资源管理上面限制了对系统资源使用，其对应的 RESOURCE_PLAN 的名称为 DEFAULT_MAINTENANCE_PLAN。
用户可以根据各自系统的业务场景来配置是否开启自动收集统计信息，也可以调整窗口调度的开始时间、持续时间和资源组限制等。


SET line 9999 PAGESIZE 9999
col WINDOW_NAME format a18
col REPEAT_INTERVAL format a55
col DURATION format a15
col resource_plan format a25
SELECT T1.WINDOW_NAME,
T1.REPEAT_INTERVAL,
T1.DURATION,
T1.ENABLED,
T1.RESOURCE_PLAN
FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2
WHERE T1.WINDOW_NAME = T2.WINDOW_NAME
AND T2.WINDOW_GROUP_NAME IN
('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');






begin
sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval',
value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value
=> '0 05:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute =>
'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration',
value => '0 05:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute =>
'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration',
value => '0 05:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute =>
'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration',
value => '0 05:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval',
value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value
=> '0 05:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute =>
'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration',
value => '0 10:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval',
value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value
=> '0 10:00:00');
end;
/








 6、动态采样




有两种方法可以开启动态采样：
（1）将参数 OPTIMIZER_DYNAMIC_SAMPLING 的值设为大于或等于 1。从 Oracle 10g 开始，该值默认为2，若设置为 0，则禁用动态采样。
（2）使用动态采样的 Hint：DYNAMIC_SAMPLING(T LEVEL)。该 Hint 表示对目标表 T 强制使用等级为参数 level 指定值的动态采样。
 eg:select /*+ dynamic_sampling (my_table 2) */ * from my_table;

默认采样数据块数量受隐含参数“_OPTIMIZER_DYN_SMP_BLKS”的控制，其默认值是 32，表示动态采样时默认采样数据块数量为 32。
采样的数据块越多，得到的分析数据就越接近于真实，但同时伴随着资源消耗也越大。


引入动态采样有如下几方面的作用：
1 CBO 依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle 需要使用动态采样技术来帮助 CBO 获取尽可能多的信息。
2 全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO 要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。
3 为了相对准确地估算出当目标 SQL 语句 WHERE 条件中出现有关联关系的列时整个 WHERE 条件的组合可选择率，进而能相对准确地估算出返回结果集的 Cardinality。动态采样除了可以在段对象没有分析
时，给 CBO 提供分析数据之外，还可以对不同列之间的相关性做统计。
4 在 Oracle 11gR2 开始，Oracle 对动态采样进行了增强。在 Oracle 提供的增强特性中，对于并行或大表的复杂条件，即使表上存在统计信息，Oracle 也会开启动态采样的功能，试图来更精准的评估返回结
果集的记录数，并且自行定义动态采样的级别，Oracle 会忽略 OPTIMIZER_DYNAMIC_SAMPLING 参数或提示Hint 的 DYNAMIC_SAMPLING 值，而自行决定采样级别


可以通过“alter session set "_fix_control"='7452863:OFF';”或关闭表的并行来屏蔽该动态采样的增强特性。


动态采样的一些缺点如下所示：
1 采样的数据块有限，对于海量数据的表，结果难免有偏差。
2 采样会消耗系统资源，特别是 OLTP 数据库，尤其不推荐使用动态采样。动态采样也需要额外的消耗数据库资源。在 OLTP 系统中，SQL 被反复执行，变量被绑定，硬解析很少，在这样一个环境中，是不
宜使用动态采样的。在 OLAP 或者数据仓库环境下，SQL 执行消耗的资源要远远大于 SQL 解析，那么让解析在消耗多一点资源做一些动态采样分析，从而做出一个最优的执行计划是非常值得的。所以，一般在
OLAP 或者数据仓库环境中，将动态采样的 level 设置为 3 或者 4 比较好。相反，在 OLTP 系统下，尽量避免使用动态采样。







7、锁住统计信息




EXEC DBMS_STATS.LOCK_TABLE_STATS('table_owner','table_name'); --表
EXEC DBMS_STATS.LOCK_PARTITION_STATS(); --分区表
EXEC DBMS_STATS.LOCK_SCHEMA_STATS(schema); --锁定用户统计信息
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS(schema);--解锁用户统计信息


  exec dbms_stats.unlock_table_stats('table_owner','table_name');   ---解锁表


---查询某用户统计信息被锁的表或索引：


SELECT D.OWNER, D.INDEX_NAME, D.TABLE_OWNER, D.TABLE_NAME, D.PARTITION_NAME,
D.SUBPARTITION_NAME, D.OBJECT_TYPE
FROM DBA_IND_STATISTICS D
WHERE STATTYPE_LOCKED = 'ALL' and owner='SVSCOMMON'
UNION ALL
SELECT '', '', D.OWNER, D.TABLE_NAME, D.PARTITION_NAME, D.SUBPARTITION_NAME, D.OBJECT_TYPE
FROM DBA_TAB_STATISTICS D
WHERE STATTYPE_LOCKED = 'ALL' and owner='SVSCOMMON';







8、待定的统计信息（Pending Statistic）




在 Oracle 11g 中，推出了统计信息管理的一种新技术——待定的统计信息（Pending Statistic）技术。简单的说，DBA 可以对一系列的数据表设置 PENDING 属性。设置 PENDING 属性之后，数据的统计信息在数
据字典中相当于已经锁定。当新的统计信息生成之后，不是直接替换原有的数据，而是存放在 PENDING 数据字典中

---查询统计信息在全局、SCHEMA 和表级别是否自动发布（默认情况下都是自动发布）：
SELECT DBMS_STATS.GET_PREFS('PUBLISH') GLOBAL,DBMS_STATS.GET_PREFS('PUBLISH','SVSCOMMON') SCHEMA,DBMS_STATS.GET_PREFS('PUBLISH','SVSCOMMON','S_CSQ') TB_LEVEL FROM 
DUAL;

---禁止自动发布


    

  • 全局：EXEC DBMS_STATS.SET_GLOBAL_PREFS(PNAME=>'PUBLISH',PVALUE=>'FALSE');
    • 

  • SCHEMA：EXEC DBMS_STATS.SET_SCHEMA_PREFS(OWNNAME=>USER,PNAME=>'PUBLISH',PVALUE=>'TRUE');
    • 

  • 表：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'T_LHR','PUBLISH','FALSE');
    • 



缺省情况下，优化器使用数据字典视图中已发布的统计信息。如果希望优化器使用新收集的待定统计信息，那么可以设置初始化参数 OPTIMIZER_USE_PENDING_STATISTICS 的值为 TRUE（缺省值为 FALSE）。


ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;


可以使用下面的 SQL 语句为一个特定的数据对象发布待定统计信息：


EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');


如果不想发布待定的统计信息，那么可以执行下面的语句删除这些待定的统计信息：


EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');


---查询某个表统计信息版本


SELECT H.TABLE_NAME, TO_CHAR(H.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME FROM USER_TAB_STATS_HISTORY H WHERE H.TABLE_NAME = '';


---还原到某个时间点的统计信息


EXEC DBMS_STATS.RESTORE_TABLE_STATS(OWNNAME => USER,TABNAME =>'T_PS_20170605_LHR',AS_OF_TIMESTAMP => TO_DATE('2017-06-05 15:54:17','YYYY-MM-DD HH24:MI:SS'));




9、基数反馈（Cardinality Feedback ）


在SQL第一次执行时，记录存储实际的基数和评估的基数之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的基数重新决策生成执行计划，但是需要注意的是，当使用更准确的基数重新生成执行计划时，生成的执行计划与第一次时使用的执行计划完全有可能是相同的。这个技术的出现是由于优化器在一些情况下不能很好的去计算基数的数值，比如：统计信息缺失或陈旧、多谓词、直方图缺失等等。


---Oracle 只针对下面情况开启 CFB：
1 没有收集表的统计信息，并且动态采样（Dynamic Sampling）也没有开启。
2 查询条件复杂（比如条件有函数）或者涉及多列，但却没有收集扩展的统计信息（Extended Statistics）。


在这几种情况下，CBO 是无法估算出准确的 Cardinality 的。针对上述情况，Oracle 会监控操作的实际行数（A-Row），然后对比 CBO 估算的行数（E-Row）。如果两个值相差很大，那么就记录实际行


（A-Row），做上标记。下次执行时再次进行硬解析，根据实际行数来重新生成执行计划。如果两个值相差不大，那么 CBO 就不再监控这条 SQL 语句。




专门在 V$SQL_SHARED_CURSOR 中增加了 USE_FEEDBACK_STATS 列来记录SQL 是否使用了基数反馈。基数反馈的开启和关闭通过一个隐含参数“_OPTIMIZER_USE_FEEDBACK”来控制，该参数默认为 TRUE，
表示开启技术反馈特性。此参数除了可以在 SESSION 和 SYSTEM 级别进行设置之外，还可以在 SQL 语句级使用 Hint 进行开启和关闭，如下所示：

SELECT /*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'FALSE') */ COUNT(*) FROM TEST;
SELECT /*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'TRUE') */ COUNT(*) FROM TEST;

如果动态采样被启用，那么是不会使用基数反馈特性的。若使用了该特性则在执行计划的 Note 部分可以看到“cardinality feedback used for this statement”字样
由于在 Oracle 11g 中存在过多的 Bug，常见的问题就是在第二次执行 SQL 时候性能下降很多。因此在 Oracle 11g 的数据库中往往会对 11.2.0.4 以下的数据库会将该特性关闭




10、查询表和索引的历史统计信息




---查询历史收集统计信息的时间
SELECT B.OWNER,
B.OBJECT_NAME TABLE_NAME,
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
D.ROWCNT
FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
WHERE D.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME IN
('TEST_STAT', 'TPCCBOKBAL_TMP')
ORDER BY D.OBJ#, D.SAVTIME;


---查询索引的历史统计信息的 SQL 语句
SELECT B.OWNER,
B.OBJECT_NAME INDEX_NAME,
TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
D.ROWCNT,
D.BLEVEL,
D.LEAFCNT,
D.DISTKEY,
D.CLUFAC
FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
WHERE D.OBJ# = B.OBJECT_ID
AND B.OBJECT_NAME IN ('IND_TEST')
ORDER BY D.OBJ#, D.SAVTIME;








默认情况下统计信息将被保留 31 天，可以使用下面的命令修改：
EXECUTE DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XX); --xx 是保留的天数
注意：这些统计信息在 SYSAUX 表空间中占有额外的存储开销，所以应该注意并防止统计信息将表空间填满。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;--查询统计信息当前保留的天数。

下面的查询返回统计信息已经被删除到的日期（所以只有在这日期之后的统计信息才可能被恢复）。
任何恢复到比这日期旧的统计信息的请求都会失败：“ORA-20006: Unable to restore statistics , statistics history not available”：
SELECT TO_CHAR(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

查询到可以恢复统计信息到某一个比较好的时间之后，可以执行下面的命令进行恢复：
EXECUTE DBMS_STATS.RESTORE_TABLE_STATS ('OWNER','TABLE',DATE);--恢复表的统计信息
EXECUTE DBMS_STATS.RESTORE_DATABASE_STATS(DATE);--恢复数据库的统计信息
EXECUTE DBMS_STATS.RESTORE_DICTIONARY_STATS(DATE);--恢复数据字典的统计信息
EXECUTE DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(DATE);--恢复固定表的统计信息
EXECUTE DBMS_STATS.RESTORE_SCHEMA_STATS('OWNER',DATE);--恢复某个用户的统计信息
EXECUTE DBMS_STATS.RESTORE_SYSTEM_STATS(DATE);--恢复 SYSTEM 的统计信息

可以通过如下的命令返回 2 次统计信息的比较结果：
SELECT *
FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(OWNNAME => 'TPLHR',
TABNAME => 'TPLHR_AB',
TIME1 => TO_TIMESTAMP('2016-09-07
10:24:45','YYYY-MM-DD HH24:MI:SS'),--SYSTIMESTAMP
TIME2 => TO_TIMESTAMP('2016-09-07
10:29:22','YYYY-MM-DD HH24:MI:SS')));




11、并发地收集统计信息


对于大表的统计信息收集可以通过 DEGREE 参数使得扫描大表的时候进行并行扫描，从而加快扫描速度，缩短了收集统计信息的时间。但是，即使加了 DEGREE 参数，在收集统计信息的时候，还是进行一个表一个表的扫描，并没有并发的同时扫描多个表。在 Oracle 11.2.0.2 之后，有了一个参数，可以并发扫描表，这就是 CONCURRENT 参数。可以通过以下 SQL 语句查询数据库是否启用了 CONCURRENT 收集统计信息，默认为 FALSE，表示没有开启并发收集统计信息：




SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
开启方式为:
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');






开启 CONCURRENT 之后，收集统计信息就会以并发的形式进行，会并发出多个 JOB 进程。在并发收集统计信息时，数据库生成的 JOB 数会根据具体情况来分配。在大多数情况下，DBMS_STATS 程序会给每个
对象分配一个 JOB；但如果对象（表或者分区）的大小太小，为了节省资源，Oracle 会合并多个表和分区在一个 JOB 中执行。为了防止同时处理多个分区表的分区时发生死锁，所以，对于分区表的处理机制是每
次只能处理一个分区表，其它的分区表需要等待，待前一个分区表处理完后再处理下一个。在 Oracle11.2.0.2 到 11.2.0.4 的版本上，CONCURRENT 可取的值为 TRUE（开启并发）和 FALSE（关闭并发）。在
Oracle 12c 的版本上，可以设置以下的值：

MANUAL：只有当手动收集时，并发有效
AUTOMATIC：只有当自动收集时，并发有效
ALL：当手动/自动收集，并发都有效
OFF：并发无效






---监控并发收集统计信息 JOB 的 SQL 代码如下：
SELECT JOB_NAME, STATE, COMMENTS
FROM DBA_SCHEDULER_JOBS
WHERE JOB_CLASS LIKE 'CONC%';
SELECT STATE,COUNT(*)
FROM DBA_SCHEDULER_JOBS
WHERE JOB_CLASS LIKE 'CONC%'
GROUP BY STATE;




对于并发收集统计信息需要注意如下几点：


（1）用 CONCURRENT 收集统计信息，需要收集统计信息的用户具有 CREATE JOB、MANAGE SCHEDULER 和 MANAGE ANY QUEUE 权限。即使是该用户具有了 DBA 角色，也还是需要显式授权上述权限。否则执行 JOB 的时候，可能会报错：“ORA-27486 insufficient privileges”、“ORA-20000: Statistics collection failed for 32235 objects in the database”。
（2）因为 CONCURRENT 不能控制并发度的大小，所以，如果数据库的初始化参数JOB_QUEUE_PROCESSES 设置的太高（在 Oracle 11.2.0.3 之后，这个值的默认值是 1000，所以就可能并发出1000 个 JOB），那么对数据库的性能影响较大。所以开启 CONCURRENT 的另外一个建议就是使用 ResourceManager 来控制资源的使用。
（3）下表列出了并发和并行在收集统计信息方面的一些区别：




12、关于收集统计信息需要注意以下几点：




1 对于数据量不大的 OLTP 类型的系统，建议使用自动收集统计信息，并对一些特殊的大表写 JOB定时收集统计信息。如果是数据量很大的 OLAP 或者 DSS 系统，那么建议 DBA 自己写 JOB 脚本来收集统计信息。
2 在导入大量数据后应及时收集统计信息后才能进行相关的后续业务处理（包括查询和修改），否则可能会由于实际数据量和统计信息里记录的数据量存在巨大差异而导致 CBO 选择错误的执行计划。
3 全局临时表默认不能收集统计信息，在生成执行计划时采用动态采样比较好。
4 对于某些新上线或新迁移的系统，建议进行全库收集一次统计信息。
5 建议及时对包含日期型字段的表收集统计信息，避免出现谓词越界现象。
6 统计信息收集作业采样比例：对于 Oracle 11g 及其以上的版本收集统计信息的采样比例建议采用DBMS_STATS.AUTO_SAMPLE SIZE。如果是 Oracle 10g，那么建议将采样比例的初始值设为 30％，然后根据目标
 SQL 的实际执行情况再做调整。
7 系统统计信息：如果系统的硬件环境发生了变化，那么建议要额外收集一次系统统计信息。
8 内部对象统计信息：在明确诊断出系统已有的性能问题是因为 X$表的内部对象统计信息不准引起的，这个时候就应该收集 X$表的内部对象统计信息，其它情形就不要收集了。
9 表的大小、是否并行：若表很大，而系统空闲，则可以使用并行来收集统计信息。
10 表是否分区：若是分区表则建议收集全局的统计信息并且收集数据量有变更的单个分区（加GRANULARITY 和参数并设置属性 INCREMENTAL）的统计信息。
11 是否收集索引的统计信息：一般情况下都应该收集索引的统计信息。
12 是否收集直方图。对直方图统计信息的收集策略是对已经存在直方图统计信息的列才收集直方图统计信息，而目标列的初次直方图统计信息则是由了解系统的 DBA 手工来收集直方图。设置 METHOD_OPT的值为
“FOR ALL COLUMNS SIZE REPEAT”。
13 是否可以并发收集统计信息：若系统有很多小表，则可以考虑并发收集统计信息。
14 系统的负载情况：在手动收集统计信息的时候需要注意系统的负载情况。
15 预估多久可以收集完成：对 OLAP 系统的大表而言，根据平时收集统计信息的经验要预估出收集统计信息要花费多长的时间。
16 基于数据库、SCHEMA 或是表级别：根据情况判断是否有必要在数据库或 SCHEMA 级别来收集统计信息。
17 是否需要收集扩展列的统计信息。如果表中的数据倾斜度较大，那么收集直方图能最大程度的帮助优化器计算出准确的 Cardinality，从而避免产生差的执行计划；再进一步，如果存在倾斜的多个列共同构成了 
Predicate 里的等值连接且这些列间存在较强的列相关性的话，那么生成带有直方图的多列统计信息是一个上佳的选择，能够最大程度的帮助优化器准确预测出 Cardinality。
18 是否设置 NO_INVALIDATE 为 FALSE。该选项有 TRUE、FALSE 和 DBMS_STATS.AUTO_INVALIDATE 这 3个值。如果取值为 TRUE，那么表示收集统计信息后不进行游标失效动作，原有的 Shared Cursor
保持原有状态。如果取值为 FALSE，那么表示将统计信息对象相关的所有 Cursor 全部失效。如果设置为AUTO_INVALIDATE，那么 Oracle 自己决定 Shared Cursor 失效动作，当 SQL 再次执行时间距离上次收集
统计信息的时间超过 5 小时（隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定）则对 SQL 重新做硬解析。AUTO_INVALIDATE 为默认选项。有些 DBA 在收集统计信息时，没有使用 NO_INVALIDATE=>FALSE 
选项，所以，即使收集了统计信息，执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效，设置方法为：
EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集 SH.SALES 表上的统计信息时，让所有依赖于该表的游标不失效
19 对于 OLTP 类型的数据库，需要特别关注 DML 比较频繁的以及数据加载比较大的表及分区表。
20检查是否有临近统计信息收集窗口的数据加载工作，如果有，是否能在数据库统计信息的窗口时间完成，如果不能在窗口时间完成，那么应该针对这段时间加载的数据，特别是大量的数据，在相关加载脚本完成之后，
加入统计信息的收集。
21 如果加载数据量比较大，并且是分区表，每个分区的业务数据呈现的是均匀的，在 Oracle 11g 可以考虑采用 DBMS_STATS.COPY_TABLE_STATS 先把统计信息做个快速的设置，然后，再收集该分区的统计信息。




13、什么是基数（Cardinality ）和可选择率（Selectivity ）？


基数（Cardinality）是 Oracle 预估的返回行数，即对目标 SQL 的某个具体执行步骤的执行结果所包含记录数的估算值。如果是针对整个目标 SQL，那么此时的 Cardinality 就表示该 SQL 最终执行结果所包含记录数的估算值。例如，一张表 T 有 1000 行数据，列 COL1 上没有直方图，没有空值，并且不重复的值（Distinct Value）有 500 个。那么，在使用条件“WHERE COL1=<VALUE>”去访问表的时候，优化器会假设数据均匀分布，它估计出会有 1000/500=2 行被选出来，2 就是这步操作的 Cardinality。通常情况下，Cardinality 越准确，生成的执行计划就会越高效。


可选择率（Selectivity） ）是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。可选择率的取值范围显然是 0～1，它的值越小，就表明可选择性越好。当可选择率为 1 时的可选择性是最差的。CBO 就是用可选择率来估算对应结果集的 Cardinality 的，可选择率和Cardinality 之间的关系如下所示：


cardinality=NUM_ROWS*selectivity


 14、对表执行 TRUNCATE 操作会将表的统计信息也清除掉吗？


不会。对表执行 TRUNCATE 操作，表及其索引的统计信息都不会被清除掉，除非使用系统包DBMS_STATS 中的相关 DELETE 存储过程才能将表或索引的统计信息清除掉。


 






		

		
posted @ 
2021-08-24 16:08 
harrison辉 
阅读(586) 
评论(0) 
 
收藏 
举报