Re: Intermittent Failure When Accessing DB Constantly?

lazyboy_2k_at_yahoo.com wrote:
> Hi All,
>
> We have a script in a cron job which will login our oracle 9.2.0.4 DB
> constantly (meaning every minutes, 7 days/week, & 365 days/yr), and
> the script is to check an availability of a record in a table which is
> passed through a variable in a script. The script is running
> perfectly fine except that it has an intermittent failure :( The
> weird thing is that the failure happens at exactly the same time but
> at random day. My 1st logical thought is that we may have some other
> job in a system which interferes this job; However, when I look
> through the system crontab file and other places & can't find any cron
> job which runs at that time. Please keep in mind that this is an
> intermittent failure. I already look through the log file and the
> failure indicates that "the record is not available in a table xyz",

Is it possible that the routine which is supposed to write this record to the table has not yet committed? If so, then your other session will not be able to read this record until COMMIT.

> but I know for sure that record is there. I know that accessing DB
> like this is that we are looking for troubles, but there is a strong
> argument that we should be able to access oracle DB at any time (which
> is true), but I don't have info to say "Not to that extents".

It might be better if you had a persistent connection. But that is the only place that comes to mind right now. Making a connection over and over again in such a short time can cause problems. But issuing the query over and over again should not (provided the query is optimized).

>
> PROD Cancelled at Sun Apr 8 00:34:00 PDT 2007
> PROD Cancelled at Tue Apr 10 00:34:00 PDT 2007
> PROD Cancelled at Sun Apr 15 00:34:00 PDT 2007
> PROD Cancelled at Wed Apr 18 00:34:00 PDT 2007
> PROD Cancelled at Mon Apr 30 00:34:00 PDT 2007
>
> Question: Is there a limitation in accessing oracle DB constantly &
> intensively like the way we have? If there is, could you please
> provide some info? However, if there is not, please let me know if
> there are any other ideas/ways to troubleshoot this problem?

Like I said, the connection request can take a bit of time. Otherwise, constant queries on the db aren't a problem so long as the queries are optimized. I have one database that sees the same query, over and over again, approximately 300-400 times per second. (The vendor's idea...not mine. And I've been trying to get this changed). The query is optimized so it runs in sub-second time. The only impact is that this does tax my CPU's a bit (to perform logical reads, etc). So long as I have the server resources, all is fine.

> Any info/suggestions/inputs are greatly appreciated.
>
> Btw, sorry for a long email but i want you to have a detail info as
> much as possible.
>
> TIA,
> -Chris
>

Do you have Oracle errors? Or is it just that the record is missing and you expect it to be there? If the former, then post the errors. If the latter, then it looks like an application issue, not an Oracle issue.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com