Re: Temporary table use and the redo log
From: joel garry <joel-garry_at_home.com>
Date: Wed, 9 Sep 2009 11:12:18 -0700 (PDT)
Message-ID: <bc197935-eb34-4e7f-97ba-51a70e81dbf2_at_f20g2000prn.googlegroups.com>
On Sep 9, 10:44 am, "codefragm..._at_googlemail.com" <codefragm..._at_googlemail.com> wrote:
> 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
Date: Wed, 9 Sep 2009 11:12:18 -0700 (PDT)
Message-ID: <bc197935-eb34-4e7f-97ba-51a70e81dbf2_at_f20g2000prn.googlegroups.com>
On Sep 9, 10:44 am, "codefragm..._at_googlemail.com" <codefragm..._at_googlemail.com> wrote:
> 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
No, it is an optimizer directive. If it seems like it is optional, it
is because you don't have enough information. It could even be
ignored because of a typo.
See http://jonathanlewis.wordpress.com/2006/11/11/what-is-a-hint/
To really dig into hints, google 10053 trace and study Jonathan's
Optimizer book.
jg
-- _at_home.com is bogus. http://tkyte.blogspot.com/2006/05/three-funny-things.htmlReceived on Wed Sep 09 2009 - 13:12:18 CDT