在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划！可以说统计信息关乎sql的执行计划是否正确，属于sql执行的指导思想，oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值:
BASIC :收集基本的统计信息
TYPICAL：收集大部分统计信息(数据库的默认设置)
ALL：收集全部统计信息
统计信息包含：
行统计信息(user_tables)：行数(NUM_ROWS)，块数(BLOCKS)，行平均长度(AVG_ROW_LEN)；
列统计信息(user_tab_columns)：列中唯一值的数量（NUM_DISTINCT），NULL值的数量(NUM_NULLS)，数据分布(HISTOGRAM)；
索引统计(user_index)：--叶块数量(LEAF_BLOCKS)，等级(BLEVEL)，聚簇因子(CLUSTERING_FACTOR)；
统计信息的收集时间由时间窗口来调度！
一：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;
©著作权归作者所有：来自51CTO博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任
oraclestatisticsOracle
随时随地看视频
