Re: Sqlid details inside Procedure

One related doubt I have, say we have proc_1, called from proc_2, which is called from Proc_3. Now I see in dba_hist_active_sess_history, all the sqls executed inside proc_1 are showing the top_level_sql_id as PROC_3 only. even plsql_entry_object_id and plsql_entry_subprogram_id are pointing to the same Proc_3. Is there any way to track the exact immediate top program/procedure name for the sql_id from ASH(which in this case should be shown as proc_1)?

On Mon, Feb 7, 2022 at 5:24 PM Krishnaprasad Yadav <chrishna0007_at_gmail.com> wrote:

> Hi All,
> Thanks for your reply , will try out the recommendations.
>
> Regards,
> Krishna
>
> On Mon, 7 Feb 2022, 13:56 Noveljic Nenad, <nenad.noveljic_at_vontobel.com>
> wrote:
>
>> Hi Krishna
>>
>>
>>
>> When identifying SQL which takes most of the time in PLSQL, I would leave
>> the column current_obj# out, that is:
>>
>>
>>
>> The problem with including current_obj# is that a query that takes lots
>> of time, but that time is spent on accessing a large number of tables and
>> indexes, wouldn’t show up at the top, like it’s the case with 1dckrmkg3rpqu.
>> 1dckrmkg3rpqu seems to be the query of interest.
>>
>>
>>
>> I would use PLSQL profiler only when there’s a substantial number of
>> samples where sql_id=top_level_sql_id, i.e. the time is spent in PL/SQL
>> code, unlike on SQL.
>>
>>
>>
>> By the way, for the sake of efficiency, I recommend using Tanel Poder’s
>> ashtop and dashtop for aggregations on v$active_session_history and
>> dba_hist_active_sess_history, respectively. Notice that
>> v$active_session_history has higher resolution than
>> dba_hist_active_sess_history.
>>
>>
>>
>> The ashtop equivalent of your query (without current_obj#) is:
>>
>>
>>
>> _at_ashtop sql_id top_level_sql_id='8r602jwaxp6fm' "to_date('2022-02-04
>> 11:00:00','yyyy-mm-dd hh24:mi:ss')" "to_date('2022-02-04
>> 12:0:00','yyyy-mm-dd hh24:mi:ss')"
>>
>>
>>
>> Best regards,
>>
>>
>>
>> Nenad
>>
>>
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>> Behalf Of *Andy Sayer
>> *Sent:* Montag, 7. Februar 2022 08:56
>> *To:* chrishna0007_at_gmail.com
>> *Cc:* Oracle L <oracle-l_at_freelists.org>
>> *Subject:* Re: Sqlid details inside Procedure
>>
>>
>>
>> **** E-Mail from outside Vontobel:* Do not click on links or open
>> attachments unless you know the content is safe. ***
>>
>> 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
>>
>>
>>
>> ____________________________________________________
>>
>> Please consider the environment before printing this e-mail.
>>
>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>
>>
>> Important Notice
>>
>> This message is intended only for the individual named. It may contain
>> confidential or privileged information. If you are not the named addressee
>> you should in particular not disseminate, distribute, modify or copy this
>> e-mail. Please notify the sender immediately by e-mail, if you have
>> received this message by mistake and delete it from your system.
>> Without prejudice to any contractual agreements between you and us which
>> shall prevail in any case, we take it as your authorization to correspond
>> with you by e-mail if you send us messages by e-mail. However, we reserve
>> the right not to execute orders and instructions transmitted by e-mail at
>> any time and without further explanation.
>> E-mail transmission may not be secure or error-free as information could
>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>> processing of incoming e-mails cannot be guaranteed. All liability of
>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>> is excluded. You are advised that urgent and time sensitive messages should
>> not be sent by e-mail and if verification is required please request a
>> printed version.
>> Please note that all e-mail communications to and from the Vontobel Group
>> are subject to electronic storage and review by Vontobel Group. Unless
>> stated to the contrary and without prejudice to any contractual agreements
>> between you and Vontobel Group which shall prevail in any case,
>> e-mail-communication is for informational purposes only and is not intended
>> as an offer or solicitation for the purchase or sale of any financial
>> instrument or as an official confirmation of any transaction.
>> The legal basis for the processing of your personal data is the
>> legitimate interest to develop a commercial relationship with you, as well
>> as your consent to forward you commercial communications. You can exercise,
>> at any time and under the terms established under current regulation, your
>> rights. If you prefer not to receive any further communications, please
>> contact your client relationship manager if you are a client of Vontobel
>> Group or notify the sender. Please note for an exact reference to the
>> affected group entity the corporate e-mail signature. For further
>> information about data privacy at Vontobel Group please consult
>> www.vontobel.com.
>>
>

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