Re: How to read parent session data - without forcing a commit in the parent
Date: Tue, 21 Oct 2008 09:15:10 -0700 (PDT)
Message-ID: <c39b09e5-4393-4a75-b332-b4f81c8f7d89@k7g2000hsd.googlegroups.com>
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 Received on Tue Oct 21 2008 - 11:15:10 CDT