Home » SQL & PL/SQL » SQL & PL/SQL » Commit after DDL (Oracle 9i,solaris)
Commit after DDL [message #352730] Thu, 09 October 2008 04:09 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Hi all,

For every DML statement,commit is issued explicitly in order to make the database changes permanent whereas for DDL statement, it is auto commit.

My question is, Will the database performance degrade if we issue commit after every DDL statement?


Please revert me with the answer.

With Regards,
Balaji
Re: Commit after DDL [message #352732 is a reply to message #352730] Thu, 09 October 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will the database performance degrade if we issue commit after every DDL statement?

Yes. Every commit implies latches, signal, lgwr wake up and so on. Even if there is nothing to do.

Regards
Michel
Re: Commit after DDL [message #352757 is a reply to message #352730] Thu, 09 October 2008 06:36 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@balaji1482,
balaji1482 wrote on Thu, 09 October 2008 14:39

Will the database performance degrade if we issue commit after every DDL statement?


Quote From Oracle Documentation

Oracle Database implicitly commits the current transaction before and after every DDL statement.


Database Concepts

So why is there a need for an explicit COMMIT after a DDL? http://img2.mysmiley.net/imgs/smile/confused/confused0006.gif

Regards,
Jo
Re: Commit after DDL [message #352762 is a reply to message #352757] Thu, 09 October 2008 07:39 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

hi joice,

Actually i was just curious to know the answer for this john Thats it. Smile

[Updated on: Thu, 09 October 2008 07:40]

Report message to a moderator

Re: Commit after DDL [message #352835 is a reply to message #352730] Thu, 09 October 2008 18:36 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
"Yes. Every commit implies latches, signal, lgwr wake up and so on. Even if there is nothing to do."

But its unlikely you will ever to the volumes required for it to be particularly measurable.

How many ddl statements can one process in a single sequence. I hardly think it would be thousands, let alone millions.
Re: Commit after DDL [message #352866 is a reply to message #352835] Thu, 09 October 2008 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why doing more works than necessary?
And it impacts not only your session but ALL sessions. Your commit will slow down all sessions.

Regards
Michel
Re: Commit after DDL [message #352905 is a reply to message #352866] Fri, 10 October 2008 03:17 Go to previous message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Thanks for your information.
Previous Topic: oracle 10g procedure ORA-06550 + PLS-00103
Next Topic: Assistance with retrieving rows limited by the value of specific fields
Goto Forum:
  


Current Time: Thu Dec 08 02:12:36 CST 2016

Total time taken to generate the page: 0.10191 seconds