RE: Global temporary tables with No logging

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Mon, 8 Jun 2009 13:29:23 -0400
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F295D626A_at_US-BOS-MX011.na.pxl.int>

Michael,  

    I've been all over Metalink on the subject, and yes GTT's do generate redo, for the purpose of allowing one to rollback to a savepoint. But then again, all of the complaints, notes, etc... point to versions of Oracle less than 10.2.0.2.0.  

Dick Goulet
Senior Oracle DBA
PAREXEL International  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Rosenblum
Sent: Monday, June 08, 2009 12:08 PM
To: oracle-l_at_freelists.org
Subject: RE: Global temporary tables with No logging

Unfortunately, operations against temporary tables DO have an impact on the volume of generated logs - at least as far as I understand.  

The problem is that temporary tables do generate rollback - and the rollback is logged. So, temporary tables will generate some redo.  

Just to summarize the rule of thumb (quoting Tom Kyte's "Expert Oracle Database Architecture" - p.322 If you don't have it in paper, here is a link:
http://books.google.com/books?id=TmPoYfpeJAUC&printsec=frontcover#PPA322 ,M1 ):

• INSERT - little to no rollback/redo activity
• DELETE - the same amount of redo as normal table
• UPDATE - about half the redo of an UPDATE of a normal table.

Regards,

Michael Rosenblum

Dulcian Inc

---

                From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Goulet, Richard

		Sent: Monday, June 08, 2009 3:10 PM
		To: walid_alkaakati_at_yahoo.com; oracle-l_at_freelists.org
		Subject: RE: Global temporary tables with No logging
		Importance: High
		
		
		If these are global temp tables then their are not the

cause of your increased archive log activity. Global temp tables have a default of the temp tablespace and are never logged. The reason is that once a session using a global temp table ends the table drops all of it's data & storage. Transaction preservation of global temps is per their definition. Refer to :
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statement s_7002.htm#i2095331. It's fro 11g, but still applicable to 10g.                  

                You can specify nologging with the "alter table" command, but that can be overridden if the database has supplemental logging turned on as in there exists a physical standby.                  

                The biggest thing is to check with the application developers of provider. Some apps like PeopleSoft create "temporary" tables that are there to pass data between sessions in a batch job. If that's so, your stuck with them. If not consider rebuilding them as global temps.                  

		Dick Goulet 
		Senior Oracle DBA 
		PARE  XEL International 

		 

________________________________

		From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of walid alkaakati

		Sent: Monday, June 08, 2009 8:15 AM
		To: oracle-l_at_freelists.org
		Subject: Global temporary tables with No logging
		
		




	Hallo list,
	 
	i have an application that uses about 60 Temporary tables, the

problem is that each day we i have more than 500 Mb as an increase in Archive logs.          

        How can i specify No Logging option for these Temporary tables ? when i try i get a syntax error, also i have read in one blog that Global temporary tables do Not Genrate Redo, so can some one please clarify the truth ?                    

        Thank you all !  

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 08 2009 - 12:29:23 CDT