#### 检查统计信息是否过期 ``` sql --刷新监控信息 exec dbms_stats.flush_database_monitoring_info; --检查 set lines 200 pages 999 col owner for a20 col table_name for a30 col object_type for a15 select owner,table_name,object_type,stale_stats,to_char(last_analyzed,'yyyy/mm/dd hh24:mi') last_analyzed from dba_tab_statistics where stale_stats='YES' and owner in (select username from dba_users where created>(select created from v$database)); --or 12c+ 检查 set lines 200 pages 999 col pdb_name for a15 col owner for a20 col table_name for a30 col object_type for a15 break on pdb_name skip 1 select p.name pdb_name,t.owner,t.table_name,t.object_type,t.stale_stats,to_char(t.last_analyzed,'yyyy/mm/dd hh24:mi') last_analyzed from cdb_tab_statistics t,v$pdbs p where stale_stats='YES' and t.con_id=p.con_id and (t.con_id,t.owner) in (select con_id,username from cdb_users where created>(select created from v$database)); --查看统计信息过期原因 select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications where table_owner='' and table_name=''; --检查统计信息过期阈值 select dbms_stats.get_prefs('STALE_PERCENT','MYTEST','T1') stale_percent from dual; --查看表的采样比例 SELECT DBMS_STATS.GET_PREFS ('estimate_percent', 'mytest','t1') AS "STAT_PREFS" FROM DUAL; --设置统计信息过期阈值 exec dbms_stats.set_table_prefs(user,'T1','STALE_PERCENT','5'); --12c+ 查看优先级参数 SELECT DBMS_STATS.GET_PREFS ('preference_overrides_parameter', 'mytest','t1') AS "STAT_PREFS" FROM DUAL; --设置 EXEC DBMS_STATS.SET_TABLE_PREFS ('mytest','t1','preference_overrides_parameter', 'true'); ``` 使用dbms_stats.gather_*_stats,优先级如下 - 1.gather_%_stats - 2.table preference - 3.global Preference 使用preference_overrides_parameter=true，优先级 - 1.table preference - 2.global Preference - 3.gather_%_stats #### 收集统计信息 ``` sql --收集统计信息,如想立即生效，加 "no_invalidate=>false" exec dbms_stats.gather_table_stats('MYTEST','T_OBJECTS',degree => 4,cascade => true); --收集用户 begin dbms_stats.gather_schema_stats('DBO_ZS', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size auto',cascade=>TRUE,degree=>4); end; / --收集数据库,注意直方图 begin dbms_stats.gather_database_stats( ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns size auto', cascade=>true, degree=>8); end; / ``` #### 直方图 ```sql -- 对所有索引列收集直方图, or 自动收集 for all indexed columns size auto exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',estimate_percent => auto,method_opt => 'for all indexed columns',cascade => true); --对某一列收集直方图 自动，只对用过的列收集 exec dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'dept',method_opt => 'for columns size auto DEPTNO',cascade => true,estimate_percent => 100); --查看 select column_name,density,num_buckets,histogram from dba_tab_col_statistics where table_name='DEPT'; --查看表柱状图详细信息 col OWNER for a15 col column_name for a20 col ENDPOINT_ACTUAL_VALUE for a20 select * from dba_tab_histograms where table_name='DEPT'; ``` #### 迁移统计信息 ```sql -- eg：迁移用户 -- 1.创建临时表 BEGIN DBMS_STATS.CREATE_STAT_TABLE (ownname => 'scott',stattab => 'opt_stats'); END; / --2.手动收集信息 EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SH'); --3.导出统计信息 BEGIN DBMS_STATS.EXPORT_SCHEMA_STATS (ownname => 'scott',stattab => 'opt_stats'); END; / -- 4.导出到dmp expdp scott/tiger DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats -- 5.目标端导入 impdp scott/tiger DIRECTORY=dpump_dir1 DUMPFILE=stat.dmp TABLES=opt_stats --6.导入统计信息到数据字典 BEGIN DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => 'scott',stattab => 'opt_stats'); END; / -- 迁移数据库 --建表 exec dbms_stats.create_stat_table( ownname => 'system',stattab =>'stat_table'); --导出 exec dbms_stats.export_database_stats(stattab=>'stat_table',statown=>'SYSTEM'); --导入 exec dbms_stats.import_databsae_stats(stattab=>'stat_table',statown=>'SYSTEM'); ```