Posted By

mprabhuram on 08/31/10


Tagged

Oracle PLSQL


Versions (?)

Elapsed Time Calculation & SLEEP


 / Published in: SQL
 

Elapsed Time Calculation

  1. SET SERVEROUTPUT ON
  2. DECLARE
  3. v_seq_value NUMBER;
  4.  
  5. --Following are used for elaspsed time calculation
  6. v_start_time NUMBER;
  7. v_end_time NUMBER;
  8.  
  9. BEGIN
  10. --Retrieve sequence via SELECT INTO statement
  11. v_start_time := DBMS_UTILITY.GET_TIME;
  12.  
  13. DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.GET_TIME);
  14.  
  15. FOR i IN 1..100000 loop
  16. SELECT test_seq.NEXTVAL
  17. INTO v_seq_value
  18. FROM dual;
  19. END LOOP;
  20.  
  21. v_end_time := DBMS_UTILITY.GET_TIME;
  22. DBMS_OUTPUT.PUT_LINE
  23. ('Elapsed time to retrieve sequence via SELECT INTO: ' ||
  24. (v_end_time - v_start_time));
  25.  
  26. -- Retrieve sequence via PL/SQL expression
  27. v_start_time := DBMS_UTILITY.GET_TIME;
  28.  
  29. FOR i IN 1..100000 LOOP
  30. v_seq_value := test_seq.NEXTVAL;
  31. END LOOP;
  32.  
  33. v_end_time := DBMS_UTILITY.GET_TIME;
  34.  
  35. DBMS_OUTPUT.PUT_LINE
  36. ('Elapsed time to retrieve seqence via PL/SQL expression : ' ||
  37. (v_end_time - v_start_time));
  38.  
  39. END;
  40.  
  41.  
  42.  
  43. -- ALTERNAT LOGIC
  44.  
  45. SET SERVEROUTPUT ON
  46. DECLARE
  47. -- DECLARATIONS
  48. stime TIMESTAMP(9);
  49. etime TIMESTAMP(9);
  50. BEGIN
  51. dbms_output.enable(buffer_size => NULL);
  52. stime := SYSTIMESTAMP;
  53. user_lock.sleep(6000); -- 1 minute
  54. etime := SYSTIMESTAMP;
  55.  
  56. dbms_output.put_line(stime);
  57. dbms_output.put_line(etime);
  58. dbms_output.put_line(etime-stime);
  59.  
  60. END;
  61.  
  62.  
  63. --RESULT
  64.  
  65. 09/16/2010 16:20:06
  66. 09/16/2010 16:21:06
  67. +000000000 00:00:59.998283000
  68. PL/SQL procedure successfully completed.

Report this snippet  

You need to login to post a comment.