一：10g关闭自动收集统计信息任务
-
begin
-
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
-
end;
二：11g关闭自动收集统计信息任务
-
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
三：手动收集统计信息，采样10%，并行度为8，METHOD_OPT选项代表收集index列分布情况，并生成直方图
-
BEGIN
-
dbms_stats.gather_schema_stats(
-
ownname=>'HR',
-
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',
-
CASCADE=>TRUE,
-
ESTIMATE_PERCENT=>10,
-
DEGREE=>8);
-
END;
四：查看表或索引的统计信息
-
select table_name,
-
last_analyzed,
-
num_rows,
-
avg_row_len,
-
row_movement，
-
monitoring
-
from dba_tables
-
where owner = 'HR';
-
-
select index_name,
-
table_name,
-
blevel,
-
status,
-
clustering_factor,
-
last_analyzed,
-
num_rows,
-
leaf_blocks,
-
distinct_keys,
-
avg_leaf_blocks_per_key
-
from dba_indexes
-
where owner = 'HR';
五：导出统计信息
-
begin
-
dbms_stats.create_stat_table(ownname => 'HR',
-
stattab => 'HR_STAT_BAK');
-
end;
-
-
begin
-
dbms_stats.export_schema_stats(ownname => 'HR',
-
stattab => 'HR_STAT_BAK',
-
statid => 'N1',
-
statown => 'HR');
-
end;
-
-
select * from HR_STAT_BAK；
六：删除统计信息
-
begin
-
dbms_stats.delete_schema_stats(ownname => 'HR');
-
end;
七：导入统计信息
-
begin
-
dbms_stats.import_schema_stats(ownname => 'HR',
-
stattab => 'HR_STAT_BAK',
-
statid => 'N1',
-
statown => 'HR');
-
end;
八：异机导入，首先需要将HR_STAT_BAK表通过expdp/impdp方式导入到其他oracle服务器上
-
SQL> exec dbms_stats.upgrade_stat_table('HR','HR_STAT_BAK');
-
PL/SQL 过程已成功完成。
-
-
SQL> EXEC dbms_stats.import_schema_stats(ownname => 'HR',stattab => 'HR_STAT_BAK',statid => 'N1',statown=>'HR');
-
PL/SQL 过程已成功完成。
九：锁定与解锁统计信息
-
1：锁定统计信息
-
begin
-
dbms_stats.lock_schema_stats(ownname=>'HR');
-
end;
-
-
2：查询哪些表或索引的统计信息被锁定
-
select * from user_tab_statistics where stattype_locked='ALL';
-
select * from user_ind_statistics where stattype_locked='ALL';
-
-
3：如果在锁定条件下收集统计信息，则会出现如下报错
-
ERROR at line 1:
-
ORA-20005: object statistics are locked (stattype = ALL)
-
ORA-06512: at "SYS.DBMS_STATS", line 20337
-
ORA-06512: at "SYS.DBMS_STATS", line 20360
-
ORA-06512: at line 1
-
-
4：解锁统计信息
-
begin
-
dbms_stats.lock_schema_stats(ownname=>'HR');
-
end;