Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: January 27, 2017 - 3:10 am UTC

Last updated: January 27, 2017 - 6:59 am UTC

Version: oracle database 11g

Viewed 1000+ times

You Asked

Dear Sir,
Q 1 > I want to know how much time to take complete execution of store procedure either procedure or function with resource consumption.
Q 2 > Can I know execution path of sql statement written in the store procedure?
Q 3 > how we can examine index in used or not for store procedure?
how can i know in oracle 11g database?

and Connor said...

There is nothing that automatically tracks this, but its easy to implement, for example:

SQL> create or replace
  2  procedure MYPROC is
  3    s timestamp := localtimestamp;
  4    x int;
  5  begin
  6    select count(*)
  7    into   x
  8    from   dba_objects;
  9
 10    dbms_output.put_line('elapsed = '||(localtimestamp-s));
 11  end;
 12  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec myproc
elapsed = +000000000 00:00:00.122000000

PL/SQL procedure successfully completed.


Check out https://github.com/OraOpenSource/Logger for an instrumentation layer for your code.

To track SQL statements, and their plans, you can use AWR or examine V$SQLSTATS and/or V$SQLPLAN, but that's a large topic. So much so we've got a whole book on it

http://docs.oracle.com/database/121/TGSQL/toc.htm




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

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