oracle统计信息收集之dbms_stats

深碍√TFBOYSˉ_ 2022-04-10 08:56 1034阅读 0赞

dbms\_stats包收集统计信息：

dbms\_stats 包含很多存储过程，为收集、删除、导出、导入统计信息提供了存储过程常见如下，详细的请参见官方文档PL/SQL Packages and Types Reference  
dbms\_stats包收集统计信息是oracle推荐使用的

DBMS\_STATS包中用于收集统计信息常用的过程：  
EXPORT\_COLUMN\_STATS：导出列的分析信息  
EXPORT\_INDEX\_STATS：导出索引分析信息  
EXPORT\_SYSTEM\_STATS：导出系统分析信息  
EXPORT\_TABLE\_STATS：导出表分析信息  
EXPORT\_SCHEMA\_STATS：导出方案分析信息  
EXPORT\_DATABASE\_STATS：导出数据库分析信息  
IMPORT\_COLUMN\_STATS：导入列分析信息  
IMPORT\_INDEX\_STATS：导入索引分析信息  
IMPORT\_SYSTEM\_STATS：导入系统分析信息  
IMPORT\_TABLE\_STATS：导入表分析信息  
IMPORT\_SCHEMA\_STATS：导入方案分析信息  
IMPORT\_DATABASE\_STATS：导入数据库分析信息  
GATHER\_INDEX\_STATS：分析索引信息  
GATHER\_TABLE\_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息  
GATHER\_SCHEMA\_STATS：分析方案信息  
GET\_COLUMN\_STATS：获取字段的统计信息  
GET\_SYSTEM\_STATS：获取系统的统计信息  
GET\_INDEX\_STATS：获取索引的统计信息  
GET\_TABLE\_STATS：获取表的统计信息  
SET\_COLUMN\_STATS：设置字段的统计信息。通常应用在测试环境，也不排除在极端情况下起到奇效。  
SET\_SYSTEM\_STATS：设置系统的统计信息  
SET\_INDEX\_STATS：设置索引的统计信息  
SET\_TABLE\_STATS：设置表的统计信息  
DELETE\_COLUMN\_STATS：删除字段的统计信息  
DELETE\_SYSTEM\_STATS：删除系统的统计信息  
DELETE\_INDEX\_STATS：删除索引的统计信息  
DELETE\_TABLE\_STATS：删除表的统计信息  
DELETE\_DATABASE\_STATS：删除数据库的统计信息  
DELETE\_DICTIONARY\_STATS：删除数据字典的统计信息  
DELETE\_SCHEMA\_STATS：删除用户方案的统计信息  
DELETE\_FIXED\_OBJECTS\_STATS：删除固定对象的统计信息  
GATHER\_SCHEMA\_STATS：分析方案信息  
GATHER\_DATABASE\_STATS：分析数据库信息  
GATHER\_SYSTEM\_STATS：分析系统信息  
CREATE\_STAT\_TABLE：建立存放统计信息的表  
DROP\_STAT\_TABLE：删除存放统计信息的表  
LOCK\_TABLE\_STATS：锁定表的统计信息。当觉得当前统计信息非常好，且表数据几乎不变化时，可以考虑锁定统计信息，锁定之后相关的所有数据分析，包括表级，列级，直方图、索引的分析都将被锁定，不允许被更新。  
LOCK\_SCHEMA\_STATS：锁定用户方案的统计信息  
UNLOCK\_TABLE\_STATS：解锁表的统计信息  
UNLOCK\_SCHEMA\_STATS：解锁用户方案的统计信息  
RESTORE\_SYSTEM\_STATS：还原系统的统计信息  
RESTORE\_INDEX\_STATS：还原索引的统计信息  
RESTORE\_TABLE\_STATS：还原表的统计信息  
RESTORE\_DATABASE\_STATS：还原数据库的统计信息  
RESTORE\_DICTIONARY\_STATS：还原数据字典的统计信息  
RESTORE\_SCHEMA\_STATS：还原用户方案的统计信息  
RESTORE\_FIXED\_OBJECTS\_STATS：还原固定对象的统计信息

DBMS\_STATS.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);

参数说明:  
ownname:要分析表的拥有者  
tabname:要分析的表名.  
partname:分区的名字,只对分区表或分区索引有用.  
estimate\_percent:采样行的百分比,取值范围\[0.000001,100\],null为全部分析,不采样. 常量:DBMS\_STATS.AUTO\_SAMPLE\_SIZE是默认值,由oracle决定最佳取采样值.  
block\_sapmple:是否用块采样代替行采样.  
method\_opt:决定histograms信息是怎样被统计的  
通过设置 method\_opt参数可以智能地生成直方图,具体取值如下:  
for all columns:10g默认值(根据版本的不同，默认值也会有所差异)，统计所有列的histograms.  
for all indexed columns:统计所有indexed列的histograms.  
for all hidden columns:统计你看不到列的histograms  
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围\[1,254\];  
REPEAT上次统计过的histograms;  
AUTO由oracle决定N的大小;  
SKEWONLY选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。  
假如dbms\_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。  
degree:决定并行度.默认值为null.  
granularity:设置分区表收集统计信息的粒度，分别有  
all：对表全局，分区，子分区的数据都做分析  
auto：Oracle根据分区的类型，自动决定做哪一种粒度的分析  
global：只做全局级别的分析  
global and partition：只对全局和分区级别做分析，对子分区不做分析，这是和all的一个区别  
partition：只做分区级别做分析  
subpartition：只做子分区做分析  
exec DBMS\_STATS.GATHER\_TABLE\_STATS(NULL,'T3', GRANULARITY => 'SUBPARTITION', CASCADE => TRUE);  
exec DBMS\_STATS.GATHER\_TABLE\_STATS(NULL,'T2', GRANULARITY => 'PARTITION', CASCADE => TRUE);     
exec DBMS\_STATS.GATHER\_TABLE\_STATS(NULL,'T1', GRANULARITY => 'GLOBAL', CASCADE => TRUE);  
其中，T1为全表，T2为分区，T3为子分区  
cascace:是收集索引的信息.默认为falase.  
stattab指定要存储统计信息的表；  
statid如果多个表的统计信息存储在同一个stattab中用于进行区分；  
statown存储统计信息表的拥有者。以上三个参数若不指定,统计信息会直接更新到数据字典.  
no\_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.  
force:即使表锁住了也收集统计信息.

其他过程参数说明参见oracle官方文档  
例子：  
BEGIN  
   DBMS\_STATS.CREATE\_STAT\_TABLE ('hr', 'savestats'); ---创建存储统计信息的统计信息表  
   DBMS\_STATS.GATHER\_TABLE\_STATS ('hr', 'employees', stattab => 'savestats');  --收集表的统计信息并导出到统计信息表里  
END;

BEGIN  
   DBMS\_STATS.DELETE\_TABLE\_STATS ('hr', 'employees'); ---删除表的统计信息  
   DBMS\_STATS.IMPORT\_TABLE\_STATS ('hr', 'employees', stattab => 'savestats'); --从一张统计信息表把统计信息导入到当前的模式下  
END;

收集整个模式的统计信息：  
exec dbms\_stats.gather\_schema\_stats(ownname=>'hr');  
收集模式中任何“过时”的表的统计信息  
exec dbms\_stats.gather\_schema\_stats(ownname=>'hr'，options=>'gather stale');