08 Considerations while using PLSQL in programming (Oracle)


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

Oracle tuning : Issue commits as frequently as possible

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

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s