Get nested procedure and sql execution oracle 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?
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