| Computers Forum Index » Computer - Databases - Oracle (Server) » Temporary table use and the redo log... |
|
Page 1 of 2 Goto page 1, 2 Next |
|
| Author |
Message |
| codefragment at (no spam) googlemail.com... |
 Posted: Mon Sep 07, 2009 4:23 pm |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Mon Sep 07, 2009 5:28 pm |
|
|
|
Guest
|
Quote: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
thanks for the reply, the links don't work for me though... |
|
|
| Back to top |
|
|
|
| Mladen Gogala... |
| Posted: Mon Sep 07, 2009 5:57 pm |
|
|
|
Guest
|
On Mon, 07 Sep 2009 09:23:31 -0700, codefragment at (no spam) googlemail.com wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Mon Sep 07, 2009 7:56 pm |
|
|
|
Guest
|
On 7 Sep, 20:22, Robert Klemme <shortcut... at (no spam) googlemail.com> wrote:
Quote: On 07.09.2009 19:57, Mladen Gogala wrote:
On Mon, 07 Sep 2009 09:23:31 -0700, codefragm... at (no spam) 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/f?p=100:11:0::::P11_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? |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Mon Sep 07, 2009 7:57 pm |
|
|
|
Guest
|
Quote: 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\<oracle name>\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 |
|
|
| Back to top |
|
|
|
| Robert Klemme... |
| Posted: Mon Sep 07, 2009 8:35 pm |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Mon Sep 07, 2009 10:05 pm |
|
|
|
Guest
|
Perhaps a table variable instead of a temporary table. Don't know much
about these so one to look into. |
|
|
| Back to top |
|
|
|
| John Hurley... |
| Posted: Mon Sep 07, 2009 10:09 pm |
|
|
|
Guest
|
On Sep 7, 3:57 pm, "codefragm... at (no spam) googlemail.com"
<codefragm... at (no spam) googlemail.com> wrote:
snip
Quote: 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.
If you are running in archivelog mode you probably want more than 3
online log files. Perhaps something like 10 ( your mileage may
vary ).
If you have 3 online logs and you are running something generating a
lot of log activity ... online 1 can fill up ... switching to online
2 ... it fills up ... switch to online 3 ... it fills up ... switch to
log 1 ... except it is still being archived ... oops ... everyone
waits.
Sizing of online logs and the number of them needs to be looked at in
a system running in archivelog mode. |
|
|
| Back to top |
|
|
|
| Robert Klemme... |
| Posted: Mon Sep 07, 2009 11:20 pm |
|
|
|
Guest
|
On 07.09.2009 19:28, codefragment at (no spam) googlemail.com wrote:
Quote: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_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/ |
|
|
| Back to top |
|
|
|
| Robert Klemme... |
| Posted: Mon Sep 07, 2009 11:22 pm |
|
|
|
Guest
|
On 07.09.2009 19:57, Mladen Gogala wrote:
Quote: On Mon, 07 Sep 2009 09:23:31 -0700, codefragment at (no spam) 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/f?p=100:11:0::::P11_QUESTION_ID:15826034070548#50549336675394
(Hopefully that link works...)
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/ |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Tue Sep 08, 2009 1:32 pm |
|
|
|
Guest
|
I've included an example below, yes I know the sql sucks but I've just
copied a template file to
get something working, no need for a package, etc.
It assumes a table called DeleteMe, e.g.
create table DeleteMe(
someColumn int);
and a table called OneToTen, same structure as the above, which holds
10 values. Just a quick way of building a result set of 1 million
rows.
I truncate DeleteMe, drop the temporary table after a single use.
Again just for expediency, I'm assuming it won;t
affect the result.
What I find is looking in the archive directory I get 130M of files
created for the temporary tables rows and 242M of
files created for the real table rows. So not too much difference.
What I've read suggests that that the redo logs should grow through
the inserts into the temporary table by a much lesser degree than
those into the real table. So what am I doing wrong here (no sarcastic
answers please)
CREATE OR REPLACE PACKAGE MYTEST AS
PROCEDURE MYTEST (
pIgnore IN NUMBER);
END ;
/
CREATE GLOBAL TEMPORARY TABLE TP_MYTEST
(
SOMECOLUMN INT
) ON COMMIT DELETE ROWS;
--) ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE PACKAGE BODY MyTest AS
PROCEDURE MyTest (
pIgnore IN NUMBER) is
BEGIN
-- insert into TP_MYTEST
insert into DeleteMe
select 1 from onetoten t1
join onetoten t2 on 1=1
join onetoten t3 on 1=1
join onetoten t4 on 1=1
join onetoten t5 on 1=1
join onetoten t6 on 1=1;
END MyTest;
END MyTest;
/ |
|
|
| Back to top |
|
|
|
| Eugene Pokopac... |
| Posted: Tue Sep 08, 2009 2:06 pm |
|
|
|
Guest
|
Because the contents are temporary, they don't generate redo. They
still generate undo, that's true. The undo segment updates cause
'some'
redo to be produced, but nevertheless: the amount of redo an update on
a
temporary table causes should be much less than the amount of redo an
identical 'proper' table would create. That means less log switches,
less
checkpoints, less archives etc. Again, much lower overhead. |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Tue Sep 08, 2009 2:44 pm |
|
|
|
Guest
|
Quote: the amount of redo an update on a temporary table causes should be
much less than the amount of redo an
identical 'proper' table would create.
which isn't what I'm seeing here |
|
|
| Back to top |
|
|
|
| Randolf Geist... |
| Posted: Wed Sep 09, 2009 8:30 am |
|
|
|
Guest
|
On Sep 8, 3:32 pm, "codefragm... at (no spam) googlemail.com"
<codefragm... at (no spam) googlemail.com> wrote:
Quote: I've included an example below, yes I know the sql sucks but I've just
copied a template file to
get something working, no need for a package, etc.
As discussed above, if your main interest is to avoid REDO generation,
you have two options:
- Use the temporary table and add the "/*+ APPEND */" hint to the
insert:
insert /*+ append */ into ...
- Use a conventional table, mark it with NOLOGGING either at create
time or later with ALTER TABLE ... NOLOGGING, and again additionally
use the "/*+ APPEND */" hint when running the insert
Both approaches should minimize your REDO generation.
As already mentioned, you need to be aware of that:
- Enabled indexes on either form of tables will still generate REDO,
UNDO, and REDO for the UNDO
- Direct-path inserts have restrictions. You can check for a
successful direct-path operation by querying the modified object
within the same transaction; you'll get a "ORA-12838: cannot read/
modify an object after modifying it in parallel" if you was a
successful direct-path operation. If you can still access the object
after the insert within the same transaction, it was a conventional
DML operation.
- 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.
- Direct-Path inserts always allocate new blocks above the current
High Water Mark (HWM) of the segment and don't reuse any free space in
already allocated blocks. So a repeated "delete + insert /*+ append
*/" operation will let the segment grow every time because the empty
blocks won't get re-used. You would need to truncate instead of delete
to reset the HWM of the segment, if your process logic allows for it
due to the implicit commit performed by truncate.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
| Back to top |
|
|
|
| codefragment at (no spam) googlemail.com... |
| Posted: Wed Sep 09, 2009 12:28 pm |
|
|
|
Guest
|
Quote: - 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? |
|
|
| Back to top |
|
|
|
|
