Sqlid details inside Procedure
Date: Mon, 7 Feb 2022 09:35:20 +0530
Message-ID: <CAO8FHeUv1tKKds3-OAqWF+OBHJXNVn+s=K7Pdn=AiNj4RTtUFw_at_mail.gmail.com>
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
Received on Mon Feb 07 2022 - 05:05:20 CET