Re: Temporary table use and the redo log
From: <codefragment_at_googlemail.com>
Date: Wed, 9 Sep 2009 10:44:10 -0700 (PDT)
Message-ID: <9448e086-fe23-4bc9-b33b-3f8f8406434d_at_r39g2000yqm.googlegroups.com>
On 9 Sep, 17:34, joel garry <joel-ga..._at_home.com> wrote:
> On Sep 9, 6:53 am, "codefragm..._at_googlemail.com"
>
>
>
>
>
> <codefragm..._at_googlemail.com> wrote:
> > On 9 Sep, 13:28, "codefragm..._at_googlemail.com"
>
> > <codefragm..._at_googlemail.com> wrote:
> > > > - This also means that you can't access an object modified by direct-
> > > > path inserts within the same transaction which needs to be considered
> > > > for your process logic. You need to commit to access the inserted the
> > > > rows which means a "on commit delete rows" temporary table doesn't
> > > > make sense with a direct-path insert operation, since after the commit
> > > > the data will be gone.
>
> > > Thanks for the reply.
> > > I gather you can have Autonomous Transactions within a procedure, so I
> > > could:
> > > - start a transaction,
> > > - load the temporary table up,
> > > - do my selects
> > > - commit the transaction,
>
> > > then carry on with the rest of the procedure?
>
> > > Any views on that?
>
> > b.t.w. this seems to work for me
> > - Proc1 calls Proc2
> > - Proc2 does the autonomous transaction with the +append hint.
>
> > It seems fast and theres no redo archive logs getting created :-)
>
> > So what I'm worried about now are side effects.
> > (1) Proc2 is purely inserting, Proc1 is purely a select, I can't see
> > theres any chance of locking here
> > (2) Global Temporary tables, so can't lock with other sessions
> > (3) A session gets created, a few calls are made (but only one call to
> > Proc1) and is then discarded (rightly or wrongly)
> > (4) Any issues with writing over the high water mark and repeatedly
> > losing that storage to the temporary table
>
> > I guess one issue is that I'm a complete novice and haven't seen this
> > technique mentioned anywhere
>
> > If this works we get time to look at this issue properly and remove
> > the tables which would be nice
>
> As long as you don't try to have simultaneous transactions by the same
> session, you should be fine. Tom Kyte illustrates this in Expert one-
> on-one Oracle. He also explains how autonomous transactions work and
> the caveats. Of course, he thinks they are almost universally misused
> and should be removed from the database:http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
>
> jg
> --
> _at_home.com is bogus.http://threatpost.com/blogs/microsoft-cisco-issue-patches-tcp-dos-fla...- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 9 Sep 2009 10:44:10 -0700 (PDT)
Message-ID: <9448e086-fe23-4bc9-b33b-3f8f8406434d_at_r39g2000yqm.googlegroups.com>
On 9 Sep, 17:34, joel garry <joel-ga..._at_home.com> wrote:
> On Sep 9, 6:53 am, "codefragm..._at_googlemail.com"
>
>
>
>
>
> <codefragm..._at_googlemail.com> wrote:
> > On 9 Sep, 13:28, "codefragm..._at_googlemail.com"
>
> > <codefragm..._at_googlemail.com> wrote:
> > > > - This also means that you can't access an object modified by direct-
> > > > path inserts within the same transaction which needs to be considered
> > > > for your process logic. You need to commit to access the inserted the
> > > > rows which means a "on commit delete rows" temporary table doesn't
> > > > make sense with a direct-path insert operation, since after the commit
> > > > the data will be gone.
>
> > > Thanks for the reply.
> > > I gather you can have Autonomous Transactions within a procedure, so I
> > > could:
> > > - start a transaction,
> > > - load the temporary table up,
> > > - do my selects
> > > - commit the transaction,
>
> > > then carry on with the rest of the procedure?
>
> > > Any views on that?
>
> > b.t.w. this seems to work for me
> > - Proc1 calls Proc2
> > - Proc2 does the autonomous transaction with the +append hint.
>
> > It seems fast and theres no redo archive logs getting created :-)
>
> > So what I'm worried about now are side effects.
> > (1) Proc2 is purely inserting, Proc1 is purely a select, I can't see
> > theres any chance of locking here
> > (2) Global Temporary tables, so can't lock with other sessions
> > (3) A session gets created, a few calls are made (but only one call to
> > Proc1) and is then discarded (rightly or wrongly)
> > (4) Any issues with writing over the high water mark and repeatedly
> > losing that storage to the temporary table
>
> > I guess one issue is that I'm a complete novice and haven't seen this
> > technique mentioned anywhere
>
> > If this works we get time to look at this issue properly and remove
> > the tables which would be nice
>
> As long as you don't try to have simultaneous transactions by the same
> session, you should be fine. Tom Kyte illustrates this in Expert one-
> on-one Oracle. He also explains how autonomous transactions work and
> the caveats. Of course, he thinks they are almost universally misused
> and should be removed from the database:http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
>
> jg
> --
> _at_home.com is bogus.http://threatpost.com/blogs/microsoft-cisco-issue-patches-tcp-dos-fla...- Hide quoted text -
>
> - Show quoted text -
Thanks for the reply.
Turns out it didn't work :-(
I tried it using a noddy example, adding 1 million rows into a
temporary table. Great, no redo log activity. Tried it in the actual
problem stored procedure
and still got a lot of redo logs. No idea why. In the end every insert
statement had the /* +append hint around it, all in autonomous
transactions so they could be queries from later, commit at the end,
redo use growing and growing...
My guess is that its a hint, not a promise Received on Wed Sep 09 2009 - 12:44:10 CDT