2.1_9 Oralce 收集统计信息方式1_SYS.DBMS_STATS

GroupiesM

已于 2022-05-10 12:28:49 修改

阅读量4k

点赞数

分类专栏： # 2.1 Oracle 文章标签：数据库

于 2020-09-28 17:48:07 首次发布

本文链接：https://blog.csdn.net/qq_43529621/article/details/108767270

版权

2.1 Oracle 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

本文详细介绍了Oracle数据库中DBMS_STATS包的使用，包括自动收集统计信息的配置、查看任务状态、停止和开启任务的方法。重点讨论了DBMS_STATS常用语句，如gather_schema_stats，以及各种参数的含义和用法，如estimate_percent、method_opt和degree等。此外，还涵盖了统计信息的删除和设置，以及如何调整自动收集任务的时间窗口以优化数据库性能。

摘要由CSDN通过智能技术生成

一. SYS.DBMS_STATS

1.1 Constants 常量

The DBMS_STATS package uses the constants shown in Table 142-1:
Table 142-1 DBMS_STATS Constants

Name 名称	Type 类型	Value 值	Description 描述
AUTO_CASCADE	BOOLEAN	NULL	Lets Oracle decide whether to collect statistics for indexes or not 让Oracle决定是否收集索引的统计信息
AUTO_DEGREE	NUMBER	32768	Lets Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters. For definition of default parallel degree, see “Degree of Parallelism” in Oracle Database VLDB and Partitioning Guide. 让Oracle根据对象的大小、cpu的数量和初始化参数选择并行度。有关默认并行度的定义，请参阅Oracle数据库VLDB和分区指南中的"并行度"。
AUTO_INVALIDATE	BOOLEAN	NULL	Lets Oracle decide when to invalidate dependent cursors 让Oracle决定何时使依赖游标失效
AUTO_SIMPLESIZE	NUMBER	0	Indicates that auto-sample size algorithms should be used 表示自动样本大小应该使用的算法

2 自动收集统计信息

2.1 查看自动收集统计信息任务状态

SQL> select client_name,status from Dba_Autotask_Client where client_name='auto optimizer stats collection';

CLIENT_NAME                                                      STATUS
------------------------------------------------------------------------
auto optimizer stats collection                                  ENABLED

　　oracle 11g 中统计信息自动收集任务的名称是 auto optimizer stats collection。11g中自动任务默认的执行时间窗口(oracle时间窗口介绍)为：

周一到周五 10:00p 点开始到 2 点结束
周末 6:00 am，持续 20 个小时。

　　这期间一般服务器压力比较小。服务器的资源可以留下来收集统计信息了，收集统计信息也是比较耗资源的。可以通过如下语句查看：

SQL> set line 234
SQL> set pagesize 120
SQL> col WINDOW_NEXT_TIME fora34                                                                                                         
SQL> select 
WINDOW_NAME,
WINDOW_NEXT_TIME,
WINDOW_ACTIVE,OPTIMIZER_STATS 
from DBA_AUTOTASK_WINDOW_CLIENTS 
order by WINDOW_NEXT_TIME ;

WINDOW_NAME                    WINDOW_NEXT_TIME                   WINDO OPTIMIZE
---------------------------------------------------------------- ----- --------
TUESDAY_WINDOW                22-SEP-20 10.00.00.000000 PM  PRC  FALSE  ENABLED
WEDNESDAY_WINDOW              23-SEP-20 10.00.00.000000 PM  PRC  FALSE  ENABLED
THURSDAY_WINDOW               24-SEP-20 10.00.00.000000 PM  PRC  FALSE  ENABLED
FRIDAY_WINDOW                 25-SEP-20 10.00.00.000000 PM  PRC  FALSE  ENABLED
SATURDAY_WINDOW               26-SEP-20 06.00.00.000000 AM  PRC  FALSE  ENABLED
SUNDAY_WINDOW                 27-SEP-20 06.00.00.000000 AM  PRC  FALSE  ENABLED
MONDAY_WINDOW                 28-SEP-20 10.00.00.000000 PM  PRC  FALSE  ENABLED



7 rows selected.

执行此命令时间为2020-09-22 15:22 显示最近7条将要采集统计信息的时间

　　如上，每天都有统计信息的任务在执行，但如果夜间数据库服务器压力比较重，负载高，这个任务就不一定会执行了。则需要调整这个任务及时间了。可以禁用也可以修改，但一般都不会禁用。则需要修改时间窗口到特定的时间

--查询现在周五采集统计信息时间
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        ENABLED
	-------------   ------------------------------------------------------  -------------   --------
	FRIDAY_WINDOW   freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00   TRUE

--如下示例，将周五时间窗口时间到晚间23点30分
--1。DBMS_SCHEDULER.DISABLE 
       BEGIN
          DBMS_SCHEDULER.DISABLE (name =>'"SYS"."FRIDAY_WINDOW"', force => TRUE);
       END;

--2.DBMS_SCHEDULER.SET_ATTRIBUTE 
       BEGIN
          DBMS_SCHEDULER.SET_ATTRIBUTE (
              name        =>'"SYS"."FRIDAY_WINDOW"',
              attribute   => 'REPEAT_INTERVAL',
              VALUE       =>'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0');
       END;

--3.DBMS_SCHEDULER.ENABLE 
       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

2.2 停止和开启单个任务（即停止某一日任务）

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;

相关视图：

dba_autotask_task
dba_autotask_client
dba_autotask_client_job
dba_autotask_window_clients
dba_autotask_client_history

dba_scheduler_jobs
dba_scheduler_job_classes
dba_scheduler_window_groups
dba_scheduler_windows
dba_scheduler_wingroup_members

　　dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

3 常用DBMS_STATS语句

以gather_schema_stats为例 => 142-43 SYS.DBMS_STATS.gather_schema_stats
Gathers statistics for all objects in a schema
收集方案所有对象的统计信息
这里涉及到另外一个点，11g起 Oralce不一定会按照你指定的estimate_percent指定的比率进行采样
- 见=> Oracle为什么不按照estimate_percent来分析表

exec
 dbms_stats.gather_schema_stats( 
 ownname          => 'SCOTT', 
 estimate_percent => dbms_stats.auto_sample_size, 
 method_opt       => 'for all columns size repeat', 
 degree           => 15 ,
 options          => 'GATHER AUTO' )

options参数 => 控制Oracle统计的刷新方式：
- gather：重新分析整个架构（Schema）。
- gather empty：只分析目前还没有统计的表。
- gather stale：只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
- gather auto：重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。
　　注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。
　　这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
estimate_percent参数 => 采样比例
estimate_percent 参数是一种比较新的设计，可以指定全部分析、抽样调查两种方式收集统计信息。要验证自动统计采样的准确性，可以查看dba_tables.sample_size列。
- auto_sample_size(默认值)：抽样调查。在收集统计数据时，自动估计要采样的一个segment的最佳百分比。在使用自动采样时，Oracle会为一个样本尺寸选择5%到20%。统计数据质量越好，CBO做出的决定越好。
- null：全部分析数据。收集统计信息准确，但效率较低。
method_opt参数 => 收集方式（指定对象，指定大小，指定直方图参数）
- for all [indexed | hidden] columns [size_clause]
  - for all columns：收集所有列的直方图
  - for all indexed columns：收集索引列直方图，不推荐使用这个选项值，除非DBA可以确认业务中SQL的连接谓词只会使用索引字段，出现其他字段时就会因为没有统计信息可能生成错误的执行计划。
  - for all hidden columns：收集不可见列直方图，一般不推荐使用这个选项值. 这个选项值通常只用于这种情况, 在一个所有列的统计信息都是准确的表中新增了一个或几个不可见或者说是虚拟的列, 只需要收集这个或者这几个不可见列的统计信息, 而不再重复去其他列的统计信息, 可使用此选项。
- for columns [size_clause] column [size_clause] [,column [size_clause]...]
  - for columns owner：收集所有列的直方图
  - for columns owner for columns object_name：收集所有列的直方图
  - size_clause := size {integer | repeat | auto | skewonly} =>指定大小的子句，可省略
    - integer => 收集统计信息时，指定直方图的桶数 N,N∈[1,254]。注意如果桶数为 1, 即 size 1 意味着不建立直方图, 如果已经有直方图的列则会删除该列的直方图.
    - repeat => 只对已有直方图的列重新收集统计信息。一般不推荐使用这个选项, 因为新的直方图使用的桶数将不能超过旧的直方图中的桶数. 假设当前直方图中桶数为 5, 当使用 size repeat 重新收集直方图时, 新的直方图使用的桶数将不能超过 5 , 这钟方式可能不会取得好的效果.
    - auto => 【默认方式】自动决定桶数N大小。Oracle 自己决定根据列的统计信息(sys.col_usage$)以及列的数据倾斜程度(Data Skew)决定哪些列需要收集直方图。
    - skewonly => 只在数据不均匀分布的列上收集直方图。如果某一列存在数据偏斜(Data Skew)，则会生成该列的直方图。
    - for all columns size 10 for columns size 254 owner：收集所有列的直方图，桶数为10，单独指定owner列桶数为254
    - for all columns size 10 for columns size 254 (owner,object_name)：收集所有列的直方图，桶数为10，单独指定owner、object_name列桶数为254
    - for all columns size 10 for columns size 254 (owner,object_name) prod_id size 20 cust_id size 30：收集所有列的直方图，桶数为10，单独指定owner、object_name列桶数为254，prod_id 桶数为20，cust_id 桶数为30

dbms_stats.gather_schema_stats 数据字典

Ser
序号| Parameter
参数名称| Type
类型 | Note
参数说明 | Range
取值范围
:-------- |:-------- | :-----| :-----| :-----| :-----| :-----| :-----
1 |ownname| VARCHAR2 | Schema to analyze (NULL means current schema)
要分析的案名(‘NULL’ 表示当前方案)| NULL
　　当前方案。
[Any Schema]
　　任意指定方案。
|2|estimate_percent |NUMBER | 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_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
这个参数是一个百分比值，它告诉分析包需要使用表中数据的多大比例来做分析。
理论上来讲，采样的数据越多，得到的信息就越接近于实际，CBO做出的执行计划就越优化，但是，采样越多，消耗的系统资源必然越多。对系统的影响也越大。所以对于这个值的设置，要根据业务情况来。如果数据的直方图分布比较均匀，就可以使用默认值：AUTO_SAMPLE_SIZE，即让Oracle 自己来判断采样的比例。有时，特别是对于批量加载的表，我们可以预估表中的数据量，可以人工地设置一个合理的值。一般，对于一个有1000万数据的表分区，可以把这个参数设置为0.000001。|DBMS_STATS.
AUTO_SAMPLE_SIZE^default
　　默认值（取Oracle常量）。由oracle决定最佳取采样值，默认值可通过左侧介绍的四个存储过程进行修改。统计数据质量越好，CBO做出的决定越好。
NULL
　　全部分析,不采样。

|4|method_opt
《统计量收集Method_Opt参数使用（上）》
《统计量收集Method_Opt参数使用（下）》 | VARCHAR2 | Accepts:
　● FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
　● FOR COLUMNS [size_clause] column [size_clause] [,column [size_clause]...]
　
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension
- 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 on which to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns on which 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_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
决定histograms信息是怎样被统计的
√ 决定哪些columns统计被收集
√ 决定哪些columns直方图被收集以及直方图包含多少个buckets| get_param(‘METHOD_OPT’)^default
　　= DEFAULT_METHOD_OPT
　　= FOR ALL COLUMNS SIZE AUTO
由Oralce决定N的大小
for all columns
　　统计所有列的直方图(histograms)。
for all indexed columns
　　统计所有有索引列的直方图(histograms)。
for all hidden columns
　　统计你看不到列的直方图(histograms)。
for all columns SIZE 10
　　所有列buckt数量为10, buck∈【1,254】
for all columns SIZE REPEAT
　　上次统计过的直方图(histograms)。
for all columns SIZE AUTO
　　【默认】由oracle决定N的大小。
for all columns SIZE SKEWONLY
　　根据数据列的分布情况来判断直方图的生成。如果数据呈现出偏移倾斜的情况，会去生成直方图。
for columns size 10 owner
　　owner列的NUM_BUCKET=10
for columns size 10 owner for columns size 5 object_name
　　owner列的NUM_BUCKET=10
　　object_name列的NUM_BUCKET=5
for columns size 10 owner object_name object_id
　　object_name 列的NUM_BUCKET=10
　　object_id列的NUM_BUCKET=10
　…　
|5| degree| NUMBER | Degree of parallelism. The default for degree is NULL. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS 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 between 1 (serial execution) and DEFAULT_DEGREE (the system default value based on number of CPUs and initialization parameters) according to the size of the object. When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.
决定并行度（线程数），默认值为NULL
√ 根据业务情况选择，业务闲时可设置为-1
√ 一般来说，degree值越大执行越快，如果服务器主机只有一个CPU，并行进程可能会争用CPU，从而导致实际性能可能会下降。
√ degree一般不超过10就可以了，过大会导致任务卡死，根据实际情况选择 | DBMS_STATS.
DEFAULT_DEGREE ^default
　　此值为默认值（取Oracle常量）。由oracle决定并行度，默认值可通过左侧介绍的四个存储过程进行修改。
　　根据对象的大小，degree值介于1（串行）和DEFAULT_DEGREE（基于CPU数量和初始化参数系统默认值）之间。
NULL
　　表示使用CREATE table或ALTER table语句中的DEGREE子句指定的表并行度默认值。《Oracle并行处理》
正整数
　　容忍范围内，值越大运行时间越短。但值过大会抢占数据库资源，甚至任务卡死，根据实际情况选择。
|10| options | VARCHAR2 | Further specification of which objects to gather statistics for:
- GATHER: Gathers statistics on all objects in the schema.
- GATHER AUTO: Gathers all necessary statistics automatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.
- GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.
- GATHER EMPTY: Gathers statistics on objects which currently have no statistics. Return a list of objects found to have no statistics.
- LIST AUTO: Returns a list of objects to be processed with GATHER AUTO
- LIST STALE: Returns a list of stale objects as determined by looking at the _tab_modifications views
- LIST EMPTY: Returns a list of objects which currently have no statistics | GATHER^default
　　收集方案下(schema)所有对象的统计信息。
GATHER AUTO
　　　由Oracle自动决定收集哪些必要的统计数据。当指定参数为GATHER AUTO时，附加有效参数只有stattab, statid, objlist和statown;所有其他参数设置将被忽略。返回已处理对象的列表。
GATHER STALE
　　　收集通过查看 _tab_modify视图确定的陈旧对象的统计信息。返回值为已过期对象的列表。
GATHER EMPTY
　　　收集当前没有统计信息的对象的统计信息。返回没有统计信息对象的列表。
LIST AUTO
　　　返回要用 GATHER AUTO 处理对象的列表。
LIST STALE
　　　返回要用 GATHER STALE 处理对象的列表。
LIST EMPTY
　　　返回要用 GATHER EMPTY 处理对象的列表。

┄┄┄注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。
┄┄┄这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。

其他常用DBMS_STATS语句

分析数据库（包括所有的用户对象和系统对象）：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  

--收集数据库信息
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

--收集schema信息
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

--收集表信息
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

--收集index信息
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

--删除收集信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);

--创建备份收集信息表
begin
dbms_stats.create_stat_table(USER,stattab => ‘STAT_TABLE’);
end;

--备份收集信息
BEGIN
dbms_stats.export_table_stats(USER,tabname => ‘FEI_T’,stattab => ‘STAT_TABLE’);
END;

--删除收集信息
BEGIN
DBMS_STATS.delete_table_stats(USER,tabname => ‘FEI_T’);
END;

--导入收集信息
BEGIN
dbms_stats.IMPORT_TABLE_STATS(USER,’FEI_T’,stattab => ‘STAT_TABLE’);
END;

--说明：
当前用户可以使用user代替用户名
分析表相关对象信息cascade => true