CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Sara Reimertz
Sara Reimertz

Reputation: 5

GET_TIME in Oracle

I have a code that is supposed to give me the compilation time for the procedure, but it only gets me 0 secs! Is the calculation wrong? Is there any other way?

    create or replace PROCEDURE proc_time AS
    v NUMBER := 1;
    x NUMBER := 0;
    counter number := 0;
    summ NUMBER;
    ex_start NUMBER;
    ex_end NUMBER;
    ex_time NUMBER;
    BEGIN
    ex_start := dbms_utility.get_time;
    while  counter <= 19
    loop  
        counter := counter + 1;
        summ := x+v;    
        x := v;
        V := summ;  
        dbms_output.put_line('Fibonacci nr'||counter||': '||summ);
    END loop;
  ex_end := DBMS_UTILITY.GET_TIME;
  ex_time := (ex_end-ex_start)/100; 
  DBMS_OUTPUT.PUT_LINE( 'Exekveringstid: ' || ex_time || ' sekunder.' );
END;
/

Upvotes: 0

Views: 5429

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

  • Perhaps, it is executing too quickly. You can try to increase the number of loops instead of 19 to something larger.

  • Also, it would give you the execution time, not the compilation time. The procedure is already compiled and stored in database. The time in the output is at run time, i.e. when you execute the procedure.

For example,

SQL> CREATE OR REPLACE PROCEDURE test_time
  2  AS
  3    l_start  NUMBER;
  4    l_loops  NUMBER := 10000000;
  5    l_number NUMBER := 0;
  6  BEGIN
  7    l_start := DBMS_UTILITY.get_time;
  8    FOR i IN 1 .. l_loops
  9    LOOP
 10      l_number := l_number + i;
 11    END LOOP;
 12    DBMS_OUTPUT.put_line('time taken: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 13  END test_time;
 14  /

Procedure created.

SQL> set serveroutput on
SQL> BEGIN
  2     test_time;
  3  END;
  4  /
time taken: 101 hsecs

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

As an alternative use the TIMESTAMP data type, this supports up to nanoseconds (provided your server supports it as well)

DECLARE
    ex_start TIMESTAMP(9);  
    Duration INTERVAL DAY TO SECOND;

    v        NUMBER := 1;
    x        NUMBER := 0;
    counter  NUMBER := 0;
    summ     NUMBER;

BEGIN
    ex_start := LOCALTIMESTAMP;  
    WHILE counter <= 10000 LOOP
        counter := counter + 1;
        summ    := x       +v;
        x       := v;
        V       := summ;
    END LOOP;

    Duration :=  LOCALTIMESTAMP - ex_start;
    DBMS_OUTPUT.PUT_LINE ( EXTRACT(SECOND FROM Duration) /1000||' msec.');
END;

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

As explained in the above answer by Lalit. Your snippet runs too fast to calculate the time difference. Try to increase the while loop with some greater number and here you go !!!

CREATE OR REPLACE PROCEDURE proc_time
AS
  v        NUMBER := 1;
  x        NUMBER := 0;
  counter  NUMBER := 0;
  summ     NUMBER;
  ex_start NUMBER;
  ex_end   NUMBER;
  ex_time  NUMBER;
BEGIN
  ex_start := dbms_utility.get_time;
  dbms_output.put_line('start time ==>'||NVL(ex_start,0));
  WHILE counter <= 190000
  LOOP
    counter := counter + 1;
    summ    := x       +v;
    x       := v;
    V       := summ;
    --        dbms_output.put_line('Fibonacci nr'||counter||': '||summ);
  END LOOP;
  ex_end := DBMS_UTILITY.GET_TIME;
  dbms_output.put_line('End time ==>'||ex_end);
  ex_time := (ex_end-ex_start);
  DBMS_OUTPUT.PUT_LINE( 'Exekveringstid: ' || ex_time || ' sekunder.' );
END;

set serveroutput on;
exec proc_time;

-------------------------------OUTPUT----------------------------------------

start time ==>1761607275
End time ==>1761607281
Exekveringstid: 6 sekunder.

Upvotes: 1

Related Questions