时间戳

首页 > 解决方案 > 相同 DML 的相同执行时间

sql - 相同 DML 的相同执行时间

问题描述

我想在有和没有触发器的表上对事务执行时间、DML 进行一些测试。

为此，我运行相同的 DML 5 次并收集执行时间。我将开始和停止时间保留在 2 个变量中，但从第二次执行开始，开始和停止时间与第一次停止时间相同。

这是代码：

declare
    l_start number;
    l_stop  number;
begin
        for i in 1 .. 5 loop
        l_start := dbms_utility.get_time;
        update table_1 set field_1 = 5 where rowid = 'ABFpFhAC2AAAAkNAAB';
        l_stop := dbms_utility.get_time;
        dbms_output.put_line('l_start: ' || l_start);
        dbms_output.put_line('l_stop: ' || l_stop);
        dbms_output.put_line('execution time: ' || to_char((l_stop - l_start) / 100) || ' miliseconds');
        rollback;
    end loop;
end;

...这是输出：

l_start: -1012926990
l_stop: -1012926988
execution time: .02
miliseconds l_start: -1012926988
l_stop: -1012926988
execution time: 0
miliseconds l_start: -1012926988
l_stop: -1012926988
execution time: 0
miliseconds l_start: -1012926988
l_stop: -1012926988
execution time ：0 毫秒
l_start：-1012926988
l_stop：-1012926988
执行时间：0 毫秒

谁能解释为什么每次循环通过时 l_start 和 l_stop 都没有重新初始化？

标签： sqloracle

解决方案

您测量的是秒数而不是分数。改用时间戳：

declare
    l_start number;
    l_stop  number;
    t_start timestamp;
    t_stop  timestamp;
begin
    for i in 1 .. 5 loop
        t_start := systimestamp;
        l_start := dbms_utility.get_time;   
        dbms_session.sleep(i/1000);
        l_stop := dbms_utility.get_time;
        t_stop := systimestamp;
        dbms_output.put_line(i || ' n start/stop: ' || l_start || ' / ' || l_stop || '     execution time: ' || to_char((l_stop - l_start) / 100) || ' seconds'  );
        dbms_output.put_line(i || ' t start/stop: ' || to_char(t_start,'SSXFF') || ' / ' || to_char(t_stop,'SSXFF') || '     execution time: ' || to_char(t_stop - t_start) || ' seconds'  );
    end loop;
end;


1 n start/stop: 43402125 / 43402125     execution time: 0 seconds
1 t start/stop: 02,404420000 / 02,404459000     execution time: +000000000 00:00:00.000039000 seconds
2 n start/stop: 43402125 / 43402125     execution time: 0 seconds
2 t start/stop: 02,404506000 / 02,404514000     execution time: +000000000 00:00:00.000008000 seconds
3 n start/stop: 43402125 / 43402125     execution time: 0 seconds
3 t start/stop: 02,404525000 / 02,404530000     execution time: +000000000 00:00:00.000005000 seconds
4 n start/stop: 43402125 / 43402125     execution time: 0 seconds
4 t start/stop: 02,404538000 / 02,404542000     execution time: +000000000 00:00:00.000004000 seconds
5 n start/stop: 43402125 / 43402126     execution time: ,01 seconds
5 t start/stop: 02,404550000 / 02,414774000     execution time: +000000000 00:00:00.010224000 seconds

对于非常小的时间段，这也不够细化。然后，您必须更频繁地执行此过程以获得良好的时机。

推荐阅读