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'
表示可能过期- 大表建议开启自动统计信息收集
- 被锁定的统计信息需要手动处理