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