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:
begindbms_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,
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列
3索引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);
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);