how to determine->improve performance of Oracle SP's

Question

We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long.

Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research.

Currently I am using PL/SQL developer to execute the SP

Answer 1

The explain plan is readily accessible - it provides insight into the efficiency, which will translate into time/resources.

If you want real insight into stored procedures, look at running a trace using tkprof. Here's an article on it.

Answer 2

If you are able to execute the queries then I would suggest getting familiar with the DBMS_PROFLIER functionality. The Oracle PL/SQL Profiler is a tool that can help provide statistics about the run-time behavior of PL/SQL code. The tool gathers information about PL/SQL source code as it executes and stores that information in tables. Once the execution is complete you can report on the stored profiler data to determine:

• The total number of times a line was executed
• The the minimum, maximum, and average execution time of each line of code
• How long SQL statements took to execute to completion
• Code coverage (the actual lines executed during the analysis period)

One this information is summarized for a Profiler run you can determine which lines of source code or SQL statements can benefit the most from tuning.

Oracle documentation on the DBMS_PROFILER package is here for 10g.

Metalink Doc 243755.1 has a script to produce pretty HTML output for your profiling runs.

PROFSUM.SQL is another script that reports on the gathered data, it is available here.

Answer 3

I do a lot of this kind of work and I find that having a good logging tool is the way to go. Invariably the problem will be an one or more SQL DML statements - especially UPDATES and DELETES. If you create a simple logging procedure that writes to a table (using AUTONOMOUS_TRANSACTION) with a timestamp column and wrap calls to this around all SQL you will very quickly identify where your time is going. I have developed a mature and sophisticated package specifically to instrument PL/SQL code like this and for real time monitoring of production code - get back to me if you would like a copy.

Sadly most developers seem to think the DBMS_OUTPUT will solve the problem - can do, but not very efficient ... or pretty.

Other Oracle tracing tools are definitely a help too but a little more difficult to use.

Finally - use Enterprise manager and analyze a snapshot across a period of time. Look at the top 10 SQL queries - and if you like use SQL advisor.

Hope that helps ...