Re: Sqlid details inside Procedure

Hi Krishna,

Get the object_id of the procedure and look at rows in v$sql where the program_id is this.

If you order by elapsed time you can see the SQLs that are most responsible for the execution time.

Don’t forget, if your procedure is doing any processing, that could also take some time. The hierarchical plsql profiler is ideal for checking this out. It takes a little bit of setup (much easier in the latest versions) but will give you some useful information.

Hope this helps,
Andy

On Mon, 7 Feb 2022 at 04:05, Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi Experts ,
>
> Currently there is a situation , were we are looking for alternative to
> get Top expensive sqlid details from execution procedure .
> we tried to trace down(i.e 10046 trace with level 12) the procedure with
> sqlid of it , but we see spike occurring in oem .
> we are trying to get sqlid by dba_hist_active_sess_history ,but we are
> unable to get top sql contributions in procedure since it just shows
> the random sqlid , we are looking to top sql present in procedure .
> Below is output of what we have tried :
> SQL> select count(0), sql_id, CURRENT_OBJ#
> from dba_hist_active_sess_history
> where TOP_LEVEL_SQL_ID='8r602jwaxp6fm'
> and
> sample_time between
> to_date('2022-02-04 11:00:00','yyyy-mm-dd hh24:mi:ss')
> and
> to_date('2022-02-04 12:0:00','yyyy-mm-dd hh24:mi:ss')
> group by sql_id, CURRENT_OBJ#
> order by 1 desc;
>
>
> COUNT(0) SQL_ID CURRENT_OBJ#
> ---------- ------------- ------------
> 112 8r602jwaxp6fm -1
> 60 8r602jwaxp6fm 639493
> 56 c6quqbusshg6a -1
> 54 5nv8stdv9wnxa 639493
> 46 gydh9m5b3jkkm 639493
> 41 gydh9m5b3jkkm -1
> 40 -1
> 39 1dckrmkg3rpqu 3156305
> 36 fu01n34dw54wn -1
> 34 1dckrmkg3rpqu 3357339
> 34 1dckrmkg3rpqu 3221186
> 33 1dckrmkg3rpqu -1
> 31 1dckrmkg3rpqu 3031763
> 29 1dckrmkg3rpqu 2965174
> 28 1dckrmkg3rpqu 2713166
> 28 1dckrmkg3rpqu 3293309
> 28 5nv8stdv9wnxa 642284
> 26 8r602jwaxp6fm 642284
> 26 frq0qj978uqnt -1
> 26 1dckrmkg3rpqu 2895871
> 26 1dckrmkg3rpqu 2895862
> 24 1dckrmkg3rpqu 1830648
> 24 639493
> 22 fu01n34dw54wn 3548953
> 21 363anj011mq2t 639455
> 21 1dckrmkg3rpqu 2521114
> 20 1dckrmkg3rpqu 3422360
> 20 b9p3mvgfu51nr -1
>
> Regards,
> Krishna
>
>

--
http://www.freelists.org/webpage/oracle-l