| PL/SQL commit [message #65652] |
Tue, 23 November 2004 04:25  |
Bharath Kumar ,V
Messages: 18
Registered: February 2004
|
Junior Member |
|
|
Hi,
I've a Procedure to make a data transfer between two tables. with-in this procedure I'm using cursor loop, in this process I did commit for every record successfully transfer. In this regard the performence is very very slow. can any one suggest me instead of commit I can use savepoint? or I make intervel to commit the records.
TIA
Bharath
|
|
|
|
|
|
| Re: PL/SQL commit [message #65655 is a reply to message #65652] |
Tue, 23 November 2004 18:22  |
Dipankar Bhattacharya
Messages: 4
Registered: October 2003
|
Junior Member |
|
|
|
U can commit after every 100-500 records, based on record size. If u do not commit at intervals ur RBS will be affected, causing serious performance issues, also committing after each record in a loop is not recommended .
|
|
|
|
| Re: PL/SQL commit [message #65657 is a reply to message #65655] |
Tue, 23 November 2004 20:08 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
"your RBS will be affected, causing serious performance issues"
This is only true if there are more processes accessing the same data. If there is only 1 process, how does extending Rollbackspace affect performance ??
|
|
|
|
| Re: PL/SQL commit [message #65658 is a reply to message #65657] |
Tue, 23 November 2004 21:34 |
dipankar
Messages: 8
Registered: March 2004
|
Junior Member |
|
|
|
Please clarify if you mean that if rollback segment is getting filled up , and it is not cleared by committing , it won't slow the process , and also if it is saturated, how will further processing take place ? thanx in advance Frank
|
|
|
|
| Re: PL/SQL commit [message #65659 is a reply to message #65655] |
Wed, 24 November 2004 03:15 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
It is a true fact that frequent commit frees the Rollback segments ( undo segment).
But when rollback segments are freed 'pre-maturely' that leads to an ora-1555.
for every commit, more redo information is generated in redo buffer and 'force' cleared and is put in a queue to
be written to an OS file.
Committing based on count of records is Totally flawed ( to some extent in very certain cases,
committing based on TIME is much favoured).
Frequent commit will NOT reduce ANY RESOURCE CONSUMED.
>>>If u do not commit at intervals ur RBS will be affected, causing serious performance issues
The solution here is to size the RBS correctly, not committing frequently.
Please have a look here and see what Thiru says regarding
the process that happens FOR EVERY COMMIT
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
