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?
1 Answer 1
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