To find out how long a piece of PL/SQL takes, you need a clock you can read before and after. DBMS_UTILITY.GET_TIME returns elapsed time and GET_CPU_TIME returns CPU time used by the session, both in hundredths of a second. The difference between two readings is the time spent between them.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_start := dbms_utility.get_time; -- elapsed, hundredths of a second v_cpu := dbms_utility.get_cpu_time; -- session CPU, hundredths of a second ... dbms_output.put_line(dbms_utility.get_time - v_start);
The values themselves mean nothing; only the difference between two readings does.
Time a Loop
The block adds up two million square roots and reports both times.
Example:
declare
v_elapsed pls_integer := dbms_utility.get_time; -- hundredths of a second
v_cpu pls_integer := dbms_utility.get_cpu_time;
v_n number := 0;
begin
for i in 1 .. 2000000 loop v_n := v_n + sqrt(i); end loop;
dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - v_elapsed) || ' cs, cpu: '
|| (dbms_utility.get_cpu_time - v_cpu) || ' cs');
end;
/Output:
elapsed: 40 cs, cpu: 40 cs PL/SQL procedure successfully completed.
The loop is pure computation, so elapsed time and CPU time are the same: about 0.4 seconds. Your numbers will differ.
Waiting Compared with Working
Example:
declare
v_elapsed pls_integer := dbms_utility.get_time;
v_cpu pls_integer := dbms_utility.get_cpu_time;
begin
dbms_session.sleep(0.5); -- waiting, not working
dbms_output.put_line('elapsed: ' || (dbms_utility.get_time - v_elapsed) || ' cs, cpu: '
|| (dbms_utility.get_cpu_time - v_cpu) || ' cs');
end;
/Output:
elapsed: 50 cs, cpu: 0 cs PL/SQL procedure successfully completed.
DBMS_SESSION.SLEEP waits half a second: 50 hundredths of elapsed time and no CPU. When elapsed time is much larger than CPU time, the code is waiting, for locks, disk, or the network, rather than computing.
Things to Know
- The resolution is a hundredth of a second; time a short piece of code many times in a loop to measure it.
- The counter can wrap around, so a difference can come out negative in rare cases; for wall-clock times with microseconds, subtract SYSTIMESTAMP values instead.
- For a breakdown of time by subprogram, use the hierarchical profiler rather than many timers.
Related Guides
Conclusion
DBMS_UTILITY.GET_TIME and GET_CPU_TIME measure elapsed and CPU time in hundredths of a second. Read them before and after the code, compare the two to tell computing from waiting, and repeat short code to get measurable times.
