以下是檢查 Oracle 統(tǒng)計(jì)信息更新時(shí)間的常用方法：

1. 查看表級(jí)統(tǒng)計(jì)信息更新時(shí)間

-- 查看用戶表統(tǒng)計(jì)信息
SELECT table_name, 
       num_rows,
       last_analyzed,
       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as analyze_time,
       stale_stats
FROM user_tables
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

-- 查看所有表（含系統(tǒng)時(shí)間戳）
SELECT owner,
       table_name,
       num_rows,
       blocks,
       last_analyzed,
       ROUND((SYSDATE - last_analyzed) * 24) as hours_ago
FROM dba_tables
WHERE owner = '用戶名'
ORDER BY last_analyzed;

2. 查看索引統(tǒng)計(jì)信息更新時(shí)間

-- 查看索引統(tǒng)計(jì)信息
SELECT index_name,
       table_name,
       last_analyzed,
       num_rows,
       distinct_keys,
       TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') as analyze_time
FROM user_indexes
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

-- 查看所有索引統(tǒng)計(jì)信息
SELECT owner,
       index_name,
       table_name,
       last_analyzed,
       DENSE_RANK() OVER (PARTITION BY owner ORDER BY last_analyzed DESC) as rank
FROM dba_indexes
WHERE owner = '用戶名'
ORDER BY last_analyzed DESC;

3. 查看列統(tǒng)計(jì)信息（直方圖）

-- 查看列的統(tǒng)計(jì)信息
SELECT table_name,
       column_name,
       num_distinct,
       num_nulls,
       last_analyzed,
       histogram
FROM user_tab_columns
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

-- 查看直方圖統(tǒng)計(jì)信息
SELECT table_name,
       column_name,
       endpoint_number,
       endpoint_value,
       last_analyzed
FROM user_tab_histograms
WHERE table_name = '表名'
ORDER BY table_name, column_name, endpoint_number;

4. 查看分區(qū)統(tǒng)計(jì)信息

-- 查看分區(qū)表統(tǒng)計(jì)信息
SELECT table_name,
       partition_name,
       num_rows,
       last_analyzed,
       ROUND((SYSDATE - last_analyzed) * 24, 2) as hours_ago
FROM user_tab_partitions
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

-- 查看子分區(qū)統(tǒng)計(jì)信息
SELECT table_name,
       partition_name,
       subpartition_name,
       num_rows,
       last_analyzed
FROM user_tab_subpartitions
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

5. 統(tǒng)計(jì)信息是否過期（STALE_STATS）

-- 查看過期統(tǒng)計(jì)信息
SELECT owner,
       table_name,
       stale_stats,
       last_analyzed,
       CASE 
           WHEN stale_stats = 'YES' THEN '統(tǒng)計(jì)信息過期'
           WHEN stale_stats = 'NO' THEN '統(tǒng)計(jì)信息有效'
           ELSE '未分析'
       END as status
FROM dba_tab_statistics
WHERE owner = '用戶名'
AND stale_stats = 'YES'  -- 只看過期的
ORDER BY last_analyzed;

-- 查看所有表狀態(tài)
SELECT owner,
       COUNT(*) as total_tables,
       SUM(CASE WHEN stale_stats = 'YES' THEN 1 ELSE 0 END) as stale_tables,
       SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) as never_analyzed
FROM dba_tab_statistics
WHERE owner = '用戶名'
GROUP BY owner;

6. 匯總腳本

-- 統(tǒng)計(jì)信息健康度報(bào)告
SELECT 
    owner,
    table_name,
    num_rows,
    blocks,
    last_analyzed,
    stale_stats,
    CASE 
        WHEN last_analyzed IS NULL THEN '從未分析'
        WHEN stale_stats = 'YES' THEN '已過期'
        WHEN (SYSDATE - last_analyzed) > 7 THEN '超過7天未更新'
        WHEN (SYSDATE - last_analyzed) > 1 THEN '超過1天未更新'
        ELSE '最近更新'
    END as analyze_status,
    ROUND((SYSDATE - last_analyzed) * 24, 2) as hours_since_analyze
FROM dba_tab_statistics
WHERE owner = '用戶名'
AND (stale_stats = 'YES' OR last_analyzed IS NULL OR (SYSDATE - last_analyzed) > 1)
ORDER BY last_analyzed NULLS FIRST;

7. 檢查統(tǒng)計(jì)信息收集任務(wù)

-- 查看自動(dòng)統(tǒng)計(jì)信息收集窗口
SELECT window_name,
       REPLACE(REPLACE(resource_plan, '<![CDATA[', ''), ']]>', '') as resource_plan,
       enabled,
       next_start_date,
       repeat_interval
FROM dba_scheduler_windows
WHERE window_name LIKE 'MON%' OR window_name LIKE 'WEE%'
ORDER BY window_name;

-- 查看統(tǒng)計(jì)信息收集作業(yè)狀態(tài)
SELECT job_name,
       enabled,
       state,
       next_run_date,
       repeat_interval
FROM dba_scheduler_jobs
WHERE job_name LIKE '%GATHER%STATS%';

-- 查看最近執(zhí)行的統(tǒng)計(jì)信息收集任務(wù)
SELECT job_name,
       job_action,
       run_date,
       SUCC_DONE
FROM dba_scheduler_job_run_details
WHERE job_name LIKE '%GATHER%STATS%'
ORDER BY run_date DESC
FETCH FIRST 20 ROWS ONLY;

8. 檢查統(tǒng)計(jì)信息鎖

-- 查看表是否被鎖定統(tǒng)計(jì)信息
SELECT owner,
       table_name,
       stattype_locked
FROM dba_tab_statistics
WHERE owner = '用戶名'
AND stattype_locked IS NOT NULL;

-- 查看索引是否被鎖定統(tǒng)計(jì)信息
SELECT owner,
       index_name,
       table_name,
       stattype_locked
FROM dba_ind_statistics
WHERE owner = '用戶名'
AND stattype_locked IS NOT NULL;

9. 對(duì)比不同時(shí)間點(diǎn)的統(tǒng)計(jì)信息

-- 對(duì)比歷史統(tǒng)計(jì)信息（使用AWR）
SELECT 
    snap_id,
    begin_interval_time,
    end_interval_time,
    table_name,
    num_rows
FROM dba_hist_tab_stat_history
WHERE owner = '用戶名'
AND table_name = '表名'
ORDER BY snap_id DESC;

10. 實(shí)用診斷腳本

-- 完整的統(tǒng)計(jì)信息診斷報(bào)告
SELECT 
    '表統(tǒng)計(jì)信息' as type,
    owner,
    table_name as object_name,
    last_analyzed,
    stale_stats,
    ROUND((SYSDATE - last_analyzed) * 24, 2) as hours_ago
FROM dba_tab_statistics
WHERE owner = '用戶名'
AND (stale_stats = 'YES' OR last_analyzed IS NULL)

UNION ALL

SELECT 
    '索引統(tǒng)計(jì)信息' as type,
    owner,
    index_name as object_name,
    last_analyzed,
    '' as stale_stats,
    ROUND((SYSDATE - last_analyzed) * 24, 2) as hours_ago
FROM dba_ind_statistics
WHERE owner = '用戶名'
AND last_analyzed IS NULL

ORDER BY hours_ago DESC NULLS FIRST;

11. 快速檢查特定表

-- 一鍵查看表的所有統(tǒng)計(jì)信息
SELECT 
    t.table_name,
    t.num_rows as table_rows,
    t.blocks as table_blocks,
    t.last_analyzed as table_analyzed,
    t.stale_stats as table_stale,
    COUNT(i.index_name) as index_count,
    MAX(i.last_analyzed) as latest_index_analyzed
FROM user_tables t
LEFT JOIN user_indexes i ON t.table_name = i.table_name
WHERE t.table_name = '表名'
GROUP BY t.table_name, t.num_rows, t.blocks, t.last_analyzed, t.stale_stats;

12. 檢查統(tǒng)計(jì)信息收集配置

-- 查看統(tǒng)計(jì)信息收集參數(shù)
SELECT name, value, description
FROM v$parameter
WHERE name IN (
    'optimizer_use_pending_statistics',
    'optimizer_dynamic_sampling',
    'optimizer_capture_sql_plan_baselines',
    'statistics_level',
    'preferred_aggregate_function'
);

-- 查看數(shù)據(jù)庫(kù)統(tǒng)計(jì)信息
SELECT * FROM dba_optstat_operations
ORDER BY end_time DESC
FETCH FIRST 10 ROWS ONLY;

常用場(chǎng)景示例

場(chǎng)景1：檢查未更新統(tǒng)計(jì)信息的表

SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE owner = '用戶名'
AND (last_analyzed IS NULL OR last_analyzed < SYSDATE - 7)
ORDER BY last_analyzed NULLS FIRST;

場(chǎng)景2：檢查大表的統(tǒng)計(jì)信息

SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = '用戶名'
AND num_rows > 1000000
ORDER BY num_rows DESC;

場(chǎng)景3：SQL執(zhí)行計(jì)劃改變前后的對(duì)比

-- 記錄當(dāng)前統(tǒng)計(jì)信息時(shí)間
SELECT table_name, last_analyzed 
FROM user_tables 
WHERE table_name IN ('表1', '表2');

-- 重新收集統(tǒng)計(jì)信息后對(duì)比

更新統(tǒng)計(jì)信息

-- 收集表統(tǒng)計(jì)信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '用戶名', tabname => '表名');

-- 收集所有對(duì)象統(tǒng)計(jì)信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '用戶名');

-- 收集數(shù)據(jù)庫(kù)統(tǒng)計(jì)信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS();

-- 收集統(tǒng)計(jì)信息并查看進(jìn)展
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '用戶名',
    tabname => '表名',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
);

關(guān)鍵點(diǎn)：

• last_analyzed 越新越好
• stale_stats = 'YES' 表示可能過期
• 大表建議開啟自動(dòng)統(tǒng)計(jì)信息收集
• 被鎖定的統(tǒng)計(jì)信息需要手動(dòng)處理