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

On Oct 21, 12:15 pm, ddf <orat..._at_msn.com> wrote:
> On Oct 21, 10:34 am, jj3run..._at_gmail.com wrote:
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Interesting thought, and,; yes, flashback query might be a possibility
> depending upon the undo_retention setting and/or the release of Oracle
> in use.  Release 9i and 9iR2 have flashback, but it needs to be
> enabled and used within a PL/SQL block if you want to update existing
> records with the flashback values:
>
> declare
>   --
>   -- Cursor to fetch the required data
>   --
>   cursor c1 is
>   select flerg, snerm, anguplast
>   from snangpiester;
>
>   --
>   -- A place to stash each row of the returned data
>   --
>   c1_rec c1%rowtype;
>
> begin
>
>   --
>   -- We flashback the data image to before the problem began
>   --
>   dbms_flashback.enable_at_time(to_timestamp('16-SEP-2007 06:53:00
> AM','DD-MON-RRRR HH:MI:SS AM'));
>
>   --
>   -- Open the cursor while in flashback mode
>   --
>   open c1;
>
>   --
>   -- Disable flashback mode so we can update the problem table
>   --
>   dbms_flashback.disable;
>
>   --
>   -- Go fetch the good data and update the problem table
>   --
>   loop
>           fetch c1 into c1_rec;
>           exit when c1%notfound;  -- say goodbye when there's no more
> data
>           update snangpiester
>           set anguplast = c1_rec.anguplast
>           where flerg = c1_rec.flerg
>           and snerm = c1_rec.snerm;
>   end loop;
>
>   --
>   -- Commit the changes
>   --
>
>   commit;
>
> end;
> /
>
> In 10g and later releases it's much simpler.  To select flashback
> data:
>
> select [select list here]
> from [table]
> as of timestamp to_timestamp('date/time or timestamp string');
>
> And to use that to update a table:
>
> update emp e
> set (sal, comm) = (select sal, comm
> from emp
> as of timestamp to_timestamp(trunc(sysdate))
> where empno = e.empno);
>
> Again, presuming the undo_retention has not been exceeded this could
> work to restore the changes effected.
>
> David Fitzjarrell

I guess the OP says it's a "HUGE" table. Perhaps you're right, undo_retention size could become a problem. It's a valid idea to try at least :)

jen Received on Tue Oct 21 2008 - 12:04:32 CDT