Re: Temporary table use and the redo log

From: Robert Klemme <>
Date: Wed, 09 Sep 2009 20:31:30 +0200
Message-ID: <>

On 09.09.2009 18:34, joel garry wrote:
> On Sep 9, 6:53 am, ""
> <> wrote:

>> On 9 Sep, 13:28, ""
>> <> 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:

Frankly, all this getting rid of as much redo as possible leaves a bad taste in my mouth. First of all, we do not know the schema and what these procedures do. Chances are that there might be other means to transfer the information between SP's.

Then, devising extremely complex mechanisms to get rid of a bit of redo seems to indicate to me that there might be an issue with the sizing of the system. If it is mission critical to get rid of the redo caused by the undo for statements manipulating the temp table (which I assume is small compared to the redo of the "regular" operation, i.e. on non temp tables) then the system is probably undersized.

Just my 0.02 EUR.

Kind regards


remember.guy do |as, often| as.you_can - without end
Received on Wed Sep 09 2009 - 13:31:30 CDT

Original text of this message