长时SQL查询预估时间与进度

背景

对数据量较大的基表创建索引时，执行过程中没有任何进度提示，用户不知道发生了什么情况，可能会盲目取消sql的执行。

功能介绍

执行创建索引语句时，区分客户端(手动输入)，执行时间超过 LONGOPS_PRINT_INFO_TIME 后发送Notice信息以提示当前算子的预计执行时间以及执行进度信息。用户可根据视图 V$SESSION_LONGOPS 来查看预计剩余的执行时间、已有工作量等信息，依据这些信息可判断出当前sql是否在正常执行。

配置参数

LONGOPS_PRINT_INFO_TIME

线程级配置参数，单位秒，用于指定开始打印执行进度信息的时间，默认为60秒，最小值为6秒，最大值为2147483647秒。

使用场景

创建索引。暂不支持gis、分区索引。

示例:

--环境准备
drop table t;
create table t(a int,b varchar(10));

--插入大量数据，可根据机器状况调整数据量
begin
for i in 1..10000000 loop
insert into t values(i,'yujin');
end loop;
end;
/

--创建索引：
create index i_t on t(a);

--创建索引过程主要分为三个阶段：扫描、排序、添加索引。展示的进度信息也分为这三个阶段：
SQL=> create index i_t on t(b);
NOTICE, 创建索引共分为三个阶段，当前处于第一阶段顺序扫描，预计 42秒 后执行完成，详细执行进度信息请查看视图 V$SESSION_LONGOPS
NOTICE, 当前执行进度为 24%
NOTICE, 当前执行进度为 49%
NOTICE, 当前执行进度为 74%
NOTICE, 当前执行进度为 100%
NOTICE, 创建索引共分为三个阶段，第一阶段已完成，当前处于第二阶段排序，预计 48秒 后执行完成，详细执行进度信息请查看视图 V$SESSION_LONGOPS
NOTICE, 当前执行进度为 25%
NOTICE, 当前执行进度为 50%
NOTICE, 当前执行进度为 75%
NOTICE, 当前执行进度为 100%
NOTICE, 创建索引共分为三个阶段，第一二阶段已完成，当前处于第三阶段添加索引，预计 112秒 后执行完成，详细执行进度信息请查看视图 V$SESSION_LONGOPS
NOTICE, 当前执行进度为 25%
NOTICE, 当前执行进度为 50%
NOTICE, 当前执行进度为 75%
NOTICE, 当前执行进度为 100%
CREATE INDEX


查询视图信息：
SQL=> select * from V$SESSION_LONGOPS;
 SID | SERIAL |   PLAN_ID   | OPERATION_ID |   OPERATION   |    OPNAME    | TARGET | TARGET_DESC |   SOFAR   | TOTALWORK | UNITS |      START_TIME      | TIME_REMAINING | ELAPSED_SECONDS | USERNAME |        CURRENTSQL
-----+--------+-------------+--------------+---------------+--------------+--------+-------------+-----------+-----------+-------+----------------------+----------------+-----------------+----------+--------------------------
   4 |      1 |  2073401332 |            0 | seq scan      | seq scan     | TABLE  | TABLE       |     52378 |     52378 | block | 2024-10-21 18:10:27  |              0 |              54 | SYSDBA   | create index i_t on t(b)
   4 |      1 |  2073401332 |            0 | sort          | sort         | TUPLES | TUPLES      |  20000000 |  20000000 | row   | 2024-10-21 18:11:21  |              0 |              70 | SYSDBA   | create index i_t on t(b)
   4 |      1 |  2073401332 |            0 | append index  | append index | I_T    | I_T         |  20000000 |  20000000 | row   | 2024-10-21 18:12:31  |              0 |             111 | SYSDBA   | create index i_t on t(b)
(5 rows)