I've noticed that global temporary tables produces a lot of redo loggging. Analysis of the redo log contents using logminer, showed that it produces over 90% of the redo logging and increases the amount of redo produced by more than 10-50X.
Oracle documentation claims this "DML statements on temporary tables do not generate redo logs for the data changes."
http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref769 And it also states that no redo is generated for indexes on temporary tables.
To me this is wrong. It does generate redo for DML statements on temporary tables. It may not write the 'sql_redo' and 'sql_undo', but it is entering something. I see that it enters '/* No SQL_REDO for temporary tables */' for each DML call which is still an entry, don't you agree? In addition, based on what I see when I mine the logs, I think that it is adding a significant amount of 'INTERNAL' entries in the redo since it spikes up when temporary tables are used.
Also, I do see that Oracle does state that it does generate UNDO for both index and global temporary table operations. This may be a lot redo entries if millions of records are added and deleted on a temporary table, worse if it has indexes. (Do you know what 'undo ' operations are listed for 'OPERATION_TYPE'? I wonder if they are listed as 'INTERNAL'? )
I think too much hype has been generated around global temporary tables. It deceives people into thinking that global temporary tables produce an insignificant amount of redo, when in fact, the redo generation can be large enough to bring the DB instance to its knees since it can cause a write I/O bottleneck to the redo logs.
I think global temporary tables has its use, but I still think that people should consider using plain old, tried and true, cursors instead. A cursor doesn't add any redo log entries. Sure you can't add indexes to a cursor, but for the most part, it is a bad idea to add millions of records to a global temporary table, create a few indexes on it, execute a few selects then delete all the records.
Here are some of the logminer queries that I executed:
WITHOUT GLOBAL TEMPORARY TABLE ACTIVITY:
--- What command type was the most common?
Select username, operation, count(*) from v$logmnr_contents group by username , operation order by count(*) desc;
USERNAME OPERATION COUNT(*)
------------------------------ -------------------------------- ----------
UNKNOWN INTERNAL 315247 ---> Note the low amount
UNKNOWN INSERT 283746
UNKNOWN UNSUPPORTED 188244
UNKNOWN DELETE 150442
UNKNOWN UPDATE 34304
UNKNOWN COMMIT 21850
UNKNOWN START 14435
UNKNOWN ROLLBACK 167
UNKNOWN DDL 6
9 rows selected.
--What SQL_REDO is most common from Deletes?
Select sql_redo, table_name, count(*) from v$logmnr_contents where operation_code = 2 group by sql_redo, table_name having count(*) > 10 order by count(*);
---> LOTS OF DIFFERENT COMMANDS, None had too many repetitive commands so I didn't add them here.
WITH GLOBAL TEMPORARY TABLE ACTIVITY:
-----------------------------------------------------
--- What command type was the most common?
Select username, operation, count(*) from v$logmnr_contents group by username , operation order by count(*) desc;
USERNAME OPERATION COUNT(*)
------------------------------ -------------------------------- ----------
UNKNOWN INTERNAL 1374324 ************* Internal increases significantly
UNKNOWN DELETE 499735 *************
UNKNOWN INSERT 19544
UNKNOWN COMMIT 2259
UNKNOWN START 2256
UNKNOWN UNSUPPORTED 368
UNKNOWN UPDATE 70
UNKNOWN DDL 2
--What SQL_REDO is most common from Delete operations?
Select sql_redo, table_name, count(*) from v$logmnr_contents where operation_code = 2 group by sql_redo, table_name having count(*) > 10 order by count(*);
sql_redo table_name count(*)
----------------------------------------------------------------------------
/* No SQL_REDO for temporary tables */ GC_Global_table1 499622
---> Most delete operations were done on global temporary table. 499622 out of 499735.
---> It looks like INTERER operations may be the biggest culprit of the high amount of redo generated. This must be from the UNDO generated from inserting and deleting records on the global temporary tables with indexes. What do you think?
These redo logs (500MB) were generated at about 500MB/minute. Without the temporary table usage, it generated about 500MB/hour.
Here is the ddl of the global temporary table:
CREATE GLOBAL TEMPORARY TABLE GTT1
( (columns and table names removed.)
) ON COMMIT PRESERVE ROWS ;
CREATE UNIQUE INDEX DUMMY_U1 ON GTT1(XPB_ROWID) ;
What are your thoughts and/or advice on GTT usage, Tom?
I wrote about all of this in my books - the facts are that if you have indexes on them - you'll generate a lot of redo because of the undo generated for the index structure. If you insert into a global temporary table you'll generate:
a) undo for the table - but this is trivial in general because the undo for a conventional path insert is simply "delete+rowid" - it is very small.
b) undo for the indexes - this could be non-trivial in size depending on the size of the indexed columns and the number of indexes.
However, that said, it will generate *less* redo than a conventional path insert into a "normal" table.
for example, say you have a 'temporary table need' such that
a) it has to be multi-user
b) therefore it will be necessary to delete from said temporary table to make it appear empty if you do not use a global temporary table.
then
ops$tkyte%ORA11GR2> create table stage as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create global temporary table gtt on commit delete rows
2 as
3 select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> column value new_val REDO
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE
---------------------- ----------
redo size 36495320
ops$tkyte%ORA11GR2> insert into gtt select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE DIFF
---------------------- ---------- ----------
redo size 36911000 415680
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE
---------------------- ----------
redo size 36911000
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> delete from t;
72887 rows deleted.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE DIFF
---------------------- ---------- ----------
redo size 73020096 36109096
well, would you rather have 415k of redo or 36m or redo?
Now, if we index something:
ops$tkyte%ORA11GR2> create index gtt_idx on gtt(owner,object_type,object_name);
Index created.
ops$tkyte%ORA11GR2> create index t_idx on t(owner,object_type,object_name);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> column value new_val REDO
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE
---------------------- ----------
redo size 73343908
ops$tkyte%ORA11GR2> insert into gtt select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE DIFF
---------------------- ---------- ----------
redo size 92151752 18807844
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE
---------------------- ----------
redo size 92151752
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> delete from t;
72887 rows deleted.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE DIFF
---------------------- ---------- ----------
redo size 177385212 85233460
that is 18m versus 85m - which would you rather have? and even if you use truncate (take away the multiuser aspect) you would have:
ops$tkyte%ORA11GR2> insert into t select * from stage;
72887 rows created.
ops$tkyte%ORA11GR2> truncate table t;
Table truncated.
ops$tkyte%ORA11GR2> select a.name, b.value, b.value-&REDO diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and a.name = 'redo size';
NAME VALUE DIFF
---------------------- ---------- ----------
redo size 236454188 39883836
that was the index example - almost 40mb.
the facts surrounding a global temporary table are:
a) they generate LESS redo - in most cases *significantly* less redo (no indexes) and if you can use a session based global temporary table - they can generate almost NO UNDO using insert /*+ append */
b) they do not require an expensive delete operation
c) they require less work on the part of the developer since they clean themselves out.
d) to use them with updates and deletes is what I would call "atypical" - not the normal use. most of the use is 1) insert into them, 2) query them, 3) commit and clear them out.
when you start updating and deleting - you start generating gobs of undo as that cannot be done using direct path and those two things generate the most undo possible. A delete has to record the entire row in the undo, and update has to record as much of the row that was modified.
So, if you insert, query, commit - the "normal" use pattern - they make sense.
If you need the self cleansing ability - they make sense (even if you update them)
If you are deleting from them (the worst of the worst), I'd start to question your logic.