Performance Issue ....

[BabaAli]

Hi all,
I'm running an application on Oracle 8i Server. Memory is well tunned, but at some areas of the application, it seems to be very slow. I'm doubtful about some procedures, but to make it sure, whats the best way to measure the performance of some procedures (in a application) and what points regarding performance of a procedure should be kept in mind.

Thanx for replying ......

 

[Swany]

The first step is to tune your SQL.

Make sure appropriate hints are passed to the SQL queries, especially if you are dealing with large tables and the default optimizer is set to rule.

You can time your SQL using DBMS_UTILITY.GET_TIME()

declare
  start_time binary_integer;
begin
  start_time = dbms_utility.get_time();
  --code to time here
  dbms_output.put_line('Elapsed time:"'||dbms_utility.get_time() - start_time);
end; .                             
..  Eat, think and be merry .
... ....................... .

 

[uparna]

My guess is that the problem you are facing is due to a very badly tuned query . You may be aware of the basics , but let me tell them to you anyway ,
1) Do you have Indexes that are referenced during the execution of the query ? you can find this out using the execution plans.
2) even if you have indexes for this , i suggest that you rebuild them for maximum benefit.
3)check the number of buffer gets that this query has resulted in . If it is too lare , you may need to rework on the query.
4) is your db_block_bufer size sufficient enough ?.....this is the last approach that you should take , but that is also an option.
Will suggest more if needed.
Love,
Jayaram.

 

[BabaAli]

Thanx buddies,

U both have given me good ideas. Can u explain the utilities or options to measure the performance except explain plan (if there is any). I wanted to know maximumm methods to play with tunning of Queries .....

I really appreciate your help.

Thanx.

 

[Swany]

in sql*plus type
set autot on

this turns on auto tracing

After you run a query, an explain plan will be automatically generated, along with a number of other statistics, including how much data was fetched, the number of buffer gets required, etc.

You can also use the SQL Trace utility to generate full SQL trace files. You then must use TKPROF to convert them to human readable format.

I suggest you read the Oracle Tuning Guide here:

http://technet.oracle.com/doc/server.815/a67775/toc.htm

.
.. Eat, think and be merry .
... ....................... .

 

[MikeJones]

If your looking at doing this alot it might be worth investing in a 3rd party tool, I'm currently getting to grips with the PL/SQL Profiler in TOAD and have found it extremely useful, You can run a top level PL/SQL Block and then see where the execution time was spent, what was executed the most times etc, this can be drilled down to a line of code, very, very helpful.

Of course we pay though the nose for this, but there's always the free month trial,

http://www.quest.com

for more details.

 

[uparna]

I agree with swany. A two pronged approach using both the autotrace and TKP ROF seems to be the best thing tobe done now.

Best of luck !
Keep me posted of the developments .