Avatar of jknj72
jknj72
 asked on

Run Profiler, Execution Plan.....on Package

I want to run a Profile and/or Execution PLan on my Package to see where the most resources are being used when I run my process. I am using SQL Developer and have been reading about different ways to do this but would like to know an easy way to just see what my procedure(in my package) is looking like as far as Table Scans, Reads, etc.. and maybe what I can do to help optimize what I have now before I go and redo this a more efficient way. We will be using this for now and when I have some down time I will come back and rewrite it, using some experts advice, and get it to run more efficiently.
I just need to be able to see how its doing via a Profile and/or an Execution plan now to see what I can do ? Also, if your real good at these types of thing you can tell me what to look for when I actually get the Plan back? Or I can look that part up ;)

Thanks
JK
Oracle DatabaseSQL

Avatar of undefined
Last Comment
jknj72

8/22/2022 - Mon
jknj72

ASKER
I have set up the Preferences already but if I need to have something explicitly checked please let me know
Preferences.jpg
Sean Stuber

Plans are for sql statements - not pl/sql objects
Profiles are for pl/sql objects  - not for sql statements

A plan will show you table and index usage.

A profile will show you which lines of code are executed, how long they took and how often they were run


Using auto trace you can get a summary of ALL session statistics during the run of a procedure but not anything at the individual sub-routines or lines.
Sean Stuber

So,  if you want to do a performance analysis on your pl/sql then I suggest you use the profiler.

When it's done, run a report to see which lines are the most expensive in terms of total time and then, if those lines are sql,  then generate a plan for those lines and see what can be done about them.

There is no direct path from pl/sql to table/index usage that you are looking for.  You'll have to do it in steps.
Your help has saved me hundreds of hours of internet surfing.
fblack61
jknj72

ASKER
ok thanks for the lesson. How can I run the profiler? I see the profiler icon on the package level but not on the Body where the procedures are. DO I turn it on in the package level and run it from there? Also, where would I find the profile info when Im done.
ASKER CERTIFIED SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

alternately,  you can use dbms_hprof to extract the contents of the file into tables similar to the standard profiler.

You can read more about that here

http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_profiler.htm#g3350546
jknj72

ASKER
Thanks stuber!!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.