Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

>>>>> "DA" == DA Morgan <damorgan_at_exesolutions.com> writes:

 DA> Dean Smith wrote:
>> I found that tons of redo is generated when uses Global Temporary
>> Tables in 9i. Here was my test. I tested in 8.1.7, 9i and 9i
>> Release 2. 8i does not have the same problem.
>>
>> 1) Drop large redo logs and create small ones (64k in my case) so
>> I can tell how much redo is being generated. I'll count the log
>> switches. 2) Create two tables, one as global temp the other
>> normal. 3) Insert into each table (in an idle db) and see how
>> many logs are switched.
>>
>> --Create the tables create global temporary table tabGT as select
>> * from dba_objects where rownum<1; create table tabNORM as select
>> * from dba_objects; --Check the log sequence number select * from
>> (select * from v$log_history order by first_time desc) where
>> rownum=1; --Insert into GT insert into tabGT select * from
>> dba_objects; --Check the log sequence number select * from (select
>> * from v$log_history order by first_time desc) where rownum=1;
>> --Insert into Normal Table insert into tabGT select * from
>> dba_objects; --Check the log sequence number select * from (select
>> * from v$log_history order by first_time desc) where rownum=1;
>>
>> In 8i, the global temp table insert would cause 5% of the redo
>> compared to the normal table. But in 9i, the redo generated by the
>> insert into the GT is equivalent to the redo generated by the
>> insert into the normal table. What gives? How do I fix it?

 DA> I don't think there is anything to fix. And I highly doubt the  DA> impact is significant enough to matter much to anyone.

 DA> What I'd like to know is what is anyone doing that requires GTTs
 DA> anyway. I've been coding in Oracle for more than a decade and
 DA> have yet to "need" one.

Hi Daniel,

I've used GTTs once and can think of another area where they can be useful. However, in reality, I don't treat them as temporary tables so much as tables which have temporary data - the table stays, but the data does not.

The place where I have used them is in an application which needs to collect and process data from a number of remote databases and put the results into a permanent table. While I was able to find a solution which did this without using any temporary tables, I found the use of a GTT was a bit faster and more importantly, resulted in simpler and clearer SQL. The GTT solution was selected in the end because it was simpler to follow and therefore would (hopefully) be simpler to maintain and I always try to put an emphasis on maintenance.

The second possible use of GTTs which I can think of is in applications where you have to maintain some data for a temporary period and you possibly don't want other sessions to be aware of data which they have not placed in the table. A possible example could be a web application where you want to maintain some sort of state information, but only until the session ends and possibly you have multiple sessions, but you don't want to maintain session identifiers. Again, this is not really a temporary table as the table is permanent, but the data is temporary. I find the special features of GTTs over regular tables meet these sort of situations a lot better.

So, while I agree 100% that using temporary tables within Oracle should be avoided (e.g. tables which are created and (hopefully) removed) simply to hold intermediate data during some complex processing etc), I do think temporary global tables can be very useful in some situations because of their special features (such as data only being visible to the session which inserted it, automatic removal of data when the session exits, no rollback overhead etc).

Possibly Global Temporary Tables should have been called Global Temporary Data Tables?

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!