统计信息-CSDN博客

本文链接：https://blog.csdn.net/hanbowu/article/details/49735311

1oracle自带收集的job

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

--关闭信息自动收集任务
BEGIN
      DBMS_AUTO_TASK_ADMIN.DISABLE(
                     client_name => 'auto optimizer stats collection',
                     operation => NULL, window_name => NULL);
  END;
 /

2 收集统计信息的过程

o GATHER_INDEX_STATS --收集索引统计信息

o GATHER_TABLE_STATS --收集表，列，索引统计信息

o GATHER_SCHEMA_STATS --收集schema所有对象统计信息

o GATHER_DICTIONARY_STATS –-收集所有系统用户的统计信息

o GATHER_DATABASE_STATS --收集数据库所有对象统计信息

手动收集对象job：

begin
dbms_stats.gather_schema_stats(ownname          => 'cimsuser',
                               options          => 'GATHER AUTO',
                               estimate_percent => 20,
                               cascade => true,
                               method_opt       => 'for all columns size repeat',
                               degree           => 4);
end;

手动收集分区job：

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'scott',
                                 tabname => 'P_TEST',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,

                                granularity => 'ALL',
                                 cascade=>TRUE
                                 );

end;

手动收集表job:

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                 tabname => 'DEPT',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                 cascade=>TRUE
                                 );
END;
/

其它操作：

–删除收集信息
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;

官方参考：

1表

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

2列

DBMS_STATS.GET_COLUMN_STATS (
   ownname     VARCHAR2, 
   tabname     VARCHAR2, 
   colname     VARCHAR2, 
   partname    VARCHAR2 DEFAULT NULL,
   stattab     VARCHAR2 DEFAULT NULL, 
   statid      VARCHAR2 DEFAULT NULL,
   distcnt OUT NUMBER, 
   density OUT NUMBER,
   nullcnt OUT NUMBER, 
   srec    OUT StatRec,
   avgclen OUT NUMBER,
   statown     VARCHAR2 DEFAULT NULL);

3索引

DBMS_STATS.GET_INDEX_STATS (
   ownname        VARCHAR2, 
   indname        VARCHAR2,
   partname       VARCHAR2 DEFAULT NULL,
   stattab        VARCHAR2 DEFAULT NULL, 
   statid         VARCHAR2 DEFAULT NULL,
   numrows    OUT NUMBER, 
   numlblks   OUT NUMBER,
   numdist    OUT NUMBER, 
   avglblk    OUT NUMBER,
   avgdblk    OUT NUMBER, 
   clstfct    OUT NUMBER,
   indlevel   OUT NUMBER,
   statown        VARCHAR2 DEFAULT NULL,
   guessq     OUT NUMBER,
   cachedblk  OUT NUMBER,
   cachehit   OUT NUMBER);