Re: Infinite loop, autonomous transactions, jobs...
Date: Tue, 12 Oct 2010 06:01:35 -0700 (PDT)
Message-ID: <83720695-a993-4b1b-8492-4b88055c1ca7_at_c10g2000yqh.googlegroups.com>
On Oct 12, 7:11 am, 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
Vendors often have no idea of how to properly design the database usage portion of their application.
How are the inserts done, single row at a time or bulk? You would not want to use row level triggers if direct load operations are used to insert the data but if single insert statements are used the trigger should probably not be an issue though I guess if would depend on exactly what the trigger does.
How does the infinite loop process the data? That is, does it use select for update? Does it delete the rows when it processes them? How does it know the data is new or already processed?
Since the logic is getting into what appear to be hung situations have you ran a trace on the process? This may provide you will a clue to what is going wrong and how to fix it.
HTH -- Mark D Powell -- Received on Tue Oct 12 2010 - 08:01:35 CDT