Oracle 表的统计信息收集情况查询

1 check stats status

1. select owner,last_ddl_time from dba_objects where object_name = '&table';


2. select owner,table_name, to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where owner='&owner' AND TABLE_NAME = '&table';

3. select owner,table_name,STALE_STATS,STATTYPE_LOCKED from dba_tab_statistics where owner='&owner' AND TABLE_NAME = '&table';

4. select LAST_ANALYZED,owner,index_name,STALE_STATS from dba_ind_statistics where table_owner='&owner' AND TABLE_NAME = '&table';


5. SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COL table_name FOR A30
COL table_owner FOR A20

SELECT *
FROM ( SELECT m.table_owner
, m.table_name
, t.last_analyzed
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, ( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
AND m.table_owner='&owner'
AND m.table_name like '&table'
ORDER BY "Change Factor" DESC
)

2 collect states

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => '&owner',
                                tabname          => '&table',
                                estimate_percent => 15,
                                cascade          => true);
END;
/