Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Debugging/Tracing PL/SQL Package

Hi Experts,

We have a very complex and huge PL/SQL pacakge and now it takes about 25 min to execute.

I want to know how to trace this package without using TRACE and TKPROF.  I do not wish to involve DBA's and I have limited privIleges on the schema.

I can create temp tables, but cannot create files ( so is why I dont want TRACE/TKPROF).

I have also explored DBMS_APPLICATION_INFO but does't seem that I can use it here as my package is quite huge.

Any ideas experts?

0
ajexpert
Asked:
ajexpert
  • 9
  • 7
4 Solutions
 
sdstuberCommented:
Easy way to get line-by-line summary  of execution is to use the profiler or in 11g, the hierarchical profiler.


These do require a DBA to install the profiling packages though (if they aren't there already)

To use the profiler once installed....
BEGIN
    Dbms_Profiler.start_profiler('ajtest');
    Your_package.proc1;
    Your_package.proc2;
    dbms_profiler.stop_profiler;
END;

Open in new window

0
 
sdstuberCommented:
since you can't create files, that rules out the hierarchical profiler, but the dbms_profiler should work for you.

If you don't have the package (and tables) already installed, and you don't want to talk to your dba's or they refuse to install it, then you'll have to go with inline instrumentation of your own devising.
0
 
ajexpertAuthor Commented:
We are using 10g R2

I have incorporated your code before calling my package and it kicked off without any complaints.  Seems required setup was already there

Now, my question is, how to use the profiler to see whats causing performance bottleneck in package?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
there is a report script you can run from sql*plus that can be found in your Oracle home.

or, you can run something like this


note, the source extraction is only accurate if the code hasn't change between the time you profiled and run this query.
SELECT   u.unit_type, u.unit_owner, u.unit_name, d.line#, d.total_occur,
         TO_CHAR(d.total_time / 1000000000, '990.999999') total_seconds,
         TO_CHAR(d.min_time / 1000000000, '990.999999') min_seconds,
         TO_CHAR(d.max_time / 1000000000, '990.999999') max_seconds,
         TO_CHAR(d.total_time / 1000000000 / d.total_occur,
                 '990.999999'
                ) avg_seconds,
         (SELECT TRIM(s.text)
            FROM dba_source s
           WHERE u.unit_owner = s.owner
             AND u.unit_type = s.TYPE
             AND u.unit_name = s.NAME
             AND d.line# = s.line) "text (as of now)",
         TO_CHAR(r.run_total_time / 1000000000,
                 '9990.999'
                ) total_profiled_time
    FROM plsql_profiler_runs r, plsql_profiler_data d, plsql_profiler_units u
   WHERE r.run_comment = 'ajtest'
     AND d.runid = r.runid
     AND u.runid = d.runid
     AND d.unit_number = u.unit_number
     AND d.total_occur != 0

Open in new window

0
 
sdstuberCommented:
each time you run the profiler   change the

 'ajtest'

string to give the profile a unique name
0
 
ajexpertAuthor Commented:
Thanks Sean.  I am waiting for my package to complete execution.  I will be positing updates in short while
0
 
ajexpertAuthor Commented:
I got the first output.  Thanks Sean

I need your help in understanding which of following, I should concentrate more on to find out the statement taking time?

TOTAL_OCCUR
MIN_SECONDS,
MAX_SECONDS,
AVG_SECONDS

0
 
sdstuberCommented:
only you can really say for sure, but there are 3 cases to look for.

Do you have something running 10000000 times but each time running quickly?
So total time for that step is big?  if so, you probably can't tune the individual run times but can you eliminate some executions?

conversely do you ahve something running a few times but each time running long?  If so, this is a good candidate for tuning.


last, do you have something with large total run time and with large dispartities between min/max/avg?  If so,  look to see why the code fluctuates so much.  is there something you can do to eliminate the max end, or tune the max end so it runs more like the min?
0
 
sdstuberCommented:
if there is something specific you find you'd like help tuning, open a new question for that and I'll see what I can do.
0
 
ajexpertAuthor Commented:
Thanks, I am looking at it.

Can you please tell me, which of the following columns are most significant to start with?
TOTAL_OCCUR
MIN_SECONDS,
MAX_SECONDS,
AVG_SECONDS

0
 
sdstuberCommented:
all, none, some

it depends.  Look at all 3 cases I described above.

first case.  Total_occur

second case,  total_seconds (you didn't list that one)

third case,  min, max and avg

0
 
sdstuberCommented:
total_seconds is the main driver for all 3.


if something runs 1000000 times but only consumes 10 seconds out of 25 minutes  then who cares?  move on to something else that is consuming a lot of time.


"A LOT"  is relatvive.  If the most time-consuming piece of your code only consumes 30 seconds, then that's your worse case and you need to look at that.
And you'll likely need to look at a lot of others since the execution is spread around

if the most time-consuming piece of your code consumes 24 minutes out of 25 then you've found your problem area,  eliminate/reduce/tune that and you're likely done.

0
 
ajexpertAuthor Commented:
You rock Sean!!

I got lot of exiting code things which were not under scanner but with your approach of PL/SQL Debugging with DBA assitance, it helped me a lot.

Before I close the question, what if I change the code, run it again but do not change the profile name? What are possible impacts?
0
 
ajexpertAuthor Commented:
Read it as

I got lot of *existing* code
0
 
sdstuberCommented:
if you don't give the run a unique name then you'll have duplicate data for the same run id which will skew your results.
especially if you ran, altered the code and ran again.

for instance,  line 10 in run 1 might be line 12 in run 2.  The counts and timings for line 10 will be added to the counts and timings for the new line 10 which has nothing to do with the previous one.

Even if you don't change the code, all your results will still be inflated because you're counting the same things multiple times.


but, there is an easy fix.

there is actually a unique runid generated,  the query I sent assumes your names are unique, simply because names are easier to remember than specific run numbers.

look in PLSQL_PROFILER_RUNS and alter the query to use the real id you're interested in instead of looking up by name.
0
 
ajexpertAuthor Commented:
Awesome Sean!!

I wish I could grant 1000 points :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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