RE: Sqlid details inside Procedure

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<mailto: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

Important Notice