SET SERVEROUTPUT ON
DECLARE
  v_seq_value     NUMBER;
  
  --Following are used for elaspsed time calculation
  v_start_time    NUMBER;
  v_end_time      NUMBER;
  
BEGIN
  --Retrieve sequence via SELECT INTO statement
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.GET_TIME);
  
  FOR i in 1..100000 loop
    SELECT test_seq.NEXTVAL
      INTO v_seq_value
      FROM dual;
  END LOOP;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE
    ('Elapsed time to retrieve sequence via SELECT INTO: ' ||
    (v_end_time - v_start_time));
    
  -- Retrieve sequence via PL/SQL expression
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  FOR i in 1..100000 LOOP
    v_seq_value := test_seq.NEXTVAL;
  END LOOP;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE
    ('Elapsed time to retrieve seqence via PL/SQL expression : ' ||
      (v_end_time - v_start_time));
      
END;



-- ALTERNAT LOGIC

SET SERVEROUTPUT ON
DECLARE
-- DECLARATIONS
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
BEGIN
  dbms_output.enable(buffer_size => NULL);
  stime := SYSTIMESTAMP;
  user_lock.sleep(6000); -- 1 minute
  etime := SYSTIMESTAMP;
  
  dbms_output.put_line(stime);
  dbms_output.put_line(etime);
  dbms_output.put_line(etime-stime);
                      
END;


--RESULT

09/16/2010 16:20:06
09/16/2010 16:21:06
+000000000 00:00:59.998283000
PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON
DECLARE
  v_seq_value     NUMBER;
  
  --Following are used for elaspsed time calculation
  v_start_time    NUMBER;
  v_end_time      NUMBER;
  
BEGIN
  --Retrieve sequence via SELECT INTO statement
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.GET_TIME);
  
  FOR i in 1..100000 loop
    SELECT test_seq.NEXTVAL
      INTO v_seq_value
      FROM dual;
  END LOOP;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE
    ('Elapsed time to retrieve sequence via SELECT INTO: ' ||
    (v_end_time - v_start_time));
    
  -- Retrieve sequence via PL/SQL expression
  v_start_time := DBMS_UTILITY.GET_TIME;
  
  FOR i in 1..100000 LOOP
    v_seq_value := test_seq.NEXTVAL;
  END LOOP;
  
  v_end_time := DBMS_UTILITY.GET_TIME;
  
  DBMS_OUTPUT.PUT_LINE
    ('Elapsed time to retrieve seqence via PL/SQL expression : ' ||
      (v_end_time - v_start_time));
      
END;

Elapsed Time Calculation

Elapsed Time Calculation & SLEEP

Oracle

SQL