Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: Systimestamp gives same date time

    I have a procedure that takes minutes to complete execution. Just after the 'BEGIN' and just before the 'END', I wrote a log which logs the time the process started and finished using calls to systimestamp. The systimestamp values returned in both calls are always identical even though the time elapsed in between is often several minutes. Are the systimestamp values cached? If so how do I get clean values?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PEBKAC
    Code:
    21:47:20 SQL> @demo
    21:47:26 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
    
    Session altered.
    
    Elapsed: 00:00:00.01
    21:47:26 SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    09-JUN-09 09.47.26.304306 PM -07:00
    
    Elapsed: 00:00:00.00
    21:47:26 SQL> exec dbms_lock.sleep(5);
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:05.06
    21:47:31 SQL> select systimestamp from dual;
    
    SYSTIMESTAMP
    ---------------------------------------------------------------------------
    09-JUN-09 09.47.31.363771 PM -07:00
    
    Elapsed: 00:00:00.00
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2009
    Posts
    10
    Here is my code:

    declare
    start_time timestamp;
    end_time timestamp;

    begin

    start_time := systimestamp;

    procedure_takes_minutes; --call to the procedure which takes several minutes

    end_time := systimestamp;

    end;
    /



    Both start_time and end_time values are same

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Both start_time and end_time values are same
    Are you bragging or complaining?
    You are assigning same thing.

    Your code is different than mine.
    My code gives you desired results.
    Your code leaves you wanting.

    Your choice is to use your code or use mine.
    It does not matter to me which you use.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2009
    Posts
    10
    Yes both the codes are different. In my code, it is a plsql block. and i am complaining that though the call to the procedure takes several minutes for execution, why start_time and end_time return the same systimestamp?

    In your code, you are firing a select query at different times. It is nothing but obvious that it gives different timestamp.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >In my code, it is a plsql block.
    Do I need to embed my code in PL/SQL?
    I have proven how to get what you desire.
    Is your code so sacred you refuse to change it?
    Modify my SELECT with INTO your variables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Mar 2009
    Posts
    10
    Hey, it worked.... Thanks for the reply

  8. #8
    Join Date
    Mar 2009
    Posts
    10
    But it always shows a difference of 5 seconds. This is what it gives:

    start_time = 10-JUN-09 11.16.50.539000 AM
    end_time = 10-JUN-09 11.16.55.648000 AM

    start_time = 10-JUN-09 11.20.45.867000 AM
    end_time = 10-JUN-09 11.20.50.992000 AM

    Where as the procedure takes roughly 1min 15 sec

    Can you help

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But it always shows a difference of 5 seconds
    stop babbling.

    What shows a difference of 5 seconds?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Mar 2009
    Posts
    10
    can you briefly explain what you are doing in your code? what do we achieve by using dbms_lock.sleep?

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what do we achieve by using dbms_lock.sleep?
    are both GOOGLE & SEARCH of this forum are broken for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Mar 2009
    Posts
    10
    Actually I want to calculate the time taken by the procedure to complete execution. I cant use sleep for this purpose. Is there any alternative?

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure; subtract time when the procedure had started from the time it had finished. Here's an example; I'll use previously mentioned DBMS_LOCK package in order to simulate a procedure which takes 10 seconds to finish.

    As "date - date" returns number of days, we'll have to multiply the result with number of seconds in a day (24 hours * 60 minutes per hour * 60 seconds per minute):
    Code:
    SQL> declare
      2    l_start date;
      3    l_finish date;
      4  begin
      5    l_start := sysdate;
      6    dbms_lock.sleep(10);      -- wait 10 seconds
      7    l_finish := sysdate;
      8
      9    dbms_output.put_line('Time difference = ' || (l_finish - l_start) * (24 * 60 * 60));
     10  end;
     11  /
    Time difference = 10.00000000000000000000000000000000000002
    
    PL/SQL procedure successfully completed.
    
    SQL>

  14. #14
    Join Date
    Mar 2009
    Posts
    10
    Hello Littleoot...Thanks for the reply

    But, this is not what I want. If I explicitely use sleep(10) then for sure the difference will be 10 sec.
    The problem comes when I do it in a block which takes several minutes to complete execution. So to measure the time, I did 2 calls to sysdate/systimestamp. One just after the BEGIN and one just before the END. So ideally, these 2 calls should return different sysdate/systimestamp values so that the difference gives me the time taken by the block for processing. But both the calls return the same. Refer to the following code:

    declare
    start_time timestamp;
    end_time timestamp;

    begin

    start_time := systimestamp; --first call

    ........................--other
    ........................--stuff
    ........................--in
    ........................--the
    ........................--code

    end_time := systimestamp; --second call

    end;
    /

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sleep is only being used for his example. show the real code. you have a problem and we cant help you unless we can see how you are making the mistake. If it is long, then cut out the middle but show us the real code.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •