如何评估sql语句还有多久才能执行完

OceanBase 技术问题
,
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.5.7
【问题描述】分区表，按日分区，表中有3个月的数据，每天约3000万的数据，表上有1个global index无法改造成local index，在drop partition的时候需要update global index，怎么评估drop partition…update global indexsql语句还有多久才能执行完?
查询GV$SESSION_LONGOPS，里面看到的time_remaining是0
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

#3

@论坛小助手

#4

根据您的描述，您在OceanBase 4.2.5.7版本中遇到了一个关于执行drop partition操作时更新global index的时间预估问题。针对这一情况，您可以使用obdiag工具来收集相关日志，并尝试通过分析这些信息来估计完成时间或识别可能导致长时间运行的根本原因。

虽然GV$SESSION_LONGOPS显示的time_remaining为0可能意味着Oracle无法准确预测剩余时间（可能是由于统计信息不足或其他因素），我们仍可以通过obdiag进一步调查这个问题。

日志采集场景: [DDL过程中报磁盘空间不足的问题]
考虑到您的问题是与DDL操作相关的，尽管直接提到的是时间估计问题而非磁盘空间不足，但在DDL操作过程中遇到的异常情况可能也包括了资源限制方面的问题。这里采用最接近您当前状况的日志收集命令。
日志采集命令: obdiag gather scene run --scene=observer.ddl_disk_full

请注意，这里的“DDL过程中报磁盘空间不足的问题”并不完全对应于您的实际情况，但选择此选项是因为它涉及到了DDL操作过程中的问题。对于您的具体情况——即评估特定DDL语句的执行进度和预计完成时间——当前的obdiag文档似乎没有提供直接的支持功能。然而，上述日志收集仍然可以帮助诊断潜在的问题所在。

另外，为了更有效地帮助您解决问题，请允许我询问以下几个问题：

  1. 在执行drop partition并更新global index的过程中，是否有观察到系统性能下降、响应变慢或其他异常现象？
  2. 执行此类DDL语句前，是否对表进行了适当的维护工作，例如重建索引或进行统计信息收集？最近一次是什么时候进行的？
  3. 该分区表及其关联的全局索引的具体定义是怎样的？能否提供创建它们时使用的DDL脚本片段？
  4. 此次drop partition影响的数据量大概有多少？具体来说，将要被删除的分区覆盖了多少天的数据？

希望上述建议能有所帮助。如果您可以执行提供的命令并将得到的日志文件分享给我们的话，我们会更有针对性地协助您解决这一问题。

附上敏捷诊断工具 obdiag 使用帮助链接

#5

根据您提供的信息，要评估 DROP PARTITION 语句更新全局索引所需的时间，尤其是在看到 GV$SESSION_LONGOPS 视图中 time_remaining 字段为0的情况下，可以采取以下几种方法进行评估。这些方法可以帮助您更准确地判断 SQL 执行的进度及预计完成时间。

方法 1: 查询 GV$OB_PROCESSLIST

通过查询 GV$OB_PROCESSLIST 可以获取当前正在执行 SQL 的状态信息。特别是关注 TIME 和 STATE 字段，以及 TOTAL_TIME 和 RETRY_CNT 字段。如果 STATE 字段显示为 ACTIVE 并且 TIME 持续增长，则表明 SQL 仍在正常执行中。

-- MySQL 模式
SELECT TENANT, INFO, TRACE_ID, STATE, TIME, TOTAL_TIME, RETRY_CNT, RETRY_INFO
FROM oceanbase.GV$OB_PROCESSLIST;

-- Oracle 模式
SELECT TENANT, INFO, TRACE_ID, STATE, TIME, TOTAL_TIME, RETRY_CNT, RETRY_INFO
FROM sys.GV$OB_PROCESSLIST;
  • TIME：SQL 从开始执行到现在的累计时间。
  • STATE：当前 SQL 的执行状态。
  • TOTAL_TIME：包含每次重试在内的累计执行时间。
  • RETRY_CNT：SQL 重试次数。
  • RETRY_INFO：最后一次重试的原因。

方法 2: 查询 GV$OB_SQL_AUDIT

使用 GV$OB_SQL_AUDIT 视图来检查该 SQL 语句的历史性能数据。可以通过比较历史相似操作的耗时情况，来预估本次操作的大致耗时。

SELECT tenant_id, elapsed_time, usec_to_time(request_time), substr(query_sql, 1, 50) 
FROM GV$OB_SQL_AUDIT 
WHERE tenant_id=1001 AND elapsed_time > 100000 AND request_time > time_to_usec('2021-10-08 12:00:00') 
ORDER BY elapsed_time DESC LIMIT 40;

这个查询语句用于查找指定租户（例如 tenant_id=1001）内超过特定耗时阈值（例如 elapsed_time > 100000 us）的 SQL 语句，并按耗时降序排列，以便于找到历史上类似的操作作为参考。

方法 3: 使用 OBProxy 或 OCP 监控

如果您部署了 OBProxy 或者使用 OCP (OceanBase Control Platform)，也可以利用其内置的监控工具来查看 SQL 的实时执行状态及其性能指标。这通常能提供比单纯依赖系统视图更加直观、详细的分析结果。

方法 4: 考虑手动估算

对于大规模的数据变更操作如 DROP PARTITION，还可以基于以下因素手动粗略估算：

  • 每天数据量大约 3000 万行。
  • 全局索引的存在意味着在删除分区时需要对这些索引条目进行更新。
  • 基于以往经验或者小规模测试的结果，估计单个单位数据量处理所需时间，再乘以总量得到大致总耗时。

以上方法应能够帮助您更好地理解 SQL 执行进度并做出合理预期。但请注意，在生产环境中执行这类耗时长且资源消耗大的操作前，建议先进行充分的规划与测试，确保不会对在线业务造成影响。如果可能的话，尽量选择业务低峰期进行此类维护工作。

检索到的文档如下:

  1. 配置管理介绍
  2. dump_data_dictionary_to_log_interval
  3. internal_sql_execute_timeout
  4. GV$OB_SQL_AUDIT
  5. SQL 性能问题的典型场景和排查思路
  6. 在您开始前
  7. partition_table_check_interval
  8. 客户端编程语言
  9. 并行执行问题诊断
  10. ilog_index_expire_time