Oracle统计信息相关

以下是检查 Oracle 统计信息更新时间的常用方法:

1. 查看表级统计信息更新时间

-- 查看用户表统计信息
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;

-- 查看所有表(含系统时间戳)
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. 查看索引统计信息更新时间

-- 查看索引统计信息
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;

-- 查看所有索引统计信息
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. 查看列统计信息(直方图)

-- 查看列的统计信息
SELECT table_name,
       column_name,
       num_distinct,
       num_nulls,
       last_analyzed,
       histogram
FROM user_tab_columns
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

-- 查看直方图统计信息
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. 查看分区统计信息

-- 查看分区表统计信息
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;

-- 查看子分区统计信息
SELECT table_name,
       partition_name,
       subpartition_name,
       num_rows,
       last_analyzed
FROM user_tab_subpartitions
WHERE table_name = '表名'
ORDER BY last_analyzed DESC;

5. 统计信息是否过期(STALE_STATS)

-- 查看过期统计信息
SELECT owner,
       table_name,
       stale_stats,
       last_analyzed,
       CASE 
           WHEN stale_stats = 'YES' THEN '统计信息过期'
           WHEN stale_stats = 'NO' THEN '统计信息有效'
           ELSE '未分析'
       END as status
FROM dba_tab_statistics
WHERE owner = '用户名'
AND stale_stats = 'YES'  -- 只看过期的
ORDER BY last_analyzed;

-- 查看所有表状态
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. 汇总脚本

-- 统计信息健康度报告
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. 检查统计信息收集任务

-- 查看自动统计信息收集窗口
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;

-- 查看统计信息收集作业状态
SELECT job_name,
       enabled,
       state,
       next_run_date,
       repeat_interval
FROM dba_scheduler_jobs
WHERE job_name LIKE '%GATHER%STATS%';

-- 查看最近执行的统计信息收集任务
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. 检查统计信息锁

-- 查看表是否被锁定统计信息
SELECT owner,
       table_name,
       stattype_locked
FROM dba_tab_statistics
WHERE owner = '用户名'
AND stattype_locked IS NOT NULL;

-- 查看索引是否被锁定统计信息
SELECT owner,
       index_name,
       table_name,
       stattype_locked
FROM dba_ind_statistics
WHERE owner = '用户名'
AND stattype_locked IS NOT NULL;

9. 对比不同时间点的统计信息

-- 对比历史统计信息(使用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. 实用诊断脚本

-- 完整的统计信息诊断报告
SELECT 
    '表统计信息' 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 
    '索引统计信息' 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. 快速检查特定表

-- 一键查看表的所有统计信息
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. 检查统计信息收集配置

-- 查看统计信息收集参数
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'
);

-- 查看数据库统计信息
SELECT * FROM dba_optstat_operations
ORDER BY end_time DESC
FETCH FIRST 10 ROWS ONLY;

常用场景示例

场景1:检查未更新统计信息的表

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;

场景2:检查大表的统计信息

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

场景3:SQL执行计划改变前后的对比

-- 记录当前统计信息时间
SELECT table_name, last_analyzed 
FROM user_tables 
WHERE table_name IN ('表1', '表2');

-- 重新收集统计信息后对比

更新统计信息

-- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => '用户名', tabname => '表名');

-- 收集所有对象统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '用户名');

-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS();

-- 收集统计信息并查看进展
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '用户名',
    tabname => '表名',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
);

关键点:

  • last_analyzed 越新越好
  • stale_stats = 'YES' 表示可能过期
  • 大表建议开启自动统计信息收集
  • 被锁定的统计信息需要手动处理
posted @ 2025-10-21 16:39  一个苦逼的运维人  阅读(3)  评论(0)    收藏  举报