一:收集统计信息
(1)用户级别
例1:
begin
dbms_stats.gather_schema_stats(ownname => UPPER(OWNER_NAME),
estimate_percent => 100,
method_opt => 'FOR ALL INDEXED COLUMNS',
cascade => TRUE);
end;
例2:
exec dbms_stats.gather_schema_stats('HR');
(2) 表级别
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'T1');
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm)');
analyze table abc compute statistics;
analyze table abc estimate statistics sample 20 percent;
analyze table tablename compute statistics for all indexes;
---analyze table compute statistics = analyze table compute statistics for table for all indexes for all columns;
---直方图
exec dbms_stats.gather_table_stats('chen','test1',method_opt=>'for columns col1 size AUTO');
exec dbms_stats.gather_table_stats('chen','test1',method_opt=>'for columns (ACCYEAR,ACCMONTH) size AUTO');
analyze table gl_detail compute statistics for columns prepareddatev size 254;
二:锁定统计信息
exec dbms_stats.lock_table_stats(ownname => user,tabname => table_name);
三: 删除统计信息
begin
dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'TEST1');
end;
或
ANALYZE TABLE TEST1 DELETE STATISTICS;
---删除加锁定
BEGIN
DBMS_STATS.DELETE_TABLE_STATS('HR','ORDERS');
DBMS_STATS.LOCK_TABLE_STATS('HR','ORDERS');
END;
四:导出/导入统计信息
(1) 首先创建一个分析表,该表是用来保存之前的分析值:
begin
dbms_stats.create_stat_table(ownname => 'CHEN', stattab => 'STAT_TABLE',cascade => true);
end;
(2) 导出表分析信息到stat_table中
begin
dbms_stats.export_table_stats(ownname => 'CHEN',
tabname => 'T1',
stattab => 'STAT_TABLE');
end;
(3)开始更新T1表统计信息
begin
dbms_stats.gather_table_stats(ownname => 'CHEN',
tabname => 'T1',
cascade => true);
end;
(4)删除分析信息
begin
dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'T1');
end;
(5)导入分析信息
begin
dbms_stats.import_table_stats(ownname => 'CHEN',
tabname => 'T1',
stattab => 'STAT_TABLE');
end;
五:查询统计信息
(1)查看表统计信息
DBA_TABLES
DBA_TAB_STATISTICS
(2) 查看列统计信息
DBA_TAB_COL_STATISTICS
DBA_TAB_COLUMNS
(3)查看索引统计信息
DBA_IND_STATISTICS
六:统计信息官方文档
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/toc.htm
转载自chenoracle