Home » SQL & PL/SQL » SQL & PL/SQL » Avoiding huge rollback (Any)
Avoiding huge rollback [message #434738] Thu, 10 December 2009 08:10 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member
Hi,

My question is.. I am doing a large select insert into a table T1.. To start with T1 does not have any data.. Say, for some reasons, I am not using an append nologging hint.. Meanwhile somebody kills my session... The transaction has to go through a huge rollback!!

Since, T1 had no data to start with, the rollback is least important to me.... Would have been great if there was some kind of interim commit option available with a session kill... I could have atleast truncated T1 to reach to the inital state!! However, this is seriously not possible and is very much against RDBMS principles.. seems there is no escape to this huge rollback..

Is there any technique to reach to the initial point without such a big rollback? ..just curious

Regards,
Prajjwal

Report message to a moderator

Re: Avoiding huge rollback [message #434740 is a reply to message #434738] Thu, 10 December 2009 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Insert the data in smaller chunks and commit after each.
This won't be as efficient as the way you're currently using.

I really wouldn't worry about this, under normal circumstances your session should never be killed and if that is happening on even a semi-regular basis then I'd suggest you've got a much bigger problem to worry about.

Report message to a moderator

Re: Avoiding huge rollback [message #434751 is a reply to message #434738] Thu, 10 December 2009 09:04 Go to previous messageGo to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member
Thanks for responding so quickly....

Actually, I had manually killed the session as it was the need of the situation... so no problem with the database.

Now, the technique you suggested, i mean insertion in chunks is a fair enough method I know and it very well solves the purpose. I should basically put the question some other way...

Suppose, I have a very critical database... Some novice user fires a very big insert.. after say 5 to 6 hours, the instance gets into a very bad shape (i mean all the other users are starting to face problems)... looks like the query will go on for another 10 hours or so... now what should I do as a DBA?

If I kill the session I am gone and if I do not kill I am still gone... Just assuming I do not need any consistent content of table T1.. what is the best option for me?

Regards,
Prajjwal

Report message to a moderator

Re: Avoiding huge rollback [message #434752 is a reply to message #434738] Thu, 10 December 2009 09:18 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
test the novices insert statement on a test db before letting it anywhere near production?

If someone runs a huge insert there is no nice way to kill it.

We have code review procedures and test procedures to try and make sure situations like that never arise.

Report message to a moderator

icon12.gif  Re: Avoiding huge rollback [message #434757 is a reply to message #434738] Thu, 10 December 2009 09:35 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
You could temporarily set the table (and indexes) to 'NOLOGGING' and use the 'APPEND' hint.
Shocked

Report message to a moderator

Re: Avoiding huge rollback [message #434804 is a reply to message #434757] Thu, 10 December 2009 19:49 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You've stumbled on one of the very good reasons why we have DBAs.

The DBA is responsible for EVERY piece of SQL running on the database. You - as the DBA - need to implement policies that stop novices from running SQL that YOU cannot manage.

Report message to a moderator

Previous Topic: help with 54 page pl/sql package
Next Topic: Is there any single line sql syntax to disable all constraint in a table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 13:13:38 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.