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

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! Received on Tue Oct 21 2008 - 09:27:59 CDT