Re: Infinite loop, autonomous transactions, jobs...
From: prunoki <hegyvari_at_ardents.hu>
Date: Wed, 13 Oct 2010 03:48:13 -0700 (PDT)
Message-ID: <a9c55162-3c4a-46b3-a450-e970c42c8435_at_h25g2000vba.googlegroups.com>
On Oct 12, 1:11 pm, Tiago <diariodastril..._at_gmail.com> wrote:
> I have an external non-oracle application that is set to insert rows
> on a table on my oracle (10.2.0.5) database. It does that to integrate
> a system that does not use databases with our forms/reports based
> system.
>
> The vendor insists on creating a procedure that is on an infinite loop
> and processing the new rows as they come into the table. He said not
> to use triggers in any way, because a trigger could slow down the
> performance and cause the other system to lose messages. The infinite
> loop is always select'ing two tables: the one that is updated by the
> external system and other that has a column with a value of 0 or 1, if
> 1, goes on, if 0, breaks the loop. I know I could use a pipe
> (dbms_pipe), but I feel this is just complicating what is badly
> designed.
>
> Problem is that this setup hangs the entire session and sometimes it
> is hanging the SQL Developer session that is trying to query this
> table. What I was thinking: either creating a trigger calling an
> autonomous transaction or creating a trigger that would submit jobs
> (usually I'd rather use dbms_scheduler instead of dbms_job). Both ways
> I would not prevent the external application to insert the new rows
> and would not have an application on an infinite loop. To my objection
> of creating an infinite loop, vendor suggested starting the infinite
> loop within a run-once job...
>
> Has anyone faced a problem similar to this? If so, can you please
> advise with your experience? As always, time constraints prevent me to
> going full scale testing, I have to choose an architecture in the next
> few couple weeks and present it to our developer team...
>
> I thank you very much if you can give me any insight, thanks a lot!
>
> best regards,
> Tiago
Date: Wed, 13 Oct 2010 03:48:13 -0700 (PDT)
Message-ID: <a9c55162-3c4a-46b3-a450-e970c42c8435_at_h25g2000vba.googlegroups.com>
On Oct 12, 1:11 pm, Tiago <diariodastril..._at_gmail.com> wrote:
> I have an external non-oracle application that is set to insert rows
> on a table on my oracle (10.2.0.5) database. It does that to integrate
> a system that does not use databases with our forms/reports based
> system.
>
> The vendor insists on creating a procedure that is on an infinite loop
> and processing the new rows as they come into the table. He said not
> to use triggers in any way, because a trigger could slow down the
> performance and cause the other system to lose messages. The infinite
> loop is always select'ing two tables: the one that is updated by the
> external system and other that has a column with a value of 0 or 1, if
> 1, goes on, if 0, breaks the loop. I know I could use a pipe
> (dbms_pipe), but I feel this is just complicating what is badly
> designed.
>
> Problem is that this setup hangs the entire session and sometimes it
> is hanging the SQL Developer session that is trying to query this
> table. What I was thinking: either creating a trigger calling an
> autonomous transaction or creating a trigger that would submit jobs
> (usually I'd rather use dbms_scheduler instead of dbms_job). Both ways
> I would not prevent the external application to insert the new rows
> and would not have an application on an infinite loop. To my objection
> of creating an infinite loop, vendor suggested starting the infinite
> loop within a run-once job...
>
> Has anyone faced a problem similar to this? If so, can you please
> advise with your experience? As always, time constraints prevent me to
> going full scale testing, I have to choose an architecture in the next
> few couple weeks and present it to our developer team...
>
> I thank you very much if you can give me any insight, thanks a lot!
>
> best regards,
> Tiago
Sounds interesting. I would suggest you tell him how to do his job exactly as many times as he tells you how to do your job.
Just do it as you plan it (after doing the necessary homework) and test it. He will always have new ideas but it is your ... on the line. Insist on not letting the system go live without proper testing, you can simulate any load you want. And if their system loses data, whose problem is that exactly?
Krisztian Received on Wed Oct 13 2010 - 05:48:13 CDT