Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, SS.

Asked: May 02, 2016 - 1:25 pm UTC

Last updated: May 04, 2016 - 11:34 am UTC

Version: 11g2

Viewed 1000+ times

You Asked


I have store procedure which take more time when multiple session access at a time.
so i want to see execution plan of the sql query written inside the store procedure.


How to check index are used or not ?

How to improve performance of the Store Procedure?

I found my procedure in the v$db_object_cache so i want to delete my store procedure from v$db_object_cache table how to possible it.

thnks


and Connor said...

If you query V$SQL, there is a column PROGRAM_ID which maps to the object_id of your procedure from USER_OBJECTS.

So you can use that navigate via V$SQL to V$SQL_PLAN to see the plans.

Or simply, re-run your proc, after running:

alter session set statistics_level = all;
exec dbms_monitor.session_trace_enable(waits=>true)

and consult the trace file generated (or run tkprof on it to format it). You can get the plans, plus also, you'll be able to see why things ran slower with multiple sessions by looking at the wait information in the tkprod file.

Hope this helps.

Rating

  (3 ratings)

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

Comments

tkprof

A reader, May 03, 2016 - 8:27 pm UTC

You can do alter session set sql_trace=true and run the procedure and then do a tkprof output

A reader, May 04, 2016 - 8:25 am UTC

I enable the session autotrace but how to see my trace file.
there are so many trace file.
Connor McDonald
May 04, 2016 - 11:34 am UTC

in your own session, you can run:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
-----------------------------------------------------------------------
C:\ORACLE\diag\rdbms\np12\np12\trace\np12_ora_24696.trc


Hope this helps

A reader, May 04, 2016 - 8:26 am UTC


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 channel and Chris's channel 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