Re: Same Procedure runs and fails intermittently

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 27 May 2004 17:56:57 +0200
Message-ID: <c9534a$e20$1@news.BelWue.DE>

Tyler Smith Watu wrote:
> Ed Stevens <nospam_at_noway.nohow> wrote in message news:<6g99b0tj4343bbqmdla8a09rorr70ijvif_at_4ax.com>...
>

>>On 26 May 2004 05:24:59 -0700, watuni2000_at_yahoo.co.nz (Tyler Smith
>>Watu) wrote:
>>
>>
>>>
>>>select (sysdate-to_date(t.start_time,'MM/DD/YY:HH24:MI:SS'))
>>>,s.sid,s.serial#,
>>>replace(substr(s.client_info,0,8),',')
>>>  Name  from v$transaction t,v$session s
>>> where s.taddr=t.addr and to_date(t.start_time,
>>>'DD/MM/YY:HH24:MI:SS') < (SYSDATE-(1/(96)))
>>>             and substr(s.client_info,0,2) not  in ('PS')
>>

I haven't checked the correct format of v$transaction, but your two conversions don't match:

        select (sysdate-to_date(t.start_time,'MM/DD/YY:HH24:MI:SS')) then later on:         

          to_date(t.start_time, 'DD/MM/YY:HH24:MI:SS')

>>
>>Do some occurances of t.start_time have nulls?  And sometimes these
>>are picked up by your WHERE clause?  Just an off-the-cuff guess.

>
>
>
> In response to D Rolfe ------
> I am not sure if data should be at a transient stage at anytime.
> When you examine the ACID properties of an RDBMS
> Under consistency there should be no time when
> data is under an interim/transient stage.Data
> will be seen as under the initial or final stage.
>
> ED,s suggestion------- ,except I am
> missing something if a transaction starts
> there will be a start time if it does not it
> should not have a start_time of null.
>
> Thanks for taking the time to respond
>
> TY

I'll admit, that I didn't investigate this much, but ACID properties don't necessarily apply to dynamik performance views (I read this in Cary Milsap great book on Oracle Performance Tuning, but I'm sure there is something to be found in the Oracle Documentation as well) - especially they go not through the usuall undo/redo generation.

HTH Holger Received on Thu May 27 2004 - 10:56:57 CDT