Advanced Search

Return to Snippet

Revision: 31173
at September 17, 2010 06:22 by mprabhuram
Updated Code 
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.

Revision: 31172
at August 31, 2010 12:54 by mprabhuram
Initial Code 
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;
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Elapsed Time Calculation

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Elapsed Time Calculation & SLEEP

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
Oracle

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL