Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!e12g2000yqi.googlegroups.com!not-for-mail From: "codefragment@googlemail.com" Newsgroups: comp.databases.oracle.server Subject: Re: Temporary table use and the redo log Date: Wed, 9 Sep 2009 12:15:27 -0700 (PDT) Organization: http://groups.google.com Lines: 118 Message-ID: <6414da08-9caf-4516-b9ef-9d97c2f58302@e12g2000yqi.googlegroups.com> References: <4b3fdc8a-6ad1-4275-8d5a-200ecf45bdf0@a21g2000yqc.googlegroups.com> <8f7154be-0b5b-41ed-a773-0ad38cf06583@j39g2000yqh.googlegroups.com> <3e8a48c3-5386-42fc-9f5e-7296ae9e3923@m38g2000yqd.googlegroups.com> <50bc0253-6e1f-4008-a82d-05838c6e74b0@r24g2000prf.googlegroups.com> <9448e086-fe23-4bc9-b33b-3f8f8406434d@r39g2000yqm.googlegroups.com> NNTP-Posting-Host: 86.144.225.16 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1252523727 20734 127.0.0.1 (9 Sep 2009 19:15:27 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 9 Sep 2009 19:15:27 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: e12g2000yqi.googlegroups.com; posting-host=86.144.225.16; posting-account=pxdg5QoAAABxfENvItfA06OUPHUwuomY User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; InfoPath.2; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; OfficeLiveConnector.1.3; OfficeLivePatch.0.0),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On 9 Sep, 19:12, joel garry wrote: > On Sep 9, 10:44=A0am, "codefragm...@googlemail.com" > > > > > > wrote: > > On 9 Sep, 17:34, joel garry wrote: > > > > On Sep 9, 6:53=A0am, "codefragm...@googlemail.com" > > > > wrote: > > > > On 9 Sep, 13:28, "codefragm...@googlemail.com" > > > > > wrote: > > > > > > - This also means that you can't access an object modified by d= irect- > > > > > > path inserts within the same transaction which needs to be cons= idered > > > > > > for your process logic. You need to commit to access the insert= ed the > > > > > > rows which means a "on commit delete rows" temporary table does= n'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 se= e > > > > 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 th= is > > > > 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 sam= e > > > session, you should be fine. =A0Tom Kyte illustrates this in Expert o= ne- > > > on-one Oracle. =A0He also explains how autonomous transactions work a= nd > > > the caveats. =A0Of course, he thinks they are almost universally misu= sed > > > and should be removed from the database:http://tkyte.blogspot.com/200= 6/09/classic-example-of-why-i-despise.html > > > > jg > > > -- > > > @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 =A01 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. =A0If it seems like it is optional, it > is because you don't have enough information. =A0It 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 > -- > @home.com is bogus.http://tkyte.blogspot.com/2006/05/three-funny-things.h= tml- 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)