Re: How to read parent session data - without forcing a commit in the parent

On Oct 21, 10:49 am, sybrandb <sybra..._at_gmail.com> wrote:
> On 21 okt, 16:27, nis..._at_gmail.com wrote:
>
>
>
> > On 21 Oct, 15:17, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On 21 okt, 15:26, nis..._at_gmail.com wrote:
>
> > > > Hi Folks,
>
> > > > I have an interesting problem. Basically, I have an oracle job that I
> > > > need to speed up as we are exceeding our allocated time window. The
> > > > oracle job consists of a large number of small changes across multiple
> > > > 'source' tables and then one of my functions is called to try and
> > > > process this source data in the fastest possible time - in order to
> > > > create a single huge table from the uncommitted data. If there is an
> > > > error in my processing, I simply raise an exception that causes a
> > > > rollback of the source data. So far so good.
>
> > > > Now here's the problem. The 'huge' table that I am building consists
> > > > of 2 distinct sets of SELECT queries on the uncomitted source data
> > > > tables. There is no problem in doing this serially - other than it
> > > > takes us 4 hours which is too long. To speed things up, I wanted to
> > > > run the 2 distinct SELECT queries in parallel - and therein lies the
> > > > problem. I cannot get parallel SELECT functions to run without them
> > > > forcing the source data to become committed. Is there any way to get
> > > > around this?? Below is a cut down version of the top level function
> > > > that I used to kick off the parallel functions that build the huge
> > > > table from the source tables. It works - but we cannot rollback
> > > > because the parallel functions force the source data to become
> > > > comitted!!! is there no way for a child session to have access to its
> > > > parents uncomitted data?
>
> > > > Thanks folks..
> > > > Nis.
>
> > > > PROCEDURE testParallel
> > > >   IS
>
> > > >     p_ba varchar2(100) :='CSR';
> > > >     p_no number :=100;
>
> > > >     v_command1 varchar2(200):= 'nis_test_parallel.func1(' || '''' ||
> > > > '''' || p_ba || '''' || '''' || ',' || p_no || ')';
> > > >     v_command2 varchar2(200):= 'nis_test_parallel.func2(' || '''' ||
> > > > '''' || p_ba || '''' || '''' || ',' || p_no || ')';
>
> > > >   BEGIN
>
> > > >     -- When this function is called, then at this point we have got
> > > > uncomitted data in tables. These tables are
> > > >     -- need to be read by
>
> > > >     -- The start_background_job command uses the
> > > > dbms_scheduler.create_job command to kick off the thread
> > > >     start_background_job( 'BUILD_TABLE_PART1',
> > > >                           v_command1,
> > > >                           'Background Job to build part1 of the
> > > > results table');
>
> > > >     start_background_job( 'BUILD_TABLE_PART2',
> > > >                           v_command2,
> > > >                           'Background Job to build part2 of the
> > > > results table');
>
> > > >   END testParallel;
>
> > > Evidently you are trying to repair a piece of shit by creating another
> > > piece of shit, aka symptom fighting, instead of resolving the issue.
> > > Is that going to work? NO.
> > > Your post smells you need to sit down, and try to LEARN sql, and try
> > > to LEARN to program, instead of trying to hack yourself out.
> > > Eventually this will result in cardiac surgery due to living
> > > dangerously and on high stress levels for many years.
> > > Your post has 'DISASTER AHEAD' inscribed all over it.
> > > Because: WHY ON EARTH you are creating jobs on the fly in a procedure,
> > > which has uncommitted data?
> > > You are executing DDL, so you will commit!
> > > This 'process' needs to be re-architected, SOON.
> > > And hopefully by someone who does know what he is doing.
>
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
>
> > Mr Sybrand - I know very well what i'm doing, and very well what
> > questions i'm asking. Stop ranting like a lunatic - I see that you
> > have a habit of doing just that when other people have posted
> > questions before. If you can't be useful member of this community - go
> > and join a cult and self destruct yourself (preferably soon). Good
> > luck!- Tekst uit oorspronkelijk bericht niet weergeven -
>
> > - Tekst uit oorspronkelijk bericht weergeven -
>
> Apparently you don't know what you are doing, and I'm exposing you as
> a fraud, and this is why reply so hurt and insulting.
> There have been many similar idiots like you in the more than 10 years
> I have been responding to questions here.
> You have quite some nerve to state I'm not an useful member of this
> community.
> I would love to see you destruct yourself soon. You deserve it.
> Because YOU are the lunatic!
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Nis,
  This is a very interesting problem/challenge. Is it possible to rely on flashback (or an export/datapump) of the table for emergencies where you will need to rollback? (Because I think with the current structure of having multiple child processes, there is no way to "rollback" to prior to the parent procedure.)   And ignore Sybrand, he is rude and an absolutely useless addition to this board. Your original post is very interesting. I will follow it closely.

Jen Received on Tue Oct 21 2008 - 10:34:42 CDT