计划配置ob_plan_cache_percentage ，请问计划缓存最多可使用内存（内存上限绝对值）是否有上限

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.1
【问题描述】
目前存在问题首次查询需要近1000毫秒，二次查询进需要十几毫秒
1、为解决硬解析问题，计划配置ob_plan_cache_percentage ，请问计划缓存最多可使用内存（内存上限绝对值）是否有上限
2、但是相关同事答复，计划缓存有最高生效上线1-3G的样子。
3、故想咨询比如ob内存配置50G，可以配置ob_plan_cache_percentage 20%，使得计划缓存生效达到10G，让更多的硬解析存入缓存，避免被淘汰，二次查询还需要硬解析。

image
image1737×569 210 KB

@论坛小助手

1 个赞
#3

根据文档库中的信息，针对您提出的关于 ob_plan_cache_percentage 和计划缓存内存上限的问题，以下是详细的解答：

  1. 计划缓存最多可使用内存（内存上限绝对值）是否有上限

    根据文档信息，在 OceanBase 数据库中，计划缓存最多可使用内存（内存上限绝对值）确实存在一个计算方式，但并没有明确指出一个固定的绝对上限。具体来说，计划缓存最多可使用的内存是基于租户内存上限来计算的：

    • 计划缓存最多可使用内存（内存上限绝对值）= 租户内存上限 * ob_plan_cache_percentage / 100

    这意味着，如果您配置了较大的租户内存，并且设置了较高的 ob_plan_cache_percentage，那么计划缓存可以占用更多的内存。例如，如果您的 OB 内存配置为 50G，并且设置 ob_plan_cache_percentage 为 20%，则计划缓存最多可使用的内存为 10G。

  2. 关于同事反馈的计划缓存最高生效上线1-3G的情况

    您提到的相关同事答复可能是基于特定环境或版本的经验。在某些情况下，早期版本或者特定配置下，计划缓存的大小可能受到限制。然而，在最新的版本中（如您使用的 4.2.1），并没有明确的固定上限，而是依赖于上述公式进行动态调整。

  3. 配置建议

    如果您希望避免硬解析问题，并确保更多的执行计划能够存入缓存以减少被淘汰的可能性，您可以考虑增加 ob_plan_cache_percentage 的值。例如，将 ob_plan_cache_percentage 设置为 20%，使得计划缓存能够达到 10G（假设 OB 内存配置为 50G）。这可以通过以下 SQL 语句实现：

    -- 修改租户系统变量 ob_plan_cache_percentage
set global ob_plan_cache_percentage = 20;

    修改后，可以通过以下命令查看配置是否生效：

    show variables like 'ob_plan_cache_percentage';

    预期输出应显示新的百分比值。

  4. 注意事项

    • 调整 ob_plan_cache_percentage 时，请确保不会对其他关键操作产生负面影响。过大的计划缓存可能会占用过多内存资源，影响系统的整体性能。
    • 在生产环境中进行此类变更前，建议先在测试环境中验证效果，并监控系统表现。

综上所述，根据您的描述和提供的信息，适当提高 ob_plan_cache_percentage 可以帮助缓解首次查询耗时较长的问题，同时确保计划缓存有足够的空间存储更多执行计划。请参考上述建议进行调整，并密切关注系统性能变化。

以上信息依据的是《OceanBase 数据库》文档中关于 管理数据库/租户管理/租户常见操作/修改租户系统变量 的相关内容。

具体信息可参考以下文档:

  1. 数据库内存上限
  2. memory_limit
  3. 内存相关参数
  4. 产品 FAQ
  5. SQL 调优典型场景和案例
  6. SQL 性能问题的典型场景和排查思路
  7. 常见的 SQL 调优方式
1 个赞
#4

421版本没有您说的上限，可以参考如下文档配置

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002013762

image
image1796×794 131 KB

#5

首先执行计划缓存的内存的优先级是比较高的，不容易被淘汰的，还有执行计划缓存是不落盘的，你说这块的内存不够用，是否查看了日志看到SQL执行时间长是因为plan cache不足了导致执行计划被淘汰了，还是说统计信息的变化表数据的变化导致重新解析，或者SQL路由的节点没有缓存执行计划需要重新解析

#6

第一次使用ob，请问生产上已经调用的日志，如何在ob库中查询历史的sql执行明细。
另外应该不是统计表变化，我们查询下来比如按照日期查询，

如上截图，首次按照1月4日是慢的961ms，第二次1月4日就很快86ms
再次调用1月6日查询又很慢需要近1128ms，后面都是查询1月6日的，都是15毫秒上下。

#7

根据您提供的信息和文档库中的内容，针对生产环境中查询历史 SQL 执行明细以及分析首次查询较慢的问题，以下是详细的解答：

1. 查询历史 SQL 执行明细

在 OceanBase 中，您可以使用以下几种方法来查询历史 SQL 执行明细：

方法一：使用 gv$ob_sql_audit 视图

gv$ob_sql_audit 视图记录了 SQL 的执行情况，包括执行时间、SQL 文本、执行计划等。您可以根据需要的时间范围和条件进行查询。

-- 查询特定时间段内的 SQL 执行明细
SELECT 
    sql_id, 
    svr_ip, 
    svr_port, 
    tenant_id, 
    user_name, 
    database_name, 
    sql_text, 
    elapsed_time, 
    queue_time, 
    execute_time 
FROM 
    oceanbase.gv$ob_sql_audit
WHERE 
    tenant_id = 1001 -- 替换为您的租户 ID
    AND execute_time BETWEEN UNIX_TIMESTAMP('2024-01-04 00:00:00') * 1000000 
                         AND UNIX_TIMESTAMP('2024-01-07 23:59:59') * 1000000;

方法二：使用 OCP 平台

如果您使用的是 OCP（OceanBase Control Platform），OCP 提供了完善的慢 SQL 分析功能，可以直接通过图形界面查看和分析 SQL 执行情况，包括执行计划、执行频率、耗时等。具体步骤如下：

  • 登录 OCP 管控平台。
  • 导航到“SQL 审计”或“性能诊断”模块。
  • 选择相应的数据库实例和时间范围，查看 SQL 执行明细。

2. 分析首次查询较慢的原因

根据您描述的情况，首次查询较慢而后续查询较快的现象通常是由于以下几个原因导致的：

1. 硬解析 (Hard Parse)

首次执行 SQL 语句时，数据库需要进行硬解析，即生成执行计划并将其存入计划缓存中。这个过程相对复杂且耗时。后续查询可以直接从缓存中获取执行计划，因此速度更快。

2. 数据缓存 (Data Cache)

首次查询时，数据可能不在内存中，需要从磁盘读取，这会导致较高的 I/O 开销。后续查询可以利用已经加载到内存的数据缓存，从而显著提高查询速度。

3. 计划缓存 (Plan Cache)

如前所述，首次查询时需要生成并缓存执行计划。如果计划缓存配置不当，可能会导致首次查询较慢。确保 ob_plan_cache_percentage 设置合理，以避免频繁的硬解析。

4. 统计信息 (Statistics)

不准确的统计信息可能导致优化器选择次优的执行计划。建议定期收集和更新统计信息，以确保优化器能够做出更优的选择。

-- 手动开启收集辅助统计信息
CALL dbms_stats.gather_system_stats();

-- 查询租户的辅助统计信息
SELECT * FROM oceanbase.DBA_OB_AUX_STATISTICS;

3. 综合分析与建议

为了进一步分析和优化查询性能，您可以采取以下措施：

  • 检查执行计划：使用 EXPLAIN 或 EXPLAIN ANALYZE 查看 SQL 的执行计划，确保其合理性。

    EXPLAIN SELECT * FROM your_table WHERE date_column = '2024-01-04';

  • 分析 SQL 审计日志：结合 gv$ob_sql_audit 和 gv$plan_cache_plan_stat 视图，分析 SQL 的执行时间和资源消耗。

    -- 查询平均执行时间较长的 SQL
SELECT 
    avg_exe_usec, 
    svr_ip, 
    svr_port, 
    sql_id, 
    plan_id 
FROM 
    oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE 
    tenant_id = 1001
ORDER BY 
    avg_exe_usec DESC 
LIMIT 10;

  • 监控系统资源：使用 gv$ob_processlist 和 gv$plan_cache_plan_explain 视图，监控系统资源使用情况，查找潜在的瓶颈。

    -- 查询 SQL 流量在各 OBServer 节点间的分布情况及 QPS
SELECT 
    t2.zone, 
    t1.svr_ip,  
    COUNT(*) AS RPC_COUNT,
    AVG(t1.elapsed_time), 
    AVG(t1.queue_time)
FROM 
    oceanbase.GV$OB_SQL_AUDIT t1, 
    __all_server t2
WHERE 
    t1.svr_ip = t2.svr_ip
    AND tenant_id = 1001
    AND SQL_ID = 'BF7AA13A28DF50BA5C33FF19F1DBD8A9'
    AND IS_EXECUTOR_RPC = 0
    AND request_time > (time_to_usec(now()) - 1000000)
    AND request_time < time_to_usec(now())
GROUP BY 
    t1.svr_ip;

通过以上方法，您可以全面了解 SQL 的执行情况，并针对性地进行优化。希望这些信息对您有所帮助！

以上信息依据的是《OceanBase 数据库》文档中关于 SQL 性能分析 和 SQL 审计 的相关内容。

详情请参考:

  1. 分析慢 SQL 查询
  2. 定位和分析慢查询（一）
  3. SQL 诊断
  4. GET_STATS_HISTORY_AVAILABILITY
  5. 查询排名 TOP N 的 SQL
  6. oceanbase.DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY
  7. DBA_OB_AUX_STATISTICS
  8. 使用命令行进行运维
  9. 查询 SQL 流量分布情况及 QPS

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

#8

查看 gv$ob_plan_cache_plan_stat 看下执行计划的生成时间是什么，过滤条件是 SQL_ID

1 个赞