Advanced Search

Elapsed Time Calculation & SLEEP

/ Published in: SQL
Save to your folder(s)
Elapsed Time Calculation

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.