Re: Temporary table use and the redo log
Date: Wed, 9 Sep 2009 12:11:17 -0700 (PDT)
Message-ID: <503c8822-97be-4ec8-ae60-a3ddb7a7ba46_at_k39g2000yqe.googlegroups.com>
> 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.
I sort of agree
(1) The SPs should have been written without temp tables, this is a
hangover from sql server
and its oddities in optimizing stored procedures. If you split up a
stored procedure
using temp tables it can make it a lot faster (if you have equivilent
redo issues there I've yet
to hear about it, but then you get much worse locking issues on sql
server)
(2) We are looking at ways around rewrites but in the long term
rewrites will be needed, its come to
a head because although we've made a lot of use of temp tables now
some procedures which are used
very often have needed to be changed to use them so now we can see the
problem.
(3) The size of the redo changes for a temp table compared to a non
temp table
seems to be 50%. (Example code provided earlier). 1/2 doesn't seem
small in database
terms. Funnily enough I found the below which is similiar to my
situation.
http://homepage.ntlworld.com/david.kurtz/Global_Temporary_Tables.doc
(4) The other reason we've ended up here is because we always seem to
be joining large sets
with large sets rather than having high selectivity to whittle the
data down first. I have some ideas
on that but not relevant here.
Greatful for all the replies though. Received on Wed Sep 09 2009 - 14:11:17 CDT