Calculating and Displaying Throughput Metrics (RPM) - Perceptive Content Database IN_DB_UTIL Package for Oracle Server - Foundation 24.1 - Foundation 24.1 - Ready - Perceptive Content - external

The following example combines the GET_SECONDS function and the GET_DURATION and GET_RPM stored procedures to demonstrate how you could use them to record and or display throughput metrics of various operations.

Throughput is measured as Rows Per Minute (RPM)

SET TIMING ON; 
SET SERVEROUTPUT ON;  


DECLARE  
V_START_TIMESTAMP   TIMESTAMP; 
V_ROWS_PER_MINUTE   NUMBER; 
V_TOTAL_ROWS        NUMBER; 
V_ELAPSED_SECONDS   NUMBER; 
V_ELAPSED_MESSAGE   VARCHAR2(200); 
V_ERROR_MSG         VARCHAR2(500);  

OBJECT_EXISTS EXCEPTION; PRAGMA EXCEPTION_INIT(OBJECT_EXISTS, -955);  

 BEGIN   
   -- Record the start time   
   V_START_TIMESTAMP := CURRENT_TIMESTAMP;      

   -- Execute any operation that affects rows   
   EXECUTE IMMEDIATE 'CREATE TABLE INUSER.IN_AUDIT_DETAIL_BAK AS SELECT * FROM INUSER.IN_AUDIT_DETAIL';      

   -- Record the number of rows   
   V_TOTAL_ROWS := SQL%ROWCOUNT;      

   -- Calculate Duration in Seconds   
   IN_DB_UTIL.GET_SECONDS(V_START_TIMESTAMP, CURRENT_TIMESTAMP, V_ELAPSED_SECONDS);      

   -- Get Duration Message to Display   
   IN_DB_UTIL.GET_DURATION(V_ELAPSED_SECONDS, V_ELAPSED_MESSAGE);      

   -- Get Rows Per Minute   
   IN_DB_UTIL.GET_RPM(V_TOTAL_ROWS, V_ELAPSED_SECONDS, V_ROWS_PER_MINUTE);      

   -- Rows Affected   
   DBMS_OUTPUT.PUT_LINE(CHR(10)||'Total Rows Affected was '||TRIM(TO_CHAR(V_TOTAL_ROWS, '999,999')));      

   -- Display Elapsed Time Message   
   DBMS_OUTPUT.PUT_LINE('Total Duration was '||V_ELAPSED_MESSAGE);    

   -- Display Throughput RPM (Rows Per Minute)   
   DBMS_OUTPUT.PUT_LINE('Total Rows Per Minute was '||TRIM(TO_CHAR(V_ROWS_PER_MINUTE, '999,999,999')));        

   -- Drop the backup table   
   EXECUTE IMMEDIATE 'DROP TABLE INUSER.IN_AUDIT_DETAIL_BAK';      

EXCEPTION     
   WHEN OBJECT_EXISTS THEN       
      DBMS_OUTPUT.PUT_LINE(CHR(10)||'The INUSER.IN_AUDIT_DETAIL_BAK table already exists.');     
   WHEN OTHERS THEN       V_ERROR_MSG := SUBSTR(SQLERRM, 1, 500);       
      DBMS_OUTPUT.PUT_LINE(CHR(10)||'ERROR - '||V_ERROR_MSG); 
END; 
/ 

Example Output

Total Rows Affected was 6,272 
Total Duration was .041 Seconds 
Total Rows Per Minute was 9,178,537