11 September 2010

What is that POWER (2,32) all about in the 10 September answer? (1376)

In the answer to the 10 September quiz regarding constructors, I offered an example of a type that could you use to calculate elapsed time of your program execution, down to the hundredth of a second. Here is the body of the type:
CREATE OR REPLACE TYPE BODY timer_t
AS
  CONSTRUCTOR FUNCTION timer_t (self IN OUT timer_t, title_in IN VARCHAR2)
     RETURN SELF AS RESULT
  IS
  BEGIN
     self.title := title_in;
     RETURN;
  END;

  MEMBER PROCEDURE start_timer
  IS
  BEGIN
     start_time := DBMS_UTILITY.get_time;
  END;

  MEMBER PROCEDURE show_elapsed_time
  IS
  BEGIN
     DBMS_OUTPUT.
      put_line (
        'Elapsed ' || 'for ' || self.title || ' = '
        || TO_CHAR (
MOD (DBMS_UTILITY.get_time - start_time + POWER (2, 32)
                  , POWER (2, 32)));
  END;
END;
A player wrote to me asking why I included two calls to "POWER(2,32)" - which made me realize that I had never explained that fairly odd-looking code. The general algorithm for using DBMS_UTILITY.get_time is to call it once before you start your code (start time) and then again when your code finishes (end time). Subtract start time from end time and you are left with the number of hundredths of seconds it took to run the code. So what's with the POWER calls? The integer returned by DBMS_UTILITY.get_time gets bigger and bigger until it hits an OS-specific limit. Then it set to 0 and starts incrementing again. If you call DBMS_UTILITY.get_time to get your start time just before this "roll over" occurs, you will end up with a very large negative elapsed time (impossible!). So instead, I take the difference, add a really big number to it (bigger than the rollover point for operating system), then mod by that number. In this way, I am guaranteed a positive elapsed time. I like to use this story as an example of how even the most trivial-seeming formula will end up being more complex than you originally expected. Which is why you must always hide rules and formulas behind functions.

No comments:

Post a Comment

Post a Comment