You were brought to this page based on an internet search
and as a free service to Oracle DBAs.
The text below is an except from the book,
Oracle Performance Firefighting, written by
Craig Shallahamer of
OraPub, Inc.
Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click
here.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
PleaseOut of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
-------------------------------
So in many respects, you get the best of both worlds: table-like control, along with automatic temporary segment management.
While the Oracle documentation is not entirely clear, it does seem to indicate global temporary tables do not generate any redo. While my experiments3 showed this to be incorrect, even the worst case scenario achieved a remarkable 43% redo reduction. For example, with a row size of 750 bytes, redo was consistently reduced by 68.1%. And even more important, as the row size increased, so did the redo savings.
If you have the opportunity to design batch processes, do everyone a favor and consider incorporating global temporary tables. And if you are a performance analyst stuck in a corner with an application generating far more redo than your IO subsystem can hope to adequately process, or a developer needing more memory than a single process can allocate, don't forget about global temporary tables.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
PleaseOut of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
|