文章目录
一.什么是统计信息
统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。
统计信息是存放在数据字典表中的，如tab$，一般可通过察看某些视图来获取统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS等。在这些视图中包含表示统计信息的一些字段，这些字段只有搜集过统计信息之后才有值，否则是空的。例如，last_analyzed 字段表示上次统计信息搜集的时间，可以根据这个字段，快速的了解最近一次统计信息搜集的时间。
二.使用dbms_stats收集统计信息
DBMS_STATS包，主要提供了搜集（gather），删除（delete），导出（export），导入（import），修改（set），查看（get）统计信息的方法。
1.收集统计信息
-
收集全库的统计信息
BEGIN dbms_stats.gather_database_stats( estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', options => 'GATHER AUTO', cascade => FALSE); END; /-
estimate_percent:采样的百分比，使用dbms_stats.auto_sample_size选项允许Oracle自动估算要采样的一个segment的最佳百分比。
-
method_opt选项适合在表和索引数据发生变化时刷新统计数据：
-
for table:只统计表
-
for all indexed columns:只统计有索引的表列
-
for all indexes:只分析统计相关索引
-
for all columns：分析所有的列
dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。
如果有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的选项，包括skewonly，repeat和auto：
- method_opt=>‘for all columns size skewonly’
- method_opt=>‘for all columns size repeat’
- method_opt=>‘for all columns size auto’
(1).skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。如果dbms_stat发现一个索引的各个列分布得不均匀，那么就会为该索引创建直方图，帮助基于成本的SQL优化器决定是进行索引访问，还是进行全表扫描访问。
(2).repeat选项在重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，应该采取这种方式。
(3).auto选项根据数据分布以及应用程序访问列的方式来创建直方图。
-
options控制Oracle统计信息的刷新方式:
-
gather:重新分析整个架构
-
gather empty:只分析目前还没有统计的表
-
gather stale:只重新分析修改量超过10%的表（包括插入、更新和删除）
-
gather auto:重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。使用gather auto类似于组合使用gather stale和gather empty[建议采用]
-
-
cascasde:是否级联分析索引,默认为false
-
-
收集schema统计信息
BEGIN dbms_stats.gather_schema_stats( ownname => 'DSG', options => 'GATHER AUTO', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade => FALSE); END; / -
收集单表的统计信息
BEGIN dbms_stats.gather_table_stats( ownname => 'DSG', tabname => 'T1', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade => FALSE); END; /
dbms_stats.gather_table_stats(
owner VARCHAR2,
tablename VARCHAR2,
partname VARCHAR2,
estimate_percent NUMBER,
block_sample BOOLEAN,
method_opt VARCHAR2,
degree NUMBER,
granularity VARCHAR2,
cascade BOOLEAN,
stattab VARCHAR2,
statid VARCHAR2,
statown VARCHAR2,
no_invalidate BOOLEAN,
force BOOLEAN
)
参数说明：
1.owner:要分析表的所有者
2.tablename：要分析的表的表名
3.partname：分区名
4.estimate_percent:采样行的百分比，从0.000001-100，null为全部分析，不采样。DBMS_STATS.AUTO_SAMPLE_SIZE是默认值，由Oracle决定最佳采样率。
5.block_sample:是否用块采样代替行采样。
6.method_opt：决定histograms信息是怎样被统计的，method_opt的取值如下：
- for all columns：统计所有的histograms
- for all indexed columns：统计所有index列的histograms
- for all hidden coloumns：统计hidden列的histograms
- for columns SIZE | REPEAT | AUTO | SKEWONLY 统计指定列的histograms，N的取值范围是0-254
7.degree:设置统计信息收集的并行度，默认值为null。
8.cascade：收集索引的统计信息，默认为false
9.stattab：指定存储统计信息的表。
10.statid：如果多个表的统计信息存储在一个stattab中时，statid用作分区条件。
11.statown：存储统计信息表的所有着。如果不指定上述三个参数，则统计信息会被更新到数据字典。
12.force:即使表锁住了也收集统计信息。
2.直方图
在Oracle中直方图是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。当 where 子句的值具有不成比例数量的数值时，将出现这种情况，使得全表扫描比索引访问的成本更低。这种情况下如果where 子句的过滤谓词列之上上有一个合理的正确的直方图，将会对优化器做出正确的选择发挥巨大的作用，使得SQL语句执行成本最低从而提升性能。
-
oracle中使用直方图的场景
在分析表或索引时，直方图用于记录数据的分布。通过获得该信息，基于成本的优 化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，可以在表的任何列上构建直方图。
构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划：例如，如果一到两个值构成了表中的大部分数据(数据偏斜)，相关的索引就可能无法帮助减少满足查询所需的I/O数量。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时应该根据WHERE子句中的值返回表中80％的记录。
通常情况下在以下场合中建议使用直方图：
（1）、当Where子句引用了列值分布存在明显偏差的列时：当这种偏差相当明显时，以至于 WHERE 子句中的值将会使优化器选择不同的执行计划。这时应该使用直方图来帮助优化器来修正执行路径。（注意：如果查询不引用该列，则创建直方图没有意义。这种错误很常见，许多 DBA 会在偏差列上创建柱状图，即使没有任何查询引用该列。）
（2）、当列值导致不正确的判断时：这种情况通常会发生在多表连接时，例如，假设我们有一个五项的表联接，其结果集只有 10 行。Oracle 将会以一种使第一个联接的结果集（集合基数）尽可能小的方式将表联接起来。通过在中间结果集中携带更少的负载，查询将会运行得更快。为了使中间结果最小化，优化器尝试在 SQL 执行的分析阶段评估每个结果集的集合基数。在偏差的列上拥有直方图将会极大地帮助优化器作出正确的决策。如优化器对中间结果集的大小作出不正确的判断，它可能会选择一种未达到最优化的表联接方法。因此向该列添加直方图经常会向优化器提供使用最佳联接方法所需的信息。
-
如何收集直方图
使用dbms_stata.gather_table_stats来收集统计信息是生成直方图是由参数method_opt来控制的method_opt参数的语法是由多个部分组成的.前两个部分是强制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column…]method_opt语法中的主要部分控制哪些列将收集列的统计信息(min,max,ndv,nulls).缺省是for all columns,它将会对表中所有的列(包括隐藏列)收集基本的列统计信息.
for all indexed columns将只对哪些包含索引的列进收集列统计信息.
for all hidden columns将只会对哪些虚拟列收集列统计信息.这意味着在对表收集统计时真实列是不会生成列统计信息的.这个值不能用于通常的统计信息收集.它只能用在当基表列的统计信息精确收集后在表中创建新的虚拟列.然后对新的虚拟列收集列统计信息时才使用它.
注意如果列不在统计信息收集列表中那么只会收集列的平均长度.
size用来指定直方图的桶数SIZE {integer | REPEAT | AUTO | SKEWONLY}
auto:基于列的使用信息(sys.col_usage$)和是否存在数据倾斜来收集直方图
integer:人为的指定创建直方图的桶数范围是1到254,如果size 1意味着不创建直方图
repeat:只会对已经存在直方图的列重新生成直方图.如果是一个分区表,repeat会确保对在全局级别存在直方图的列重新生成直方图.这是不被推荐的设置的.当前直方图的桶数将会作为重新生成直方图所使用的桶数的最大值.比如,当前直方图的桶数是5,那么生成的直方图最大桶数就是5,说的直白点就是刷新现有直方图的列上的统计信息.
skewonly:对任何数据分布出现倾斜列的自动创建直方图示例:
收集统计信息,但是不收集直方图
exec dbms_stats.gather_table_stats('DSG','T1',METHOD_OPT=>'for all columns size 1');收集统计信息,并且对t1表的flag列收集直方图
exec dbms_stats.gather_table_stats('DSG','T1',METHOD_OPT=>'for columns flag size skewonly');收集统计信息,并且收集所有列直方图
exec dbms_stats.gather_table_stats('DSG','T1',METHOD_OPT=>'for all columns size skewonly');
3.修改/查看统计信息参数
可以使用USE_STALE_PERCENT参数来指定统计信息的陈旧百分比为其它值.该值的阈值默认值为10%,但是可以重写,这样在行改变的百分比没有达到指定的阈值钱,统计信息不会被标记为陈旧
exec dbms_stats.set_database_prefs('STALE_PERCENT','25');
查询当前数据库的STALE_PERCENT值:
select dbms_stats.get_prefs('STALE_PERCENT') FROM DUAL;
select dbms_stats.get_prefs('STALE_PERCENT','DSG','T1') from dual;
4.删除统计信息
例如删除表统计信息
exec dbms_stats.delete_table_stats('DSG','T1');
5.导出导入统计信息
# 1.创建统计信息历史保留表
exec dbms_stats.create_stat_table(
ownname => '',
stattab => ''
)
# 2.导出整个scheme的统计信息
exec dbms_stats.export_schema_stats(
ownname => '',
stattab => ''
)
# 3.分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => '',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6
)
# 4.分析表
exec dbms_stats.gather_table_stats(
ownname => '',
tabname =>'',
estimate_percent => 10,
method_opt=> 'for all indexed columns'
)
# 5.分析索引
exec dbms_stats.gather_index_stats(
ownname => '',
indname => '',
estimate_percent => 10,
degree => 6
)
# 6.如果发现执行计划走错，删除表的统计信息
exec dbms_stats.delete_table_stats(
ownname => '',
tabname => ''
)
# 7.导入表的历史统计信息
exec dbms_stats.import_table_stats(
ownname => '',
tabname => '',
stattab => '')
6.其它常用功能
分析数据库（包括所有的用户对象和系统对象）：gather_database_stats
分析用户所有的对象（包括表、索引、簇）：gather_schema_stats
分析表：gather_table_stats
分析索引：gather_index_stats
删除数据库统计信息：delete_database_stats
删除用户方案统计信息：delete_schema_stats
删除表统计信息：delete_table_stats
删除索引统计信息：delete_index_stats
删除列统计信息：delete_column_stats
设置表统计信息：set_table_stats
设置索引统计信息：set_index_stats
设置列统计信息：set_column_stats
7.自动收集统计信息
oracle11g默认启用自动收集统计信息,当统计信息别标记为过期(行的变化百分比超过USE_STALE_PERCENT设置的阈值),那么就会在自动任务窗口中进行统计信息的收集.
首先查看scheduler program
oracle通过调度program来进行自动的收集统计信息:
SQL> select owner,program_name,program_action,enabled from dba_scheduler_programs where program_name='GATHER_STATS_PROG';
OWNER PROGRAM_NAME PROGRAM_ACTION ENABL
------------------------------ ------------------------------ -------------------------------------------------- -----
SYS GATHER_STATS_PROG dbms_stats.gather_database_stats_job_proc TRUE
执行的是dbms_stats.gather_database_stats_job_proc存储过程
查询自动收集统计信息的任务:
SELECT client_name, task_name, status
FROM dba_autotask_task
WHERE client_name = 'auto optimizer stats collection';
CLIENT_NAME TASK_NAME STATUS
----------------------- ----------------- ------------------------------ --------
auto optimizer stats collection gather_stats_prog ENABLED
查询自动任务客户端是否启用
SQL> SELECT CLIENT_NAME, STATUS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection';
CLIENT_NAME STATUS
---------------------------------------- --------
auto optimizer stats collection ENABLED
查询自动任务的频率
col REPEAT_INTERVAL for a60
col DURATION for a30
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';
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
------------------------------ ------------------------------------------------------------ ------------------------------ -----
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 TRUE
可以看到周一到周五每天的22:00开始,持续4小时,周六周日,6点开始,执行20小时.
-
启用自动收集
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; -
禁用自动收集
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END;查询自动任务客户端:
SQL> select client_name,status from dba_autotask_client; CLIENT_NAME STATUS ---------------------------------------- -------- auto optimizer stats collection DISABLED auto space advisor ENABLED sql tuning advisor ENABLED -
关闭某个单独的时间调度窗口
BEGIN DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection', operation => NULL, window_name => 'MONDAY_WINDOW'); END; /查询窗口调度, 验证关闭情况,如下，optimizer_stats列为DISABLED
SQL> 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; WINDOW_NAME WINDOW_NEXT_TIME WINDO OPTIMIZE ------------------------------ --------------------------------------------------------------------------- ----- -------- MONDAY_WINDOW 18-NOV-19 10.00.00.000000 PM PRC FALSE DISABLED4.关闭所有的窗口调度
BEGIN DBMS_AUTO_TASK_ADMIN.disable ( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /5.修改窗口属性
修改任务的持续时间，单位是分钟 BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name=>'"SYS"."FRIDAY_WINDOW"', attribute=>'DURATION', value=>numtodsinterval(180, 'minute')); END; 修改开始执行时间，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;
三.统计信息常用数据字典
- DBA_TABLES
- DBA_TAB_STATISTICS
- DBA_TAB_COL_STATISTICS
- DBA_TAB_HISTOGRAMS
- DBA_IND_STATISTICS
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
