面试宝典:介绍下Oracle数据库动态性能视图 V$SQL_WORKAREA_HISTOGRAM

最新推荐文章于 2025-11-26 14:12:29 发布

原创最新推荐文章于 2025-11-26 14:12:29 发布 · 840 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

🗃️ Oracle 19C V$SQL_WORKAREA_HISTOGRAM 动态性能视图详解

1. 概述与核心作用

V$SQL_WORKAREA_HISTOGRAM 是 Oracle 数据库中一个重要的性能视图，它以直方图形式统计 SQL 工作区内存使用情况，帮助DBA了解不同内存大小范围内SQL操作的执行效率和分布情况。

核心作用：

内存使用分布分析：显示不同内存大小范围内工作区操作的统计分布
PGA优化指导：为 PGA_AGGREGATE_TARGET 参数优化提供数据依据
性能效率评估：统计不同执行模式（最优、单遍、多遍）的操作数量
趋势分析：分析工作区内存使用模式的变化趋势

2. 使用场景

PGA内存优化：确定最佳的 PGA_AGGREGATE_TARGET 大小
性能基准建立：建立工作区内存使用的性能基准
容量规划：规划系统内存需求，特别是PGA内存
效率监控：监控SQL操作执行效率的变化趋势
问题诊断：诊断由于内存不足导致的性能问题

3. 字段含义详解

以下是 V$SQL_WORKAREA_HISTOGRAM 视图的主要字段及其含义：

字段名称	数据类型	含义说明
LOW_OPTIMAL_SIZE	NUMBER	直方图区间的下限值（字节）
HIGH_OPTIMAL_SIZE	NUMBER	直方图区间的上限值（字节）
OPTIMAL_EXECUTIONS	NUMBER	在该区间内以最优模式执行的次数
ONEPASS_EXECUTIONS	NUMBER	在该区间内以单遍模式执行的次数
MULTIPASSES_EXECUTIONS	NUMBER	在该区间内以多遍模式执行的次数
TOTAL_EXECUTIONS	NUMBER	在该区间内的总执行次数
LAST_EXECUTION	VARCHAR2(10)	最后一次执行的模式
LAST_DEGREE	NUMBER	最后一次执行的并行度
CON_ID	NUMBER	容器ID（多租户环境）
INST_ID	NUMBER	实例ID（RAC环境）
BUCKET_ID	NUMBER	直方图桶的ID
MIN_ACTIVE_MEM	NUMBER	该区间内活动内存的最小值
MAX_ACTIVE_MEM	NUMBER	该区间内活动内存的最大值
AVG_ACTIVE_MEM	NUMBER	该区间内活动内存的平均值

4. 相关视图与基表

基表：

V$SQL_WORKAREA_HISTOGRAM 是基于内存结构的视图，其底层基表是 X$KQLFSH，这是Oracle内部的内存结构，存储工作区内存使用的直方图统计信息。

5. 底层原理与工作机制

直方图统计机制：

内存区间划分：Oracle将工作区内存大小划分为多个区间（桶）
执行统计收集：对于每个SQL工作区操作，Oracle根据其最优内存大小将其归类到对应的区间
模式统计：统计每个区间内不同执行模式的操作次数
定期更新：直方图数据随着SQL执行不断更新

执行模式统计：

最优模式：工作区完全在内存中执行，效率最高
单遍模式：需要一次磁盘I/O，效率中等
多遍模式：需要多次磁盘I/O，效率最低

数据收集流程：

SQL执行 → 工作区内存分配 → 执行模式确定 → 
区间归类 → 统计计数更新 → 直方图数据生成

6. 常用查询 SQL

1. 查看工作区内存使用直方图

SELECT 
    ROUND(low_optimal_size/1024) || 'K-' || 
    ROUND(high_optimal_size/1024) || 'K' as memory_range,
    optimal_executions as optimal,
    onepass_executions as onepass,
    multipasses_executions as multipass,
    total_executions as total,
    ROUND((optimal_executions / total_executions) * 100, 2) as optimal_pct
FROM v$sql_workarea_histogram
WHERE total_executions > 0
ORDER BY low_optimal_size;

2. 分析PGA内存效率

SELECT 
    SUM(total_executions) total_execs,
    SUM(optimal_executions) optimal_execs,
    SUM(onepass_executions) onepass_execs,
    SUM(multipasses_executions) multipass_execs,
    ROUND(SUM(optimal_executions) * 100 / SUM(total_executions), 2) optimal_pct,
    ROUND(SUM(onepass_executions) * 100 / SUM(total_executions), 2) onepass_pct,
    ROUND(SUM(multipasses_executions) * 100 / SUM(total_executions), 2) multipass_pct
FROM v$sql_workarea_histogram
WHERE total_executions > 0;

3. 识别需要优化的内存区间

SELECT 
    ROUND(low_optimal_size/1024/1024, 1) || 'M-' || 
    ROUND(high_optimal_size/1024/1024, 1) || 'M' as memory_range_mb,
    multipasses_executions,
    total_executions,
    ROUND((multipasses_executions / total_executions) * 100, 2) as multipass_pct
FROM v$sql_workarea_histogram
WHERE total_executions > 100
AND multipasses_executions > 0
ORDER BY multipass_pct DESC;

4. 监控内存使用趋势

SELECT 
    CASE 
        WHEN high_optimal_size < 1024*1024 THEN '0-1M'
        WHEN high_optimal_size < 10*1024*1024 THEN '1M-10M'
        WHEN high_optimal_size < 100*1024*1024 THEN '10M-100M'
        ELSE '100M+'
    END as memory_category,
    SUM(optimal_executions) optimal,
    SUM(onepass_executions) onepass,
    SUM(multipasses_executions) multipass,
    SUM(total_executions) total
FROM v$sql_workarea_histogram
GROUP BY 
    CASE 
        WHEN high_optimal_size < 1024*1024 THEN '0-1M'
        WHEN high_optimal_size < 10*1024*1024 THEN '1M-10M'
        WHEN high_optimal_size < 100*1024*1024 THEN '10M-100M'
        ELSE '100M+'
    END
ORDER BY memory_category;

5. 结合PGA建议视图进行优化分析

WITH histogram_stats AS (
    SELECT 
        SUM(total_executions) total_execs,
        SUM(optimal_executions) optimal_execs,
        ROUND(SUM(optimal_executions) * 100 / SUM(total_executions), 2) optimal_pct
    FROM v$sql_workarea_histogram
    WHERE total_executions > 0
)
SELECT 
    h.optimal_pct,
    p.pga_target_for_estimate/1024/1024 target_mb,
    p.pga_target_factor,
    p.estd_extra_bytes_rw/1024/1024 estd_extra_mb,
    p.estd_pga_cache_hit_percentage estd_hit_pct
FROM histogram_stats h,
     v$pga_target_advice p
WHERE p.pga_target_factor = 1
ORDER BY p.pga_target_for_estimate;