RE: Global temporary tables with No logging
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: "Michael Rosenblum" <mrosenblum@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Jun 2009 15:24:00 -0400
Michael,
Yes I believe that collections, which is what I believe your talking
about, are allocated from the shared or large pools.
Dick Goulet
Senior Oracle DBA
PAREXEL International
________________________________
From: Michael Rosenblum [mailto:mrosenblum@xxxxxxxxxxx]
Sent: Monday, June 08, 2009 1:41 PM
To: Goulet, Richard; oracle-l@xxxxxxxxxxxxx
Subject: RE: Global temporary tables with No logging
Dick,
We have the same problem locally in 10.2.0.4 (MS Windows 2003 Standard,
32-bit) - at least that was the conclusion after digging into the
LogMiner.
There is a scheduled project to completely get rid of GTTs for that
system. The idea is to convert every GTT to packaged variable (object
collections) with corresponding set of APIs - but I understand that it
would require significantly more memory.
Regards,
Michael Rosenblum
Dulcian Inc
-----Original Message-----
From: Goulet, Richard [mailto:Richard.Goulet@xxxxxxxxxxx]
Sent: Monday, June 08, 2009 1:29 PM
To: Michael Rosenblum; oracle-l@xxxxxxxxxxxxx
Subject: RE: Global temporary tables with No logging
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Rosenblum
Sent: Monday, June 08, 2009 12:08 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Goulet, Richard
Sent: Monday, June 08, 2009 3:10 PM
To: walid_alkaakati@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of walid alkaakati
Sent: Monday, June 08, 2009 8:15 AM
To: oracle-l@xxxxxxxxxxxxx
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 !
Other related posts:
