Get nested procedure and sql execution oracle

Posted on

Question :

In my Oracle 11g environment, I got performance issue with a procedure named : myProc().
In myProc() it’s called to others proc named myChildProcA(), myChildProcB(),myChildProcC()……( ~10 child procedures)

In Postgresql, I have some userful tool like pg_stat_user_functions, pg_stat_statements, auto_explain…
it’s provide detail information about child function excutions.
But I don’t known much about Oracle.
I tried with v$sql, sql_trace and tkprof to get statistic information but it’s not provide child procedure information.

My question :
Are there any way or tool to get elapsed executions time for child procedures in Oracle?

Answer :

v$sql gives you the information at a SQL child cursor level and sql_trace gives you the information at a SQL execution level. v$sql also tells you the program_id and the program_line# of the compiled pl/sql code that last hard parsed it.

That said, you are probably looking for the Hierarchical Profiler

Leave a Reply

Your email address will not be published. Required fields are marked *