Home » SQL & PL/SQL » SQL & PL/SQL » How Does Temp Table interact with rollback?
How Does Temp Table interact with rollback? [message #275160] Thu, 18 October 2007 11:09 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Disclaimer: I was not sure were best to post this as it is kind of a SQL and DBMS issue so if someone feels this is better suited to DBMS please feel free to relocate.

For set-up we use several global temporary tables for certain applications were we essentially went to get a whole bunch of data into a manageable format so we can do analysis on it without having to worry about complex underlying queries. The table is defined with the following statement:

CREATE GLOBAL TEMPORARY TABLE APPS.RIDOT_SYSTEM_DETAIL_TMP
(
  EXPENDITURE_ITEM_ID      NUMBER(15),
  CDL_LINE_NUM             NUMBER(15),
  ADL_LINE_NUM             NUMBER(15),
  .
  .
  .
  ITEM_NUMBER              VARCHAR2(40 BYTE),
  ITEM_DESCRIPTION         VARCHAR2(240 BYTE)
)
ON COMMIT DELETE ROWS
NOCACHE;


... is just a few more columns which seemed like extraneous information.

Now the problem, occasionally we will make mistakes when working on the underlying queries which we use to populate this table and get ourselves an incorrectly operating query that goes out of control. When this happens if it a request we cancel the request if it's through toad we kill the session.

The result of either of these actions is that the database starts performing a rollback...a very long rollback...on this table all the while holding onto an exclusive lock on the table and the package we were using meaning we can't do anything to fix our problem without waiting a half hour sometimes for all the undo blocks to be released.

This obviously makes sense when dealing with non temporary tables in the system, but why is the DBMS going through this for a temporary table which is essentially going to disappear when the session finally does end. Is there any way to avoid this?

Thanks for any help...I'll just be hear waiting on another half hour rollback and hoping I plan my alterations a little more carefully next time.

Andrew
Re: How Does Temp Table interact with rollback? [message #275174 is a reply to message #275160] Thu, 18 October 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This makes sense as Oracle let you rollback what you are doing and this rollback must include the data you modified in your temporary table.
For instance:
insert into gtt select something
savepoint A;
update gtt set ...
<Test> if OK go on otherwise rollback to savepoint A and retry

Oracle must use undo segment to be able to allow you to rollback to previous savepoint.

Regards
Michel
Re: How Does Temp Table interact with rollback? [message #275175 is a reply to message #275174] Thu, 18 October 2007 12:32 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I get that and it makes sense for a normal table, but here we are talking about a temporary table which for all intents and purposes-since global temp tables are session specific- will not exist once this session is successfully killed. It's like painting your house so it looks nice for the demolition company...what I was really hoping was that there might be a way to define the temporary table so that Oracle will not use undo when the session is killed?
Re: How Does Temp Table interact with rollback? [message #275180 is a reply to message #275175] Thu, 18 October 2007 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you follox the exemple I posted?
In this one GTT is a temporary table.
You can change the color of your house without rebuilding it.

Your transaction may not include only undo blocks for GTT but for many other tables/indexes you used during the transaction.

Remember if the database crashes, Oracle has to rebuild it as it was at crash time including its internal structures.

Regards
Michel
Re: How Does Temp Table interact with rollback? [message #275183 is a reply to message #275180] Thu, 18 October 2007 12:48 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I think I understand what you are saying, essentially that other stuff could have happened as well and that this would need to be rolled back to preserve database integrity and that makes sense, but here the only thing modified is the temporary table there is nothing else...though I do suppose that that would not strictly speaking be true because we are dealing with a concurrent request here and there is application infrastructure in place whose state also has to be preserved....but the point remains that it seems like a way to say rollback should be ignored for this one object would be extremely useful in this situation.
Re: How Does Temp Table interact with rollback? [message #275186 is a reply to message #275183] Thu, 18 October 2007 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

but here the only thing modified is the temporary table there is nothing else

No, you also modified undo data. As I said you may want to roll back part of your work (even if it is work in GTT) so you must have undo data for this.

There is no way to say you don't want undo data (afaik).

Regards
Michel




Re: How Does Temp Table interact with rollback? [message #275187 is a reply to message #275186] Thu, 18 October 2007 13:07 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Fair enough...thanks for the help Michel
Re: How Does Temp Table interact with rollback? [message #275188 is a reply to message #275186] Thu, 18 October 2007 13:27 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
While going back and forth and after I had been googling a bit on this issue and just as a FYI for anyone interested I found this excerpt that describes in an only somewhat confusing manner exactly what logging is done for temporary vs standard Oracle tables.

Quote:


Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. The Oracle8i/9i Concepts guide puts it like this: "DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated." Even though this statement is accurate, it's pretty confusing. To give you an idea of how the database handles temporary tables, I've elaborated a bit on this statement from the Concepts guide.

Oracle writes data for temporary tables into temporary segments and thus doesn't require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it's not entirely eliminated because Oracle must log the changes made to these rollback segments. This is what Oracle is referring to when the manual states "redo logs for the undo logs are generated." If this is still confusing, let's just say that log generation should be approximately half of the log generation (or less) for permanent tables.



http://www.mbjconsulting.com/oracle_links/Temporary_Tables.htm

[Updated on: Thu, 18 October 2007 13:27]

Report message to a moderator

Previous Topic: Index stats in oracle 8i
Next Topic: reverse engineer
Goto Forum:
  


Current Time: Fri Dec 02 12:00:36 CST 2016

Total time taken to generate the page: 0.05579 seconds