• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

PRO*C Tuning Tips

Hi Guys,
Do u know any website on PRO*C tuning tips.If so please pass it on. It will be very useful.
Also websites on Performance Tuning on PRO*C involving
BULK SELECT,UPDATE and INSERT functions.
I can give more points on the question. But points available on my account is very less. Please bear with me and also tell me whether any ways to increase points.
0
jbabu090498
Asked:
jbabu090498
  • 2
1 Solution
 
mouattsCommented:
I don't know any web sites but here are a few points.

Firstly concentrate on the SQL first. Optimise this before you think of doing anything else

Key points to remember are the order of the tables within the from statement (primary table last etc)

The order of the conditions in the where statement, That is to say that the condition that will exclude the most rows should precede those that would exclude fewer.

 Use conditions that can make use of indexes and avoid those that can't for example testing for inequallity will not tend to use an index but testing for equality will.

Lastly avoid the use of functions on the lefthand side of a condition as this also prevents index use.

So far as the PRO*C is concerned there are a number of points.

Where a SQL statement is used a number of times with different bind variables ensure that the text of the statement is identical each time as this prevents reparsing of the statement.

Use the array facilities where possible to retrieve mutliple rows rather than looping. However care needs to be taken if the volume of the data transfer is too big as this will clogg up the SGA and preformance of your program or others can reduce.

If inserting or updating a large number of records do not commit just at the end. Firstly you can use too many resources for the process to complete and secondly the length of the commit can extend.

Lastly if accessing the database via SQL*Net on another machine utilise PL/SQL procedures where possible so that the processing is shared by both boxes.

If think thats your 20 points worth. :)

Steve
0
 
jbabu090498Author Commented:
Please tell me few sites to look into for tuning oracle.

0
 
jbabu090498Author Commented:
What I meant is Tuning Pro*C code in oracle.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now