Date or Varchar field

[rudejohn]

My team is writing an application which will log metrics data for a transaction into an ORACLE database. The key data piece we need is a timestamp... for start and stop time. The events, though, sometimes take as little as 10 milliseconds, and Oracle only stores timestamps down to the SECOND. We need more granularity. So the way I see it, I have two options:

(1) Use the Date field in the database. Store the start and stop time that way, and ALSO store an integer which is calculated using timeInMillis(). Then the start/stop date field isn't USED in calculating the elapsed time.

(2) Create the timestamp in Java, which would include milliseconds, and insert it into a VARCHAR field. Then, when we pull it out, use Java processing to manipulate and calculate.

Which of these, in your opinions, is more efficient? Our development team is split...

************
RudeJohn
************

 

[SantaMufasa]

...Or, as Dan (ddrillich) so aptly pointed out last Thursday in thread759-687808 (in the Oracle 9 Forum), you can upgrade your Oracle to 9i (example: 9.2.0.4), which supports "TIMESTAMP" datatypes in addition to "DATE" datatypes, that can be as granular as your input provides:

select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff6') now from dual;

NOW
--------------------------
2003-10-27 11:53:04:721000

Notice in the example, above, I specified 6 digits of retention following seconds, but the granulaity of Oracle's SYSTIMESTAMP register is 3 digits (milliseconds).

To create a column in a table, I'll copy and paste from Dan's Thursday example:

create table tst (
t TIMESTAMP(1) -- specifies 1-digit max. retention: 1/10th second
);

insert into tst values ( to_timestamp('2002-03-21 03:45:33.910', 'YYYY-MM-DD HH:MI:SS.FF') );
insert into tst values ( to_timestamp('2002-03-21 03:45:33.490', 'YYYY-MM-DD HH:MI:SS.FF') );

select to_char(t) from tst;

21-MAR-02 03.45.33.9 AM
21-MAR-02 03.45.33.5 AM

So, dependent upon version, Oracle DOES support time granularity below seconds.

Dave
Sandy, Utah, USA @ 18:53 GMT, 11:53 Mountain Time

 

[rudejohn]

We are using an Oracle 8 shared server (on an AIX box). We do not control the server and thus upgrading to a new version of Oracle is not an option. If it was, I wouldn't have posted in the Oracle 8 forum.

~Rudejohn

************
RudeJohn
************

 

[SantaMufasa]

Rude,

I promise I was not trying to insult your intelligence. I just wanted to ensure you were aware of the 9i feature in case you weren't aware of it, and if 9i was an option. Next time, I'll be forewarned that you already have all the bases covered.

 

[sem]

You may use DBMS_UTILITY.GET_TIME to measure intervals in 100th of seconds, it's available since Oracle 7 at least.

Regards, Dima

 

[saintSlen]

Not quite enough info to definitively determine which is "more efficient"; it really depends on exactly what you are doing and wish to capture. Do you want to include network times from an app server to the db server or are you really looking for pure db transaction times? That said, I'll agree with Sem. I like dbms_utility.get_time for some things, but my web app uses a java timestamp and a number to log transaction times as defined by how long each app struts action ( I've got about 700 ) takes to process on my Oracle 8i Db. This has worked out really well for us.


example of dbms_utility.get_time:

CREATE OR REPLACE PROCEDURE p_time_test IS

v_start_time number;
i number := 0;
v_dummy number := 0;

BEGIN

v_start_time := dbms_utility.get_time;
for i in 1..10 loop
SELECT /*+ ORDERED USE_NL(x1) */ ......;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('time with hints = '|| round((dbms_utility.get_time-v_start_time)/100,2)|| ' seconds');

v_start_time := dbms_utility.get_time;
for i in 1..10 loop
SELECT ......;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('time with no hints = '|| round((dbms_utility.get_time-v_start_time)/100,2)|| ' seconds');


END;
/

 

[rudejohn]

Mufasa - sorry I was just clarifying, didn't mean to sound "rude."

The DBMS utility get_time sounds good. I'll research how we can implement it for our case. Thanks for the suggestions, everyone!

RJ



************
RudeJohn
************