Reduce the number of trips to the Database
Every time SQL is executed, Oracle needs to perform all the internal processing steps, so try to reduce the number of database accesses. This reduces a lot of burden on the database.
Example: If you want to fetch the data of 2 products in the PL/SQL programming language.
BAD
SELECT productname, cost FROM PRODUCT WHERE product_ID=100 SELECT productname, cost FROM PRODUCT WHERE product_ID=200
GOOD
SELECT
MAX(CASE WHEN product_ID =100 THEN productname else NULL END) as productname1,
MAX(CASE WHEN product_ID =100 THEN cost ELSE NULL END) as cost1
FROM PRODUCT,
MAX(CASE WHEN product_ID =200 THEN productname else NULL END) as productname2,
MAX(CASE WHEN product_ID =200 THEN cost ELSE NULL END) as cost2
FROM PRODUCT
WHERE product_ID in (100,200)
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
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
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)
Life Less Ordinary 
Issuing frequents commits is NOT a good idea. It messes with the log writer. Only commit when you have a completed the business transaction.
LikeLike