Re: Temporary table use and the redo log
From: <codefragment_at_googlemail.com>
Date: Wed, 9 Sep 2009 12:15:27 -0700 (PDT)
Message-ID: <6414da08-9caf-4516-b9ef-9d97c2f58302_at_e12g2000yqi.googlegroups.com>
On 9 Sep, 19:12, joel garry <joel-ga..._at_home.com> wrote:
> 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...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.
> Seehttp://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.html- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 9 Sep 2009 12:15:27 -0700 (PDT)
Message-ID: <6414da08-9caf-4516-b9ef-9d97c2f58302_at_e12g2000yqi.googlegroups.com>
On 9 Sep, 19:12, joel garry <joel-ga..._at_home.com> wrote:
> 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...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.
> Seehttp://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.html- Hide quoted text -
>
> - Show quoted text -
Given that I'll look into this again tomorrow, if the hint can't be ignored then a typo is possible.
Funny thing looking into Oracle is that it seems impossible to find
some advice that doesn't
eventually lead you to Tom Kyte or Jonathan Lewis :-) (with good
reason I'll add, good writers,
just need to force the knowledge into my brain somehow)
Received on Wed Sep 09 2009 - 14:15:27 CDT