一:收集统计信息

(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

最后修改:2022 年 02 月 26 日
如果觉得我的文章对你有用,请随意赞赏