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 ;)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jknj72Author Commented:
I have set up the Preferences already but if I need to have something explicitly checked please let me know
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.
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jknj72Author Commented:
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.
if you're using the standard profiler then something like this...

    Dbms_Profiler.start_profiler('jknj72');  -- it's not required, but helpful to give your profile runs distinct names

Then you can query the results with something like this...
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,
                ) 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,
                ) total_profiled_time
    FROM plsql_profiler_runs r, plsql_profiler_data d, plsql_profiler_units u
   WHERE r.run_comment = 'jknj72'
     AND d.runid = r.runid
     AND u.runid = d.runid
     AND d.unit_number = u.unit_number
     AND d.total_occur != 0
ORDER BY d.total_time DESC

Open in new window

if you're using the hierarchical profiler then you'll need access to the db server

    DBMS_HPROF.start_profiling (location => 'YOUR_PROFILING_DIRECTORY',  filename => 'jknj72.txt');

Then you can use   plshprof   to generate a report on your output file.

plshprof -output  jknj72.txt

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
jknj72Author Commented:
Thanks stuber!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.