SQL Tuning – DBMS_UTILITY.CPU_TIME

Set Timing On

It is very common when developing some new piece of SQL to want to know how long it takes to run. For this you can use the SET TIMING ON command within SQL Plus. It’s very handy for a quick glance at how long your query is going to take to run.

Accuracy = DBMS_UTILITY

If, however, you wanted to run a piece of code in a SQL loop or multiple pieces of SQL embedded within FOR LOOPs and IF, THEN, ELSE statements, you could use the oracle supplied DBMS_UTILITY package to get details of the CPU time used by particular queries and output that information at the end.

DBMS_UTILITY Example

Below is an example of how you could do that. The example is very simple, but is only to illustrate a point. The time to execute the FOR LOOP is summarised in the l_cpu_total variable and output at the end using dbms_output.

SET SERVEROUT ON
DECLARE

l_cpu                    NUMBER;
l_cpu2                   NUMBER;
l_cpu_total              NUMBER;
l_sysdate                DATE;

BEGIN

l_cpu := dbms_utility.get_cpu_time;

FOR i in 1..100000 LOOP
SELECT count(*)
INTO l_sysdate
FROM tableA;
END LOOP;

l_cpu2 := dbms_utility.get_cpu_time;
l_cpu_total := l_cpu2 - l_cpu;

DBMS_OUTPUT.PUT_LINE(l_cpu);
DBMS_OUTPUT.PUT_LINE(l_cpu2);
DBMS_OUTPUT.PUT_LINE(l_cpu_total);
END;

This is just one part of SQL tuning, and you will most likely want to know details about explain plans as well, which are absolutely essential for SQL writing and tuning. And if you come across the ORA-22905 error message, we have the solution for you.

Like it, share it...

Category: SQL Tuning