Pl/sql package procedure reivew/tuning

Posted on 2013-05-30
Medium Priority
Last Modified: 2013-06-19
DB: Oracle 11gR2

Hi, need some assistance, how one can review/tune a 500 lines pl/sql package, have already tried DBMS_PROFILER, but not good, also able to tune each and every sql within the package too, all the sql looks good, but when executed within this 500 lines package, taking more time then expected.

Is there any way to review/tune this whole package, what exactly it is doing and where exactly is the bottleneck.

Looking for some hints/tips or even some guidelines should work too.

Thanks in advance.
Question by:Mushfique Khan
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39207594
Unfortunately the best way I've found is just adding some debug statements inside the procedure.  Either with dbms_output or at times I create a timing table and insert rows into it as my code runs.

My timing table looks like: create table test_timing(where_am_i varchar2(30), time_stamp systimestamp);

Then as the code executes I insert into it like:
insert into test_timing('Starting run',systimestamp);

As for tuning the SQL, this is the same way you tune any SQL:  Explain Plan and/or tkprof.

Author Comment

by:Mushfique Khan
ID: 39207633
Hmmm ... means there is no way/technique to review the execution of a pl/sql package in a whole ... ?

Let's wait for some more ... let's see.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 750 total points
ID: 39207666
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 750 total points
ID: 39208807
If you have Diagnostic Pack you can get the most consuming parts from v$active_session_history
SQL_ID for sql statements (statement in V$SQL)

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question