Issue frequent commit statements

If you are writing PL/SQL blocks or any other programming blocks to perform DML operations (insert,delete update) issue frequent commits as possible so that

Information held in the roll back segments to UNDO the transactions is freed up

Locks on the table are released

Space on the REDO log buffer cache is freed up

Example

Oracle tuning : Issue commits as frequently as possible

Use Bulk Collect

The BULK Processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL Engine to the SQL Engine

Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch

Example

Oracle SQL Tuning : Use Bulk Collect

First we are creating a TYPE RECORD with two columns and then a type table with the 2 columns. Then collecting information from employees table into the employee collection. By using the BULK COLLECT statement we are ensuring that at least 100 rows are fetched at a time and stored into the collection table. When ever you have to process a set of data use the BULK COLLECT statement

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link if you found this article interesting. (This will help elevate my companies Search engine rankings)