tm1966_muthu

 Profile Answers by tm1966_muthu 

• Feb 17th, 2006

 

The different aspects of tuning PL/SQL  are

Analyzing program performance.

Tuning access to compiled code.

Tuning access to your data.

Tuning your algorithms.

Major tools are

MONITOR  

SQL_TRACE and TKPROF

EXPLAIN PLAN  

ORADBX 

ANALYZE

UTLBSTAT (begin) and UTLESTAT (end)  

Enterprise Manager/Performance Pack

prafull.vn

 Profile Answers by prafull.vn Questions by prafull.vn

• Mar 18th, 2006

 

Tune your queries .. like use <= and >= instead of between in case of range specification.

Poojitha Somareddy

• May 26th, 2006

 

 

Also the order in which the conditions are given in the 'WHERE' cluase are very important while performing a 'Select' query. The Performance Difference is unnoticed ifother wise the query is run on a Massive Database.

For example for a select statement,

SELECT Emp_id FROM Emp_table WHERE Last_Name = 'Smith' AND Middle_Initial = 'K' AND Gender = 'Female';

The look up for matches in the table is performed by taking the conditions in the WHERE cluase in the reverse order i.e., first all the rows that match the criteria Gender = 'Female' are returned and in these returned rows, the conditon Last_Name = 'Smith' is looked up.

There fore, the order of the conditions in the WHERE clause must be in such a way that the last condition gives minimum collection of potential match rows and the next condition must pass on even little and so on. So, if we fine tune the above query, it should look like,

SELECT Emp_id FROM Emp_table WHERE Gender = 'Female' AND Middle_Initial = 'K' AND Last_Name = 'Smith' ; as Last_Name = 'Smith' would return far more less number of rows than Gender = 'Female' as in the former Select case.

Maria Antony Samy

• Aug 29th, 2006

 

Hi Meenakshi,

It seems that I can get some more detail about TUNING. If I need to tune my query, what are the steps that I need to take....I know nothing about tuning....If you find time, pls give me some idea...

Regards,

maria_antony2000@yahoo.com

ORA_CRAZY

 Profile Answers by ORA_CRAZY 

• Nov 19th, 2009

 

Using Bulk binding concepts, we can tune the PL/SQL job that takes long time due to millions of records.

neerajalatha

 Profile Answers by neerajalatha 

• Jul 5th, 2010

 

Performance tuning can be improved by using FOR ALL and BULK COLLECT clauses in place of FOR LOOP and CURSORS which makes the application faster and better.