Temporary table use and the redo log

Hi
I'm using a global (session) temporary table to break up a section
of a stored procedure that is needed in
a number of places. Always for use in a select.
If the database crashes, gets rolled back, anything, I don't care
about the contents of that
table. Especially -I don't want redo logs for it-. Is there any way I
can accomplish this?

From what I can see so far I should look into:
- unrecoverable
- insert /*+APPEND */ into temp_table select...
- possibly using a table variable

Nologging is on by default for a tempory table but you still get some
logging

Am I on the right track?

thanks

On 07.09.2009 18:23, codefragment@googlemail.com wrote:
> Hi
> I'm using a global (session) temporary table to break up a section
> of a stored procedure that is needed in
> a number of places. Always for use in a select.
> If the database crashes, gets rolled back, anything, I don't care
> about the contents of that
> table. Especially -I don't want redo logs for it-. Is there any way I
> can accomplish this?
>
> From what I can see so far I should look into:
> - unrecoverable
> - insert /*+APPEND */ into temp_table select...
> - possibly using a table variable

I suggest to also look into ON COMMIT DELETE ROWS.

> Nologging is on by default for a tempory table but you still get some
> logging
>
> Am I on the right track?

http://asktom.oracle.com/pls/asktom/...50549336675394
http://asktom.oracle.com/pls/asktom/...53994537734234

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

> http://asktom.oracle.com/pls/asktom/..._QUESTION_ID:1...

thanks for the reply, the links don't work for me though...

On Mon, 07 Sep 2009 09:23:31 -0700, codefragment@googlemail.com wrote:

> Hi
> I'm using a global (session) temporary table to break up a section
> of a stored procedure that is needed in a number of places. Always for
> use in a select.
> If the database crashes, gets rolled back, anything, I don't care
> about the contents of that
> table. Especially -I don't want redo logs for it-. Is there any way I
> can accomplish this?
>
> From what I can see so far I should look into: - unrecoverable
> - insert /*+APPEND */ into temp_table select... - possibly using a table
> variable
>
> Nologging is on by default for a tempory table but you still get some
> logging
>
> Am I on the right track?
>
> thanks


The storage for temporary tables is allocated entirely from the temporary
tablespace. Blocks in a temporary tablespace are not proteced by redo so
nologging is on by default as you say. Even more than that, there is no
"current version" and "read consistent" version of the temporary blocks.
Temporary blocks are written from PGA to temporary tablespace in a way
very similar to what /*+ APPEND */ hint does. In other words, that hint is
for normal insert and has no visible effect on inserting into global
temporary tables. To verify that, you can run it with the event 10046,
level 12 and see that there are no visible differences in the trace file.




--
http://mgogala.freehostia.com

On 07.09.2009 19:28, codefragment@googlemail.com wrote:
>> http://asktom.oracle.com/pls/asktom/..._QUESTION_ID:1...
>
> thanks for the reply, the links don't work for me though...

Darn, sorry for that. You should find those entries by going to

http://asktom.oracle.com/

And searching for "global temporary redo".

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 07.09.2009 19:57, Mladen Gogala wrote:
> On Mon, 07 Sep 2009 09:23:31 -0700, codefragment@googlemail.com wrote:
>
>> Hi
>> I'm using a global (session) temporary table to break up a section
>> of a stored procedure that is needed in a number of places. Always for
>> use in a select.
>> If the database crashes, gets rolled back, anything, I don't care
>> about the contents of that
>> table. Especially -I don't want redo logs for it-. Is there any way I
>> can accomplish this?
>>
>> From what I can see so far I should look into: - unrecoverable
>> - insert /*+APPEND */ into temp_table select... - possibly using a table
>> variable
>>
>> Nologging is on by default for a tempory table but you still get some
>> logging
>>
>> Am I on the right track?
>>
>> thanks
>
>
> The storage for temporary tables is allocated entirely from the temporary
> tablespace. Blocks in a temporary tablespace are not proteced by redo so
> nologging is on by default as you say. Even more than that, there is no
> "current version" and "read consistent" version of the temporary blocks.
> Temporary blocks are written from PGA to temporary tablespace in a way
> very similar to what /*+ APPEND */ hint does. In other words, that hint is
> for normal insert and has no visible effect on inserting into global
> temporary tables. To verify that, you can run it with the event 10046,
> level 12 and see that there are no visible differences in the trace file.

But there is a bit of redo because of the undo:

http://asktom.oracle.com/pls/asktom/...50549336675394

(Hopefully that link works...)

Kind regards

robert


--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 7 Sep, 20:22, Robert Klemme wrote:
> On 07.09.2009 19:57, Mladen Gogala wrote:
>
>
>
>
>
> > On Mon, 07 Sep 2009 09:23:31 -0700, codefragm...@googlemail.com wrote:
>
> >> Hi
> >> * I'm using a global (session) temporary table to break up a section
> >> of a stored procedure that is needed in a number of places. Always for
> >> use in a select.
> >> * If the database crashes, gets rolled back, anything, I don't care
> >> about the contents of that
> >> table. Especially -I don't want redo logs for it-. Is there any way I
> >> can accomplish this?
>
> >> From what I can see so far I should look into: - unrecoverable
> >> - insert /*+APPEND */ into temp_table select... - possibly using a table
> >> variable
>
> >> Nologging is on by default for a tempory table but you still get some
> >> logging
>
> >> Am I on the right track?
>
> >> thanks
>
> > The storage for temporary tables is allocated entirely from the temporary
> > tablespace. Blocks in a temporary tablespace are not proteced by redo so
> > nologging is on by default as you say. Even more than that, there is no
> > "current version" and "read consistent" version of the temporary blocks..
> > Temporary blocks are written from PGA to temporary tablespace in a way
> > very similar to what /*+ APPEND */ hint does. In other words, that hintis
> > for normal insert and has no visible effect on inserting into global
> > temporary tables. To verify that, you can run it with the event 10046,
> > level 12 and see that there are no visible differences in the trace file.
>
> But there is a bit of redo because of the undo:
>
> http://asktom.oracle.com/pls/asktom/..._QUESTION_ID:1....
>
> (Hopefully that link works...)
>
> Kind regards
>
> * * * * robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/- Hide quoted text -
>
> - Show quoted text -

Its 'a little bit of undo' that seems to be my problem
From the asktom site:

Inserting 500 rows,

generates

3,297,752 bytes of redo generated for "insert into perm "...
66,488 bytes of redo generated for "insert into temp

So if you have a stored procedure that inserts 1000 rows when called
and is called 1000 times a minute
thats 132M a minute (66,488/500*1000*1000), 191G a day. Bearing in
mind this is temporary table I am
selecting from, I will never want to keep it.

How long should these logs persist from, what are common practices?

> The storage for temporary tables is allocated entirely from the temporary
> tablespace.

Thanks for the reply. I'm probably missing something based on limited
understanding.

I can see on my Oracle database there are 3 redo log files, from some
reading it seems these are populated in turn
and these later populate archive files. Its those archive files I'm
looking at.

I created a script which using a cursor and a loop populated a
temporary table using 1 million inserts. I can see
the archive log files increasing as this happens in:

D:\oracle\product\10.1.0\flash_recovery_area\\ARCHIVELOG
\2009_09_07

The temporary table looks something like this:

CREATE GLOBAL TEMPORARY TABLE TP_TEMP
(
somecolumn INT,

) ON COMMIT PRESERVE ROWS;

We have perhaps 10 stored procedures that create the temp table and
then call 2 stored procedures to populate it
so that they can then do whatever they need to with the output.
Once the calling stored procedures are finished then the contents of
the temporary table can be discarded.

If what your saying is true then I didn't think those archive files
would be increasing?

Also using the /*+ APPEND */ hint did seem to make a difference,
those files weren't increasing, although in
retrospect I'm not not convinced it was a valid test, it did about 1
insert of 10,000 rows as I could only
call it once before I got some kind of error. I'll try it again
tomorrow

thanks for your replies, I appreciate it

On 07.09.2009 21:57, codefragment@googlemail.com wrote:
>> The storage for temporary tables is allocated entirely from the temporary
>> tablespace.
>
> Thanks for the reply. I'm probably missing something based on limited
> understanding.

I suggest you go to http://tahti.oracle.com and read the Concepts
manual. Then there's also documentation about administration and
backups in particular.

> I can see on my Oracle database there are 3 redo log files, from some
> reading it seems these are populated in turn
> and these later populate archive files. Its those archive files I'm
> looking at.
>
> I created a script which using a cursor and a loop populated a
> temporary table using 1 million inserts. I can see
> the archive log files increasing as this happens in:
>
> D:\oracle\product\10.1.0\flash_recovery_area\\ARCHIVELOG
> \2009_09_07
>
> The temporary table looks something like this:
>
> CREATE GLOBAL TEMPORARY TABLE TP_TEMP
> (
> somecolumn INT,
>
> ) ON COMMIT PRESERVE ROWS;
>
> We have perhaps 10 stored procedures that create the temp table and
> then call 2 stored procedures to populate it
> so that they can then do whatever they need to with the output.
> Once the calling stored procedures are finished then the contents of
> the temporary table can be discarded.
>
> If what your saying is true then I didn't think those archive files
> would be increasing?

No. Redo is created and never deleted. You have these options to limit
your archive log volume

1. switch off archive log mode

2. set up RMAN so that you do regularly backups and purge old backups

2b. set up RMAN so that it only deletes old archive logs which is a bit
pointless without backups (i.e. then you'll better pick option 1).

If you do 1 you can basically forget backing up your database. Whether
this is OK for you, only you can decide. Often test databases are
excluded from backup schemes but if your DB is a production system,
chances are that you rather want option 2.

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Perhaps a table variable instead of a temporary table. Don't know much
about these so one to look into.