Skip to Main Content
  • Questions

  • how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Veeranjaneyulu.

Asked: August 03, 2016 - 1:16 pm UTC

Last updated: August 04, 2016 - 10:25 am UTC

Version: 11g

Viewed 1000+ times

You Asked

how to find bottleneck sql's in a package in oracle without using dbms_profiler or dbms_hprofiler? is there any alternatives?

and Chris said...

If you're just looking to capture SQL execution you can use TKPROF:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution#tkprof

But if you're profiling the code you really should capture the PL/SQL execution too! The hierarchical profiler is darn good for this, so I'm not sure why you'd want to use anything else ;)

But here's a couple of alternatives:

https://www.allroundautomations.com/plsprofiler.html
http://documents.software.dell.com/sql-navigator-for-oracle/7.0/user-guide/modules-windows-and-dialogs/plsql-profiler/plsql-profiler

I've not used either, so can't comment on how they compare.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alternatives to isolate performance issues in PL/SQL

RG, August 04, 2016 - 1:15 am UTC

Hi ,

You can do several things , top three things ...

1. Search the Asktom site for something like "Instrumenting the code" ..in those lines
2. dbms_session , dbms_application_info and dbms_monitor ..these packages will allow to see exactly what's happening .. you might need dba help ..they will see very good details in EM/Cloud control.
3.Use Method-R open source library for PL/SQL library .. http://method-r.com/software/ilo

The legacy continues ...
Connor McDonald
August 04, 2016 - 10:25 am UTC

Yes, using dbms_application_info to set module/action is very useful. Particularly for the DBAs monitoring your database!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library